Shulou(Shulou.com)05/31 Report--

This article shows you what the MySQl database catalog is, which is concise and easy to understand, which will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

1. You can use the mysqladmin variable to get the data directory path name directly from the server. In UNIX, the output looks similar to the following:

$mysqladmin variables

+-

| | Variable_name | Value |

+-

| | ansi_mode | OFF |

| | back_log | 50 |

| | basedir | / var/local |

| | connect_timeout | 5 |

| | concurrent_insert | ON |

| | datadir | / usr/local/var |

The output indicates the location of the data directory on the server host / usr/local/var.

In Windows, the output looks similar to the following:

C:mysqlin > mysqladmin variables

+-

| | Variable_name | Value |

+-

| | ansi_mode | OFF |

| | back_log | 50 |

| | basedir | c:mysql |

| | connect_timeout | 5 |

| | concurrent_insert | ON |

If multiple servers are running, they will listen for different TCP/IP port numbers and sockets. You can connect to the ports and sockets that each server listens on by providing the appropriate-- port or-- socket options:

$mysqladmin-port=port=port_num variables

$mysqladmin-socket=/path/to/socket variables

The mysqladmin command can be run on any host where you connect to the server. If you need to connect to a server on a remote host, use the-- host = host_name option:

$mysqladmin-host=host_name varibles

2. On the Unix platform, you can use ps to view any command line that is currently executing the mysql process. Try the following commands (depending on the version of ps supported by your system) and look for the-- datadir of the commands displayed in the output:

$ps au | grep mysqld

The ps command is particularly useful if the system is running multiple servers (because more than one data directory location is found at a time). Its disadvantage is that ps must run on the server's host, and unless the-- datadir option is explicitly specified on the mysqld command line, it will produce useless information.

3. If MySQL is installed from a source program distribution, you can check its configuration information to determine the location of the data directory. For example, this location is available in the most advanced Makefile. Be careful, however: the position is the value of the variable localstatedir in Makefile, not the value of datadir. Similarly, if the distribution is located in the NFS assembly file system and is used to establish MySQL for several hosts, the configuration information reflects the host on which the distribution was recently established. It may not display the data directory of the host you are interested in.

4. If any of the previous methods are unsuccessful, you can use find to search for database files. The following command searches for the .frm (description) file, which is part of the MySQL installer:

$find /-name "* .frm"-print

Searching on the platform is very simple, and no examples are given in this section.

In the examples in this chapter, the author represents the location of the MySQL data directory as DATADIR. You can interpret it as the location of the data directory on your own machine.

Representation of

Each database managed by MySQL has its own database directory, which is a subdirectory of the data directory and has the same name as the represented database. For example, the database my_db corresponds to the database directory DATADIR/my_db.

This notation makes it very easy to implement several database-level statements. CREATE DATABASE db_name uses ownership and methods that only allow access to MySQL server users (UNIX users running by the server) and creates an empty directory db_name in the data directory. This is equivalent to manually creating the database as a server user in the server host by executing the following command:

$mkdir DATADIR/db_name create database directory

$chmod 700DATADIR/db_name makes it accessible only to MySQL server users

The method of representing a new database through an empty directory is completely different from other database systems, which even create many control or system files for the "empty" database.

The DROP DATABASE statement is also easy to implement. DROP DATABASE db_name deletes the db_name directory and all table files in the data directory. This statement is similar to the following command:

$rm-rf DATADIR/db_name

The difference is that the server only deletes files with table extensions. If other files have been created in the database directory, the server keeps them intact and does not delete the directory itself.

SHOW DATABASE is just a list of subdirectory names located in the data directory. Some database systems need to keep a master table that lists all the databases that need to be maintained, but there is no such structure in MySQL. Due to the simplicity of the data directory structure, the list of the database is implicit in the contents of the data directory, and tables such as the main table may cause unnecessary overhead.

Representation of database tables

Each table in the database exists as three files in the database directory: a format (description) file, a data file, and an index file. The base name of each file is the table name, and the extension indicates the type of file. The extension is shown in Table 5-1. The extension of the data and index file indicates whether the table uses an older ISAM index or a newer MyISAM index.

When a CREATE TABLE tbl_name statement that defines a table structure is published, the server creates an tbl_name.frm file that contains the internal coding of the structure. The statement also creates empty data files and index files whose initial information indicates that there are no records and indexes (if the CREATE TABLE statement contains index descriptions, the index file will reflect those indexes). The ownership and manner of the file that describes the table is set to allow access only to MySQL server users.

When the ALTER TABLE statement is issued, the server re-encodes the tbl_name.frm and modifies the contents of the data file and index file to reflect the structural changes indicated by the statement. The same is true for CREATE and DROP INDEX, because the server considers them to be equivalent to ALTER TABLE statements. DROP TABLE deletes the three files that represent the table.

What are the contents of the MySQl database catalog? have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.