万答#5，binlog解析出来的日志为何无法恢复 mysql数据库sql

欢迎来到 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: 1Duplicates: 0Warnings: 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-gtidsDo 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/J6...
万答#12，MGR整个集群挂掉后，如何才能自动选主，不用手动干预
https://mp.weixin.qq.com/s/07...
『2021数据技术嘉年华·ON LINE』：《MySQL高可用架构演进及实践》
https://mp.weixin.qq.com/s/u7...
一条sql语句慢在哪之抓包分析
https://mp.weixin.qq.com/s/AY...
万答#15，都有哪些情况可能导致MGR服务无法启动
https://mp.weixin.qq.com/s/in...
技术分享 | 为什么MGR一致性模式不推荐AFTER
https://mp.weixin.qq.com/s/rN...
关于 GreatSQL GreatSQL是由万里数据库维护的MySQL分支，专注于提升MGR可靠性及性能，支持InnoDB并行查询特性，是适用于金融级应用的MySQL分支版本。
Gitee:
https://gitee.com/GreatSQL/Gr...
GitHub:
https://github.com/GreatSQL/G...
Bilibili：
https://space.bilibili.com/13...
微信&QQ群：
可搜索添加GreatSQL社区助手微信好友，发送验证信息“加群”加入GreatSQL/MGR交流微信群
【万答#5，binlog解析出来的日志为何无法恢复】QQ群：533341697
微信小助手：wanlidbc