The organization structure of data directory in MySQL

In the final analysis, MySQL is running on the operating system of a software, it needs to use the file system to store data, this article mainly introduces the MySQL data directory organization structure.

Location of the Data directory

You can view the location of the MySQL data directory in the following ways:

1 for a running MySQL server, you can get the location of the MySQL data directory by looking at the mysqld process, as follows:

[Root@lx202/data/mysql/data]# ps-ef|grep mysqld  
root      5841  7337 0 15:09 pts/1 00:00:00    grep-- Color=auto mysqld  
Root     27034     1  0  ?        00:00:00/bin/sh/opt/mysql/bin/mysqld_safe--

datadir=/data/mysql/data--pid-file=/data/mysql/data/lx202.pid  
MySQL    27889 27034  ?        111-03:03:55/opt/mysql/bin/mysqld--basedir=/opt/mysql--

datadir=/data/mysql/data--plugin-dir=/opt/mysql/ Lib/mysql/plugin--user=mysql--log-

error=/data/mysql/log/alert.log--open-files-limit=65535--pid-file=/ Data/mysql/data/lx202.pid--

Socket=/opt/mysql/run/mysql.sock--port=3306

The--datadir specifies the location of the data directory.

2 View the MY.CNF configuration information:

[Mysqld]

DataDir =/data/mysql/data

3 Login database, run show variables command view:

Mysql> Show variables like ' DataDir ';  
+---------------+-------------------+  
| variable_name | Value             |  
+---------------+-------------------+  
| datadir |       /data/mysql/data/   
| +---------------+-------------------+  
1 row in Set (0.00 sec)

How the database is represented in the file system

In MySQL, each database corresponds to a directory in a data directory, and when you create a database with the build db_name statement, a subdirectory with the same name is created under the Data directory, in which There is a db.opt file that records the default character set and sorting methods for the database, as follows:

[Root@lx202/data/mysql/data/portal]# cat db.opt  
Default-character-set=utf8  
default-collation=utf8_ General_ci

How the table is represented in the file system

Because MySQL supports multiple storage engines, the presentation of a table on a file system differs depending on the storage engine. But regardless of which storage engine, each table corresponds to at least one. frm file with the same name, which describes the table structure information. In addition to the. frm file, the files that each storage engine uses to store data vary, and here are some common storage engines:

MyISAM

-table_name. MyD: Data files

-table_name. Myi: Index File

MERGE

-table_name. MRG: Text File