Quick Look – MySQL Data Directory Files

Do you know what lies inside your MySQL Data Directory? This article is a quick guide about the files stored inside MySQL data directory.

Data managed by the MySQL server is stored within a directory known as the data directory. This directory is crucial for the functioning of MySQL databases as that’s where MySQL stores all the data. We will note various file types and their purposes. Understanding them is not only essential for MySQL administrators but also for developers and system administrators who accesses the file-system.

We have this decade old article listing the different file types in MySQL 5.7 or earlier. This blog is an update to it which includes details data directory files in MySQL 8.0.

MySQL Data Directory files

So, I just ran a “ls -lhtr” on MySQL 8 data directory. There are different files under the datadir, namely: .cnf, .pem, .index, mysql-bin.XxX, relay-bin.XxX, .pid, .sock, .sock.lock, .dblwr, undo_XxX, #ib_redoXxX_tmp, temp_10.ibt, ibdata1 and also includes multiple folders. Let’s have a quick look.

File types in MySQL Data Directory

Let us go through the files and understand what they are in short.

ibdata1 file

This ibdata1 file inside your MySQL data directory could be the largest and really important one. The ibdata1 is a single system tablespace data file (by default) created under data directory, there could be more of them. The system tablespace is the storage area for the change buffer. It may also contain table and index data if tables are created with file-per-table OFF.
In versions before MySQL 8, the system tablespace also contained the InnoDB data dictionary, doublewrite buffer storage area.

.ibd file

An IBD file is a MySQL table created by the InnoDB database engine. It contains a table-specific tablespace and index data. IBD files are created when MySQL’s innodb_file_per_table option is enabled, which it is by default.

mysqld-auto.cnf file

The mysqld-auto.cnf file in the data directory. This JSON-format file contains persisted system variable settings. It is created by the server upon execution of SET PERSIST or SET PERSIST_ONLY statements.

{"Version": 2, "mysql_dynamic_parse_early_variables": {"max_connections": {"Value": "100", "Metadata": {"Host": "localhost", "User": "root", "Timestamp": 1691930984202384}}}}

auto.cnf file

The auto.cnf file in mysql datadir contains a unique uuid for the server

[auto]
server-uuid=be590f98-0f70-11ee-9018-12d1544fa11f

.pem files

There are multiple pem files under mysql data directory namely:

ca-key.pem
ca.pem
server-key.pem
server-cert.pem
client-key.pem
client-cert.pem
private_key.pem
public_key.pem

MySQL provides these, the SSL certificate and key files and RSA key-pair files, to support encrypted connections using SSL and secure password exchange using RSA over unencrypted connections. They are auto-generated during the server initialisation.

.index file, mysql-bin.XxX, relay-bin.XxX files

These files are related to binary log and replication. The binlog.index and relay-bin.index file stores the active/ present binary and relay log names respectively.
The mysql-bin.XxX are mysql binary logs storing the DMLs changes which can be used for replication and point in time recovery purposes.
The relay-bin.XxX are relay logs created by MySQL’s IO thread by fetching the binary logs from source server and putting them in a relay log for the SQL thread to execute them.

master.info / relay-log.info files

These files are used to store the MySQL replication metadata information. The replica records metadata about the source, consisting of status and connection information as per the configuration of master_info_repository configuration option. That said, from MySQL 8.0.23 this is deprecated and the metadata is stored in mysql.slave_master_info table.

ib_XxX_X.dblwr files

The .dblwr files are doublewrite buffer. The naming convention is, #ib__.dblwr. These files holds the pages from InnoDB buffer pool before InnoDB writes them to their proper InnoDB data files.

innodb_redo, ib_logfile0, ib_logfile1, #ib_redoX, #ib_redoX_tmp files

The objects named either of above under data directory are related to InnoDB redo logs. The redo log is a disk-based data structure used during crash recovery to correct data written by incomplete transactions.

Prior to MySQL 8.0.30, InnoDB creates two redo log files in the data directory by default, named ib_logfile0 and ib_logfile1

From MySQL 8.0.30, the redo logs are auto configured using innodb_redo_log_capacity variable.
Redo log files use an #ib_redoN naming convention, where N is the redo log file number.
The redo log files denoted by a _tmp suffix are spare redo logs and they’re not yet used.

.sock file

The sock file is the unix socket file that allow client-server communication on the same machine using a more efficient inter-process communication mechanism than the network sockets.

.pid file

The .pid file, short for process ID file, contains the process ID (PID) of a running process. It is important for the mysqld process handling like stop / start.

ibtmp1 file

ibtmp1 is a global temporary table space. It stores rollback segments for changes made to user-created temporary tables. There can be more than one such files just like ibdata1.

innodb_temp

temp_X.ibt files

The innodb_temp directory stores the session temporary tablespaces store user-created temporary tables and internal temporary tables created by the optimizer. This is specifically true when internal_tmp_disk_storage_engine is configured as InnoDB.

undo_XxX files

The undo_XxX files are the undo tablespaces containing undo logs. They’re collections of records containing information about how to undo the latest change by a transaction to a clustered index record.

.ibu files

These are additional undo tablespaces that can be manually created using innodb_undo_tablespaces (< 8.0.14) or CREATE UNDO TABLESPACE (>= 8.0.14) command to help preventing undo_XxX table spaces grow much larger.

.sdi files

SDI stands for Serialized Dictionary Information. It is a metadata about database objects in JSON format. Serialized dictionary information (SDI) is present in all InnoDB tablespace files except for temporary tablespace and undo tablespace files.

.csv and .csm files

MySQL supports CSV storage engine, which creates two files. The data is stored int .csv while .csm file is created to store the corresponding metadata about the state of the table and the number of rows.

MYD and MYI files

The Aria table information is stored in these two files: .MAI file contains the table information while the .MAD file contains the data.

table#p#pX.ibd files

You might see a lot of tables with #p# followed by pN (number). These are the partitions of a MySQL InnoDB partition table.

(Note that following file-types are no longer present in MySQL 8, they have been discarded and integrated into data dictionary tables.)

References

https://dev.mysql.com/doc/refman/8.0/en/data-dictionary-schema.html
https://datacharmer.blogspot.com/2016/09/showing-hidden-tables-in-mysql-8-data.html
https://lefred.be/content/mysql-8-0-data-dictionary-tables-and-why-they-should-stay-protected/

Directories within MySQL Data Directory

The directory / folder inside mysql data directory mostly represents the databases.

mysql, performance_schema, sys are system databases.

#innodb_temp and #innodb_redo are related to the temporary files and redo log process files.

File types removed in MySQL 8.0

The following metadata files have been eliminated from MySQL 8. With few exceptions, information that was formerly stored in these metadata files is now stored in data dictionary tables.

.frm files: The .frm file stores table metadata specifically mysql table definition. In older MySQL versions (< MySQL 8.0), there used to me individual .frm files for each table under database directory. Though In MySQL 8.0 this has been removed.

.par files: Partition definition files. InnoDB stopped using partition definition files in MySQL 5.7 with the introduction of native partitioning support for InnoDB tables.

.TRN files: Trigger namespace files.

.TRG files: Trigger parameter files.

.isl files: InnoDB Symbolic Link files containing the location of file-per-table tablespace files created outside of the data directory.

db.opt files: Database configuration files. These files, one per database directory, contained database default character set attributes.

ddl_log.log file: The file contained records of metadata operations generated by data definition statements such as DROP TABLE and ALTER TABLE.

Questions?

Now, go ahead and fire an ls -l on your MySQL data directory and let me know what have I missed?

Conclusion

It is fun to explore the storage level to understand how MySQL is handling the data it is being provided. Each file inside the MySQL data directory serves a distinct purpose, contributing to tasks ranging from storage and recovery to communication and security. By understanding these files, you can gain deeper insights into MySQL’s operations and optimize your database environment.