Description:
slave global variables gtid_executed is reversed. after restore.
Is this specification or bug?
you recommend to add ' --flush-logs' ?
How to repeat:
###
### MASTER
###
[root@MASTER ~]# mysqldump --all-databases -p --triggers --routines --events > db.dmp
Enter password:
[root@MASTER ~]# grep 'INSERT INTO `gtid_executed` VALUES' db.dmp
INSERT INTO `gtid_executed` VALUES ('XXXXXXXX-YYYY-ZZZZ-AAAA-NNNNNNNNNNNN',1,10248);
[root@MASTER ~]# grep GLOBAL.GTID_PURGED db.dmp
SET @@GLOBAL.GTID_PURGED='XXXXXXXX-YYYY-ZZZZ-AAAA-NNNNNNNNNNNN:1-10669';
# mysql.gtid_executed and @@GLOBAL.GTID_PURGED have a diff.
# but MASTER is not a problem.
# It will be reflected in the automatic
###
### SLAVE
###
[root@SLAVE ~]# mysql -p < db.dmp
[root@SLAVE ~]# mysql -p -e 'select * from mysql.gtid_executed;'
Enter password:
+--------------------------------------+----------------+--------------+
| source_uuid | interval_start | interval_end |
+--------------------------------------+----------------+--------------+
| XXXXXXXX-YYYY-ZZZZ-AAAA-NNNNNNNNNNNN | 1 | 10248 |
+--------------------------------------+----------------+--------------+
[root@SLAVE ~]# mysql -p -e 'show global variables like "gtid_executed";'
Enter password:
+---------------+----------------------------------------------+
| Variable_name | Value |
+---------------+----------------------------------------------+
| gtid_executed | XXXXXXXX-YYYY-ZZZZ-AAAA-NNNNNNNNNNNN:1-10669 |
+---------------+----------------------------------------------+
[root@SLAVE ~]# /etc/init.d/mysql restart
Shutting down MySQL.. [ OK ]
Starting MySQL.. [ OK ]
[root@SLAVE ~]#
[root@SLAVE ~]# mysql -p -e 'select * from mysql.gtid_executed;'
Enter password:
+--------------------------------------+----------------+--------------+
| source_uuid | interval_start | interval_end |
+--------------------------------------+----------------+--------------+
| XXXXXXXX-YYYY-ZZZZ-AAAA-NNNNNNNNNNNN | 1 | 10248 |
+--------------------------------------+----------------+--------------+
[root@SLAVE ~]# mysql -p -e 'show global variables like "gtid_executed";'
Enter password:
+---------------+----------------------------------------------+
| Variable_name | Value |
+---------------+----------------------------------------------+
| gtid_executed | XXXXXXXX-YYYY-ZZZZ-AAAA-NNNNNNNNNNNN:1-10248 |
+---------------+----------------------------------------------+
global variables gtid_executed is reversed.
but 10249-10669 has already been written.
Replication error occurs.
Suggested fix:
mysqldump --all-databases -p --triggers --routines --events --flush-logs > db.dmp
Description: slave global variables gtid_executed is reversed. after restore. Is this specification or bug? you recommend to add ' --flush-logs' ? How to repeat: ### ### MASTER ### [root@MASTER ~]# mysqldump --all-databases -p --triggers --routines --events > db.dmp Enter password: [root@MASTER ~]# grep 'INSERT INTO `gtid_executed` VALUES' db.dmp INSERT INTO `gtid_executed` VALUES ('XXXXXXXX-YYYY-ZZZZ-AAAA-NNNNNNNNNNNN',1,10248); [root@MASTER ~]# grep GLOBAL.GTID_PURGED db.dmp SET @@GLOBAL.GTID_PURGED='XXXXXXXX-YYYY-ZZZZ-AAAA-NNNNNNNNNNNN:1-10669'; # mysql.gtid_executed and @@GLOBAL.GTID_PURGED have a diff. # but MASTER is not a problem. # It will be reflected in the automatic ### ### SLAVE ### [root@SLAVE ~]# mysql -p < db.dmp [root@SLAVE ~]# mysql -p -e 'select * from mysql.gtid_executed;' Enter password: +--------------------------------------+----------------+--------------+ | source_uuid | interval_start | interval_end | +--------------------------------------+----------------+--------------+ | XXXXXXXX-YYYY-ZZZZ-AAAA-NNNNNNNNNNNN | 1 | 10248 | +--------------------------------------+----------------+--------------+ [root@SLAVE ~]# mysql -p -e 'show global variables like "gtid_executed";' Enter password: +---------------+----------------------------------------------+ | Variable_name | Value | +---------------+----------------------------------------------+ | gtid_executed | XXXXXXXX-YYYY-ZZZZ-AAAA-NNNNNNNNNNNN:1-10669 | +---------------+----------------------------------------------+ [root@SLAVE ~]# /etc/init.d/mysql restart Shutting down MySQL.. [ OK ] Starting MySQL.. [ OK ] [root@SLAVE ~]# [root@SLAVE ~]# mysql -p -e 'select * from mysql.gtid_executed;' Enter password: +--------------------------------------+----------------+--------------+ | source_uuid | interval_start | interval_end | +--------------------------------------+----------------+--------------+ | XXXXXXXX-YYYY-ZZZZ-AAAA-NNNNNNNNNNNN | 1 | 10248 | +--------------------------------------+----------------+--------------+ [root@SLAVE ~]# mysql -p -e 'show global variables like "gtid_executed";' Enter password: +---------------+----------------------------------------------+ | Variable_name | Value | +---------------+----------------------------------------------+ | gtid_executed | XXXXXXXX-YYYY-ZZZZ-AAAA-NNNNNNNNNNNN:1-10248 | +---------------+----------------------------------------------+ global variables gtid_executed is reversed. but 10249-10669 has already been written. Replication error occurs. Suggested fix: mysqldump --all-databases -p --triggers --routines --events --flush-logs > db.dmp