MySQL data files

 

The MySQL database stores the data in disks as other databases.  The default datastore will be determined by datadir variable

>show variables like "%datadir%";

For example my output is /var/lib/mysql/

Check the directories in

mysql> system ls -ld /var/lib/mysql/*/

drwx------. 2 mysql mysql 4096 Mar 21 22:09 /var/lib/mysql/mydatabase/

drwx--x--x. 2 mysql mysql 4096 Dec 29 10:33 /var/lib/mysql/mysql/

drwx------. 2 mysql mysql 4096 Dec 29 10:33 /var/lib/mysql/performance_schema/

drwx------. 2 mysql mysql 4096 Jan  1 21:08 /var/lib/mysql/test/

at the moment we got 5 databases on my databases(note file information_schema does not have a real data file storage)

mysql> show databases;

| information_schema      |

| mydatabase                    |

| mysql                             |

| performance_schema    |

| test                                 |

data file in  MyISAM

When I create a table using MyISAM engine.

mysql> create table mytable(id int ,name varchar(20)) engine=myisam;

Query OK, 0 rows affected (0.18 sec)

You will see there are three files related with the table

-rw-rw----. 1 mysql mysql 8586 Mar 25 14:38 mytable.frm

-rw-rw----. 1 mysql mysql    0 Mar 25 14:38 mytable.MYD

-rw-rw----. 1 mysql mysql 1024 Mar 25 14:38 mytable.MYI

• *.frm file contains the table related meta files including the table’s definition information
• *.MYD file contains the data for the table.
• *.MYI file contains the index information for the table.

data file in innodb

When we create a table using innodb engine, there is a option called innodb_file_per_table which will decide if every table has single files or shared file. Please use the command

> show variables like ‘%per_table%’;

To verify the setting

To change the settings,  innodb_file_per_table=0 should be in my.cnf’s [mysqld] section then restart the database.

• innodb_file_per_table – on : each table will create a single innodb file for individual table
• innodb_file_per_table – off: tables will share the innodb files

when innodb_file_per_table is on

mysql> create table intable(id int ,name varchar(20)) engine=innodb;

Query OK, 0 rows affected (0.26 sec)

You may see two files for the table

-rw-rw----. 1 mysql mysql  8586 Mar 25 14:43 intable.frm (the meta file)

-rw-rw----. 1 mysql mysql 98304 Mar 25 14:43 intable.ibd (the data file)

when innodb_file_per_table is off.

All the table will share the same file for example ibdata1(datafile) and ib_logfile0 (logfile) but you should still be able to see the meta file

mysql> create table intablesinglefile1(id int ,name varchar(20)) engine=innodb;

Query OK, 0 rows affected (0.19 sec)

mysql> create table intablesinglefile2(id int ,name varchar(20)) engine=innodb;

Query OK, 0 rows affected (0.08 sec)

-rw-rw----. 1 mysql mysql  8586 Mar 25 15:04 intablesinglefile1.frm

-rw-rw----. 1 mysql mysql  8586 Mar 25 15:04 intablesinglefile2.frm