持续创作，加速成长！这是我参与「日新方案 10 月更文应战」的第6天，点击检查活动概况

哈喽！大家好，我是【IT邦德】，江湖人称jeames007，10年DBA工作经验
一位上进心十足的【大数据领域博主】！
中国DBA联盟(ACDU)成员，现在从事DBA及程序编程
擅长干流数据Oracle、MySQL、PG 运维开发，备份康复，装置迁移，功用优化、毛病应急处理等。
✨ 如果有对【数据库】感兴趣的【小可爱】，欢迎重视【IT邦德】
❤️❤️❤️感谢各位大可爱小可爱！❤️❤️❤️

@TOC

前言

从MySQL binlog解分出你要的SQL。依据不同选项，你能够得到原始SQL、回滚SQL、去除主键的INSERT SQL等,现在是Mysql的一款康复神器，本文具体阐述了binlog2sql的运用方法。

一、用处

数据快速回滚(闪回)
主从切换后新master丢数据的修复
从binlog生成规范SQL，带来的衍生功用

二、项目状态

正常保护。应用于部分公司线上环境。
已测试环境
Python 2.7, 3.4+
MySQL 5.6, 5
适用Mysql8，需求晋级操作

三、装置

shell> git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql
shell> pip install -r requirements.txt

四、运用

✨ 4.1 Mysql参数装备

[mysqld]
server_id = 1
log_bin = /var/log/mysql/mysql-bin.log
max_binlog_size = 1G
binlog_format = row
binlog_row_image = full

✨ 4.2 权限设置

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内容的权限

五、基本用法

✨ 5.1 解分出规范SQL

shell> 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); #start 570 end 736
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; #start 763 end 954
DELETE FROM `test`.`test3` WHERE `addtime`='2016-12-10 13:03:38' AND `data`='english' AND `id`=4 LIMIT 1; #start 981 end 1147

✨ 5.2 解分出回滚SQL

shell> 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); #start 981 end 1147
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; #start 763 end 954

✨ 5.3 选项说明

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.1 误删数据

test库tbl表原有数据
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 |
+----+--------+---------------------+
4 rows in set (0.00 sec)
mysql> delete from tbl;
Query OK, 4 rows affected (0.00 sec)
20:28时，tbl表误操作被清空
mysql> select * from tbl;
Empty set (0.00 sec)

✨ 6.2 康复数据步骤

登录mysql，检查现在的binlog文件

mysql> show master status;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000051 |       967 |
| mysql-bin.000052 |       965 |
+------------------+-----------+

最新的binlog文件是mysql-bin.000052，咱们再定位误操作SQL的binlog方位。误操作人只能知道大致的误操作时刻，咱们依据大致时刻过滤数据

shell> python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -ttbl --start-file='mysql-bin.000052' --start-datetime='2016-12-13 20:25:00' --stop-datetime='2016-12-13 20:30:00'
输出：
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之间，再依据方位进一步过滤，运用flashback形式生成回滚sql，检查回滚sql是否正确(注：真实环境下，此步经常会进一步筛选出需求的sql。结合grep、编辑器等)

shell> python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -ttbl --start-file='mysql-bin.000052' --start-position=3346 --stop-position=3556 -B > rollback.sql | cat
输出：
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

承认回滚sql正确，履行回滚句子。登录mysql承认，数据回滚成功。

shell> mysql -h127.0.0.1 -P3306 -uadmin -p'admin' < 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 |
+----+--------+---------------------+

七、约束（比照mysqlbinlog）

mysql server有必要开启，离线形式下不能解析
参数 binlog_row_image 有必要为FULL，暂不支撑MINIMAL
解析速度不如mysqlbinlog

八、优点（比照mysqlbinlog）

纯Python开发，装置与运用都很简略
自带flashback、no-primary-key解析形式，无需再装补丁
flashback形式下，更适合闪回实战
解析为规范SQL，便利了解、筛选
代码容易改造，能够支撑更多个性化解析