MySQL DBA system learning (data files for MySQL)

Data files for MySQL

First, the system parameter DataDir

In MySQL, each database will have a folder named after the database in the defined (or default) data directory, which is used to store various table data files in the database DataDir specified directory is the parent directory of the database directory, The general creation of the database will create two database MySQL and test, if you create a database will be a corresponding database folder

Mysql> Show variables like "%datadir%";

+---------------+-----------------------+

| variable_name | Value |

+---------------+-----------------------+

| DataDir | /usr/local/mysql/var/|

+---------------+-----------------------

mysql> system ls/usr/local/mysql/var/

Ibdata1 ib_logfile1 Test Test4.pid test4.wolf.org.pid

IB_LOGFILE0 MySQL Test4.log test4.wolf.org.err

mysql> CREATE DATABASE Chen;

Query OK, 1 row affected (0.03 sec)

mysql> system ls/usr/local/mysql/var/

Chen Ib_logfile0 MySQL test4.logtest4.wolf.org.err

Ibdata1 ib_logfile1 Test Test4.pidtest4.wolf.org.pid

Second, the data file of the MyISAM engine

Let's try to create a table in the Chen database to see what happens!!

Mysql> use Chen;

Database changed

Mysql> CREATE TABLE TT (ID int, name varchar) Engine=myisam;

Query OK, 0 rows affected (0.15 sec)

Results generate the following file

[Root@test4 ~]# cd/usr/local/mysql/var/chen/

[Root@test4 chen]# Ll-h

Total 20K

-RW-RW----. 1 MySQL MySQL 4 20:04 db.opt

-RW-RW----. 1 mysql mysql 8.4K Sep 4 20:22 tt.frm

-RW-RW----. 1 mysql MySQL 0 Sep 4 20:22 TT. MyD

-RW-RW----. 1 mysql mysql 1.0K Sep 4 20:22 TT. Myi

The. frm file is stored in the ". frm" file with table-related metadata (meta) information, including the definition information for the table structure. Regardless of the storage engine, each table has a ". frm" file named after the table name. All the ". frm" files are stored under the folder of the owning database. And the size doesn't change.

“. MyD "File". The myd "file is a MyISAM storage engine dedicated to storing the MyISAM table data. Every MyISAM table will have one ". The MyD file corresponds to the same folder as the owning database, together with the. frm file.

“. Myi "File". Myi "files are also specifically owned by the MyISAM storage engine, which mainly holds index-related information for MyISAM tables. For MyISAM storage, the content that can be cache is mainly from ". Myi "file. Each MyISAM table corresponds to one ". Myi "file, stored in location and". frm "and". MyD "the same.