Introduction MySQL log redo log and binlog

##Free learning recommendation: mysql video tutorial

Preface

As long as you are a programmer who has been exposed to MySQL, you have more or less heard of redo log (redo log) and binlog (archive log). Today, let’s share the uses and differences between these two logs.

Simply put, redo log is a log unique to InnoDB. If you use other storage engines, there will be no redo log, only binlog.

Binlog is the log of the server layer of MySQL. No matter what storage engine is used, there will be a binlog. So, why do we need redo log and binlog? Can’t everything be solved with just one binlog? Next, let’s take a detailed look at the difference between redo log and binlog.

redo log

In MySQL, if you want to update a statement, you need to bring update conditions, such as

update T set name = 'god-jiang' where id=6, generally the statement with id=6 is queried first, and then the update operation is performed.

If the number of updates is 100, 1,000 or even 10,000, each update needs to be written to the disk. Then the corresponding record must be found on the disk and then updated. The IO cost and search cost of the entire process are too high. In order to solve this problem, the designers of MySQL used WAL technology to solve it. The full name of WAL is

Write Ahead Logging, which means writing the log first and then writing to the disk.

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

But the size of the redo log is fixed, and it is impossible to write infinitely all the time. Let us see how MySQL does it.

write pos is the position of the current record, moving backward while writing. The check point is the current position to be erased, which also moves backward and circulates. Before erasing the record, the record must be updated to the data file.

The green part between write pos and check point indicates that new operations can be recorded. If the write pos catches up with the check point, it means that the redo log is full. At this time, new operations cannot be continued. It is necessary to stop and erase some records and push the check point back.

With redo log, InnoDB can ensure that even if the database detects an exception and restarts, previously submitted transactions will not be lost. This capability is also called

crash-safe.

The above is the introduction to redo log. After reading this, you can try to ask your company's DBA colleagues whether MySQL can be restored to the state of any second within half a month. The answer will definitely be Yes, this is all thanks to the redo log.

binlog

Looking at 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 discussed above is a log unique to the InnoDB engine, while the binlog is a log belonging to the server layer, also called an archive log.

The difference between redo log and binlog

redo log is unique to the InnoDB engine; binlog is implemented by the Server layer of MySQL and can be used by all engines
• The redo log is a physical log, which records "XXX modifications were made on the XXX data page"; the 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 definitely run out, so write pos and check point need to be matched; binlog is written additionally, and it will switch to the next one when it reaches a certain size, and will not overwrite the previous log

Demonstrates the internal process of the executor and the InnoDB engine through a simple update statement

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

Use the executor to retrieve the record with id=6 from the InnoDB engine and then load it into memory
1. The executor gets the result returned by the engine, changes the name to 'god-jiang', and then calls the storage engine interface again to write new data
2. The engine updates the new data into the memory , and write this update operation to the redo log at the same time. At this time, the redo log is in the prepare state
3. The executor generates the binlog of this operation and writes the binlog to the disk
4. The executor calls the engine Submit the transaction interface, and change the redo log just written to the commit state, and the update is completed

The corresponding flow chart

Finally, why does writing to the redo log put it in the prepare state, and then writing to the binlog changes to the commit state? In fact, this process is called "two-stage submission".

Two-phase submission

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

For example: update T set name = ‘god-jiang’ where id = 6What will happen without two-phase submission?

Write redo log first and then binlog. Assume that after writing the redo log, the binlog has not yet been written, and MySQL restarts abnormally at this time. Because the redo log has been written, name=‘god-jiang’ when restoring the system. However, the binlog has not been written, so the binlog does not record this statement. When the binlog is used to restore data at this time, the restored name is the original value, which is different from the redo log.

Similarly, if you write binlog first and then redo log, you will also find that the data recovered by the two logs is different. This inconsistency will lead to master-slave inconsistency online.

Summary

• The redo log can save crash-safe capabilities and ensure that MySQL restarts abnormally and the data is not lost
• The binlog can record the corresponding SQL statements can also ensure that data will not be lost when MySQL restarts abnormally
• The two-stage submission of the transaction can maintain the logical consistency of the data

Related free learning recommendations ：mysql database(Video)

The above is the detailed content of Introduction MySQL log redo log and binlog. For more information, please follow other related articles on the PHP Chinese website!