万答#5,binlog解析出来的日志为何无法恢复
欢迎来到 GreatSQL社区分享的MySQL技术文章,如有疑问或想学习的内容,可以在下方评论区留言,看到后会进行解答
问题描述
问题来自一位群友,简单说就是用 mysqlbinlog 工具读取 binlog 欲进行恢复,却发现数据并没被恢复。
先一起来看下他是怎么做恢复的。
表中原来有几条数据,但不小心被清空了:
[yejr]> select * from t1;
+----+
| c1 |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
+----+
查看binlog event,有几条插入数据,最后还有一条 truncate table 的"误操作",现在想要把表数据恢复到误删数据前的状态。
[yejr]> show binlog events in 'binlog.000003';
+---------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
| binlog.000003 | 4 | Format_desc | 3306 | 125 | Server ver: 8.0.25-15, Binlog ver: 4 |
| binlog.000003 | 125 | Previous_gtids | 3306 | 196 | aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1:1-5 |
| binlog.000003 | 196 | Gtid | 3306 | 282 | SET @@SESSION.GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1:6' |
| binlog.000003 | 282 | Query | 3306 | 358 | BEGIN |
| binlog.000003 | 358 | Rows_query | 3306 | 405 | # insert into t1 select 1 |
| binlog.000003 | 405 | Table_map | 3306 | 454 | table_id: 91 (yejr.t1) |
| binlog.000003 | 454 | Write_rows | 3306 | 494 | table_id: 91 flags: STMT_END_F |
| binlog.000003 | 494 | Xid | 3306 | 525 | COMMIT /* xid=75 */ |
| binlog.000003 | 525 | Gtid | 3306 | 611 | SET @@SESSION.GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1:7' |
| binlog.000003 | 611 | Query | 3306 | 687 | BEGIN |
| binlog.000003 | 687 | Rows_query | 3306 | 734 | # insert into t1 select 2 |
| binlog.000003 | 734 | Table_map | 3306 | 783 | table_id: 91 (yejr.t1) |
| binlog.000003 | 783 | Write_rows | 3306 | 823 | table_id: 91 flags: STMT_END_F |
| binlog.000003 | 823 | Xid | 3306 | 854 | COMMIT /* xid=76 */ |
| binlog.000003 | 854 | Gtid | 3306 | 940 | SET @@SESSION.GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1:8' |
| binlog.000003 | 940 | Query | 3306 | 1016 | BEGIN |
| binlog.000003 | 1016 | Rows_query | 3306 | 1063 | # insert into t1 select 3 |
| binlog.000003 | 1063 | Table_map | 3306 | 1112 | table_id: 91 (yejr.t1) |
| binlog.000003 | 1112 | Write_rows | 3306 | 1152 | table_id: 91 flags: STMT_END_F |
| binlog.000003 | 1152 | Xid | 3306 | 1183 | COMMIT /* xid=77 */ |
| binlog.000003 | 1183 | Gtid | 3306 | 1269 | SET @@SESSION.GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1:9' |
| binlog.000003 | 1269 | Query | 3306 | 1345 | BEGIN |
| binlog.000003 | 1345 | Rows_query | 3306 | 1392 | # insert into t1 select 4 |
| binlog.000003 | 1392 | Table_map | 3306 | 1441 | table_id: 91 (yejr.t1) |
| binlog.000003 | 1441 | Write_rows | 3306 | 1481 | table_id: 91 flags: STMT_END_F |
| binlog.000003 | 1481 | Xid | 3306 | 1512 | COMMIT /* xid=78 */ |
| binlog.000003 | 1512 | Gtid | 3306 | 1596 | SET @@SESSION.GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1:10' |
| binlog.000003 | 1596 | Query | 3306 | 1693 | use `yejr`; truncate table t1 /* xid=87 */ |
运行下面的命令想要进行恢复数据,但发现不能正确恢复:
$ mysqlbinlog --start-position=4 --stop-position=1512 binlog.000003 | mysql -f yejr
已经指定了读取binlog的位置是从4开始,到1512截止,因为再下一个event就是"误操作"了。之后查询 yejr.t1 表数据还是空的,没有被正确恢复。
问题解决
首先再次强调线上操作要非常谨慎,如果只是普通的DML操作,建议事先打开事务模式,这样万一误操作了还能执行 rollback 回滚。
但如果是DDL操作,则基本上只能依赖备份或者延迟从库,所以务必再三确认是在测试环境还是线上环境,以及即将执行的命令影响如何。
再回到问题本身。
在执行 mysqlbinlog 解析binlog并尝试恢复时,观察新的binlog,确认没有写入新数据,说明确实没执行恢复操作。
解析binlog查看,并没有类似 SET SQL_LOG_BIN=0 等忽略binlog的设置,而如果复制SQL指令执行出来手动执行,却是可以成功的。说明binlog本身没问题,是其他原因导致的。
再查看 binlog event 时注意到启用了 GTID 模式,在 GTID 模式下有个特点是要求 全局唯一。
除了GTID值持续递增之外,有个不容易注意的特点是,当事务GTID在已执行过的GTID集合(Executed_Gtid_Set)中的话,就不会再次被执行。假设以下几种场景:
假设当前Executed_Gtid_Set集合是 1-10,如果下一个GTID是5,它在这个范围内,则无法被执行。
假设当前Executed_Gtid_Set集合是 1-10, 20-30,如果下一个GTID是13,不在这个范围内,则可以被执行。
分别做个测试:
# 查看当前gtid
[yejr]> show master status;
+---------------+----------+--------------+------------------+----------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+----------------------------------------------+
| binlog.000004 | 762 | | | aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1:1-15:20 |
+---------------+----------+--------------+------------------+----------------------------------------------+
# 下一个GTID在范围内,不会被执行
[yejr]> set session gtid_next='aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1:13';
[yejr]> insert into t1 select 6;
Query OK, 0 rows affected (0.00 sec) #<-- 0 rows
# 下一个GTID不在范围内,会被执行
[yejr]> set session gtid_next='aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1:17';
[yejr]> insert into t1 select 6;
Query OK, 1 row affected (0.01 sec) #<-- 1 row affected
Records: 1 Duplicates: 0 Warnings: 0
# 再次查看gtid
[yejr]> show master status;
+---------------+----------+--------------+------------------+-------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------------------------------------+
| binlog.000004 | 1091 | | | aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1:1-15:17:20 |
+---------------+----------+--------------+------------------+-------------------------------------------------+
验证结果确实如上所说。
知道原因就好办了,查看 mysqlbinlog 的帮助文档,查看选项 --skip-gtids 的解释:
--skip-gtids Do not preserve Global Transaction Identifiers; instead
make the server execute the transactions as if they were
new.
它可以使得解析出来的binlog event不再保留原来的 GTID 信息,这就可以当成新的 event 被执行了。所以,如果想要从binlog恢复数据,执行mysqlbinlog时,记得加上 --skip-gtids 选项。
全文完。
Enjoy MySQL 😃
文章推荐:
GreatSQL MGR FAQ
https://mp.weixin.qq.com/s/J6wkUpGXw3YkyEUJXiZ9xA
万答#12,MGR整个集群挂掉后,如何才能自动选主,不用手动干预
https://mp.weixin.qq.com/s/07o1poO44zwQIvaJNKEoPA
『2021数据技术嘉年华·ON LINE』:《MySQL高可用架构演进及实践》
https://mp.weixin.qq.com/s/u7k99y6i7riq7ScYs7ySnA
一条sql语句慢在哪之抓包分析
https://mp.weixin.qq.com/s/AYibbzl860D90rOeyjB6IQ
万答#15,都有哪些情况可能导致MGR服务无法启动
https://mp.weixin.qq.com/s/inSGpd0Q_XIl2Mb-VsvNsA
技术分享 | 为什么MGR一致性模式不推荐AFTER
https://mp.weixin.qq.com/s/rNeq479RNsklY1BlfKOsYg
关于 GreatSQL
GreatSQL是由万里数据库维护的MySQL分支,专注于提升MGR可靠性及性能,支持InnoDB并行查询特性,是适用于金融级应用的MySQL分支版本。
Gitee:
https://gitee.com/GreatSQL/GreatSQL
GitHub:
https://github.com/GreatSQL/GreatSQL
Bilibili:
https://space.bilibili.com/1363850082/video
微信&QQ群:
可搜索添加GreatSQL社区助手微信好友,发送验证信息“加群”加入GreatSQL/MGR交流微信群
QQ群:533341697
微信小助手:wanlidbc
本文由博客一文多发平台 OpenWrite 发布!