×
Community Blog What are the Differences and Functions of the Redo Log, Undo Log, and Binlog in MySQL?

What are the Differences and Functions of the Redo Log, Undo Log, and Binlog in MySQL?

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.

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.

Redo log

Description

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.

Content

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.

When to Create

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 to Release

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).

Physical File

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.

Other Operations

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.

1

The logs in the InnoDB log buffer are flushed to the disk in three ways:

  1. The Master Thread performs the process of refreshing the Innodb_log_buffer to the redo log files once per second.
  2. The redo log is refreshed to the redo log when each transaction is submitted.
  3. When the redo log cache free space is less than half, the redo log cache is flushed to the redo log file.

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.

Undo Log

Description

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.

Content

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.

When to Create

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.

When to Release

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.

Physical File

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.

2

Other Operations

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.

Binlog

Description

  1. It is used for replication. In master-slave replication, the slave database replays the binlog stored in the master database to achieve master-slave synchronization.
  2. It is used for the database point-in-time restore.

Content

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 to Create

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.

When to Release

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.

3

Physical File

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:

4

Other Operations

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.

  1. Different Functions: Redo logs ensure the durability of transactions at the transaction level, while binlogs serve as a restore function at the database level (and can also be accurate to the transaction level.) Both indicate restoration but have different levels of data protection.
  2. The content is different. Redo log is a physical log, which is a physical record after the data page is modified. Binlog is a logical log, which can be simply considered as a record of SQL statements.
  3. In addition, the log generation time, the time that can be released, and the cleanup mechanism when the log can be released are completely different.
  4. Data Recovery Efficiency: Redo log-based data recovery is more efficient than binlog-based logical statement logs.

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.

Summary

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.

0 0 0
Share on

Alibaba Clouder

2,631 posts | 633 followers

You may also like

Comments

Alibaba Clouder

2,631 posts | 633 followers

Related Products