Shulou(Shulou.com)06/02 Report--

The usage of redolog and binlog in MySQL log system is believed to be at a loss for many inexperienced people. Therefore, this article summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

Previously, we learned about the execution flow of a query statement and introduced the processing modules involved in the execution process. The execution process of a query statement usually goes through functional modules such as connector, analyzer, optimizer, executor and so on, and finally reaches the storage engine.

So, what is the execution process of an SQL update statement?

First, we create a table user_info with the primary key id, and the creation statement is as follows:

CREATE TABLE `T` (`ID` int (11) NOT NULL, `c` int (11) DEFAULT NULL, PRIMARY KEY (`ID`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

Insert a piece of data:

INSERT INTO T VALUES ('2girls,' 1')

If you want to add 1 to the value of c in the ID=2 line, the SQL statement is:

UPDATE T SET c = c + 1 WHERE ID = 2

The basic execution link of the SQL statement was introduced earlier, so bring me that diagram here. Because the update statement will also go through the process of the query statement.

Through the connector, the client establishes a connection with the MySQL

The update statement empties all query cache results on the T table.

The parser recognizes that this is an update statement through lexical and grammatical analysis.

The optimizer will decide to use the ID index (clustered index)

The executor is responsible for the specific execution, finds the matching line, and then updates

Redo log (redo log) and binlog (archive log) operations are also involved in the update process.

By default, these two kinds of logs are in the data directory of the database. Redo log is in ib_logfile0 format and binlog is in xxx-bin.000001 format.

Next, let's take a look at redo log and binlog in the logging module, respectively.

Log module: redo log

In MySQL, if every update operation needs to be written to disk, and then the disk has to find the corresponding record, and then update, the whole process IO cost, search cost is very high. In order to solve this problem, the designers of MySQL adopted redo log to improve update efficiency.

The whole process of log and disk coordination is actually the WAL technology in MySQL. The full name of WAL is Write-Ahead Logging, and its key point is to write the log first and then write the disk.

Specifically, when there is a record that needs to be updated, the InnoDB engine first writes the record to redolog (redolog buffer) and updates the memory (buffer pool), and the update is complete. At the same time, the InnoDB engine updates the operation record to disk (scrubbing dirty pages) at an appropriate time (such as when the system is idle).

Redo log is the log of the InnoDB storage engine layer, also known as the redo log file. Redo log is written in a loop, and redo log does not record the status of the updated data page, but records what changes have been made to the page.

Redo log is a fixed size, for example, can be configured as a group of 4 files, each file size is 1GB, then the log can record a total of 4GB operations. Write from the beginning, and then go back to the beginning cycle at the end of the write, as shown in the following figure.

The figure shows a group of 4-file redo log logs. Checkpoint is the current location to be erased. Before erasing the record, you need to put the corresponding data on disk (update the memory page and wait for the dirty page to be brushed). The part between write pos and checkpoint can be used to record new operations. If write pos and checkpoint meet, the redolog is full, and the database stops the execution of database update statements and synchronizes the redolog logs to disk instead. The part between checkpoint and write pos waits for the disk to fall (update the memory page first, and then wait for the dirty page to be brushed).

With the redo log log, when the database is restarted abnormally, it can be recovered according to the redo log log, which achieves the crash-safe.

Redo log is used to guarantee crash-safe capabilities. When the innodb_flush_log_at_trx_commit parameter is set to 1, the redo log of each transaction is persisted directly to disk. It is recommended to set this parameter to 1 to ensure that data is not lost after an abnormal restart of MySQL.

Log module: binlog

MySQL as a whole, there are actually two pieces: one is the Server layer, which mainly does things at the functional level of MySQL, and the other is the engine layer, which is responsible for storage-related specific matters. Redo log is unique to the InnoDB engine, while the Server layer also has its own log, called binlog (Archive Log).

Binlog belongs to the logical log, which records the original logic of this statement in binary form. There is no crash-safe ability to rely on binlog.

Binlog has two modes, statement format is to remember the sql statement, row format will record the contents of the line, write down two, both before and after the update.

When the sync_binlog parameter is set to 1, the binlog of each transaction is persisted to disk. This parameter is also recommended to be set to 1 to ensure that binlog is not lost after an abnormal restart of MySQL.

Why are there two journals?

Because at first there was no InnoDB engine in MySQL. MySQL comes with an engine of MyISAM, but MyISAM does not have the capability of crash-safe, and binlog logs can only be used for archiving. InnoDB is another company that introduced MySQL in the form of plug-ins, and since there is no crash-safe capability to rely on binlog alone, InnoDB uses another logging system, that is, redo log, to implement crash-safe capabilities.

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 that records what changes have been made on a data page; binlog is a logical log that records the original logic of the statement.

Redo log is written in a loop and the fixed space is used up; binlog can be appended to write. Append write means that when the binlog file is written to a certain size, it will switch to the next one and will not overwrite the previous log.

With a conceptual understanding of these two logs, let's look at the internal flow of the executor and the InnoDB engine when executing the update statement.

The actuator first goes to the engine to fetch the ID=2 line. ID is the primary key, and the engine uses a tree search to find this line. If the data page of the ID=2 line is already in memory, it is returned directly to the executor; otherwise, you need to read the memory from disk and then return it.

The executor takes the row data given by the engine, adds 1 to this value, for example, it was N, now it is Number1, gets a new row of data, and then calls the engine interface to write this new line of data.

The engine updates this new line of data to memory (InnoDB Buffer Pool) and records the update operation to redo log, when the redo log is in the prepare state. The executor is then informed 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, and the engine changes the redo log just written to the commit (commit) state, and the update is completed.

The following figure shows the execution flow chart of the update statement, with a gray box indicating that it is executed inside the InnoDB and a green box indicating that it is executed in the executor.

The write to redo log is split into two steps: prepare and commit, which is called two-phase commit (2PC).

Two-phase commit (2PC)

MySQL uses two-phase commit to mainly solve the problem of data consistency between binlog and redo log.

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. The following is a schematic diagram of the two-phase submission of MySQL:

Description of the principle of two-phase submission:

InnoDB redo log writes disk, InnoDB transaction enters prepare state.

If the previous prepare succeeds and binlog writes, then continue to persist the transaction log to binlog. If the persistence is successful, then the InnoDB transaction enters the commit state (write a commit record in redo log)

Note: a XID event is recorded at the end of each transaction binlog, indicating whether the transaction was committed successfully, that is, during the recovery process, the content after the last XID event of the binlog should be purge.

How to recover data from log-related problems?

Binlog records all logical operations in the form of appended writes. When you need to restore to a specified second, for example, you found that there was an erroneous deletion of the table at 12:00 this afternoon, and you need to retrieve the data, you can do this:

First, find the most recent full backup and restore it to the temporary library

Then, starting from the point in time of the backup, the backup binlog is taken out in turn and replayed to the moment before the table was mistakenly deleted at noon.

In this way, your temporary library is the same as the online library before it was deleted by mistake, and then you can take the table data out of the temporary library and restore it to the online library as needed.

How are redo log and binlog related?

Redo log and binlog have a common data field called XID. When the crash recovers, the redo log is scanned sequentially:

If you encounter a redo log with both prepare and commit, submit it directly.

If you encounter a redo log with only parepare but no commit, take the XID to binlog to find the corresponding transaction.

How does MySQL know that binlog is complete?

The binlog of a transaction is in full format:

Binlog in statement format, and finally COMMIT.

Binlog in row format, and finally there will be a XID event

After MySQL 5.6.2, the binlog-checksum parameter was also introduced to verify the correctness of the binlog content. For cases where binlog logs may make errors in the middle of the logs due to disk reasons, MySQL can find out by verifying the results of checksum. Therefore, MySQL has a way to verify the integrity of the transaction binlog.

How big is the general setting of redo log?

If the redo log is too small, it will soon be full, and then have to brush redo log forcefully, so that the power of the WAL mechanism can not be brought into full play.

If there are several TB disks, set redo log directly to 4 files, each file 1GB.

After the data is written, is it updated from redo log or from buffer pool?

In fact, redo log does not record the complete data of the data page, so it does not have the ability to update the disk data page on its own, so there is no case that the past data will be updated by redo log.

After the data page is modified, it is inconsistent with the data page of the disk, which is called dirty page. In the end, when the data is on the disk, it is to write the data pages in memory to the disk. This process has nothing to do with redo log.

In a crash recovery scenario, if InnoDB determines that a data page may have lost updates during crash recovery, it reads it into memory and asks redo log to update the memory contents. When the update is complete, the memory page becomes dirty and returns to the state of the first case.

What is redo log buffer? Do you want to modify the memory or write the redo log file first?

During the update process of a transaction, the log is written multiple times. For example, the following transaction:

Begin;INSERT INTO T1 VALUES ('1mm,' 1'); INSERT INTO T2 VALUES ('1mm,' 1'); commit

This transaction inserts records into two tables, and during the process of inserting data, the generated logs have to be saved first, but they cannot be written directly to the redo log file before commit.

So you need redo log buffer to come out, which is a piece of memory used to store redo logs first. That is, when the first insert is executed, the memory of the data is modified and the redo log buffer is written to the log.

However, actually writing the log to the redo log file is done when the commit statement is executed.

The following is the source code of some of the redo log buffer I intercepted:

/ * * redo log buffer * / struct log_t {char pad1 [cache _ LINE_SIZE]; offset#ifndef UNIV_HOTBACKUP char pad2 [cache _ LINE_SIZE] of the starting point of the remaining space in lsn_t lsn; ulint buf_free; / / buffer; LogSysMutex mutex; LogSysMutex write_mutex Char pad3 [cache _ LINE_SIZE]; FlushOrderMutex log_flush_order_mutex;#endif / *! UNIV_HOTBACKUP * / byte* buf_ptr; / / implicit buffer byte* buf; / / actual operating buffer bool first_in_use; ulint buf_size / / buffer size bool check_flush_or_checkpoint; UT_LIST_BASE_NODE_T (log_group_t) log_groups;#ifndef UNIV_HOTBACKUP / * * The fields involved in the log buffer flush @ {* / ulint buf_next_to_write; volatile bool is_extending; lsn_t write_lsn / *! < last written lsn * / lsn_t current_flush_lsn; lsn_t flushed_to_disk_lsn; ulint naughting filling flushesus; os_event_t flush_event; ulint nasty logarithmic ios; ulint n_log_ios_old Time_t last_printout_time; / * * Fields involved in checkpoints @ {* / lsn_t log_group_capacity; lsn_t max_modified_age_async; lsn_t max_modified_age_sync; lsn_t max_checkpoint_age_async; lsn_t max_checkpoint_age Ib_uint64_t next_checkpoint_no; lsn_t last_checkpoint_lsn; lsn_t next_checkpoint_lsn; mtr_buf_t* append_on_checkpoint; ulint checking checking pointwriting writes; rw_lock_t checkpoint_lock # endif / *! UNIV_HOTBACKUP * / byte* checkpoint_buf_ptr; byte* checkpoint_buf; / * @} * /}

Redo log buffer is essentially a byte array, but in order to maintain this buffer, you need to set up a lot of other meta data, all of which are encapsulated in the log_t structure.

Summary

This article mainly introduces the two most important logs in MySQL, the physical log redo log (redo log) and the logical log binlog (archive log). It also explains some log-related problems.

In addition, it also introduces two-phase commit (2PC), which is closely related to MySQL log system. Two-phase commit is a common scheme to solve the consistency problem of distributed system, similar to three-phase commit (3PC) and PAXOS algorithm.

After reading the above, have you mastered the usage of redolog and binlog in the MySQL log system? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.