Understanding MySQL 8 Directory Structure and Table Storage

1. MySQL 8 Main Directory Structure

After installing MySQL 8, the directory layout can be inspected with:

# 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)

Result shows the data directory is /var/lib/mysql/ .

1.1 Related command directories

Command binaries such as mysqladmin, mysqlbinlog, mysqldump reside in /usr/bin and /usr/sbin.

1.2 Configuration file directories

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

# cd /usr/share/mysql-8.0/

2. Relationship Between Databases and the File System

2.1 View default databases

List the databases present on the server:

SHOW DATABASES;

The system provides four built‑in databases:

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

information_schema – contains metadata about all other databases (tables, views, triggers, columns, indexes). Tables prefixed with innodb_sys expose internal InnoDB structures.

performance_schema – records runtime performance metrics, useful for monitoring.

sys – provides convenient views that combine information_schema and performance_schema data.

2.2 Database representation in the file system

Examining /var/lib/mysql shows many files and sub‑directories. Apart from the virtual information_schema, each database has its own directory.

For example, creating a table test in database rainbowsea creates a test.frm file that describes the table structure. The .frm file is binary and not human‑readable.

2.3 Table representation in the file system

InnoDB storage engine

InnoDB stores table metadata in a .frm file and data/indexes in either the shared system tablespace ( ibdata1) or a per‑table tablespace ( *.ibd).

# Example: CREATE TABLE test (c1 INT);
-- In the database directory a file test.frm is created.
-- If file‑per‑table is enabled, a file test.ibd is also created to hold data and indexes.

The system tablespace file ibdata1 is auto‑extending and defaults to 12 MiB; its size grows as needed.

[server]
innodb_data_file_path=data1:512M;data2:512M:autoextend

The innodb_file_per_table variable controls whether new tables use a separate .ibd file (value 1) or the shared system tablespace (value 0).

SHOW VARIABLES LIKE 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.01 sec)

MyISAM storage engine

MyISAM stores the table structure in *.frm, data in *.MYD, and indexes in *.MYI files.

CREATE TABLE student_myisam (
  id BIGINT NOT NULL AUTO_INCREMENT,
  name VARCHAR(64),
  age INT,
  sex VARCHAR(2),
  PRIMARY KEY (id)
) ENGINE=MYISAM DEFAULT CHARSET=utf8mb3;

This creates three files in the database directory: student_myisam.frm, student_myisam.MYD, and student_myisam.MYI.

4. Summary

In MySQL 5.7 a database directory contains a db.opt file with character set and collation settings; this file is no longer created in MySQL 8.0. In MySQL 8.0 the .frm file is merged into the .ibd file for InnoDB tables, while MyISAM tables use .sdi, .MYD, and .MYI files.