Why Xtrabackup Does Not Need to Backup Binlog Files

In a company assessment question, the author explains why Xtrabackup does not require backing up MySQL binlog files.

MySQL’s internal two‑phase commit ensures consistency between the binlog and redo log during crash recovery: if a transaction’s redo log is prepared but not committed, the system checks the binlog to decide whether to commit or roll back.

Xtrabackup, after restoring a backup, performs a similar crash‑recovery step by replaying redo logs and committing or rolling back transactions. However, it does not need the binlog because it only has to guarantee two things during backup:

Consistency among non‑transactional data.

Consistency between the data and the binlog position.

During the global lock phase of a backup, Xtrabackup runs commands such as:

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 FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS command prevents the InnoDB log from being flushed to disk when innodb_flush_log_at_trx_commit is not 1, and SHOW MASTER STATUS obtains the binlog position.

The key point is that Xtrabackup only needs to ensure “data and binlog position consistency,” not full data‑binlog consistency. After backup, two requirements must be met:

The start position for a point‑in‑time recovery using binlog is correct, avoiding replay or omission of transactions.

The replication start position for a restored slave is correct, preventing duplicate or missing transactions.

During the two‑phase commit, the flush, sync, and commit queues are protected by exclusive locks. A large transaction may hold the commit lock for seconds, blocking the FTWRL (flush‑to‑write‑log‑record) operation until the commit finishes and the global lock is released.

Because of this locking, Xtrabackup’s backup redo log contains only two kinds of transactions: those already committed and those that have not started committing. There are no transactions left in the PREPARE state, so during recovery Xtrabackup does not need to verify whether a prepared transaction exists in the binlog.

Consequently, Xtrabackup does not need to back up the binlog files.