Introduction to MySQL's InnoDB file

A notable feature of MySQL is its pluggable storage engine, so there are two types of MySQL files, one that is related to the MySQL database itself, and one that is related to the storage engine. This article mainly describes the files associated with InnoDB storage engines.

Table Space File

InnoDB also imitates Oracle's design on storage, and data is stored in tablespaces, but unlike Oracle, Oracle's tablespace is a logical concept, while InnoDB tablespace is a physical concept.

You can set the default tablespace file by parameter Innodb_data_file_path, and all tables based on the InnoDB storage engine will be stored in the file. If you want to generate a table space file on a per-table basis, you can set the parameter innodb_file_per_table to on so that the table's data, index, and insert buffer messages are stored in a separate tablespace file, but the rest is stored in the default tablespace file.

The following illustration shows how table space files are stored:

The following storage files are mainly available in the above illustration:

1 table structure definition file (. frm): MySQL each table and each view has a corresponding. frm file to record the definition of tables and views. Note: This file is independent of the storage engine and belongs to the MySQL database itself.

2 Default table Space file (ibdata)

3 Individual table Space file (. ibd)

Redo log Files

The redo log file is critical to the InnoDB storage engine, and the transaction log is logged, and if the database fails due to downtime, the redo log can be used to restore the consistency status before the outage.

MySQL's redo logs are similar to Oracle's, overwriting reuse through loops, and the following figure shows a redo log file group with 3 redo log files:

Here's the question, too, to record the transaction log, what's the difference between InnoDB's redo log file and MySQL's own binary file? The main differences are the following three points:

1 First, the range is different. Binary files record all MySQL-related logging, including Innodb,myisam, heap, and other storage engine logs. The InnoDB redo log only records INNODB-related transaction logs.

2) Second, the content is different. Binary files record the specific operational content of a transaction, while the InnoDB redo log records the physical condition of each data page change.

3 Write time is different. Binary files are recorded before a transaction is committed, and repeated log entries are written to the redo log file during the transaction.