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

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

MySQL is a commonly used database storage application, we use it to store information, query information, and deal with transactions. In particular, transaction consistency, master-slave replication, data recovery and other functions are used to improve availability. When we use these functions, have we ever thought about the principles and mechanisms behind them? Today we focus on the logging mechanisms of redo log and binlog MySQL and how they work together to improve MySQL storage reliability. Today you will learn the following:

Redo log

What problem did Redo log solve?

The execution process of Redo log

The way Redo log is written

Redo log record form

Binlog

What problem did a.Binlog solve?

Log format of b.Binlog

The difference and Cooperation between Redo log and Binlog

Redo log

What problem did 1.Redo log solve?

Dealing with transactions is an important task in MySQL applications, and among the four features of transaction processing (ACID), there is a Durability, which means that during the execution of a transaction, all changes to the data must be saved to some physical storage device before the transaction ends successfully.

In other words, as long as the transaction commits successfully, the changes made to the database are saved permanently, and it is impossible to return to the original state for any reason. So why consider transaction persistence in MySQL? Suppose such a scenario, when the data storage transaction is being executed but the data has not yet been saved, and the database is down, then the data that has not been stored to disk is lost. If there is a mechanism to record the operation of the transaction, when the database service resumes, then the data that has not been stored in time can be saved correctly.

This is how Redo log achieves transaction persistence. In the above scenario, the database server is down, and if other failures result in dirty pages that have not been written to disk, they can also be restored through Redo log.

The execution process of 1.Redo log

After you understand why you use redo log, let's look at its execution process, as shown in figure 1:

Figure 1 redo log execution flow

The swimming lane diagram consists of the MySQL client, the MySQL Server layer, and the MySQL storage engine layer. Since redo log is used in the Innodb storage engine, it is assumed that the storage engine is Innodb. Because the MySQL Server layer is mainly responsible for the analysis, optimization and execution of SQL statements, while the MySQL storage engine layer is mainly responsible for storage, redo log also runs on this layer.

Follow the sequence number in the figure to see how redo log runs.

1. Request the statement "update T set aquired 1 where id=2" from the MySQL client and discover the MySQL Server layer.

two。 After receiving the SQL request, the MySQL Server layer will analyze, optimize and execute the SQL request, and send the generated SQL execution plan to the storage engine layer for execution.

3. The storage engine layer records the operation of "a to 1" in memory.

4. After the record is recorded in memory, the redo log record is modified, and a row is added with the content of "what changes need to be made on which data page".

5. After that, the state of the transaction is set to prepare, indicating that the transaction is ready to commit.

6. After the MySQL Server layer finishes processing the transaction, the state of the transaction is set to commit, that is, the transaction is committed.

7. After receiving the request for transaction commit, redo log will write to disk the operation record that has just been written to memory, thus completing the whole logging process.

The way 2Redo log is written

From the execution process of Redo log described above, it is not difficult to see that redo log writes its contents to memory before writing to disk. Therefore, the writing of redo log consists of two parts: one is the in-memory log buffer, called redo log buffer;, and the other is the disk log file, called redo log file. Every time MySQL executes a DML statement, the update record is written to redo log buffer and then to redo log file. This method of writing logs first and then writing disks is called WAL (Write-Ahead Logging) technology.

As shown in figure 2:

Figure 2 redo log write mode

Looking from left to right in the direction of the arrow, the log is initially written to the redo log buffer located in the storage engine Innodb, which is called user space, and then saved to the OS buffer in the operating system kernel space (kernel space).

Finally, write from OS buffer to redo log file on disk to complete the write operation, which is also called "flushing disk".

After understanding how redo log is written, we find that the main completion is the writing process from redo log buffer to redo log file on disk, which needs to be transferred through OS buffer. The timing when redo log buffer is written to redo log file can be configured by parameter innodb_flush_log_at_trx_commit. The values of each parameter are as follows:

When the parameter is 0, it is called "deferred write". The log in redo log buffer is not written to OS buffer when the transaction is committed, but is written to OS buffer every second and the call is written to redo log file. In other words, this method initiates a write to disk every second, assuming that the system crashes and only 1 second of data is lost.

When the parameter is 1, it is called "real-time write, real-time brush". Each time the transaction commits, the log in redo log buffer is written to OS buffer and saved to redo log file. The point is that even if the system crashes, it will not lose any data, and the disadvantage is obviously that every transaction commit has to be operated on disk, and the performance is poor.

When the parameter is 2, it is called "real-time write, delay brush". Each transaction commit is written to OS buffer, and then the log is written to redo log file every second. The performance is better this way, but the disadvantage is that the transaction data in 1 second will be lost in the event of a system crash.

3.Redo log record form

Redo log is saved through circular writes.

As shown in figure 3:

Figure 3 redo log circular writing (material from the Internet)

Redo log buffer (in memory) is made up of four files connected end to end, namely: ib_logfile_1, ib_logfile_2, ib_logfile_3, and ib_logfile_4.

The way to write is also to write from the head of the file (assuming), each additional log record will be added to the end of the file, until the four files are full, and then go back to the beginning of the file (ib_logfile_1) to continue to write, continue to write will overwrite the previous record.

In figure 3, the write pos represents the current write record location (the logical sequence location of the data page written to the disk), and the check point represents the corresponding position after the disk is brushed (written to disk). The part between write pos and check point is used to record new logs, which is the space left for new records. Between check point and write pos is the record of the disk to be brushed. If not, it will be overwritten by the new record.

When the write pos pointer catches up with the check point (that is, when the new record is about to overwrite the old record), it pushes the check point forward, urging it to brush the record to disk to make room for the new record.

When redo log buffer flushes disk according to check pint, disk storage is carried out on a page-by-page basis for the Innodb engine. A transaction may have one or more data pages, and each page modifies multiple bytes. A restore operation occurs when the Innodb storage engine is restarted. Because redo log records physical changes in data pages, recovery is faster than logical logs (binlog).

When restarting Innodb, the logical sequence position of the data page on disk is first checked, and if the logical sequence position of the data page is less than the position in the log, the recovery starts from check point. If you are in the process of flushing the disk in check point during the downtime, and the progress of the data page exceeds that of the log page, it will appear that the logical sequence recorded in the data page is greater than that in the log. At this time, the part that exceeds the log progress will not be redone, because it represents what has been done and does not need to be redone.

Binlog

What problem did 4.Binlog solve?

It is an eternal topic for MySQL database to increase the reliability of data, in which master-slave replication and data recovery are two important functions to enhance data reliability. Binlog is set up to implement these two functions.

In the master-slave replication scenario, binlog is enabled on the Master side, and then the binlog is sent to each Slave side, and the Slave side replays the binlog so that the data on the Slave side is consistent with that on the Master side. In the data recovery scenario, the data is restored to a specified point in time by using the mysqlbinlog tool and the corresponding binlog. Then the problems solved by binlog can be summarized into two points, that is, master-slave replication and data recovery.

Log format of 5.Binlog

From the point of view of the recording mode, binlog records by appending. When the log file size is larger than the given value, the subsequent logs will be recorded on the new file. This is in sharp contrast to redo log's circular record, and binlog can set the size of each binlog file through the configuration parameter max_binlog_size.

From the log format, there are three formats for Binlog logs, which are STATMENT, ROW, and MIXED.

Before MySQL 5.7.7, the default format was STATEMENT, and after MySQL 5.7.7, the default value was ROW. The log format is specified by binlog-format. The definitions, advantages and disadvantages of the three formats are as follows:

LSTATEMENT: a copy based on SQL statements (statement-based replication, SBR) that records modified SQL statements.

N advantages: because there is no need to record changes in each line of log, the log file is small, the log volume is reduced, IO is saved, and performance is improved.

N disadvantage: poor accuracy, some system rows can not be accurately copied, such as: now (), uuid ().

LROW: row-based replication (row-based replication, RBR), which does not record the context information of each SQL statement, but only the changes to the actual data per row.

N advantage: high accuracy, can accurately copy the changes of data.

N disadvantage: the resulting log files are larger, resulting in larger network IO and disk IO. In particular, the alter table will make the log soar.

LMIXED: mixed replication based on STATMENT and ROW modes (mixed-based replication, MBR), which is saved in STATEMENT mode by default, and ROW mode is used for operations that cannot be replicated in STATEMENT mode.

N advantages: high accuracy and moderate file size.

N disadvantage: the phenomenon of inconsistency between the main body may occur.

Events in the binlog log can be viewed through the "show binlog events" command in MySQL. As shown in code snippet 1, check the binlog log in the "mysql-bin.000002" file with the above command.

Mysql > show binlog events in 'mysql-bin.000002'

Code snippet 1

As shown in figure 4:

Figure 4 shows the contents of the binlog log

Display the corresponding binlog log events through the above command, from left to right as follows:

Log_name: describes the name of the file where the binlog log is stored.

Pos: describes where the journal starts.

Event_type: describes the time type, such as query, insert, etc.

Server_id: the ID of the corresponding database server.

End_log_pos: the location where the log ends.

Info: the SQL statement executed.

Above are the events for viewing the log, and you can also view the contents of the binlog through the mysqlbinlog command. As shown in snippet 2, view the contents of the mysql-bin.000002 through the mysqlbinlog command.

Mysql > mysqlbinlog 'mysql-bin.000002'

Code snippet 2

As shown in figure 5:

Figure 5 contents of the binlog log

We will intercept some of the results returned by the above view command to explain to you, and we will look at it from the top down:

"at 294" indicates the starting point of the "event", that is, from the 294th byte of the file.

"120330 17:54:46" represents the timestamp of the event.

"end_log_pos 388" indicates the byte position at which the log recording ends, that is, at the 388th byte end of the file.

"exec_time=28", indicating the time it takes for the event to execute.

"error_code=0" means that the error code is 0, that is, there are no errors.

"server id 1" represents the identity id of the server.

It is important to note that the transaction commit of binlog is to commit the transaction at once (a transaction contains one or more SQL statements). Redo log, on the other hand, can start gradually writing to disk after the transaction begins. So commit is fast for transactions, even for larger transactions, but when binlog is turned on, commits for larger transactions may become slower. Because the binlog transaction commit is an one-time write.

Difference and Cooperation between 6.Redo log and Binlog

Redo log and binlog were introduced earlier, so here is a summary of the differences between them in the following table.

Redo logBinlog applies to scenarios that apply to crash recovery (crash-safe). Suitable for master-slave replication and data recovery. The implementation of InnoDB engine layer is not available to all engines. Implemented in the Server layer, binlog logs can be used by all engines. Recording method redo log records in a circular way, and 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 configured value, subsequent logs will be recorded on the new file. File size the size of the redo log is fixed. Binl og sets the size of each binlog file through the configuration parameter max_binlog_size.

The difference between binlog and redo log shows that binlog logs are only used for archiving, but 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 log record 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.

So how do you keep the two logs consistent?

As shown in figure 5:

Figure 5 redo log and binlog transactions are consistent

The figure follows the example in figure 1, with a slight difference in adding a step. See that the part of the green dotted wireframe adds the step of writing binlog. When the transaction is in prepare state, between commit transactions, the log is saved to binlog, then committed to redo log in Innodb, and finally the operation of commit is completed.

Then focus on the state changes of redo log and binlog in both successful and failed submission cases.

As shown in figure 6:

Fig. 6 redo log and binlog state transition diagram (material from the Internet)

From top to bottom, first look at the part of the red line. When redo log is written and the transaction status is prepare, if the write is successful and the binlog is written directly, if the binlog write is also successful, the redo log status is set to commit. If you fail to write to binlog, roll back the transaction up the red arrow. Go back to the top and look at the green arrow. If the write redo log status is prepare, the write fails at this time, the binlog is no longer written, and the transaction is rolled back directly.

You can see that in order to maintain the consistency of the two logs, two commits are used. Redo log and binlog are two separate logics. If you don't need two-phase commit, either write redo log first and then write binlog, or write binlog first and then write redo log. See what's wrong with these two approaches:

Write redo log first and then binlog. Suppose that when the redo log is finished and the binlog is not finished, that is, the binlog has no updated statements in the transaction. At this point, MySQL restarts and uses binlog to recover the data. Because the previously updated statement does not save the database, there will be one less update, resulting in data inconsistency.

Write binlog first and then redo log. If the server goes down after binlog has been written, because redo log has not been written, that is, the data has not been written to the database. But it is already recorded in binlog, which means to record data that should not have been updated in the update. At this point, when the MySQL database is restarted, the data that should not be updated will be updated to the database, resulting in data inconsistency.

At this point, the study of "what is Redo log and Binlog" 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.