Technology Sharing | How does Xtrabackup not back up binlog to ensure consistency?

Author: Hu Chengqing

DBA team member, good at fault analysis and performance optimization, personal blog: 1613f05102ba9a https://www.jianshu.com/u/a95ec11f67a8, welcome to discuss.

Source of this article: original submission

* Produced by the Aikesheng open source community, original content is not allowed to be used without authorization, please contact the editor and indicate the source for reprinting.

---

There is an interesting question in the assessment questions from the company boss:

Known: MySQL's internal two-phase commit is to solve the consistency of binlog and redo log (in the process of crash recovery, if it is found that the redo log of a transaction has completed the prepare phase, but the commit has not been completed, it will be verified. Whether the transaction is in the binlog, if it exists, it will be committed, otherwise it will be rolled back).

It is also known that after Xtrabackup restores the backup, it will perform actions similar to crash recovery (play back the contents of the backup redo log into the data, and commit/roll back the transaction), so why does Xtrabackup not need to back up the binlog file?

After thinking about it, it doesn't seem to be possible to say clearly in one sentence, let me try to answer it.

answer:

Operations done in the global lock phase during backup:

2020-08-17T09:58:36.167905+08:00         2116 Query     FLUSH TABLES WITH READ LOCK
2020-08-17T09:58:36.490928+08:00         2116 Query     SHOW VARIABLES
2020-08-17T09:58:36.498670+08:00         2116 Query     SHOW SLAVE STATUS
2020-08-17T09:58:36.499435+08:00         2116 Query     SHOW MASTER STATUS
2020-08-17T09:58:36.499747+08:00         2116 Query     SHOW VARIABLES
2020-08-17T09:58:36.503341+08:00         2116 Query     FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS
2020-08-17T09:58:36.704273+08:00         2116 Query     UNLOCK TABLES

The most important thing here is to ensure two points:

• Consistency between non-transactional data;
• Consistency of data and binlog site.

Among them, FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS is to prevent innodb_flush_log_at_trx_commit from being not equal to 1, and the redo log is not flushed to the disk. The SHOW MASTER STATUS is to obtain binlog sites.

The key point is: xtrabackup only needs to ensure that "data and binlog are consistent", not "data and binlog are consistent".

The crash recovery process must ensure that "the data is consistent with the binlog", because after the crash, the transaction cannot be redo committed and the binlog is not recorded, which will result in the loss of data from the library. What do you need to consider for backup? There are two points:

• After the backup is restored, when performing accurate restoration based on binlog, the position of --start-position is correct, and things will not be replayed or missed;
• After the backup is restored, when copying data from the library to the main library, the copy start position is correct, and things will not be replayed or missed.

In fact, these two points are the same, which is to ensure that the "data and binlog locations are consistent" during backup. How is xtrabackup implemented?

First of all, we have to know that the three queues flush, sync, and commit in the two-phase commit process of the transaction all have exclusive locks. A large transaction commit may take a few seconds, then the execution of FTWRL will be blocked at this time, and it can be obtained after the commit ends. Global lock, after obtaining the global lock, the execution of commit will be blocked:

This ensures that there are only two transactions in the redo log backed up by xtrabackup: those that have been submitted and those that have not yet started (transactions that have not been committed may be flushed by the background thread), and there will be no transactions in the prepare state. There is another knowledge point: GTID generation and binlog writing are done in the binlog flush phase of the second phase submission. In combination, it means: execute show master status after FTWRL to get the binlog location, and only the completed transaction will be in it, so this ensures that the binlog location is consistent with the binlog.

Therefore, during the recovery process of xtrabackup, there is no need to process the transaction in the prepare state, and there is no need to verify whether the transaction is in the binlog.