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

What this article shares to you is about how to use the binlog log in MySQL. The editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

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: it can be simply understood that what is recorded is the sql statement.

Physical log: because the mysql data is ultimately saved in the data page, the physical log records changes to the data page.

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 practical application, 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.

Timing of binlog flushing disk

For the InnoDB storage engine, the biglog is recorded only when the transaction is committed, and the record is still in memory, so when did the biglog be flushed to disk? Mysql controls the timing of biglog flushing through the sync_binlog parameter, and the value range is 0murn:

0: no mandatory requirement, and it is up to the system to decide when to write to disk.

1: write binlog to disk every time you commit

N: for every N transactions, the binlog is 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.

Recommended reading: disassembly of Java interview questions

Binlog log format

Binlog logs are available in three formats, STATMENT, ROW, and MIXED.

Before MySQL 5.7.7, the default format was after STATEMENT,MySQL 5.7.7, and the default value was ROW. The log format is specified by binlog-format.

STATMENT

Based on the replication of SQL statements (statement-based replication, SBR), each sql statement that modifies the data is recorded in binlog.

Advantages: no need to record the changes of each line, reduce the number of binlog logs, save IO, and improve performance

Disadvantages: in some cases, it can lead to inconsistent master-slave data, such as executing sysdate (), slepp (), and so on.

ROW

Row-based replication (row-based replication, RBR) does not record the context information of each sql statement, only which piece of data has been modified.

Advantages: there is no problem that the calls and triggers of stored procedures, or function, or trigger can not be copied correctly under certain circumstances.

Disadvantages: a large number of logs will be generated, especially in the case of alter table.

MIXED

Mixed replication based on STATMENT and ROW modes (mixed-based replication, MBR). General replication uses STATEMENT mode to save binlog, and ROW mode to save binlog for operations that cannot be replicated in STATEMENT mode.

Redo log

Why do you need redo log

As we all know, one of the four characteristics of a transaction is persistence, that is, as long as the transaction is successfully committed, the changes made to the database will be saved permanently, and it is impossible to return to the original state for any reason. So how does mysql ensure consistency? The easiest thing to do is to flush all the data pages that the transaction involves modification to disk each time the transaction commits. But there are serious performance problems in doing so, mainly in two aspects:

Because Innodb interacts with disk on a page-by-page basis, and a transaction is likely to modify only a few bytes in a data page, brushing the complete data page to disk at this time is a waste of resources!

A transaction may involve modifying multiple data pages, and these data pages are not physically contiguous, so write performance using random IO is too poor!

So mysql designed redo log, specifically to record only what changes the transaction made to the data page, which perfectly solved the performance problem (relatively small files and sequential IO).

Search the official account of Java bosom friend, reply to "back-end interview" and send you a treasure book of Java interview questions.

Basic concepts of redo log

Redo log consists of two parts: one is the in-memory log buffer (redo log buffer), and the other is the log file on disk (redo log file). Every time mysql executes a DML statement, it first writes records to redo log buffer, and then writes multiple operation records to redo log file at a later point in time. This technique of writing log first and then disk is the WAL (Write-Ahead Logging) technology that is often mentioned in MySQL.

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:

Redo log record form

As mentioned earlier, redo log actually records changes to the data page, but it is not necessary to save all of these changes, so the redo log implementation uses a fixed size and circular write mode. When writing to the end, it will go back to the beginning and write the log cycle. As shown below:

At the same time, it is easy to know that in innodb, there are both redo log and data pages that need to be flushed. The main purpose of redo log is to reduce the requirements for data pages. In the figure above, write pos represents the LSN (logical serial number) location of the current record of the redo log, and check point represents the LSN (logical serial number) location of the redo log after the data page changes the record.

The part between write pos and check point is the empty part of redo log, which is used to record new records; between check point and write pos is the data page change record of the redo log disk to be dropped. 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. Because redo log records physical changes in data pages, recovery is much faster than logical logs such as binlog.

When restarting innodb, the LSN of the data page on disk is checked first, and if the LSN of the data page is less than the LSN in the log, the recovery starts from checkpoint.

In another case, the flushing process of checkpoint is going on before downtime, and the flushing progress of the data page exceeds that of the log page, and the LSN recorded in the data page is greater than the LSN in the log page. In this case, the part that exceeds the log progress will not be redone, because it means that something has been done and does not need to be redone.

The difference between redo log and binlog

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.

Undo log

One of the four major characteristics of database transactions is atomicity, specifically, atomicity refers to a series of operations on the database, either all successful or all failed, it is impossible to have partial success.

In fact, the bottom layer of atomicity is achieved through undo log. 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.

The above is how to use the binlog log in MySQL. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

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.