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

This article mainly introduces "how to use binlog, redo log and undo log of MySQL". In daily operation, I believe many people have doubts about how to use binlog, redo log and undo log of MySQL. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts about "how to use binlog, redo log and undo log of MySQL". Next, please follow the editor to study!

1 、 binlog

Binlog is used to record information about written operations (excluding queries) performed by the database and is stored on disk in binary form. Binlog is the logical log of mysql and is recorded by the Server layer, and binlog logs are recorded in the mysql database using any storage engine.

Logical log: can be simply understood as a sql statement

Physical log: the data in MySQL is saved in the data page, and the physical log records the changes on the data page; insert the code chip here

Binlog is written by appending. You can set the size of each binlog file through the max_binlog_size parameter. When the file size reaches a given value, a new file is generated to save the log.

Binlog usage scenario

In the practical application of the project, there are two main usage scenarios of binlog, namely, master-slave replication and data recovery.

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

Data recovery: restore data by using the mysqlbinlog tool.

Principle of MySQL master-slave synchronization

Primary node binlog dump thread

When the slave node connects to the master node, the master node creates a log dump thread to send the contents of the binlog. When reading an operation in binlog, this thread locks the binlog on the master node, and when the read is complete, even before initiating to the slave node, the lock is released

Slave Node I to O Thread

When the start slave command is executed on the slave node, the slave node creates an Icano thread to connect to the master node and request the updated binlog in the master library. After receiving the update from the binlog dump process of the primary node, the iPot O thread saves it in the local relaylog.

Slave node SQL thread

The SQL thread is responsible for reading the contents of the relaylog, parsing them into specific operations and executing them, thus ensuring the consistency of the master-slave data.

Principle of Master-Slave synchronization in MySQL Database

The content of binlog

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

Format of binlog

There are three formats of binlog, which are statement, row and mixed. Before MySQL 5.7.7, the default is to use statement,MySQL 5.7.7. After that, row is used by default. The format of the log can be modified through binlog-format in the my.ini configuration file.

(1) statement: replication based on sql statements (statement-based replication,SBR). Every sql statement that modifies data is recorded in binlog.

Advantages: no need to record specific changes in a line, save space, reduce io, and improve performance

Disadvantages: when performing operations such as sysdate () or sleep (), it may lead to inconsistency between master and slave data.

(2) row: row-based replication,RBR based on row records, which does not record context-sensitive information about sql statements, but records the details of which record was modified.

Advantages: record the details of changes in each line in great detail, so that the data cannot be copied correctly

Disadvantages: because each entry records the details of the changes in great detail, this will result in a large amount of log content. Suppose you now have a update statement that modifies many records, and each change record is recorded in binlog. In particular, the alter table operation changes every row of records due to changes in the table structure, resulting in a surge in log volume.

(3) mixed: according to the above, statement and row have their own advantages and disadvantages, so there is a version of mixed, which mixes the two. Generally use statement format to save, when encounter statement can not solve, switch to row format to save.

In particular, as mentioned above, the new version (after MySQL 5.7.7) uses the row format by default, and the row here has also been optimized to record in statement format when encountered with alter table, while the rest of the operations still use row format.

Timing of binlog flushing disk

For InnoDB storage engine, binlog is recorded only when the transaction is committed, and the record is still in memory. MySQL uses sync_binlog to control the flushing time of binlog. The value range is 0mi N:

0: it is not forced to be brushed to the disk, and it is up to the system to decide when to write to the disk.

1: write binlog to disk after each submission

N: only every N transactions will binlog be written to disk

As you can see 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, so in practice, you can also increase the value appropriately at the expense of a certain amount of consistency to achieve better performance.

Physical file size of binlog

In the my.ini configuration file, you can configure the size of the binlog through max_binlog_size. When the log exceeds the size of the binlog file, the system will regenerate a new file to continue saving the file. When a transaction is large, or when there are more and more logs, what if it takes up too much physical space? MySQL provides a mechanism for automatic deletion, or in the my.ini configuration file, it can be solved by configuring the parameter expire_logs_days (in days). When this parameter is 0, it will never be deleted; if it is N, it will be deleted automatically after the N day.

2 、 redo log

Redolog is the proprietary logging system of the InnoDB engine. It is mainly used to achieve transaction persistence and crash-safe functions. Redolog is a physical log, which records the specific changes on the data page after the execution of the sql statement.

We all know that when MySQL is running, data is loaded from disk into memory. When the sql statement is executed to modify the data, the modified content is only temporarily stored in memory, and if there is a power outage or other conditions, these changes will be lost. Therefore, when the data is modified, MySQL will look for opportunities to brush these in-memory records back to disk. However, there is a performance problem, mainly in two aspects:

InnoDB interacts with disk on a page-by-page basis, and a transaction probably only modifies a few bytes on a page. If a complete data page is brushed back to disk, resources are wasted.

A transaction may involve multiple data pages, which are logically continuous and not physically contiguous, and the performance of using random IO is too poor

Therefore, MySQL designed redolog to record exactly what changes the transaction made to the data page, and then brushed the redolog back to disk. You may be wondering that you want to reduce io in the first place, but don't you add another io? The designers of InnoDB had this in mind at the beginning of their design. Redolog files are generally small and are sequential io when flushing back to disk, which has better performance than random io.

Basic concepts of redo log

Redolog consists of two parts, one is the in-memory log cache redolog buffer, and the other is the log file redolog file on disk. Each time a change is made to the data record, the changes are first written into the redo log buffer, and then the changes in memory are brushed back to the redo log file at the right time. This technique of writing log first and then disk is WAL (Write-Ahead Logging) technology. It should be noted that redolog flushes back to disk before the data page, clustered index, secondary index, undo page changes, all need to record redolog.

In the computer operating system, the buffer data under the user space (user space) can not be directly written to the disk, and the middle must pass through the operating system kernel space (kernel space) buffer (OS Buffer). Therefore, redo log buffer actually writes OS Buffer to redo log file, and then brushes it into redo log file through the system call fsync (), as follows:

Mysql supports three times when redo log buffer can be written to redo log file, which can be configured through the parameter innodb_flush_log_at_trx_commit. The values of each parameter are as follows:

The parameter value 0 (delay write) does not write the log in redo log buffer to os buffer when the transaction commits, but writes os buffer every second and calls fsync () to write to redo log file. In other words, when set to 0, the data written to disk is refreshed every second, and when the system crashes, 1 second of data is lost. 1 (real-time write, real-time brush) every time a transaction commits, it writes the log in redo log buffer to os buffer and calls fsync () to redo log file. In this way, no data is lost even if the system crashes, but because every commit is written to disk, IO has poor performance. 2 (real-time write, delayed brush) each commit is only written to os buffer, and then fsync () is called every second to write the logs in os buffer to redo log file.

Redo log record form

Redolog uses a fixed-size, circular write format, and when the redolog is full, it will start all over again. Why is it so designed?

The main purpose of redo log is to reduce the requirement of data page flushing. Redolog records changes on the data pages, but when the data pages are also brushed back to disk, these records are useless. So when MySQL determines that the previous redolog has lost its function, the new data will overwrite the invalid data. So how to judge whether it should be covered or not?

The figure above is a schematic diagram of redolog file, and write pos represents the log sequence number LSN (log sequence number) currently recorded by redolog. When the data page has also been flushed back to disk, the LSN in redo log file will be updated to indicate that the data before this LSN has been dropped to disk, and this LSN is check point. The part between write pos and check point is the spare part of redolog, which is used to record new records; between check point and write pos is the modified part of the data page that redolog has recorded, but at this time the data page has not been brushed back to disk. When write pos catches up with check point, it pushes check point forward to make room for a new log.

When you start innodb, a restore operation is always performed, regardless of whether the last shutdown was normal or abnormal. During recovery, the LSN in the data page will be checked first. If the LSN is less than the LSN in the redolog, that is, the write pos location, it means that the outstanding operations on the data page are recorded on the redolog, and then the data will be synchronized from the nearest check point.

Is it possible that the LSN in the data page is greater than the LSN in redolog? The answer is yes, of course. When this happens, the part that goes beyond the redolog will not be redone, because this in itself represents what has already been done and does not need to be redone.

The difference between redo log and binlog

The size of the redo logbinlog file size redo log is fixed. Binlog sets the size of each binlog file through the configuration parameter max_binlog_size. Redo log is implemented by the InnoDB engine layer and is not available to all engines. Binlog is implemented in the Server layer, and all engines can use binlog logging. Redo log records in a circular way. When writing to the end, it will go back to the beginning and cycle to write the log. Binlog records by appending. When the file size is larger than the given value, subsequent logs will be recorded on the new file. Redo log is suitable for crash recovery (crash-safe) binlog is suitable for master-slave replication and data recovery

From the difference between binlog and redo log, we can see that binlog logs are only used for archiving and there is no crash-safe capability to rely on binlog alone. But not only redo log, because redo log is unique to InnoDB, and the records in the log will be overwritten when the disk is set. 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 down and restarted.

Two-phase submission

The above briefly introduced redolog and binlog, when making changes to the data, they will save these changes to the ground, but one is a physical log, the other is a logical log. So how did they implement it in the process of revision?

Suppose you now have a update statement to execute, update from table_name set c=c+1 where id=2, and the execution process is as follows:

Locate the id=2 record first.

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 the new data.

The engine updates the new line of data to memory and records the update operation to redolog, when the redolog is in the prepare state. Then inform the executor that the execution is complete and the transaction can be committed at any time.

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

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

The schematic diagram is as follows:

This process of splitting redolog writes into two steps, prepare and commit, is called a 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 do not use two-phase commit, but write one first and then the other may cause some problems.

Update is still used as an example at this time. Assuming the current id=2, there is a field called canti0, which analyzes the following situations:

Write redolog first and then binlog

Suppose you write redolog first, and when redolog is finished, but binlog is not finished, then an exception occurs in MySQL, which leads to a restart. Since the redolog has been written before, the modified record still exists after the system reboot, so the value of c on this line after recovery is 1. However, due to the system restart, there is no record of this in binlog. Later, when you back up the log, there is no such statement 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 missing, the temporary library will lose this update, and the value of the recovered line c is 0, which is different from the value of the original library.

Write binlog first and then redolog

If you write binlog first, and then write redolog, the system restarts. After the restart, there is no record of changes to c in redolog, and the value of c is still 0. But the log "change c from 0 to 1" has been recorded in binlog. So, when you use binlog to recover later, there is one more transaction, and the value of the recovered row c is 1, which is different from the value of the original library.

Therefore, to sum up, if you write one log first and then another, the state of the database will be inconsistent with that of the library recovered using binlog.

3 、 undo log

Undolog is mainly used to record the status of a row record before it is modified, and to record the data before modification. In this way, when the transaction is rolled back, the record can be restored to the way it was before the transaction started through undolog. The atomicity and persistence of transactions also rely on undolog. Undo log mainly records the logical changes of the data, such as an INSERT statement corresponding to a undo log of DELETE, and for each UPDATE statement, a undo log of the opposite UPDATE, so that when an error occurs, it can be rolled back to the data state before the transaction. At the same time, when data recovery is carried out, it is combined with binlog,redolog to ensure the correctness of data recovery.

The flow of undolog is as follows:

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

Start making changes to save the modified data in memory

Persist undolog to disk

Brush the data page back to disk

Transaction commit

It is important to note that, like redolog, undolog goes back to disk before the data page is brushed. When recovering data, if the undolog is complete, you can roll back the transaction according to the undolog.

The same piece of data may be modified multiple times in a transaction, so do the records before each change have to be recorded in undolog? In this case, the number of undolog logs will be too large, and redolog will play at this time. In a transaction, if a change is made to the same record, undolog only records the original record before the transaction starts, and when the record is modified again, redolog records subsequent changes. During the data recovery, redolog completes the roll forward, undolog completes the rollback, and the two coordinate to complete the data recovery. The process is as follows:

At this point, the study on "how to use binlog, redo log and undo log of MySQL" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.