This installment focuses on the three log files involved in the write process, which you probably already know at least a little bit from previous installments (or from other sources). For example, the two-phase commit used in the entire write operation, or the log files involved in the operation, but in general, it is not very systematic and not comprehensive.

Today we will meet the three brothers.

Note: Mind mapping

Two-phase commit

It’s a term you’ve heard many times, so let me introduce my old friend.

A two-phase commit is literally a two-step constraint. And so it is. The protagonists of these two steps are two of the most important players in today’s lecture: binlog and redo log.

When it comes to two-phase commit, the flow of SQL statement execution is not bypassed. Nothing. I’ve mentioned it many times, but I have to pull it out again. It’s just that the focus will be a little different.

The specific operation process is as follows:

When SQL is executed for a write operation, the engine updates the row to memory, records the operation in the redo log, and is prepared. And the completion information to the actuator.

The executor generates a binlog of the corresponding operation and writes the binlog to disk. Then call the engine’s commit transaction interface and change the redo log status to COMMIT.

Ok, now that we know about the two-phase commit, let’s take a look at these log files.

Redo log

The first is the **redo log, located in the storage engine layer. ** This is the physical log file that records what changes were made to the data page.

Hi-tech technology

When it comes to redo logs, WAL is of course a no-brainer. It stands for write-Ahead Logging. That is, logs are written before disk synchronization, and the system synchronizes the logs to disks according to certain policies.

Necessity of existence

From the two-phase commit process above, we can see how WAL technology can be used. If you’re wondering, why don’t you write a redo log and just sync your updates to disk? Silly boy, syncing to disk means that every write has to generate a random write to disk, which is very slow.

You might be smart enough to say, can I sync from memory to disk again in a certain amount of time? Here, I’m gonna blow your brains out. Think about it. My service’s restarted. Do you still have the data? Memory is volatile, and you don’t know what anomalies can cause data loss. So you need an intermediate file that can persist, act as a buffer, and write quickly.

The redo log was born. Although it is also stored on disk, sequential writes are not affected in terms of speed (for those wondering, the difference between random and sequential reads on disk).

In addition to the redo log function of “delaying” disk file synchronization, the redo log can also be used to recover data in the event of a database server outage.

Write the timing

Write to redo log after memory update. The answer is no, because there is also a redo log buffer. Each statement executed by Mysql is written to the redo log buffer and then to the redo log file (on disk) at a certain time during commit operations.

Note that data in the redo log buffer is written to the redo log file at commit time.

The timing of the write is controlled by the following parameters:

(Picture from Internet)

Written way

Knowing the timing of writing, here is a brief introduction to the way of writing. In Innodb, the redo log size is fixed, so you have to write in a circular fashion. If I have four files, I write from the first file until the last file is full, then I go back to the beginning, synchronize data to the file, erase and continue writing.

Write POS in the figure represents the position of the current record, which gradually moves backward as the write continues. When ib_logfile_3 is written, the entire redo log is filled. The update operation is blocked. The system erases records based on the check point bits (provided, of course, they are synchronized to disk).

In general, redo log writing is a process of writing, filling, erasing, and writing again.

Binary log

Redo log: Log file: Log file: log file: Log file: Log file: Log file: Log file: Log file: Log file: Log file: log file: log file

For example, update the field whose ID is 1.

Of course, in addition to recording operations, it also supports master-slave synchronization and data exception recovery capabilities.

Write mode

There are three write modes in binlog. Let’s take a look at the differences and the corresponding advantages and disadvantages:

(Picture from Internet)

Written way

Unlike redo log cycles, binlogs are written appending, and when one file reaches a certain size, it switches to another.

Association with redo log

The redo log status is changed to COMMIT by calling the commit transaction interface of the engine after writing the binlog. So how does it find the corresponding record, or in other words, how do the two relate?

The answer is to use a common field called XID, which can be used to find a transaction not only during commit time but also during crash recovery if a redo log writes prepare but does not commit.

Review the writing process

Here it is worth reviewing the operation of the write process, using updating a field as an example:

Rollback log (undo log)

At this point, you may be wondering where the undo log is in the whole story, you cheat and play with women’s feelings of the man!

Don’t worry, it’s coming!

From the literal meaning, you should be able to guess what it does. A rollback is a chance to regret it. When data is modified, the undo log is also recorded, that is, the logical log of the opposite operation is also recorded. You can think of it as writing a reverse update record for an UPDATE operation and an insert record for a DELETE operation.

When the transaction is rolled back. After reading the corresponding logical record from the undo log, the rollback operation can be performed.

conclusion

Two-phase commit

  • In the two-phase commit process, the memory is updated and the corresponding operations are recorded in the redo log. The generated binlog is written to disk and the transaction is committed.

Redo log

  • The redo log is a physical log file located in the storage engine layer that records what was changed on the data page. The circular write method is used to record the appearance of the data after modification. It also provides data recovery capability.

Binary log

  • Binlog is a logical log file at the service layer that records what was changed to the data. Unlike redo logs, appending is always possible. It also supports master/slave synchronization and data exception recovery.

Roll back log

  • Recording the Undo log during data modification ensures that data will be restored during transaction rollback.

About the author

Author: Hello, I’m Lewu, a brick remover from BAT. From a small company to a big factory, I have learned a lot along the way. I want to share these experiences with people in need, so I created a public account [MIGRANT workers in IT industry]. At the same time, I have given you a Redis manual, which can be obtained by replying to [PDF] in my official account. I hope it will be helpful to you.