从MySQL binlog解析出你要的SQL。根据不同选项，你可以得到原始SQL、回滚SQL、去除主键的INSERT SQL等
官方文档: https://github.com/danfengcao/binlog2sql
数据快速回滚(闪回)主从切换后新master丢数据的修复从binlog生成标准SQL，带来的衍生功能 3.适用环境
Python 2.7, 3.4+
MySQL 5.6, 5.7
git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sqlpip install -r requirements.txt#安装pipcurl https://bootstrap.pypa.io/pip/2.7/get-pip.py -o get-pip.py
5.使用使用前提:
#MySQL server必须设置以下参数(值不需要和下面相同，只要开启即可):[mysqld] server_id = 1log_bin = /var/log/mysql/mysql-bin.logmax_binlog_size = 1Gbinlog_format = row #回滚user需要的最小权限集合：select, super/replication client, replication slave 建议授权 GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO#权限说明select：需要读取server端information_schema.COLUMNS表，获取表结构的元信息，拼接成可视化的sql语句super/replication client：两个权限都可以，需要执行'SHOW MASTER STATUS', 获取server端的binlog列表replication slave：通过BINLOG_DUMP协议获取binlog内容的权限
基本用法:
#解析出标准sqlshell> python binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -t test3 test4 --start-file='mysql-bin.000002'输出：INSERT INTO `test`.`test3`(`addtime`, `data`, `id`) VALUES ('2016-12-10 13:03:38', 'english', 4); UPDATE `test`.`test3` SET `addtime`='2016-12-10 12:00:00', `data`='中文', `id`=3 WHERe `addtime`='2016-12-10 13:03:22' AND `data`='中文' AND `id`=3 LIMIT 1;DELETE FROM `test`.`test3` WHERe `addtime`='2016-12-10 13:03:38' AND `data`='english' AND `id`=4 LIMIT 1; #start 981 end 1147#解析出回滚sqlshell> python binlog2sql.py --flashback -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -ttest3 --start-file='mysql-bin.000002' --start-position=763 --stop-position=1147输出：INSERT INTO `test`.`test3`(`addtime`, `data`, `id`) VALUES ('2016-12-10 13:03:38', 'english', 4); UPDATe `test`.`test3` SET `addtime`='2016-12-10 13:03:22', `data`='中文', `id`=3 WHERe `addtime`='2016-12-10 12:00:00' AND `data`='中文' AND `id`=3 LIMIT 1;
参数选项:
#mysql连接配置-h host; -P port; -u user; -p password#解析模式--stop-never 持续解析binlog。可选。默认False，同步至执行命令时最新的binlog位置。-K, --no-primary-key 对INSERT语句去除主键。可选。默认False-B, --flashback 生成回滚SQL，可解析大文件，不受内存限制。可选。默认False。与stop-never或no-primary-key不能同时添加。--back-interval -B模式下，每打印一千行回滚SQL，加一句SLEEP多少秒，如不想加SLEEP，请设为0。可选。默认1.0。#解析范围控制--start-file 起始解析文件，只需文件名，无需全路径 。必须。--start-position/--start-pos 起始解析位置。可选。默认为start-file的起始位置。--stop-file/--end-file 终止解析文件。可选。默认为start-file同一个文件。若解析模式为stop-never，此选项失效。--stop-position/--end-pos 终止解析位置。可选。默认为stop-file的最末位置；若解析模式为stop-never，此选项失效。--start-datetime 起始解析时间，格式'%Y-%m-%d %H:%M:%S'。可选。默认不过滤。--stop-datetime 终止解析时间，格式'%Y-%m-%d %H:%M:%S'。可选。默认不过滤。#对象过滤-d, --databases 只解析目标db的sql，多个库用空格隔开，如-d db1 db2。可选。默认为空。-t, --tables 只解析目标table的sql，多张表用空格隔开，如-t tbl1 tbl2。可选。默认为空。--only-dml 只解析dml，忽略ddl。可选。默认False。--sql-type 只解析指定类型，支持INSERT, UPDATE, DELETE。多个类型用空格隔开，如--sql-type INSERT DELETE。可选。默认为增删改都解析。用了此参数但没填任何类型，则三者都不解析。
6.实际案例操作误操作
mysql> select * from db_status;+------+--------+-------------+---------------+----------------------+---------------------+---------------------+| ID | STATUS | LOCATION_ID | EXTERNAL_IP | DBNAME | CREATED_DATE | MODIFIED_DATE |+------+--------+-------------+---------------+----------------------+---------------------+---------------------+| 0 | 0 | ind01 | 1292 | gachar | 2020-11-24 17:15:16 | 1997-01-01 00:00:00 || 1808 | 1 | ind01 | 1293 | NULL | 2020-11-25 15:34:09 | 1997-01-01 00:00:00 || 1809 | 1 | us01 | 1294 | change | 1970-01-02 00:00:00 | 1997-01-01 00:00:00 |+------+--------+-------------+---------------+----------------------+---------------------+---------------------+3 rows in set (0.08 sec)#误删除全表mysql> delete from web_db_status;Query OK, 3 rows affected (0.20 sec)mysql> select * from web_db_status;Empty set (0.00 sec)可以看到表中数据已经被删除
开始恢复
#查看当前binlog的位点mysql> show master statusG;*************************** 1、row *************************** File: mysql_bin.000020 Position: 94967010 Binlog_Do_DB: Binlog_Ignore_DB:Executed_Gtid_Set:1 row in set (0.00 sec)#最新的binlog文件是mysql_bin.000020，我们再定位误操作SQL的binlog位置。误操作人只能知道大致的误操作时间，我们根据大致时间过滤数据#注意这里可以根据位点和时间判断，因为binlog文件都会有写入时间#如果知道位点直接位点方式过滤，如果不知道可以用大概时间解析出标准sqlshell>python binlog2sql/binlog2sql.py -h127.0.0.1 -P 3306 -u root -p -dtest -tweb_db_status --start-file='mysql_bin.000020' --start-datetime='2021-01-05 16:00:00' --stop-datetime='2021-01-05 16:22:00'输出:DELETe FROM `test`.`web_db_status` WHERe `STATUS`=0 AND `MODIFIED_DATE`='1997-01-01 00:00:00' AND `EXTERNAL_IP`='1292' AND `DBNAME`='gachar' AND `CREATED_DATE`='2020-11-24 17:15:16' AND `LOCATION_ID`='ind01' AND `ID`=0 LIMIT 1; #start 94966615 end 94966979 time 2021-01-05 16:05:04DELETe FROM `test`.`web_db_status` WHERe `STATUS`=1 AND `MODIFIED_DATE`='1997-01-01 00:00:00' AND `EXTERNAL_IP`='1293' AND `DBNAME` IS NULL AND `CREATED_DATE`='2020-11-25 15:34:09' AND `LOCATION_ID`='ind01' AND `ID`=1808 LIMIT 1; #start 94966615 end 94966979 time 2021-01-05 16:05:04DELETe FROM `test`.`web_db_status` WHERe `STATUS`=1 AND `MODIFIED_DATE`='1997-01-01 00:00:00' AND `EXTERNAL_IP`='1294' AND `DBNAME`='change' AND `CREATED_DATE`='1970-01-02 00:00:00' AND `LOCATION_ID`='us01' AND `ID`=1809 LIMIT 1; #start 94966615 end 94966979 time 2021-01-05 16:05:04#可以看到上面解析出来了我们刚才删除的三条sql语句（注意这里命令行没有加-B参数即解析出标准sql）#接下来我们直接解析回滚sql（命令中加入-B参数 即--flashback）shell> python binlog2sql/binlog2sql.py -h127.0.0.1 -P 3306 -u root -p -B -dtest -tweb_db_status --start-file='mysql_bin.000020' --start-datetime='2021-01-05 16:00:00' --stop-datetime='2021-01-05 16:22:00' > rollback.sqlshell> cat rollback.sqlINSERT INTO `test`.`web_db_status`(`STATUS`, `MODIFIED_DATE`, `EXTERNAL_IP`, `DBNAME`, `CREATED_DATE`, `LOCATION_ID`, `ID`) VALUES (1, '1997-01-01 00:00:00', '1294', 'change', '1970-01-02 00:00:00', 'us01', 1809); #start 94966615 end 94966979 time 2021-01-05 16:05:04INSERT INTO `test`.`web_db_status`(`STATUS`, `MODIFIED_DATE`, `EXTERNAL_IP`, `DBNAME`, `CREATED_DATE`, `LOCATION_ID`, `ID`) VALUES (1, '1997-01-01 00:00:00', '1293', NULL, '2020-11-25 15:34:09', 'ind01', 1808); #start 94966615 end 94966979 time 2021-01-05 16:05:04INSERT INTO `test`.`web_db_status`(`STATUS`, `MODIFIED_DATE`, `EXTERNAL_IP`, `DBNAME`, `CREATED_DATE`, `LOCATION_ID`, `ID`) VALUES (0, '1997-01-01 00:00:00', '1292', 'gachar', '2020-11-24 17:15:16', 'ind01', 0); #start 94966615 end 94966979 time 2021-01-05 16:05:04#可以看到已经生成了反向插入sql，直接将这些sql执行到数据库中#确认一下生成的回滚sql使我们对应的表的反向sql，是否正确，确认无误导入数据库mysql> source /root/binlog2sql/rollback.sql;Query OK, 1 row affected (0.04 sec)Query OK, 1 row affected (0.00 sec)Query OK, 1 row affected (0.01 sec)mysql> select * from db_status;+------+--------+-------------+---------------+----------------------+---------------------+---------------------+| ID | STATUS | LOCATION_ID | EXTERNAL_IP | DBNAME | CREATED_DATE | MODIFIED_DATE |+------+--------+-------------+---------------+----------------------+---------------------+---------------------+| 0 | 0 | ind01 | 1292 | gachar | 2020-11-24 17:15:16 | 1997-01-01 00:00:00 || 1808 | 1 | ind01 | 1293 | NULL | 2020-11-25 15:34:09 | 1997-01-01 00:00:00 || 1809 | 1 | us01 | 1294 | change | 1970-01-02 00:00:00 | 1997-01-01 00:00:00 |+------+--------+-------------+---------------+----------------------+---------------------+---------------------+3 rows in set (0.08 sec)#对比前后的数据#数据已经成功恢复
7.优缺点对比(mysqlbinlog) 限制（对比mysqlbinlog）mysql server必须开启，离线模式下不能解析参数 binlog_row_image 必须为FULL，暂不支持MINIMAL解析速度不如mysqlbinlog 优点（对比mysqlbinlog）
纯Python开发，安装与使用都很简单自带flashback、no-primary-key解析模式，无需再装补丁flashback模式下，更适合闪回实战 解析为标准SQL，方便理解、筛选 代码容易改造，可以支持更多个性化解析