Logging MySQL logging module-Mysql Tutorial-php.cn

Free learning recommendation: mysql video tutorial

Table of Contents

1. Introduction

2. Redo log

3. Binlog

4. Internal workflow

MySql Learning Column

1. Detailed explanation of MySQL infrastructure

2. MySQL index underlying data structure and algorithm

3. MySQL5.7 enables binlog logs, and Simple example of data recovery

4. MySQL log module

1. Introduction

MySQL has two important log modules:redo log (redo log) and binlog (archive log) .

redo log is the log of the InnoDB storage engine layer, and binlog is the log recorded by the MySQL Server layer. Both are logs that record certain operations, but the formats of the records are different.

2. redo log

redo log: also known as (redo log) file, used to record changes in transaction operations, what is recorded isThe value after the data is modified will be recorded regardless of whether the transaction is submitted.

In the event of media failure, the redo log file can come in handy. If the database loses power, the InnoDB storage engine will use the redo log to restore to the time before the power loss to ensure the integrity of the data.

When a record needs to be updated, the InnoDB engine will first write the record to the redo log and update the memory. At this time, the update is completed.

The InnoDB engine will update this operation record to the disk at the appropriate time, and this update is usually completed when the system is relatively idle to improve update efficiency.

This involves WAL That is Write-Ahead Logging technology, his key The point is Write the log first, then write to the disk.

InnoDB’s redo log has a fixed size. For example, it can be configured as a set of 4 files, and the size of each file is 1GB. Then a total of 4GB operations can be recorded.

The redo log will start writing from the beginning, and when it reaches the end, it will return to the beginning and write in a loop, as shown in the figure below.

write pos is the position of the current record. It moves backward while writing. After writing to the end of file No. 3, it returns to the beginning of file No. 0. .

check point is the current position to be erased, which also moves backward and circulates. Before erasing the record, the record must be updated to the data file. The area between

write pos and check point is the unused part, which can be used to record new operations.

If write pos catches up with check point, it means that the redo log record is full. At this time, no new updates can be performed. You have to stop and erase some records first. , push check point forward.

With redo log, InnoDB can ensure that even if the database restarts abnormally, previously submitted records will not be lost. This capability is called crash-safe.

Why use redo log?

If we perform DML operations on the database and directly write the executed SQL to the disk, when the writing concurrency is large, the pressure on data writing to the disk will have a certain impact,

When we insert the operation and find that the current non-leaf node has insufficient data on one page, we need to perform a paging algorithm, which will be less efficient;

When I use redo log logs, first write our DML operations into the log , through a "transfer station", and then write to the disk through check point when free, the efficiency will be much higher;

MySQL settings Redo Log

The size of innodb_log_buffer_size when writing: (default 8M)
innodb_log_file_size The size of the redo log file.
innodb_log_files_in_group specifies the number of files in the redo log file group, the default is 2
innodb_mirrored_log_groups specifies the number of log mirror file groups, the default is 1
innodb_log_group_home_dir specifies the location of the log file group The path, the default is ./, means it is in the data directory of the database
innodb_flush_log_at_trx_commit How to flush the logs in the log buffer to the log file when setting commit (value 0, 1, 2) Default 1

3. binlog

redo log is a log unique to the InnoDB engine, and Server The layer also has its own log, called binlog (archived log).

Why are there two logs?

Because there was no InnoDB engine in MySQL at the beginning. MySQL's own engine is MyISAM, but MyISAM does not have crash-safe capabilities, and binlog logs can only be used for archiving.

InnoDB is another company that introduced MySQL in the form of a plug-in. Since relying only on binlog does not have crash-safe capabilities, InnoDB uses another log system-that is, redo log to achieve crash-safe capability.

The two logs have the following three differences.

redo log is unique to the InnoDB engine; binlog is implemented by the Server layer of MySQL and can be used by all engines.
redo log is a physical log, which records "What modifications were made on a certain data page"; binlog is a logical log, which records this statement The original logic, such as "Add 1 to the c field of the row with ID=2".
The redo log is written in a loop, and the space will always be used up; the binlog can be written additionally. "Append writing" means that after the binlog file reaches a certain size, it will switch to the next one and will not overwrite the previous log.

4. Internal workflow

Take a table update statement as an example to take a look at the internal workflow of the executor and the InnoDB engine:

mysql> update T set c=c+1 where ID=2;

Copy after login

As shown in the figure below, the light box indicates that it is executed inside InnoDB, and the dark box indicates that it is executed in the executor:

The executor first looks for the engine to get the line ID=2. ID is the primary key, and the engine directly uses tree search to find this row. If the data page where the ID=2 row is located is already in the memory, it will be returned directly to the executor; otherwise, it needs to be read into the memory from the disk and then returned.
The executor gets the row data given by the engine, adds 1 to this value, for example, it used to be N, but now it is N 1, 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 redo log. At this time, the redo log 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 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 last three steps seem a bit "circular". The writing of redo log is split into two steps: prepare and commit. In fact, this is "Two-stage commit".

Why does the log need "Two-phase commit"? This can be explained by proof by contradiction.

Since redo log and binlog are two independent logics, if two-stage submission is not required, either the redo log must be written first and then the binlog, or the reverse order must be adopted. Using the previous update statement as an example, let's see what problems there are in these two methods.

Assume that in the current row with ID=2, the value of field c is 0, and also assume that during the execution of the update statement, after the first log is written, a crash occurs before the second log is written. What happens?

1. Write redo log first and then binlog. Suppose that the MySQL process restarts abnormally when the redo log is finished but before the binlog is finished. After the redo log is written, even if the system crashes, the data can still be restored, so the value of c in this line after recovery is 1.

But because the binlog crashed before it was finished, this statement was not recorded in the binlog at this time. Therefore, when the log is backed up later, this statement will not be included 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, and the value of c in the restored line is 0, which is the same as the original The value of the library is different.

2. Write binlog first and then redo log. If there is a crash after the binlog is written, since the redo log has not been written yet, the transaction will be invalid after the crash recovery, so the value of c in this line is 0.

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.

It can be seen that if "two-phase commit" is not used, thenThe state of the database may be inconsistent with the state of the library restored using its log .

Related free learning recommendations: mysql database(Video)

The above is the detailed content of Logging MySQL logging module. For more information, please follow other related articles on the PHP Chinese website!