There are six types of log files in MySQL: redo log, undo log, binlog, error log, slow query log, general log, relay log. Redo logs and undo logs are closely related to transaction operations. Binlogs are also related to transaction operations. These three types of logs are important for understanding transaction operations in MySQL.
The following briefly summarizes the logs that are related to each other.
Description ensures the durability of transactions and prevents dirty pages from being written to the disk at the point in time of the failure. When the MySQL service is restarted, redo according to the redo log to achieve the durability of the transaction.
Logs are in the physical format and record modification information of physical data pages. The redo logs are written sequentially to the physical files of the redo log file.
A redo log is generated after a transaction starts. The redo log is not written when the transaction is committed but written into the redo log file during transaction execution.
When the dirty pages of the corresponding transaction are written to the disk, the redo log mission is completed, and the space occupied by the redo log can be reused (overwritten).
By default, the corresponding physical files are located in the ib_logfile1 & ib_logfile2 in the data directory of the database.
innodb_log_group_home_dir specifies the path where the log file group is located. The default is ./, which means it is in the data directory of the database.
innodb_log_files_in_group: The number of files in the redo log filegroup. The default value is 2.
The size and number of files are configured by the following two parameters:
innodb_log_file_size and the size of the redo log file.
Innodb_mirror_log_groups specifies the number of log image file groups. The default value is 1.
It is very important to know when the redo log wrote to the disk. As mentioned earlier, it was written gradually after things started.
Redo logs are written gradually to the redo log files after a transaction starts, instead of being written to the redo log cache only when the transaction is committed.
The reason is that redo logs have a cache size of Innodb_log_buffer. The default size of Innodb_log_buffer is 8MB (16MB is set here), and the InnoDB storage engine writes redo logs into innodb_log_buffer first.
The logs in the InnoDB log buffer are flushed to the disk in three ways:
Innodb_log_buffer to the redo log files once per second.As shown in the preceding figure, redo logs are written to disks in more than one way. Firstly, Innodb_log_buffer is a scheduled task of the Master Thread.
Therefore, the write disk of the redo log is not necessarily written to the redo log files only when the transaction is committed but started gradually when the transaction starts.
Even if a transaction has not been committed, the InnoDB storage engine still flushes the redo log cache to the redo log file every second.
This point must be known because it can explain that the commit time of a large transaction is also very short.
It stores a version of the data before the transaction occurs, which can be used for rollback. At the same time, it can provide reads (MVCC) under Multi-Version Concurrency control, which is read without locking.
This is different from redo logs. Rather than implementing operations on the physical page, the undo operation is only used to restore data logically to the state prior to the transaction.
Before the transaction starts, the undo log is generated for the current version. The redo log also generates the redo log to ensure the reliability of the undo log.
The undo log cannot be deleted immediately after the transaction is committed; instead, it is placed in the linked list to be cleaned. The purge thread determines whether other transactions use the version information before the previous transaction in the table in the undo segment to clean up the log space in the undo log.
Prior to MySQL5.6, the undo tablespace was located in the rollback segment of the shared tablespace. The default name of the shared tablespace is ibdata, which is located in the data file directory.
After MySQL 5.6, the undo tablespace can be configured as a separate file, but it must be configured in the configuration file in advance. It takes effect after the database initialization is completed, and the number of undo log files cannot be changed.
If no relevant configuration is made before initializing the database, it cannot be configured as a separate tablespace.
Regarding the independent undo tablespaces after MySQL5.7, the configuration parameters are listed below:
innodb_undo_directory = /data/undospace/ -- The Directory of an undo independent tablespace
innodb_undo_logs = 128 -- the size of the rollback segment is 128KB.
innodb_undo_tablespaces = 4 -- specify four undo log files.If undo uses a shared tablespace, the shared tablespace stores undo information and defaults to the data directory with MySQL. Then, its properties are configured by the parameter innodb_data_file_path.
Undo is a version of modified data that is saved before a transaction starts. The generation of undo logs is accompanied by the generation of redo logs, which is similar to the transaction persistence protection mechanism.
By default, undo files are kept in shared tablespaces called ibdatafile files. When some large transactional operations occur in the database, a large amount of undo information is generated and saved in the shared tablespace.
Therefore, the shared tablespace may become large. By default, when the undo log uses a shared tablespace, the "stretched" shared tablespace will not (and cannot) be shrunk automatically.
Therefore, it is necessary to configure the "independent undo tablespace" after MySQL 5.7.
A log in the logical format can be considered as the SQL statements in transactions that have been executed.
It is not all SQL statements, but it contains the reverse information of executed SQL statements (add, delete, and modify). This means "delete" corresponds to "delete" and its reverse insert “update” corresponds to the version information before and after update execution, and “insert” corresponds to “delete and insert.”
Some facts will be clear after mysqlbinlog is used to parse the binlog. Therefore, the flashback function that is similar to Oracle can be implemented based on binlog. It relies on the log records in binlog.
When a transaction is submitted, each SQL statement in the transaction (one transaction may correspond to multiple SQL statements) is recorded in the binlog in a specific format.
The difference with redo logs is that redo logs are not necessarily flushed to the disk when a transaction is committed. Redo logs are written to the disk after the transaction starts.
Therefore, commit is very fast for transactions, even for large transactions. However, it may become slower with bin_log enabled for large transactions because the binlog is written one time when a transaction is submitted, which can be verified through testing.
The default retention time of binlogs is set by the expire_logs_days parameter. Here, an inactive log file is deleted automatically after the number of days generated by expire_logs_days.
The path of the configuration file is log_bin_basename, and the binlog file size is the specified value. When the log file reaches the maximum size, a rolling update is performed to generate a new log file.
Each binlog file is organized by a unified index file:
One of the functions of binary logs is to restore the database, which is similar to redo logs. Many people confuse them, but the two are different.
Regarding transaction commit, the writing order of redo log and binlog, in order to ensure master-slave consistency during master-slave replication (including the use of binlog for point-in-time restore), it should be strictly consistent. MySQL achieves transaction consistency through a two-phase commit, which is the consistency between the redo log and the binlog. Theoretically, the redo log is written first, and the binlog is written afterward. The transaction is completed after both logs are committed to the disk.
In MySQL, you can specify only one chapter for each of the preceding three types of logs. This article gives a rough summary of the characteristics and roles of the three types of logs to help you understand MySQL and the principles behind them.
Article originally published on WeChat Account Programmer Bai Nannan
Disclaimer: The views expressed herein are for reference only and don't necessarily represent the official views of Alibaba Cloud.
2,603 posts | 747 followers
FollowApsaraDB - February 22, 2023
oceanbaseworld - November 29, 2019
Alibaba Clouder - April 8, 2018
ApsaraDB - June 1, 2022
Jack008 - May 6, 2020
Alibaba Clouder - April 10, 2018
2,603 posts | 747 followers
Follow
Database Overview
ApsaraDB: Faster, Stronger, More Secure
Learn More
PolarDB for MySQL
Alibaba Cloud PolarDB for MySQL is a cloud-native relational database service 100% compatible with MySQL.
Learn More
AnalyticDB for MySQL
AnalyticDB for MySQL is a real-time data warehousing service that can process petabytes of data with high concurrency and low latency.
Learn More
ApsaraDB RDS for MySQL
An on-demand database hosting service for MySQL with automated monitoring, backup and disaster recovery capabilities
Learn MoreMore Posts by Alibaba Clouder