Analysis of MySQL crash recovery based on Redo Log and Undo Log

MySQL crash recovery process

Buffer Pool is a core component of MySQL memory structure. You can imagine it as a black box.

1. Update data flow under black box

When we query data, we will first query in the Buffer Pool. If the data does not exist in the Buffer Pool, the storage engine will first load the data from the disk into the Buffer Pool, and then return the data to the client; similarly, when we update certain data, if the data does not exist in the Buffer Pool, the data will also be loaded first, and then the data in memory will be modified. The modified data will be flushed to disk later.

MySQL crash recovery:

This process seems to be fine, but it is actually unethical. Suppose we successfully modify the data in the Buffer Pool, but MySQL crashes before we can flush the data to disk. What should we do? According to the logic in the above figure, the updated data only exists in the Buffer Pool. If MySQL crashes at this time, this part of the data will be permanently lost.

Furthermore, if an error suddenly occurred halfway through the update and I want to roll back to the version before the update, what should I do? That's a mess. How can we talk about crash recovery if we can't even guarantee data persistence and transaction rollback?

2. Redo Log & Undo Log

And judging by the fact that MySQL can recover from a crash, MySQL must have done something fancy. That’s right, these are the other two key functions we are going to introduce next, Redo Log and Undo Log .

These two logs belong to the InnoDB storage engine logs and are not the same dimension as the Binlog of the MySQL Server.

(1) Redo Log records the data status after the transaction is completed , and records the value after the update. (2) Undo Log records the data status before the transaction starts , and records the value before the update. So there is a clear difference between these two types of logs. I will use a more common example to explain these two types of logs.

Redo Log is like you typed a long command in the command line, pressed Enter to execute it, and then got an error message. At this point we just need to type ↑ again to get the previous command and execute it again.

Undo Log is like you just committed something in Git, and then made a more complicated change, but as you were making the changes, you lost your mind and didn’t want the changes you just made, so you directly git reset --hard $lastCommitId to return to the previous version.

3. Update process after implementing log

With Redo Log and Undo Log, let's improve the above picture.

MySQL crash recovery:

First, when updating data, it will still determine whether the data exists in the Buffer Pool. If not, it will be loaded. We mentioned the rollback issue above. Before updating the data in the Buffer Pool, we need to write the state of the data before the transaction starts into the Undo Log. If an error occurs during the update, we can use Undo Log to roll back to the start of the transaction.

The executor then updates the data in the Buffer Pool, and after a successful update, writes the latest state of the data to the Redo Log Buffer. Because a transaction may involve multiple read and write operations, writing to the buffer in groups is much more efficient than writing to disk files one by one.

redo-log-buffer:

Then why doesn’t Undo Log also have an Undo Log Buffer to speed up Undo Log so that both can benefit? Let's assume that there is a buffer in InnoDB, write the data status before the transaction starts to the Undo Log Buffer, and then start updating the data.

Suddenly, very quickly, MySQL exits due to an unexpected process. At this time, an embarrassing thing will happen. If part of the updated data has been flushed back to the disk, but the transaction is not successful and needs to be rolled back, you will find that the Undo Log is gone with the process exit. At this time, there is no way to roll back through the Undo Log.

What if MySQL crashes right after the memory is updated? At this time, the Redo Log Buffer may not even be written. Even if it is written, it is not flushed to the disk and the Redo Log is lost.

In fact, it does not matter. Because of the unexpected downtime, the transaction did not succeed. Since the transaction did not succeed, it needs to be rolled back. After MySQL restarts, it will read the Redo Log file on the disk and load its status into the Buffer Pool. The state restored through the disk Redo Log file is the same as the state before the transaction started before the downtime, so there is no impact. Then after waiting for the transaction to be committed, the Redo Log and Binlog will be flushed to the disk.

3. Problems that still exist in the process

You might think that this is perfect, but it is not. Suppose that MySQL crashes suddenly after we flush the Redo Log to disk, and the binlog has not been written in time. If the system is restarted at this time, the status represented by the Redo Log will be inconsistent with the status represented by the Binlog. The A field of a row restored to the Buffer Pool by the Redo Log is 3, but the data read out by any database monitoring its Binlog is 2.

Even if both Redo Log and Binlog are written to files, if the physical machine or VM where MySQL is located crashes, the logs will still be lost. When you write a file, the current OS will first write the changed content to the OS Cache to improve efficiency. Then, based on the policy (affected by the parameters you configured), the data in the OS Cache is flushed to disk.

4. Consistency guarantee based on 2PC

From this you can see a key issue, which is that the data consistency of Redo Log and Binlog must be guaranteed when the transaction is committed. Either both exist or neither exists. MySQL is implemented through **2PC (two-phase commit protocol)**.

MySQL crash recovery:

Let me briefly introduce 2PC, which is a protocol that ensures the consistency of distributed transaction data. Its Chinese name is two-phase commit, which splits the submission of distributed transactions into two stages, namely Prepare and Commit/Rollback.

Just like before two boxers start a match, the referee will confirm the status of the two fighters in the middle, similar to asking "Are you ready?" After confirmation, the referee will say Fight .

The referee asks the players about their status, which corresponds to第一階段Prepare ; after receiving an affirmative answer, the referee announces the official start of the game, which corresponds to第二階段Commit . However, if one player is not ready, the referee will announce a pause in the game, which corresponds to the failure of the first stage, and the status of the second stage will change to Rollback . The referee corresponds to協調者Coordinator in 2PC, and the player corresponds to參與者Participant .

Let's take a look at the whole process through a picture: 2PC flash to disk

In the Prepare phase, the Redo Log is written to a file and flushed to disk, the ID of the internal XA transaction is recorded, and the Redo Log status is set to Prepare. After the Redo Log is written successfully, the Binlog is also flushed to the disk to record the XA transaction ID.

In the Commit phase, the Commit flag is written to the Redo Log on disk, indicating that the transaction is committed. The executor then calls the storage engine's interface to commit the transaction. That's the whole process.

5. Verify the availability of the 2PC mechanism

This is the process of 2PC submitting Redo Log and Binlog. If an exception occurs during this period, can the 2PC mechanism really guarantee data consistency?

Assume that the Redo Log is flushed successfully, but MySQL crashes before it can flush the Binlog. After restarting, you will find that the Redo Log does not have a Commit marker. At this time, find this transaction based on the recorded XA transaction and roll it back.

If the Redo Log is flushed successfully and the Binlog is also flushed successfully, but MySQL crashes before you can change the Redo Log from Prepare to Commit, when you restart, you will find that although the Redo Log does not have a Commit flag, the Binlog queried by XID has been successfully flushed to disk.

At this time, although there is no Commit mark in the Redo Log, MySQL still needs to commit the transaction. Because once the Binlog is written, it may be consumed by the slave or any consumer that consumes the Binlog. If MySQL does not commit the transaction at this time, data inconsistency may occur. Moreover, Redo Log and Binlog are actually ready from the data level, except for a flag.

This concludes this article on MySQL crash recovery analysis based on Redo Log and Undo Log. For more information about MySQL crash recovery process, please search previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!