通过mysqlbinlog —skip-gtids后再用xtrabackup备份产生的一个奇怪问题

 
通过mysqlbinlog —skip-gtids后再用xtrabackup备份产生的一个奇怪问题
版本
[code][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 |
+------------+
[/code]
源库
[code]表结构与数据
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)
[/code]
源库再次应用一下已经执行过得binlog, 再次应用insert into users values(50); 这一条


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


[code][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 |
+------------------+----------+--------------+------------------+---------------------------------------------+
[/code]
源库Executed_Gtid_Set 已经到1299了
备份
[code]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!
[/code]
从备份输出信息和xtrabackup_binlog_info都可以看到,这个全备备份了1-1299
[code][root@uz22199 full2]# more xtrabackup_binlog_info 
mysql-bin.000005 617 5c351518-78ec-11e7-8e7a-005056a610c3:1-1299
[/code]
把备份随便搞到一个地方恢复出来
(恢复过程省略)
查看恢复出来的库的Executed_Gtid_Set
[code]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 |
+-------+
[/code]
如果此时我们直接将该库作为从库,change master到源库,那么start slave会报错,1299会再执行一边insert 50,会报1062错误.而如果我们flush binary logs一次,再做全备
[code]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
[/code]
Executed_Gtid_Set依旧是1-1299
再次将备份恢复出来,查看新恢复出来的库
[code]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 |
+------------------+----------+--------------+------------------+---------------------------------------------+
[/code]
此时恢复出来的库Executed_Gtid_Set为1-1299了疑问
为什么不flush logs备份,恢复出来的Executed_Gtid_Set会少?

 
已邀请:

Fan - 菜的抠脚

赞同来自:

Markdown here出来还是有问题,如果看不清可以看下面的连接
http://b2daf797.wiz03.com/share/s/2OSLun3V44CI2LVCy70ROCI32vDiMb1Po4Pc2bEvLW3L19KM
 

Fan - 菜的抠脚

赞同来自:

邮件和论坛两个月了,至今未收到答复,两位老师求助啊:)

Uest - A675-李选耀-广州

赞同来自:

说下个人的见解(GTID环境)
1、使用innobackupex的备份做从库,在还原后都需要通过xtrabackup_binlog_info或者xtrabackup_binlog_pos_innodb中的信息,SET @@GLOBAL.GTID_PURGED='uuid:seq';然后再做change master to操作
文中搭建的从库估计没有做set @@global.gtid_purged,才会导致原本执行过的GTID再次在从库应用
2、为什么两次还原出来后使用show master status得到的结果不一样?
恢复出来我们看到的Executed_Gtid_Set信息应该来自 mysql.gtid_executed,这个表的信息是如何记录的呢?
如果开启了binlog,在binlog切换时(flush binary logs/达到max_binlog_size/重启),将当前的GTID插入到mysql.gtid_executed表中
如果没有开启binlog,每个事务提交前,会执行一个insert操作
主库你本身开启了binlog,在你执行flush binary logs时会将当前的GTID信息写入到mysql.gtid_executed表。。。
so,后面的你懂的了
以上纯属个人见解,还望吴老师、叶老师能解释下~

要回复问题请先登录注册