Introduction to MySQL InnoDB table space and log files

One notable feature of MySQL is its pluggable storage engine, so the MySQL file is divided into two types: one is the MySQL server itself files (mainly some log files, such as error log, binary log, etc.), all the storage engine sharing, and the other is the specific storage engine-related files. This article mainly introduces and InnoDB storage engine related files (data + log), as for the MySQL server itself log files, you can refer to the [MySQL] log file overview.

InnoDB Table Space File

InnoDB is very much like Oracle in many ways, and its data is stored as a tablespace, and the table space is a logically monolithic block of storage, by default, all data is placed in a shared storage tablespace.

The following two variables are related to shared table spaces:

1) Innodb_data_home_dir: Set the home directory of the shared table space, the default equals DataDir value

2) Innodb_data_file_path: Sets the shared table space file, which can contain multiple files separated by semicolons.

If you don't want to put all the data in a shared table space, but instead want to generate a single file based on each table, then set Innodb_file_per_table to ON, which will create a tablespace for each table, but even so, shared tablespaces are essential, Because the data dictionary needs to be put inside, it's just not going to take up so much space.

The following figure shows how InnoDB is stored:

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

1 table structure definition file (. frm): MySQL each table, each view has a corresponding. frm file is used 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) Shared table space file (ibdata)

3 Individual table Space file (. ibd)

At the same time, to improve performance, you can configure the buffer of tablespace files through variable innodb_buffer_pool_size.

InnoDB log File

InnoDB In addition to the MySQL server itself log file, there is a unique log file: Redo log files, used to log the transaction, if the database due to downtime caused the instance to fail, restart, you can use the Redo log to restore the consistency before the downtime.

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:

The variables associated with the InnoDB log file are:

1) Innodb_log_group_home_dir: Defines the directory of log files

2) Innodb_log_files_in_group: Defines the number of log files

3) Innodb_log_file_size: Define log file size

4) Innodb_log_buffer_size: Log buffer size

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. The binaries record all MySQL-related log records, including the logs of storage engines such as INNODB,MYISAM,HEAP. 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.

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/MySQL/