Understanding MySQL 8 Directory Structure and Table Storage

1. MySQL 8 Main Directory Structure

After installing MySQL 8, you can inspect the directory layout.

# find / -name mysql

MySQL database files are stored in /var/lib/mysql/

mysql> show variables like 'datadir';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.02 sec)

The result shows that on this machine the MySQL data directory is /var/lib/mysql/ .

1.1 Related Command Directories

Command binaries are located in /usr/bin (e.g., mysqladmin, mysqlbinlog, mysqldump) and /usr/sbin .

1.2 Configuration File Directories

Configuration files are under /usr/share/mysql-8.0 (commands and defaults) and /etc/mysql (e.g., my.cnf).

# cd /usr/share/mysql-8.0/

2. Relationship Between Database and File System

2.1 View Default Databases

List existing databases:

mysql> SHOW DATABASES;

Four system databases are present:

mysql – stores user accounts, privileges, stored procedures, events, logs, help, time‑zone data, etc.

information_schema – contains metadata about all other databases (tables, views, triggers, columns, indexes).

performance_schema – records runtime performance metrics for monitoring.

sys – provides convenient views that combine information_schema and performance_schema for administrators.

2.2 Database Representation in the File System

Contents of /var/lib/mysql on this machine:

# cd /var/lib/mysql
# ll
总用量 188876
-rw-r-----. 1 mysql mysql       56 2月   9 17:33 auto.cnf
-rw-r-----. 1 mysql mysql     1720 2月   9 20:35 binlog.000001
-rw-r-----. 1 mysql mysql       16 2月   9 17:37 binlog.index
-... (other files omitted for brevity) ...

Each non‑system database has its own subdirectory under the data directory. For example, the temp database appears as a subdirectory.

In MySQL 8.0, the db.opt file is no longer provided.

3. Table Representation in the File System

3.1 InnoDB Storage Engine

1. Table Structure

InnoDB creates a .frm file in the database’s subdirectory to describe the table structure. table_name.frm Example: creating a table test in database rainbowsea:

mysql> USE rainbowsea;
Database changed
mysql> CREATE TABLE test (
  c1 INT
);
Query OK, 0 rows affected (0.03 sec)

This creates rainbowsea/test.frm.

2. Data and Index Storage

System tablespace – a single file ibdata1 (default 12 MB, auto‑extendable) stores data and indexes for all tables when innodb_file_per_table is disabled.

Independent tablespace (file‑per‑table) – each table gets its own .ibd file storing both data and indexes.

# my.cnf configuration example
[server]
innodb_data_file_path=data1:512M;data2:512M:autoextend

When innodb_file_per_table=1 (default), each table creates table_name.ibd alongside its .frm file:

test.frm
test.ibd

Switching to system tablespace can be done by setting innodb_file_per_table=0 in the server configuration:

[server]
innodb_file_per_table=0  # 0 = system tablespace, 1 = file‑per‑table

Current setting:

mysql> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.01 sec)

Other tablespace types – general tablespace, temporary tablespace, etc.

3.2 MyISAM Storage Engine

Table Structure – also stored as .frm files. table_name.frm Data and Index – data is stored in .MYD files, indexes in .MYI files, separate from the structure file.

Example creating a MyISAM table:

CREATE TABLE `student_myisam` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(64) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `sex` varchar(2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MYISAM AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb3;

Resulting files in the database directory:

student_myisam.frm   // table definition
student_myisam.MYD   // data
student_myisam.MYI   // indexes

4. Summary

InnoDB table b creates b.frm (structure) and, depending on tablespace mode, either stores data/index in ibdata1 (system tablespace) or in b.ibd (file‑per‑table). MyISAM table b creates three files: b.frm , b.MYD (data), and b.MYI (indexes). In MySQL 8.0 the structure file is b.xxx.sdi instead of b.frm .

5. Final Note

For more details, refer to the original article at https://www.cnblogs.com/TheMagicalRainbowSea/p/18729003 . © Original author.