Shulou(Shulou.com)05/31 Report--

This article mainly introduces what is the difference between redo log and binlog of MySQL log, it has certain reference value, interested friends can refer to it, I hope you can learn a lot after reading this article, let the editor take you to know it.

The difference between redo log and binlog

Redo log

In MySQL, if you want to update a statement, you need to bring an update condition, such as update T set name = 'god-jiang' where id=6. You usually query the statement of id=6 first and then update it.

If the number of updates is 100, 1000 or even 10000, each update needs to be written to disk. Then the disk also has to find the corresponding record, and then update, the whole process IO cost, search cost is too high, in order to solve this problem, MySQL designers use WAL technology to solve. The full name of WAL is Write Ahead Logging, which means to write a log before writing to disk.

Specific operation: when a record needs to be updated, the InnoDB engine will first write the record to redo log and update the memory. At this time, the update is complete. At the same time, the InnoDB engine updates the operation record to disk at the appropriate time (when the system is idle), often when the system is idle.

But the size of redo log is fixed, and you can't write indefinitely all the time, so let's see how MySQL does it.

Write pos is the location of the current record, moving backwards as you write. Check point is the current location to be erased, and it is also moved backwards and looped, and the record is updated to the data file before erasing the record.

The green part between write pos and check point indicates that new actions can be recorded. If write pos catches up with check point, indicating that the redo log is full, you can't continue to perform new operations at this time, you need to stop erasing some records, and push check point back.

Redo log,InnoDB ensures that even if the database finds an abnormal restart, the previously committed transaction will not be lost. This capability is also known as crash-safe.

The above is the introduction of redo log. After reading it, you can try to ask your company's DBA colleagues whether MySQL can return to any second in half a month. The answer must be yes, thanks to redo log.

Binlog

From the perspective of MySQL as a whole, it is actually divided into two layers, one is the Server layer, and the other is the storage engine layer. The redo log mentioned above is the log that is specific to the InnoDB engine, while binlog is the log that belongs to the Server layer, also known as the archive log.

The difference between redo log and binlog

Redo log is specific to the InnoDB engine; binlog is implemented at the Server layer of MySQL and can be used by all engines

Redo log is a physical log, which records "XXX modifications made on the XXX data page"; binlog is a logical log, which records the original logic, and its record is the corresponding SQL statement

Redo log is written in a loop, and the space will be used up, so it needs to be matched with write pos and check point. If binlog is appended, it will switch to the next one when writing to a certain size and will not overwrite the previous log.

Demonstrate the internal flow of the actuator and InnoDB engine through a simple update statement

Update T set name = 'god-jiang' where id = 6

The id=6 record is extracted from the InnoDB engine through the actuator and loaded into memory.

The executor gets the result returned by the engine, changes the name to 'god-jiang', and then calls the interface of the storage engine again to write new data.

The engine updates the new data to memory and writes the update operation to redo log, when the redo log is in the prepare state

The executor generates the binlog for this operation and writes the binlog to disk

The executor calls the interface of the engine to commit the transaction, and changes the redo log just written to the commit state, and the update is completed.

Corresponding flow chart

Why did writing redo log end up in prepare state, and then writing binlog to commit state? In fact, this process is called "two-phase commit".

Two-phase submission

In fact, both redo log and binlog can be used to represent the commit status of a transaction, and two-phase commit is to keep the two states logically consistent.

For example: update T set name = 'god-jiang' where id = 6 what happens without a two-phase commit?

Write redo log first and then binlog. Assuming that the redo log,binlog has not been written yet, MySQL restarts abnormally at this time. Because redo log has finished writing, name='god-jiang' when restoring the system. But binlog is not finished, so binlog does not record this statement. When you use binlog to recover data, the recovered name is the original value, which is different from redo log.

By the same token, writing binlog first and then writing redo log will also find that the data recovered by the two logs is different. This inconsistency will lead to the inconsistency of the main body online.

Summary

Redo log can preserve crash-safe capability and ensure that MySQL abnormal restart data will not be lost.

Binlog can record the corresponding SQL statements, and it can also ensure that MySQL abnormal restart data will not be lost.

A two-phase commit of a transaction that maintains data logical consistency.

Thank you for reading this article carefully. I hope the article "what is the difference between redo log and binlog in MySQL logs" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.