A brief analysis of the differences between undo, redo and binlog in MySQL

Preface

There are six types of log files in MySQL: redo log, undo log, binary log, error log, slow query log, general log, and relay log.

Among them, redo logs and rollback logs are closely related to transaction operations, and binary logs also have a certain relationship with transaction operations. These three logs are of great significance for understanding transaction operations in MySQL.

【undo log】

Before the transaction starts, generate an undo log for the current transaction version (Tips: undo log will also generate a redo log to ensure the reliability of the undo log).

After a transaction is committed, the undo log is not deleted immediately, but is put into a linked list to be cleaned up. The purge thread determines whether other transactions are using the version information before the previous transaction in the table in the undo segment, and thus decides whether the log space of the undo log can be cleaned up.

One of the four major characteristics of database transactions is atomicity. Specifically, atomicity means that a series of operations on the database must either succeed completely or fail completely. It is impossible for partial success to occur.

In fact, the underlying atomicity is achieved through undo log. The undo log mainly records the logical changes of the data. For example, for an INSERT statement, there is a corresponding DELETE undo log. For each UPDATE statement, there is a corresponding opposite UPDATE undo log. In this way, when an error occurs, the data state before the transaction can be rolled back. For example, the original records in the user table are as follows:

When executing sql update user set name = 'xiaohong' where id = 1; the undo log generated is probably update user set name = 'xiaoming' where id = 1;

At the same time, undo log is also the key to the implementation of MVCC (multi-version concurrency control).

【redo log】

How does MySQL ensure the persistence of transactions? The simplest approach is to refresh all modified data pages involved in the transaction to disk each time the transaction is committed. However, doing so will have serious performance problems, mainly in two aspects:

• Because Innodb interacts with the disk in pages, and a transaction may only modify a few bytes in a data page, it would be a waste of resources to flush the entire data page to the disk at this time!
• A transaction may involve modifying multiple data pages, and these data pages are not physically continuous, so the performance of using random IO writes is too poor!

Therefore, MySQL designed a redo log mechanism and optimized performance through WAL (Write-Ahead Logging) technology. The core of WAL is to write log disks by sequential IO first, and then write data disks by random IO, which saves the IO consumption of random disk writing. Each time MySQL executes a DML statement, it first appends the records to the redo log buffer in sequence and updates the data in the memory. It then writes the records to the disk in batches for persistence when there are idle threads, insufficient memory, or the Redo Log is full.

【binlog】

Binlog is the logical log of MySQL and is recorded by the Server layer. It records the write operation (excluding query) information of any database engine and is saved in the disk in binary form.

In practical applications, binlog is mainly used in two scenarios: master-slave replication and data recovery.

• Master-slave replication: Open binlog on the Master side, and then send binlog to each Slave side. The Slave side replays binlog to achieve master-slave data consistency.
• Data recovery: Recover data by using the mysqlbinlog tool.

During the data update process, if the system fails and restarts abnormally, how can we ensure the persistence and atomicity of the transaction? Here is an overview:

1. Record the snapshot site of the data record before this update (that is, write undo log)
2. Read the data needed for this update into memory
3. Update data in memory (high efficiency)
4. Write redo log and set the redo log status to prepare
5. Writing binlog
6. Set the redo log status to commit

Based on the simplified undo log, redo log, and binlog writing process above, let's sort out the reliability guarantees of atomicity, persistence, and consistency:

A) If a failure occurs in any of steps 1/2/3, after recovery, it is found that there is no unfinished record in the redo log. After recovery, you only need to roll back the undo log to restore the scene;

B) If a failure occurs in any step of step 4 or 5, and the redo log is found to be in the prepare state after failure recovery, further determine whether it has been written to the binlog:

1. If the data has been written to the binlog, re-execute the relevant records of the redo log until the commit state is successfully reached (master-slave consistency);
2. If the binlog is not written, roll back the undo log to restore the scene (atomicity);
   

C) If a failure occurs in step 6, after recovery, the redo log is found to be in the committed state, indicating that the process was completed normally, and nothing needs to be done.

Summarize

This is the end of this article about the difference between undo, redo and binlog in MySQL. For more information about the difference between undo, redo and binlog in MySQL, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!