Tables in MySQL Directory Structure

🌐 中文

English

This article aims to introduce the directory structure related to tables in MySQL on Linux.

Directory Where MySQL Stores Tables

The directory structure for storing data in the database is as follows:

1
cd /var/lib/mysql

Select a database folder to enter and view the table files within it.

Table Files for Different Engines

In the InnoDB engine,

1. In MySQL 5.7, table files have the suffix .ibd, and table data files have the suffix .frm.
2. In MySQL 8.0, table files have the suffix .ibd, and table data is also written into them. You can use the ibd2sdi command to convert .ibd files to .txt files for viewing.

   1
   ibd2sdi --dump-file=table.txt table.ibd
   

   In the MyISAM engine,

3. The files storing table data are .MYD (data) and .MYI (index).
4. The file storing table structure is .frm.

Independent Tablespace and System Tablespace

1. Independent tablespace (after MySQL 5.66): Tablespace files and table structure files are stored separately in the database folder.
2. System tablespace (before MySQL 5.66): Tablespace files and table structure files are stored together in a single file under /var/lib/mysql. (You can add the innodb_file_per_table parameter under the [server] section in the my.cnf file and assign the corresponding value: 0 represents using the system tablespace; 1 represents using the independent tablespace.)

Views

A view is a virtual table that does not store data, so it only generates a .frm file.

Summary

1. The suffix of table files depends on the storage engine type.
2. The difference between independent tablespace and system tablespace lies in the storage location of tablespace files and table structure files.
3. A view is a virtual table that does not store data, so it only generates a .frm file.