Redo log, undo log, and binlog of MySQL)

Redo log, undo log, and binlog of MySQL)

There are six types of log files in MySQL: redo log, undo log, binlog, and errorlog) slow query log, general query log, and relay log ).
The redo log and rollback log are closely related to transaction operations, and the binary log is also related to transaction operations. These three logs are of great significance for understanding the transaction operations in MySQL.

Here we will briefly summarize the logs with certain relevance among the three.
 
Redo log)

Purpose:
Ensure the transaction persistence.
Prevent dirty pages from being written to the disk at the time of the fault. When the mysql service is restarted, redo the logs to ensure transaction persistence.
Content:
Logs in the physical format record the modification information on the physical data page. The redo logs are written to the physical files of the redo log file in sequence.
When to generate:
The redo log is generated after the transaction starts. the disk of the redo log is not written as the transaction is committed, but is written into the redo log file during the execution of the transaction.
When to release:
After the dirty pages of the corresponding transaction are written to the disk, the redo log task is completed, and the space occupied by the redo log can be reused (overwritten ).
Corresponding physical files:
By default, the corresponding physical file is located in ib_logfile1 & ib_logfile2 under the data Directory of the database.
Innodb_log_group_home_dir specifies the path of the log file group. The default value is./, which indicates that it is in the database data directory.
Innodb_log_files_in_group specifies the number of files in the redo log file group. The default value is 2.
The file size and quantity are configured with the following two parameters.
Innodb_log_file_size resizes the log file.
Innodb_mirrored_log_groups specifies the number of log image file groups. The default value is 1.
Others:
It is very important that when is the redo log written to the disk? As mentioned above, the disk is written gradually after the transaction starts.
The reason for this is that the redo log is gradually written to the redo log file after the transaction starts, not necessarily the transaction commit before the redo log cache is written,
The reason is that the redo log has a cache zone Innodb_log_buffer. The default Innodb_log_buffer size is 8 MB (16 Mb is set here). The Innodb Storage engine first writes the redo log to innodb_log_buffer.

　　

Then, the innodb log buffer logs are refreshed to the disk in the following three ways:
1. The Master Thread refresh Innodb_log_buffer every second to redo the log file.
2. When each transaction is committed, the redo log is refreshed to the redo log file.
3. When the available space of the redo log cache is less than half, the redo log cache is refreshed to the redo log file.
It can be seen that the redo log is written to the disk in more than one way, especially for the first method, Innodb_log_buffer to the redo log file is a scheduled task of the Master Thread.
Therefore, the write disk of the redo log does not necessarily write the redo log file as the transaction is committed, but starts gradually as the transaction starts.
In addition, we reference the original statement on MySQL technology insider Innodb Storage engine (page37:
Even if a transaction has not been committed, the Innodb Storage engine refresh the redo log cache to the redo log file every second.
This point must be known, because it can well explain that the commit time of another large transaction is also very short.

Undo log)

Purpose:
A version that stores the data before a transaction occurs and can be used for rollback. It also provides read (MVCC) under multi-version concurrency control, that is, non-locked read.

Content:
When executing undo logs in logical format, the data is only restored from the logic to the State before the transaction, rather than from the physical page, this is different from redo log.

When to generate:
Before the transaction starts, the current version is used to generate the undo log, and the undo will also generate the redo to ensure the reliability of the undo log.

When to release:
After a transaction is committed, the undo log cannot be immediately deleted,
Instead, it is placed into the linked list to be cleared. The purge thread determines whether the version information of other transactions before the previous transaction in the table using the undo segment, and whether the log space of the undo log can be cleared.

Corresponding physical files:
Before MySQL5.6, the undo tablespace is 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 MySQL5.6, the undo tablespace can be configured as an independent file, but it must be configured in the configuration file in advance. After the database Initialization is completed, it takes effect and the number of undo log files cannot be changed.
If no related configuration is performed before database initialization, it cannot be configured as an independent tablespace.
The independent undo tablespace configuration parameters after MySQL5.7 are as follows:
Innodb_undo_directory =/data/undospace/-- storage directory of the undo independent tablespace
Innodb_undo_logs = 128 -- the rollback segment is KB
Innodb_undo_tablespaces = 4 -- four undo log files are specified.

If undo uses a shared tablespace, it not only stores undo information. By default, the shared tablespace is under the Data Directory of MySQL, and its properties are configured by the innodb_data_file_path parameter.
　　

Others:
Undo is a version of the modified data that is saved before the transaction starts. When an undo log is generated, it is also generated with a redolog similar to the mechanism to protect transaction persistence.
By default, the undo file is kept in the shared tablespace, that is, in the ibdatafile file. When large transaction operations occur in the database, a large amount of undo information is generated, all are saved in the shared tablespace.
Therefore, the shared tablespace may become very large. By default, when the undo log uses the shared tablespace, the shared tablespace that is "large" won't and cannot be automatically scaled down.
Therefore, it is necessary to configure the "independent undo tablespace" after mysql5.7.

Binary log (binlog ):

Purpose:
1. Used for replication. In master-slave replication, the slave database uses the binlog on the master database for replay to achieve master-slave synchronization.
2. Used for restoration of databases based on time points.
Content:
A log in a logical format can be considered as an SQL statement in a transaction that has been executed.
However, it is not as simple as an SQL statement, but the reverse information of the executed SQL statement (addition, deletion, modification, and so on,
This means that delete corresponds to delete itself and its reverse insert; update corresponds to the version information before and after update execution; insert corresponds to the delete and insert information.
After mysqlbinlog is used to parse binlog, some of them will be clear.
Therefore, the flashback function similar to Oracle Based on binlog is actually dependent on the log records in binlog.

When to generate:
When a transaction is committed, the SQL statements in the transaction (a transaction may correspond to multiple SQL statements) are recorded in the binlog in a certain format.
The obvious difference between the redo log and redo log is that the redo log is not necessarily refreshed to the disk when the transaction is committed, and the redo log is gradually written to the disk after the transaction starts.
Therefore, for the commit of a transaction, even a large transaction, commit (commit) is very fast, but when bin_log is enabled, for the commit of a large transaction, it may become slower.
This is because the binlog is written at one time when the transaction is committed, which can be verified through testing.

When to release:
By default, the retention time of binlog is configured by the expire_logs_days parameter. That is to say, for non-active log files, it is automatically deleted after the generation time exceeds the days configured by expire_logs_days.
　　

Corresponding physical files:
The path of the configuration file is log_bin_basename. The binlog log file is of the specified size. When the log file reaches the specified maximum size, it is updated in a rolling manner to generate a new log file.
Each binlog log file is organized by a unified index file.

　

Others:
One of the functions of binary logs is to restore the database, which is similar to the redo log. Many people are confused, but the two are essentially different.
1. Different roles: the redo log ensures transaction persistence at the transaction level. As a restoration function, the binlog is at the database level (of course it can be accurate to the transaction level ), although they all mean restoration, the layers of data protection are different.
2. Different content: redo log is a physical log, a physical record after the data page is modified, and binlog is a logical log. You can simply think that the record is an SQL statement.
3. In addition, the log generation time, release time, and cleanup mechanism are completely different when logs can be released.

For the write sequence of the redo log and binlog when the transaction is committed, in order to ensure the consistency between the master and slave nodes during the master-slave replication (including the use of binlog for Restoration Based on Time points ), is strictly consistent,
MySQL completes transaction consistency through the two-phase commit process, that is, the consistency between the redo log and the binlog. In theory, it first writes the redo log and then the binlog, when both logs are submitted successfully (flushed into the disk), the transaction is truly completed.

Summary:

In MySQL, each of the above three types of logs can be refined to write a chapter. Here we roughly summarize some of the characteristics and functions of the three types of logs, to help understand the principles behind things and things in MySQL.

Reference: MySQL technology insider Innodb Storage engine PDF download see