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

Editor to share with you what are the common log problems in MySQL. I hope you will gain a lot after reading this article. Let's discuss it together.

There are two logs in MySQL: the redo log (redo log) and the archive log (binlog).

Among them, binlog can be used by the standby database or saved to recover the historical data of the database. It is implemented in the server layer and can be shared by all engines. Redo log is a log unique to InnoDB and is used to support crash-safe capabilities.

You must have heard of the two-phase commit of a MySQL transaction, which means that when a transaction commits, it is divided into prepare and commit phases.

As shown in figure 1, the execution flow of a transaction, you can see in the last three steps that redo log is completed by prepare, then binlog, and finally enters the redo log commit phase.

Fig. 1 schematic diagram of two-phase submission

Here, I would like to explain to you a misunderstanding question: isn't this diagram the execution flow of a update statement? why is the commit statement called?

Usually, the reason you have this doubt is to confuse the two concepts of "commit":

The "commit statement" in the question refers to the command used to commit a transaction in MySQL syntax. It is usually paired with begin/start transaction.

The "commit step" used in our diagram refers to a small and final step in the transaction commit process. When this step is completed, the transaction is committed.

When the commit statement is executed, it contains the commit step.

In our example, there is no explicit opening of the transaction, so the update statement is itself a transaction, and this "commit step" is used when the transaction is committed after execution.

Next, let's analyze what happens when an abnormal restart of MySQL occurs at different times of the two-phase commit.

If a crash occurs at time An in the figure, that is, after the write redo log is in the prepare phase and before the binlog is written, the transaction will be rolled back when the crash resumes because the binlog has not been written and the redo log has not been committed. At this time, binlog has not been written, so it will not be transferred to the repository. At this point, we can all understand.

And we understand that there will be problems, mainly focused on time B, that is, when binlog is finished, and crash occurs before redo log is commit, what will MySQL do when it crashes and recovers?

Let's first take a look at the rules of judgment at the time of crash recovery.

1. If the transaction in the redo log is complete, that is, it already has the commit identity, commit it directly.

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

a. If so, commit the transaction

b. Otherwise, roll back the transaction.

Here, the occurrence of crash at time B corresponds to the case of 2 (a), and the transaction is committed during the crash recovery process.

Now, let's extend it a little further for the two-phase submission.

Question: how does 1:MySQL know that binlog is complete?

Answer: the binlog of a transaction has a full format:

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.

Question 3: the redo log in the prepare phase plus the full binlog can be restored by rebooting. Why is MySQL designed in this way?

Answer: in fact, this question is still related to the consistency of data and backup that we mentioned in the absurdity. At time B, that is, after binlog has finished writing, the MySQL crashes, by which time the binlog has been written, and then it will be used from the library (or the library recovered with this binlog).

Therefore, this transaction should also be committed on the main library. With this strategy, the data of the main database and the standby database are consistent.

Question4: if so, why two-phase submission? Just finish redo log first, then write binlog. When you recover from a crash, you must have both logs complete. Is it the same logic?

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

If there is one scenario that must be cited to illustrate the need for this, it is the persistence of transactions.

For the InnoDB engine, if the redo log commit is completed, the transaction cannot be rolled back (if this also allows rollback, updates for other transactions may be overwritten). If redo log commits directly, and then binlog fails to write, InnoDB cannot roll back, and the data is inconsistent with the binlog log.

The purpose of two-phase submission is to give everyone a chance to submit together when everyone says "I ok".

Question 5: without the introduction of two logs, there is no need for two-phase commit. Just use binlog to support crash recovery and archiving, isn't it?

Answer: if I translate this question again, I mean that only binlog can be retained, and then the submission process can be changed to this:.-> "data update to memory"-> "write binlog"-> "commit transaction". Can it also provide crash recovery?

The answer is no.

If there is a historical reason, it is that InnoDB is not the native storage engine of MySQL. The native engine of MySQL is MyISAM, and there is no support for crash recovery at the beginning of the design.

InnoDB was an engine that provided crash recovery and transaction support before it was added to the MySQL engine family as a plug-in to MySQL.

After InnoDB connects to MySQL, it is found that since binlog does not have the ability to crash and recover, it is better to use InnoDB's original redo log.

And if there are reasons for realization, there are many. According to the question, just use binlog to implement the crash recovery process, I drew a diagram, there is no redo log here.

Figure 2 crash recovery only supported by binlog

Under this process, binlog still cannot support crash recovery. Let me say one point that is not supported: binlog does not have the ability to recover the "data page".

If at the location marked in the diagram, that is, when binlog2 is finished, but the whole transaction does not have commit, crash occurs in MySQL.

After restart, engine internal transaction 2 will be rolled back, and then binlog2 can be applied to fix it; but for transaction 1, the system already thinks that the commit is complete, and binlog1 will not be applied again.

However, the InnoDB engine uses WAL technology, and when a transaction is executed, the transaction is completed after writing memory and logs. If it crashes later, rely on the log to recover the data page.

In other words, if a crash occurs at this location in the figure, transaction 1 may also be lost, and it is a loss at the data page level. At this time, binlog does not record the update details of the data page, which cannot be made up.

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

So, at least for now, the binlog capability doesn't support crash recovery.

Question 6: can it be the other way around and just use redo log instead of binlog?

Answer: it is possible only from the point of view 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 open on the official production repository. Because binlog has functions that cannot be replaced by redo log.

One is filing. Redo log is written in a loop, and you have to go back to the beginning to write at the end. In this way, the history log cannot be kept, and the redo log cannot be archived.

One is that the MySQL system depends on binlog. As a function of MySQL from the very beginning, binlog has been used in many places. Among them, the foundation of high availability of MySQL system is binlog replication.

There are also many companies that have heterogeneous systems (such as data analysis systems) that update their data by consuming MySQL's binlog. If you turn off binlog, these downstream systems will not be able to enter.

In short, because many system mechanisms, including the high availability of MySQL, rely on binlog, the "dove" redo log can't do it yet. You see, how important it is to develop ecology.

Finally, I recommend that you follow Ding Qi's "MySQL 45 lectures" 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 analyze and discuss with you the specific problems often encountered in the development process, and help you understand the nature behind the problems. You will get a detailed explanation and principle of MySQL core technology and 36 common pain points of MySQL.

Binlog in statement format, and finally COMMIT.

Binlog in row format will end up with a XID event.

In addition, after MySQL 5.6.2, the binlog-checksum parameter was 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 still has a way to verify the integrity of the transaction binlog.

How do the question 2:redo log and binlog relate?

Answer: they have a common data field called XID. When the crash recovers, the redo log is scanned sequentially:

After reading this article, I believe you have a certain understanding of the common log problems in MySQL, want to know more about it, 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.