前言

使用 binlog2sql 要求日志是ON状态 ，并且 binlog_format 要为 ROW 类型，否则本文不适用

数据恢复

1、查看binlog日志是否开启

show variables like 'log_bin';

2、确认当前binlog日志

mysql> show master status;

这里可确认最新的binlog文件是mysql-bin.000052

3、安装binlog2sql

git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql

pip install -r requirements.txt

gitee下载

4、定位误操作SQL的binlog位置
需要知道误操作的时间段

python binlog2sql.py -h127.0.0.1 -P3306 -uroot -pyourpwd -d database -t table --start-file="mysql-bin.000052" --start-datetime="2025-01-13 12:00:00" --stop-datetime="2025-01-13 14:00:00" > flashback.sql

输出：
INSERT INTO `test`.`tbl`(`addtime`, `id`, `name`) VALUES ('2016-12-13 20:26:00', 4, '小李'); #start 317 end 487 time 2016-12-13 20:26:26
UPDATE `test`.`tbl` SET `addtime`='2016-12-12 00:00:00', `id`=4, `name`='小李' WHERE `addtime`='2016-12-13 20:26:00' AND `id`=4 AND `name`='小李' LIMIT 1; #start 514 end 701 time 2016-12-13 20:27:07
DELETE FROM `test`.`tbl` WHERE `addtime`='2016-12-10 00:04:33' AND `id`=1 AND `name`='小赵' LIMIT 1; #start 728 end 938 time 2016-12-13 20:28:05
DELETE FROM `test`.`tbl` WHERE `addtime`='2016-12-10 00:04:48' AND `id`=2 AND `name`='小钱' LIMIT 1; #start 728 end 938 time 2016-12-13 20:28:05
DELETE FROM `test`.`tbl` WHERE `addtime`='2016-12-13 20:25:00' AND `id`=3 AND `name`='小孙' LIMIT 1; #start 728 end 938 time 2016-12-13 20:28:05
DELETE FROM `test`.`tbl` WHERE `addtime`='2016-12-12 00:00:00' AND `id`=4 AND `name`='小李' LIMIT 1; #start 728 end 938 time 2016-12-13 20:28:05

可以看出误操作SQL的准确位置在728-938之间，生成回滚SQL时需要用到这个参数

5、生成回滚SQL

python binlog2sql.py --flashback -h127.0.0.1 -P3306 -uroot -pyourpwd -d database -t table --start-file="mysql-bin.000052" --start-position=728 --stop-position=938 > rollback.sql

输出：
INSERT INTO `test`.`tbl`(`addtime`, `id`, `name`) VALUES ('2016-12-12 00:00:00', 4, '小李'); #start 728 end 938 time 2016-12-13 20:28:05
INSERT INTO `test`.`tbl`(`addtime`, `id`, `name`) VALUES ('2016-12-13 20:25:00', 3, '小孙'); #start 728 end 938 time 2016-12-13 20:28:05
INSERT INTO `test`.`tbl`(`addtime`, `id`, `name`) VALUES ('2016-12-10 00:04:48', 2, '小钱'); #start 728 end 938 time 2016-12-13 20:28:05
INSERT INTO `test`.`tbl`(`addtime`, `id`, `name`) VALUES ('2016-12-10 00:04:33', 1, '小赵'); #start 728 end 938 time 2016-12-13 20:28:05

6、执行回滚SQL，恢复数据

shell> mysql -h127.0.0.1 -P3306 -uroot -pyourpwd < rollback.sql

mysql> select * from tbl;
+----+--------+---------------------+
| id | name   | addtime             |
+----+--------+---------------------+
|  1 | 小赵   | 2016-12-10 00:04:33 |
|  2 | 小钱   | 2016-12-10 00:04:48 |
|  3 | 小孙   | 2016-12-13 20:25:00 |
|  4 | 小李   | 2016-12-12 00:00:00 |
+----+--------+---------------------+

小结

1、踩坑
2、为什么使用binlog2sql？

• 纯Python开发，安装与使用都很简单
• 可直接解析为标准SQL，方便理解、筛选与回滚