MySQL mysqldump GTID_PURGED Bug in 5.7.36 Causes Data‑GTID Mismatch During Replication

Background

During a migration of a small MySQL database, the author used mysqldump --single-transaction --master-data to export data and set up replication. After the dump, the replica’s SQL thread reported "update statement cannot find the row", even though the tables were InnoDB.

The author noticed that a row inserted during the backup was missing on the replica, suggesting that the backup missed some changes made while the dump was running.

Hypothesis

Inspecting the dump file revealed that the SET @@GLOBAL.GTID_PURGED statement, which normally appears at the beginning of the file, was instead placed at the end.

--
-- GTID state at the end of the backup 
--

SET @@GLOBAL.GTID_PURGED='cb75d9d8-5328-11ec-87b4-02000aba3c08:1-13637422';
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
... 
-- Dump completed on 2021-11-30 20:24:21

The MySQL version on the production server was 5.7.36, while the local test environment used 5.7.33, indicating a change in mysqldump behavior after 5.7.33.

Investigation

Release notes for 5.7.36 list a bug fix: the GTID sets were not persisted correctly after restoring a dump, and the dump file order was changed. A new option --skip-mysql-schema was added.

The author reproduced the issue locally:

Experiment with 5.7.33

Created a test database, populated it with a large amount of data, and performed a dump using 5.7.33. The SET @@GLOBAL.GTID_PURGED statement appeared at the beginning of the dump and the GTID matched the data.

mysql> create database test;
... (table creation and data insertion) ...
mysqldump -u -p -h -P --single-transaction --master-data=2 --set-gtid-purged=on -ER --databases test > 1.sql

Experiment with 5.7.36

Repeated the same steps with mysqldump 5.7.36. This time the SET @@GLOBAL.GTID_PURGED statement was at the end of the file and the GTID value did not include the rows inserted during the backup, leading to a mismatch.

... (similar dump command) ...

General logs showed that 5.7.33 fetched @@GLOBAL.GTID_EXECUTED before releasing the global lock, while 5.7.36 fetched it after the data dump, causing GTID values to be recorded after new transactions that were not backed up.

Conclusion

The change in the timing of GTID retrieval in mysqldump 5.7.36 results in dump files where the GTID set does not correspond to the backed‑up data, breaking replication after restore. Until the bug is fixed, the author advises using an older mysqldump version (e.g., 5.7.33) for reliable backups.

The issue has been reported to MySQL Bug Tracker: https://bugs.mysql.com/bug.php?id=105761 .