How to use MySQL's binlog, redo log and undo log

1. Binlog

Binlog is used to record information about write operations performed in the database. It excludes query operations and is saved on disk in binary format. . Binlog is the logical log of mysql and is recorded by the server layer. Mysql databases using any storage engine will record binlog logs.

• Logical log: can be simply understood as a sql statement;

• Physical log: The data in MySQL is stored in the data page. The physical log records changes on the data page; insert the code snippet here

The binlog is written by appending, and the size of each binlog file can be set through the max_binlog_size parameter. , when the file size reaches the given value, a new file will be generated to save the log.

binlog usage scenarios
Project In actual applications, there are two main usage scenarios for binlog, namely master-slave replication and data recovery.

• Master-slave replication: Enable binlog on the Master side, and then send the binlog to each Slave side. The Slave side replays the binlog to achieve master-slave data consistency.

• Data recovery: Recover data by using the mysqlbinlog tool.

MySQL master-slave synchronization principle

• ##Master node binlog dump thread

  When the slave node connects to the master node, the master node will create a log dump thread to send the contents of the binlog. When reading operations in the binlog, this thread will lock the binlog on the master node. When the read is completed, the lock will be released even before launching to the slave node;
  

• Slave node I/O thread

  When the start slave command is executed on the slave node, the slave node will create an I/O thread to connect to the master node and request the updated binlog in the master library. After the I/O thread receives the update from the master node binlog dump process, it saves it in the local relaylog;
  

• Slave node SQL thread

  The SQL thread is responsible for reading the relaylog The content is parsed into specific operations and executed, ultimately ensuring the consistency of master-slave data;
  MySQL database master-slave synchronization principle
  

binlog content As mentioned above, binlog is a logical log, which can be simply understood as a sql statement, but in fact it also contains the reverse logic of the executed sql statement. delete corresponds to delete itself and the reverse insert information; update contains information about the data rows before and after the corresponding update is executed; insert contains its own insert and corresponding delete information.

binlog format There are three binlog formats, namely statement, row and mixed. Before MySQL 5.7.7, statement was used by default, and after MySQL 5.7.7, row was used by default. The format of the log can be modified through binlog-format in the my.ini configuration file.
(1) Statement: Statement-based replication (SBR) based on SQL statements. Each SQL statement that modifies data will be recorded in the binlog.

• Advantages: No need to specifically record changes in a certain row, saving space, reducing IO, and improving performance;

• Disadvantages: When executing sysdate( ) or sleep() and other operations, it may lead to inconsistency between master and slave data;

(2) row: row-based replication (RBR), It does not record sql statement context-related information, but records the details of which record was modified.

• Advantages: The details of each row record modification are recorded in great detail, so there will be no situation where the data cannot be copied correctly;

• Disadvantages : Since the details of each record modification will be recorded in great detail, a large amount of log content will be generated. Assume that there is an update statement and many records are modified. Each modified record will be recorded in the binlog. In particular, for the alter table operation, due to changes in the table structure, each row of records will change, resulting in a sudden increase in log volume;

(3)mixed: According to the above, Statement and row each have their own advantages and disadvantages, so the mixed version appeared to mix the two. Under normal circumstances, the statement format is used for saving. When the statement cannot be solved, switch to row format for saving.

In particular, as mentioned above, the new version (after MySQL 5.7.7) uses the row format by default. The row here has also been optimized accordingly. When encountering the alter table operation, the statement format is used for recording. The rest Operations still use row format.

binlog flushing timing

For the InnoDB storage engine, the binlog will only be recorded when the transaction is submitted. At this time, the record is still in the memory, and MySQL passes sync_binlog controls the flushing timing of binlog. The value range is 0-N:

• 0: It is not forced to flush to the disk, and the system will decide when to write to the disk;

• 1: The binlog must be written after each submission. Write to the disk;

• N: Every N transactions, the binlog will be written to the disk;

As can be seen from the above , the safest setting for sync_binlog is 1, which is also the default value for MySQL versions after 5.7.7. However, setting a larger value can improve database performance. Therefore, in actual situations, you can also increase the value appropriately and sacrifice a certain degree of consistency to obtain better performance.

Physical file size of binlog

You can control the size of binlog by configuring the max_binlog_size parameter, which is located in the my.ini configuration file. The system will create a new file to continue to store logs when the size of the log exceeds the capacity limit of the binlog file. What should I do when a transaction is relatively large, or when there are more and more logs, and the physical space it occupies is too large? MySQL provides an automatic deletion mechanism, which can be solved by configuring the expire_logs_days parameter in the my.ini configuration file. The unit is days. When this parameter is 0, it means it will never be deleted; when it is N, it means it will be automatically deleted after the Nth day.

2. redo log

redolog is the proprietary log system of the InnoDB engine. It is mainly used to achieve transaction durability and crash-safe functions. Redolog is a physical log, which records the specific modifications on the data page after the SQL statement is executed.
We all know that when MySQL is running, data will be loaded from disk into memory. When a SQL statement is executed to modify the data, the modified content is actually only temporarily saved in the memory. If the power is cut off or other circumstances occur at this time, these modifications will be lost. Therefore, after modifying the data, MySQL will look for opportunities to flush these memory records back to the disk. But there is a performance problem, mainly in two aspects:

InnoDB interacts with the disk in data units of pages, and a transaction may only modify a few bytes on a page. , if a complete data page is flushed back to the disk, it will waste resources;

A transaction may involve multiple data pages. These data pages are only logically continuous but not physically continuous. Use random IO The performance is too poor;

Therefore, MySQL designed redolog to record the specific modifications made to the data page by the transaction, and then flush the redolog back to the disk. You may have doubts. Originally, I wanted to reduce io. Wouldn’t this add another io? The designers of InnoDB have taken these into consideration at the beginning of the design. Redo log files are usually small, and sequential I/O is used during disk flushing. Better performance compared to random I/O.

Basic concepts of redo log
Redolog consists of two parts, one is the log cache redo log buffer in the memory, and the other is the log file redo log file in the disk. Every time the data record is modified, these modifications will be written to the redo log buffer first, and then wait for the appropriate opportunity to flush the modifications in the memory back to the redo log file. This technology of writing logs first and then writing to disk is WAL (Write-Ahead Logging) technology. It should be noted that the redolog is flushed back to disk before the data page. Modifications to the clustered index, secondary index, and undo page all need to be recorded in the redolog.

In computer operating systems, buffer data in user space generally cannot be written directly to the disk, and must pass through the operating system kernel space buffer (OS Buffer). ). Therefore, writing the redo log buffer to the redo log file actually writes it to the OS Buffer first, and then flushes it to the redo log file through the system call fsync(). The process is as follows:

mysql support Three timings for writing redo log buffer to redo log file can be configured through the innodb_flush_log_at_trx_commit parameter. The meaning of each parameter value is as follows:

Parameter value	Meaning
0 (delayed writing)	When the transaction is committed, the log in the redo log buffer will not be written to the os buffer, but every second Write to the os buffer and call fsync() to write to the redo log file. That is to say, when set to 0, data is written to the disk (approximately) every second. When the system crashes, 1 second of data will be lost.
1 (real-time writing, real-time brushing)	Every time a transaction is submitted, the log in the redo log buffer will be written to the os buffer and fsync() will be called to flush to redo log file. This method will not lose any data even if the system crashes, but because each submission is written to the disk, the IO performance is poor.
2 (real-time writing, delayed brushing)	Each submission is only written to the os buffer, and then fsync() is called every second to write the data in the os buffer The log is written to the redo log file.

redo log recording format
Redolog adopts a fixed size and cyclic writing format. When the redolog is full, it will be written from the beginning again. Why is it designed like this?
The main purpose of redo log is to reduce the requirement for data page flushing. Redolog records the modifications on the data page, but when the data page is also flushed back to the disk, these records become useless. Therefore, when MySQL determines that the previous redolog has expired, the new data will overwrite the invalid data. So how to judge whether it should be covered?

The above picture is a schematic diagram of redo log file. write pos represents the log sequence number LSN (log sequence number) currently recorded by redolog. When the data page has been flushed back to the disk, the LSN in the redo log file will be updated, indicating that the data before this LSN has been written to the disk. This LSN is the check point. The part between write pos and check point is the spare part of redolog, which is used to record new records; the part between check point and write pos is the modified part of the data page that redolog has recorded, but the data page has not been flushed back to the disk at this time. part. When the write pos catches up with the check point, it will first push the check point forward, vacate the position, and then record a new log.

When starting innodb, regardless of whether it was shut down normally or abnormally last time, recovery operations will always be performed. During recovery, the LSN in the data page will be checked first. If this LSN is smaller than the LSN in the redolog, that is, the write pos position, it means that the unfinished operations on the data page are recorded in the redolog, and then it will start from the nearest check point. , start synchronizing data.

Is it possible that the LSN in the data page is greater than the LSN in the redolog? The answer is of course possible. When this happens, the part beyond the redolog will not be redone, because this itself means that what has been done does not need to be redone.
The difference between redo log and binlog

##redo logbinlogFile sizeThe size of the redo log is fixed. Binlog can set the size of each binlog file through the configuration parameter max_binlog_size. Implementation methodThe redo log is implemented by the InnoDB engine layer, and not all engines have it. Binlog is implemented by the Server layer. All engines can use binlog logsRecording methodredo log is recorded in a loop writing method. When writing to the end, it will return to the beginning and write the log in a loop. binlog is recorded by appending. When the file size is larger than the given value, subsequent logs will be recorded to new filesApplicable scenariosredo log is suitable for crash recovery (crash-safe)binlog is suitable for master-slave replication and data recovery

It can be seen from the difference between binlog and redo log: binlog log is only used for archiving, and relying only on binlog does not have crash-safe capabilities. But only redo log will not work, because redo log is unique to InnoDB, and the records in the log will be overwritten after being written to disk. Therefore, both binlog and redo log need to be recorded at the same time to ensure that the data will not be lost when the database is shut down and restarted.
Two-stage submission
The above briefly introduces redolog and binlog. When modifying data, they will save and implement these modifications, but one is a physical log and the other is a logical log. So how did they perform the modification process?

Suppose there is an update statement to be executed now, update from table_name set c=c 1 where id=2, the execution process is as follows:

• First locate id=2 This record;

• The executor gets the row data given by the engine, adds 1 to this value, gets a new row of data, and then calls the engine interface to write this new row of data;

• The engine updates this new row of data into the memory and records the update operation into the redolog. At this time, the redolog is in the prepare state. Then inform the executor that the execution is completed and the transaction can be submitted at any time;

• The executor generates the binlog of this operation and writes the binlog to the disk;

• #The executor calls the engine's commit transaction interface, and the engine changes the redo log just written to the commit state, and the update is completed;

The schematic diagram is as follows:

This process of splitting redolog writing into two steps: prepare and commit is called two-phase commit.

Both redolog 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. If you don't use two-phase commit, but write one first and then the other, it may cause some problems.

At this time, update is still used as an example. Assume that the current id=2 and there is a field c=0. Analyze the following situations respectively:
Write redolog first and then binlog
Assume that redolog is written first. When redolog is finished, but binlog has not yet been written. When I finished writing, MySQL suddenly encountered an exception and restarted. Since the redolog has been written before, the modified records still exist after the system is restarted, so the value of c in this line after recovery is 1. However, due to the system restart, this record does not exist in the binlog. When backing up the log later, this statement does not exist in the saved binlog. Then you will find that if you need to use this binlog to restore the temporary library, because the binlog of this statement is lost, the temporary library will not be updated this time. The value of c in the restored row is 0, which is the same as the value of the original library. different.
Write binlog first and then redolog
If you write binlog first and then restart the system when writing redolog. After restarting, there is no record of modification of c in the redolog, and the value of c is still 0 at this time. But the log "Change c from 0 to 1" has been recorded in the binlog. Therefore, when binlog is used to restore later, one more transaction will come out. The value of c in the restored row is 1, which is different from the value in the original database.

Therefore, to sum up, if a log is written first and then another log is written, the status of the database will be inconsistent with the status of the library restored using binlog.

3. undo log

undolog is mainly used to record the status before a certain row record is modified. It records the data before modification. Use undolog to restore records to the state before the transaction started when the transaction is rolled back. The atomicity and durability of transactions are also achieved by undolog. The undo log mainly records the logical changes of the data. For example, an INSERT statement corresponds to a DELETE undo log. For each UPDATE statement, it corresponds to an opposite UPDATE undo log, so that when an error occurs, it can be rolled back to before the transaction. data status. During the data recovery process, the combination of binlog and redolog can ensure the accuracy of data recovery.

The function process of undolog is as follows:

• Write the pre-modified version to the undo log before the transaction starts;

• Start the modification and save the modified data to the memory;

• Persist the undolog to the disk;

• Flash the data page back to the disk;

• Transaction submission;

Like redolog, undolog also needs to be Flush data pages back to disk. If the undolog is complete, the information recorded in it can be used to roll back the transaction to recover the data.

In a transaction, the same piece of data may be modified multiple times, so should the record before each modification be recorded in the undolog? In this case, the amount of undolog logs will be too large, and redolog will come into play at this time. In a transaction, if the same record is modified, undolog will only record the original record before the transaction starts. When this record is modified again, redolog will record subsequent changes. During the data recovery process, data recovery is completed by coordinating the functions of redolog and undolog, and rolling forward and rollback operations. The process is as follows:

The above is the detailed content of How to use MySQL's binlog, redo log and undo log. For more information, please follow other related articles on the PHP Chinese website!