Introduction to common logging issues in MySQL

This article brings you an introduction to common log issues in MySQL. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

There are two logs in MySQL, namely: redo log (redo log) and archive log (binlog). (Recommended course: MySQL Tutorial)

Among them, binlog can be used for the standby database, or it can be saved for restoring database historical data. It is implemented at the server layer and can be shared by all engines. The redo log is a log unique to InnoDB and is used to support crash-safe capabilities.

You must have heard of the two-phase submission of MySQL transactions, which means that when the transaction is submitted, it is divided into two stages: prepare and commit.

Figure 1 shows the execution process of a transaction. You can see in the last three steps that the redo log prepare is completed first, then the binlog is written, and finally the redo log commit stage is entered.

Figure 1 Two-stage submission diagram

Here, I want to explain a misunderstanding to you first: Isn’t this diagram the execution of an update statement? Process? How come the commit statement is called?

Usually, the reason why you have this question is that you confuse the two concepts of "commit":

In the question The "commit statement" refers to the command used to commit a transaction in MySQL syntax. Generally used in conjunction with begin/start transaction.

The "commit step" used in our picture refers to a small step in the transaction submission process and is also the last step. When this step is completed, the transaction is submitted.

When the "commit statement" is executed, it will include the "commit step".

In our example, the transaction is not explicitly started, so the update statement itself is a transaction. This "commit step" will be used when the transaction is submitted after execution.

Next, let’s analyze what happens when MySQL restarts abnormally at different times during the two-phase submission.

If a crash occurs at time A in the figure, that is, after the redo log is written in the prepare stage and before the binlog is written, the binlog has not been written yet. , the redo log has not yet been committed, so when recovering from a crash, the transaction will be rolled back. At this time, the binlog has not been written yet, so it will not be transferred to the standby database. At this point, we can all understand.

We understand that problems will occur mainly at time B, that is, after the binlog is written and a crash occurs before the redo log is committed, how will MySQL handle the crash recovery?

Let’s first look at the judgment rules for crash recovery.

1. If the transaction in the redo log is complete, that is, it already has a commit identifier, it will be submitted directly;

2. If the transaction in the redo log only has complete prepare, then Determine whether the corresponding transaction binlog exists and is complete:

a. If so, commit the transaction;

b. Otherwise, roll back the transaction.

Here, the crash occurring at time B corresponds to situation 2(a), and the transaction will be committed during the crash recovery process.

Now, let’s continue to extend the two-phase submission.

Question 1: How does MySQL know that the binlog is complete?

Answer: The binlog of a transaction has a complete format:

If you encounter both prepare and Commit the redo log, submit it directly;

If you encounter a redo log that only has parare but no commit, use the XID to go to the binlog to find the corresponding transaction.

Question 3: The redo log in the prepare stage plus the complete binlog can be restored after restarting. Why is MySQL designed in this way?

Answer: In fact, this question should be discussed in the proof by contradiction. The data received is related to the consistency of the backup. At time B, that is, after the binlog is written, MySQL crashes. At this time, the binlog has been written, and will be used by the slave library (or the library restored using this binlog).

So, this transaction must also be submitted on the main database. Using this strategy, the data in the main database and the standby database are guaranteed to be consistent.

Question 4: If this is the case, why do we need two-stage submission? Simply finish writing the redo log first, and then write the binlog. When recovering from a crash, both logs must be complete. Is it the same logic?

Answer: In fact, two-phase commit is a classic distributed system problem and is not unique to MySQL.

If I have to cite a scenario to illustrate the necessity of doing this, it is the issue of transaction durability.

For the InnoDB engine, if the redo log submission is completed, the transaction cannot be rolled back (if this allows rollback, it may overwrite the updates of other transactions). And if the redo log is submitted directly, and then the binlog fails to be written, InnoDB cannot roll back, and the data and binlog are inconsistent.

Two-stage submission is to give everyone a chance. When everyone says "I'm ok", submit together.

Question 5: Without introducing two logs, there is no need for two-phase submission. Isn’t it enough to only use binlog to support crash recovery and also support archiving?

Answer: If I translate this question again, it means that only the binlog is retained, and then the submission process can be changed to this:... -> "Update data to memory" -> "Write binlog " -> "Commit transaction", does it also provide the ability to recover from crashes?

The answer is no.

If we talk about historical reasons, it is that InnoDB is not the native storage engine of MySQL. The native engine of MySQL is MyISAM, which was not designed to support crash recovery.

Before InnoDB joined the MySQL engine family as a MySQL plug-in, it was already an engine that provided crash recovery and transaction support.

After InnoDB was connected to MySQL, I found that since the binlog did not have the ability to recover from crashes, it was better to use InnoDB’s original redo log.

And if we talk about implementation reasons, there are many. As mentioned in the question, only binlog is used to implement the crash recovery process. I drew a schematic diagram, and there is no redo log here.

Figure 2 Only binlog supports crash recovery

Under such a process, binlog still cannot support crash recovery. Let me talk about a point that is not supported: binlog does not have the ability to restore "data pages".

If at the marked position in the figure, that is, when binlog2 has been written but the entire transaction has not yet been committed, MySQL crashes.

After restarting, engine internal transaction 2 will be rolled back, and then binlog2 can be applied to make it up; but for transaction 1, the system has already considered the submission completed and will not apply binlog1 again.

However, the InnoDB engine uses WAL technology. When executing a transaction, the transaction is completed after writing to the memory and log. If it crashes later, rely on the log to recover the data pages.

That is to say, if a crash occurs at this location in the figure, transaction 1 may also be lost, and the data page level will be lost. At this time, the update details of the data page are not recorded in the binlog and cannot be restored.

If you want to say it, can I optimize the content of binlog and let it record changes in the data page? Yes, but this is actually just making another redo log.

So, at least the current binlog capability cannot support crash recovery.

Question 6: Can it be reversed and only use redo log instead of binlog?

Answer: It is ok if only from the perspective of crash recovery. You can turn off binlog so that there is no two-phase commit, but the system is still crash-safe.

However, if you look at the usage scenarios of various companies in the industry, you will find that binlog is always turned on in official production libraries. Because binlog has functions that redo log cannot replace.

One is archiving. Redo log is written in a loop. When writing to the end, you have to go back to the beginning and continue writing. In this way, the historical log cannot be retained, and the redo log cannot serve as an archive.

One is that the MySQL system relies on binlog. Binlog is a function that MySQL has from the beginning and is used in many places. Among them, the basis of high availability of MySQL system is binlog replication.

There are also many companies that have heterogeneous systems (such as some data analysis systems), and these systems rely on consuming MySQL binlog to update their own data. If binlog is turned off, these downstream systems will not be able to input.

In short, since many system mechanisms including MySQL high availability now rely on binlog, "dove occupying the magpie's nest" redo log is not yet possible. You see, how important it is to develop an ecology.

Finally, I recommend you to pay attention to Ding Qi’s "MySQL Practical Lectures 45" column. In the column, Ding Qi will help you sort out the main knowledge of learning MySQL, such as transactions, indexes, locks, etc., and will also analyze and discuss with you specific problems often encountered during the development process, and help you understand the essence behind the problems. . You will gain detailed explanations and principles of MySQL core technology and analysis of 36 common MySQL pain points. The binlog in

statement format will have COMMIT at the end; the binlog in

row format will have an XID event at the end.

In addition, after MySQL version 5.6.2, the binlog-checksum parameter was also introduced to verify the correctness of the binlog content. For binlog logs that may have errors in the middle of the log due to disk reasons, MySQL can find out by verifying the checksum results. Therefore, MySQL still has a way to verify the integrity of the transaction binlog.

Question 2: How are redo log and binlog related?

Answer: They have a common data field called XID. During crash recovery, the redo log will be scanned in order:

The above is the detailed content of Introduction to common logging issues in MySQL. For more information, please follow other related articles on the PHP Chinese website!