通过mysqlbinlog --skip-gtids恢复后再备份可能造成的坑
来源：互联网 发布：淘宝买全民晒单 编辑：程序博客网 时间：2024/06/05 17:17
通过mysqlbinlog –skip-gtids恢复后再备份可能造成的坑
版本
[root@uz22199 backup]# innobackupex --versioninnobackupex 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: usersCreate Table: CREATE TABLE `users` ( `email` varchar(10) DEFAULT NULL, UNIQUE KEY `email` (`email`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb41 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_Setroot@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 |mysqlroot@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.gz170907 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] ...done170907 18:45:15 [00] Streaming <STDOUT>170907 18:45:15 [00] ...donextrabackup: 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] ...done170907 22:00:58 [00] Streaming <STDOUT>170907 22:00:58 [00] ...donextrabackup: 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-1299Executed_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 |mysqlroot@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.gzMySQL 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] ...done170908 17:27:42 [00] Streaming <STDOUT>170908 17:27:42 [00] ...donextrabackup: 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阅读全文
0 0
- 通过mysqlbinlog --skip-gtids恢复后再备份可能造成的坑
- mysqlbinlog备库上的备份恢复
- mysqlbinlog恢复增量备份数据
- mysqlbinlog恢复的一个实例
- SQL数据库恢复后出现对象名无效(SQL Server备份还原时造成孤立用户的解决方案
- 结合mysqlbinlog与mysqldump进行MySQL数据备份与恢复（适用于较小的数据量）
- 结合mysqlbinlog与mysqldump进行MySQL数据备份与恢复（适用于较小的数据量）
- mysql备份与恢复笔记（mysqlbinlog部分）
- 将正在运行的项目war包重命名后再恢复原名造成问题的疑问
- mysqlbinlog基于datetime和position的恢复
- mysqlbinlog基于postion的数据恢复
- 数据备份恢复后的完整性检测
- 备份数据库恢复后的若干问题
- 数据库恢复备份后的回滚
- 恢复备份后创建的数据文件
- 使用mysqlbinlog工具通过日志文件恢复数据
- mysql 利用mysqlbinlog通过binlog增量备份，还原实例
- mysqldump全备份+mysqlbinlog日志备份实现将数据恢复到指定时刻
- 第二周项目1函数参数传递的三种方式
- shiro 学习
- PHP面向对象之简单工厂模式
- 客户端Socket概述与实例
- 分页池和非分页池的基本概念
- 通过mysqlbinlog --skip-gtids恢复后再备份可能造成的坑
- argc argv参数
- Linux服务器I/O
- 表单验证
- Test2 打印出所有的"水仙花数"
- crosstool-ng详解
- Maven使用—拷贝Maven依赖jar包到指定目录
- 简单记录一下sping线程池的配置和实现
- Android之多线程实现方式及并发与同步
