Why Xtrabackup’s GTID Mismatch Happens: Deep Dive into MySQL 5.7 & 8.0 Backups

Background

Xtrabackup, an open‑source hot‑backup tool from Percona, creates a non‑blocking backup of InnoDB/XtraDB tables and writes the binlog position and GTID information to a file named xtrabackup_binlog_info. After restoring a backup, the SHOW MASTER STATUS command may display GTID values that do not match those stored in the xtrabackup_binlog_info file.

Xtrabackup 2.4.18 for MySQL 5.7.26

Phenomenon

<ol><li><code># cat xtrabackup_binlog_info</code></li><li><code>mysql-bin.000003    86412752    55d3d9b9-4d49-11ea-932c-02000aba3fa6:1-595859</code></li></ol>

<ol><li><code>mysql> show master status\G</code></li><li><code>File: mysql-bin.000001</code></li><li><code>Position: 154</code></li><li><code>Executed_Gtid_Set: 55d3d9b9-4d49-11ea-932c-02000aba3fa6:1-326661</code></li></ol>

The restored instance shows an Executed_Gtid_Set of 1-326661, while the backup file records 1-595859, suggesting a missing GTID range.

Root‑cause analysis

The backup flow of Xtrabackup 2.4 is roughly:

start backup

copy ibdata1 / .ibd files

execute ftwrl backup non‑InnoDB tables

write xtrabackup_binlog_info execute FLUSH NO_WRITE_TO_BINLOG execute UNLOCK TABLES copy ib_buffer_pool completed OK

During step 5 Xtrabackup runs SHOW MASTER STATUS to obtain the current binlog position and GTID, then writes them to xtrabackup_binlog_info. However, the gtid_executed table in MySQL 5.7 is not updated in real time; its contents depend on the log_bin and log_slave_updates settings. If log_bin is disabled or log_slave_updates is OFF, the table may be empty, causing SHOW MASTER STATUS to return incomplete GTID data.

Possible cases after restore

If log_bin is disabled or log_slave_updates is OFF, SHOW MASTER STATUS returns empty GTID.

If log_bin is enabled but the binlog has never rotated, the command also returns empty GTID.

If log_bin is enabled and a rotation has occurred, the GTID shown by SHOW MASTER STATUS may miss the range that the gtid_executed table did not record.

Xtrabackup 8.0.8 for MySQL 8.0.18

Phenomenon

<ol><li><code># cat xtrabackup_binlog_info</code></li><li><code>binlog.000033    1459    70ec927f-4c6d-11ea-b88c-02000aba3fb1:1-621683</code></li></ol>

<ol><li><code># mysqlbinlog -vv binlog.000033 | less</code></li><li><code>... GTID ... 70ec927f-4c6d-11ea-b88c-02000aba3fb1:621685 ...</code></li></ol>

<ol><li><code>mysql> show master status\G</code></li><li><code>File: binlog.000034</code></li><li><code>Position: 191</code></li><li><code>Executed_Gtid_Set: 70ec927f-4c6d-11ea-b88c-02000aba3fb1:1-621685</code></li></ol>

Here the GTID recorded in the binlog info file ( 1-621683) is slightly off, but after restoration the SHOW MASTER STATUS output matches the actual binlog position.

Root‑cause analysis

The backup flow for Xtrabackup 8.0 differs:

start backup

copy .ibd files

backup non‑InnoDB tables

execute FLUSH NO_WRITE_TO_BINLOG select LSN, binlog position and GTID from performance_schema.log_status copy the last binlog file

write binlog.index write xtrabackup_binlog_info execute FLUSH NO_WRITE_TO_BINLOG again

copy ib_buffer_pool completed OK

MySQL 8.0 provides the performance_schema.log_status table for online backup tools. When the server is idle, the table’s binlog position and GTID are consistent. Under heavy write load, the table may show mismatched values, but Xtrabackup 8.0 also copies the rotated binlog file itself. After restoration, the new instance reads both the gtid_executed table and the copied binlog, keeping GTID consistent with the original backup.

When non‑InnoDB tables (e.g., MyISAM) exist, Xtrabackup 8.0 performs an additional ftwrl after flushing, and the performance_schema.log_status values become consistent again.

Conclusion

With Xtrabackup 2.4, the GTID recorded in xtrabackup_binlog_info is accurate, but SHOW MASTER STATUS after restore may be inaccurate due to gtid_executed table behavior.

With Xtrabackup 8.0 on a pure InnoDB instance, the GTID in xtrabackup_binlog_info can be inaccurate, yet the restored instance shows the correct GTID because the copied binlog is also applied.

When non‑InnoDB tables are present, Xtrabackup 8.0 records accurate GTID information, and the restored instance’s SHOW MASTER STATUS matches it.

Note: “Accurate” here refers to whether the GTID recorded in xtrabackup_binlog_info matches the actual binlog position and data contained in the backup.