通过mysqlbinlog –skip-gtids恢复后再备份可能造成的坑

版本

[root@uz22199 backup]# innobackupex --version
innobackupex version 2.4.8 Linux (x86_64) (revision id: 97330f7)
[root@uz22199 backup]# mysql -e"select @@version"
+------------+
| @@version  |
+------------+
| 5.7.18-log |
+------------+

源库

表结构与数据
root@mysqldb 21:51:  [fandb]> show create table users\G
*************************** 1. row ***************************
      Table: users
Create Table: CREATE TABLE `users` (
  `email` varchar(10) DEFAULT NULL,
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

root@mysqldb 18:43:  [fandb]> select* from users;
+-------+
| email |
+-------+
| 1     |
| 10    |
| 20    |
| 30    |
| 5     |
+-------+
插入一条数据
insert into users values(50);  --GTID=1297
再删掉
delete from users where email=50; ----GTID=1298

当前Executed_Gtid_Set
root@mysqldb 18:35:  [fandb]> show master status;
+------------------+----------+--------------+------------------+---------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                           |
+------------------+----------+--------------+------------------+---------------------------------------------+
| mysql-bin.000005 |      495 |              |                  | 5c351518-78ec-11e7-8e7a-005056a610c3:1-1298 |
+------------------+----------+--------------+------------------+---------------------------------------------+
1 row in set (0.00 sec)

源库再次应用一下已经执行过得binlog, 再次应用insert into users values(50); 这一条

这里先不考虑有没有可能这样子去恢复数据,只做实验

[root@test43100 backup]# mysqlbinlog --skip-gtids --include-gtids='5c351518-78ec-11e7-8e7a-005056a610c3:1297' mysql-bin.000005 |mysql

root@mysqldb 18:43:  [fandb]> select* from users;
+-------+
| email |
+-------+
| 1     |
| 10    |
| 20    |
| 30    |
| 5     |
| 50    |
+-------+

root@mysqldb 18:43:  [fandb]> show master status;
+------------------+----------+--------------+------------------+---------------------------------------------+
| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                          |
+------------------+----------+--------------+------------------+---------------------------------------------+
| mysql-bin.000005 |     617 |              |                  | 5c351518-78ec-11e7-8e7a-005056a610c3:1-1299 |
+------------------+----------+--------------+------------------+---------------------------------------------+

源库Executed_Gtid_Set 已经到1299了

备份

innobackupex --user=backup --password='backup' --stream=tar /tmp | gzip -> full.tar.gz


170907 18:45:15 Backup created in directory '/tmp/'
MySQL binlog position: filename 'mysql-bin.000005', position '617', GTID of the last change '5c351518-78ec-11e7-8e7a-005056a610c3:1-1299'
170907 18:45:15 [00] Streaming <STDOUT>
170907 18:45:15 [00]        ...done
170907 18:45:15 [00] Streaming <STDOUT>
170907 18:45:15 [00]        ...done
xtrabackup: Transaction log of lsn (3112759) to (3112768) was copied.
170907 18:45:16 completed OK!

从备份输出信息和xtrabackup_binlog_info都可以看到,这个全备备份了1-1299

[root@uz22199 full2]# more xtrabackup_binlog_info 
mysql-bin.000005        617     5c351518-78ec-11e7-8e7a-005056a610c3:1-1299

把备份随便搞到一个地方恢复出来
(恢复过程省略)
查看恢复出来的库的Executed_Gtid_Set

root@mysqldb 18:48:  [(none)]> show master status;
+------------------+----------+--------------+------------------+---------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                           |
+------------------+----------+--------------+------------------+---------------------------------------------+
| mysql-bin.000001 |      154 |              |                  | 5c351518-78ec-11e7-8e7a-005056a610c3:1-1298 |
+------------------+----------+--------------+------------------+---------------------------------------------+
1 row in set (0.00 sec)

虽然知道1298,但是50这条数据有了
root@mysqldb 18:43:  [fandb]> select* from users;
+-------+
| email |
+-------+
| 1     |
| 10    |
| 20    |
| 30    |
| 5     |
| 50    |
+-------+

如果此时我们直接将该库作为从库,change master到源库,那么start slave会报错,1299会再执行一边insert 50,会报1062错误.这就是坑

而如果我们flush binary logs一次,再做全备

root@mysqldb 21:51:  [fandb]> flush binary logs;
Query OK, 0 rows affected (0.19 sec)

root@mysqldb 21:59:  [fandb]> show master status;
+------------------+----------+--------------+------------------+---------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                           |
+------------------+----------+--------------+------------------+---------------------------------------------+
| mysql-bin.000006 |      194 |              |                  | 5c351518-78ec-11e7-8e7a-005056a610c3:1-1299 |
+------------------+----------+--------------+------------------+---------------------------------------------+
1 row in set (0.00 sec)

170907 22:00:58 Backup created in directory '/tmp/'
MySQL binlog position: filename 'mysql-bin.000006', position '194', GTID of the last change '5c351518-78ec-11e7-8e7a-005056a610c3:1-1299'
170907 22:00:58 [00] Streaming <STDOUT>
170907 22:00:58 [00]        ...done
170907 22:00:58 [00] Streaming <STDOUT>
170907 22:00:58 [00]        ...done
xtrabackup: Transaction log of lsn (3115326) to (3115335) was copied.
170907 22:00:58 completed OK!

[root@uz22199 full3]# more xtrabackup_binlog_info 
mysql-bin.000006        194    5c351518-78ec-11e7-8e7a-005056a610c3:1-1299

Executed_Gtid_Set依旧是1-1299

再次将备份恢复出来,查看新恢复出来的库

root@mysqldb 22:02:  [(none)]> show master status;
+------------------+----------+--------------+------------------+---------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                           |
+------------------+----------+--------------+------------------+---------------------------------------------+
| mysql-bin.000001 |      154 |              |                  | 5c351518-78ec-11e7-8e7a-005056a610c3:1-1299 |
+------------------+----------+--------------+------------------+---------------------------------------------+

此时恢复出来的库Executed_Gtid_Set为1-1299了

总结

那么要么以后通过mysqlbinlog –skip-gtids 恢复数据之后flush 一下binary logs;
要么恢复出来的库都手动根据xtrabackup_binlog_info去set global gtid_purged

又做了一次

源库:

root@mysqldb 17:14:  [(none)]> flush binary logs;
Query OK, 0 rows affected (0.24 sec)

root@mysqldb 17:16:  [(none)]> show master status;
+------------------+----------+--------------+------------------+---------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                           |
+------------------+----------+--------------+------------------+---------------------------------------------+
| mysql-bin.000008 |      194 |              |                  | 5c351518-78ec-11e7-8e7a-005056a610c3:1-1300 |
+------------------+----------+--------------+------------------+---------------------------------------------+

root@mysqldb 17:17:  [(none)]> select * from fandb.users order by 1;
+-------+
| email |
+-------+
| 1     |
| 10    |
| 20    |
| 30    |
| 5     |
| 50    |
| 60    |
+-------+
7 rows in set (0.00 sec)


root@mysqldb 17:23:  [(none)]> insert into fandb.users values(70);
Query OK, 1 row affected (0.06 sec)

root@mysqldb 17:23:  [(none)]> show master status;
+------------------+----------+--------------+------------------+---------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                           |
+------------------+----------+--------------+------------------+---------------------------------------------+
| mysql-bin.000008 |      448 |              |                  | 5c351518-78ec-11e7-8e7a-005056a610c3:1-1301 |
+------------------+----------+--------------+------------------+---------------------------------------------+
1 row in set (0.00 sec)

root@mysqldb 17:24:  [(none)]> delete from fandb.users where email=70;
Query OK, 1 row affected (0.05 sec)

root@mysqldb 17:24:  [(none)]> show master status;
+------------------+----------+--------------+------------------+---------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                           |
+------------------+----------+--------------+------------------+---------------------------------------------+
| mysql-bin.000008 |      702 |              |                  | 5c351518-78ec-11e7-8e7a-005056a610c3:1-1302 |
+------------------+----------+--------------+------------------+---------------------------------------------+
1 row in set (0.00 sec)

root@mysqldb 17:24:  [(none)]> select * from fandb.users order by 1;
+-------+
| email |
+-------+
| 1     |
| 10    |
| 20    |
| 30    |
| 5     |
| 50    |
| 60    |
+-------+
7 rows in set (0.00 sec)



mysqlbinlog --skip-gtids --include-gtids='5c351518-78ec-11e7-8e7a-005056a610c3:1301' /data/mysqldata/3306/binlog/mysql-bin.000008 |mysql

root@mysqldb 17:26:  [(none)]> show master status;
+------------------+----------+--------------+------------------+---------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                           |
+------------------+----------+--------------+------------------+---------------------------------------------+
| mysql-bin.000008 |      956 |              |                  | 5c351518-78ec-11e7-8e7a-005056a610c3:1-1303 |
+------------------+----------+--------------+------------------+---------------------------------------------+
1 row in set (0.00 sec)

root@mysqldb 17:26:  [(none)]> select * from fandb.users order by 1;
+-------+
| email |
+-------+
| 1     |
| 10    |
| 20    |
| 30    |
| 5     |
| 50    |
| 60    |
| 70    |
+-------+

8 rows in set (0.00 sec)


innobackupex --user=backup --password='backup' --stream=tar /tmp | gzip -> full4.tar.gz

MySQL binlog position: filename 'mysql-bin.000008', position '956', GTID of the last change '5c351518-78ec-11e7-8e7a-005056a610c3:1-1303'
170908 17:27:42 [00] Streaming <STDOUT>
170908 17:27:42 [00]        ...done
170908 17:27:42 [00] Streaming <STDOUT>
170908 17:27:42 [00]        ...done
xtrabackup: Transaction log of lsn (3121237) to (3121246) was copied.
170908 17:27:42 completed OK!




[root@uz22199 full4]# more xtrabackup_binlog_info 
mysql-bin.000008        956    5c351518-78ec-11e7-8e7a-005056a610c3:1-1303

[root@uz22199 full4]# more xtrabackup_binlog_pos_innodb 
mysql-bin.000008        956

恢复出来的库:

root@mysqldb 17:31:  [(none)]> show master status;
+------------------+----------+--------------+------------------+---------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                           |
+------------------+----------+--------------+------------------+---------------------------------------------+
| mysql-bin.000001 |      154 |              |                  | 5c351518-78ec-11e7-8e7a-005056a610c3:1-1300 |
+------------------+----------+--------------+------------------+---------------------------------------------+
1 row in set (0.00 sec)

root@mysqldb 17:31:  [(none)]> select * from fandb.users;
+-------+
| email |
+-------+
| 1     |
| 10    |
| 20    |
| 30    |
| 5     |
| 50    |
| 60    |
| 70    |
+-------+
8 rows in set (0.00 sec)

root@mysqldb 17:37:  [(none)]> reset slave all;
Query OK, 0 rows affected (0.03 sec)

root@mysqldb 17:38:  [(none)]> change master to master_host='10.4.3.100',master_port=3306,master_user='repl',master_password='repl',master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.26 sec)

root@mysqldb 17:38:  [(none)]> start slave;

Last_SQL_Error: Could not execute Write_rows event on table fandb.users; Duplicate entry '70' for key 'email', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000008, end_log_pos 417