Description:
Due to the fix below introduced in 5.7.41, mysqldump now executes a FLUSH TABLES before dumping the data. If mysqldump executes on a replica, it introduces a new GTID_EXECUTED entry, creating an errant GTID.
"The data and the GTIDs backed up by mysqldump were inconsistent when the options --single-transaction and --set-gtid-purged=ON were both used. It was because GTID_EXECUTED was fetched at the end of the dump, at which point the GTIDs on the server could have increased already. With this fixed, a FLUSH TABLES WITH READ LOCK is performed at the beginning of the dump and GTID_EXECUTED was fetched right after, to ensure its value is consistent with the snapshot taken by mysqldump."
How to repeat:
With mysqldump version 5.7.41, create a dump from a replica using GTID, and then run show master status and observe the errant GTID being introduced.
Behavior with mysqldump 5.7.40:
$ /opt/mysql/5.7.40/bin/mysqldump -umsandbox -pmsandbox --socket=/tmp/mysql_sandbox39437.sock --set-gtid-purged=OFF --add-drop-table --single-
transaction --events --routines=true test > test.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
$ ./use -e "show master status\G"
*************************** 1. row ***************************
File: mysql-bin.000002
Position: 154
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 00039436-1111-1111-1111-111111111111:1-19
Behavior with mysqldump 5.7.41:
$ /opt/mysql/5.7.41/bin/mysqldump -umsandbox -pmsandbox --socket=/tmp/mysql_sandbox39437.sock --set-gtid-purged=OFF --add-drop-table --single-
transaction --events --routines=true test > test.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
$ ./use -e "show master status\G"
*************************** 1. row ***************************
File: mysql-bin.000002
Position: 294
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 00039436-1111-1111-1111-111111111111:1-19,
00039437-2222-2222-2222-222222222222:1
$ /opt/mysql/5.7.41/bin/mysqldump -umsandbox -pmsandbox --socket=/tmp/mysql_sandbox39437.sock --set-gtid-purged=OFF --add-drop-table --single-
transaction --events --routines=true test > test.sql
Suggested fix:
Set sql_log_bin=0 in mysqdump before running the flush tables to avoid adding any entries to the binary log.
Description: Due to the fix below introduced in 5.7.41, mysqldump now executes a FLUSH TABLES before dumping the data. If mysqldump executes on a replica, it introduces a new GTID_EXECUTED entry, creating an errant GTID. "The data and the GTIDs backed up by mysqldump were inconsistent when the options --single-transaction and --set-gtid-purged=ON were both used. It was because GTID_EXECUTED was fetched at the end of the dump, at which point the GTIDs on the server could have increased already. With this fixed, a FLUSH TABLES WITH READ LOCK is performed at the beginning of the dump and GTID_EXECUTED was fetched right after, to ensure its value is consistent with the snapshot taken by mysqldump." How to repeat: With mysqldump version 5.7.41, create a dump from a replica using GTID, and then run show master status and observe the errant GTID being introduced. Behavior with mysqldump 5.7.40: $ /opt/mysql/5.7.40/bin/mysqldump -umsandbox -pmsandbox --socket=/tmp/mysql_sandbox39437.sock --set-gtid-purged=OFF --add-drop-table --single- transaction --events --routines=true test > test.sql mysqldump: [Warning] Using a password on the command line interface can be insecure. $ ./use -e "show master status\G" *************************** 1. row *************************** File: mysql-bin.000002 Position: 154 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 00039436-1111-1111-1111-111111111111:1-19 Behavior with mysqldump 5.7.41: $ /opt/mysql/5.7.41/bin/mysqldump -umsandbox -pmsandbox --socket=/tmp/mysql_sandbox39437.sock --set-gtid-purged=OFF --add-drop-table --single- transaction --events --routines=true test > test.sql mysqldump: [Warning] Using a password on the command line interface can be insecure. $ ./use -e "show master status\G" *************************** 1. row *************************** File: mysql-bin.000002 Position: 294 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 00039436-1111-1111-1111-111111111111:1-19, 00039437-2222-2222-2222-222222222222:1 $ /opt/mysql/5.7.41/bin/mysqldump -umsandbox -pmsandbox --socket=/tmp/mysql_sandbox39437.sock --set-gtid-purged=OFF --add-drop-table --single- transaction --events --routines=true test > test.sql Suggested fix: Set sql_log_bin=0 in mysqdump before running the flush tables to avoid adding any entries to the binary log.