MySQL工具之binlog2sql：一款优秀且开源的Binlog解析工具

GrowthDBA 2022-04-25

7027

binlog2sql，DBA同胞们的福音。纯Python编写，核心代码不超200行。从MySQL binlog解析出你要的SQL。根据不同选项，你可以得到原始SQL、回滚SQL、去除主键的INSERT SQL等。DBA或开发人员，有时会误删或者误更新数据，binlog2sql可以实现快速回滚。这么优秀的开源工具，要是还不知道就OUT了，

今天就来盘它。

GitHub地址：https://github.com/danfengcao/binlog2sql

环境介绍 & 工具安装

环境要求及介绍

工欲善其事，必先利其器。首先准备一台测试机，Linux操作系统版本：CentOS Linux release 7.5.1804 (Core)。

目前测试过的环境，Python版本要求2.7、3.4+，MySQL版本为5.6、5.7。

binlgo2sql的安装

第一种获取软件包方式：（需要服务器可以连接公网）

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

第二种获取方式：百度网盘。由于联网的下载网址可能还要连接外网，所以，我已经把下载好的软件包放在了百度网盘，大家需要的直接下载下来，安装即可。（链接：https://pan.baidu.com/s/1PvyuXo_ZdNXLpvkZgPN3VQ，提取码：ahak）

安装binlog2sql：

# 1、解压tar zxvf binlog2sql.tar.gz# 2、进入对应的目录，pip installcd root/binlog2sqlpip install -r requirements.txt

确认是否安装成功：

cd /root/binlog2sql/binlog2sql/ls -al

可以看到binlog2sql.py就OK了。这么看来，不能算是一个“小工具”，称之为Python脚本更贴切些

，不管怎么说，都凝聚了作者大佬的用心。

测试数据准备

MySQL配置要求

[mysqld]server_id = 1log_bin = var/log/mysql/mysql-bin.logmax_binlog_size = 1Gbinlog_format = rowbinlog_row_image = full

操作用户需要的最小权限集合：

select, super/replication client, replication slave

【权限说明】

select：需要读取server端information_schema.COLUMNS表，获取表结构的元信息，拼接成可视化的sql语句；
super/replication client：两个权限都可以，需要执行'SHOW MASTER STATUS', 获取server端的binlog列表；
replication slave：通过BINLOG_DUMP协议获取binlog内容的权限。

测试数据准备

--Create UserCREATE USER 'b2sql'@'%' IDENTIFIED BY 'b2sql';GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'b2sql'@'%';--Create DatabaseCREATE DATABASE test_binlog2sql;--Create TableUSE test_binlog2sql;CREATE TABLE `user` (    `id` INT(11) NOT NULL AUTO_INCREMENT,    `name` VARCHAR(20) DEFAULT NULL,    `addtime` DATETIME DEFAULT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB;--Insert Test DataINSERT INTO user(name,addtime) values('小赵','2013-11-11 00:04:33');INSERT INTO user(name,addtime) values('小钱','2014-11-11 00:04:48');INSERT INTO user(name,addtime) values('小孙','2016-11-11 20:25:00');INSERT INTO user(name,addtime) values('小李','2013-11-11 00:00:00');--Select DataSELECT * FROM user;

使用示例 & 原理浅析

binlog2sql工具参数选项

## 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。可选。默认为增、删、改操作均解析。用了此参数但没填任何类型，则三者都不解析。

误删数据的回滚操作案例

模拟某一时刻，我们对数据库做了删除操作：

select * from user;select now();delete from user where addtime>'2014-01-01';select * from user;

在2022-04-20 16:22左右，删除了id为2和3的小钱和小孙两行数据。

【回滚操作】

1、查看当前binlog

show master logs;

2、通过大概时间范围解析出误操作执行的SQL

最新的binlog是mysql-bin.000023。筛选出要回滚的SQL，由于误操作人只知道大致的误操作时间，我们首先根据时间做一次过滤。只需要解析test_binlog2sql库user表。（注：如果有多个sql误操作，则生成的binlog可能分布在多个文件，需解析多个文件）

cd /root/binlog2sql/binlog2sqlpython binlog2sql.py -hxxx.xx.x.xxx -P3306 -ub2sql -p'b2sql' -d test_binlog2sql -t user --start-file='mysql-bin.000023' --start-datetime='2022-04-20 16:22:00' --stop-datetime='2022-04-20 16:25:00' > tmp/raw.sqlcat tmp/raw.sql

这时，已将删除的语句解析出来了。同时，我们也得到了binlog的位置信息：#start 4002 ... end 4217。

3、根据上一步得到的位置信息，使用-B参数生成回滚SQL

通过binlog位置信息，我们确定了误操作SQL来自同一个事务，准确位置在4002-4217之间（binlog2sql对于同一个事务会输出同样的start position）。再根据位置过滤，使用-B选项生成回滚SQL，检查回滚SQL是否正确。（注：真实场景下，生成的回滚SQL经常会需要进一步筛选。结合grep、编辑器等）

cd /root/binlog2sql/binlog2sqlpython binlog2sql.py -hxxx.xx.x.xxx -P3306 -ub2sql -p'b2sql' -d test_binlog2sql -t user --start-file='mysql-bin.000023' --start-position=4002 --stop-position=4217 -B > tmp/rollback.sqlcat tmp/rollback.sql

这时，我们已经得到了回滚语句。

4、确认回滚SQL无误，执行回滚操作

与业务方确认回滚sql没问题，执行回滚语句。登录mysql，确认回滚成功。

select * from user;source tmp/rollback.sql;select * from user;

至此，被误删的数据被回滚回来了。

binlog2sql原理浅析

MySQL binlog以event的形式，记录了MySQL server从启用binlog以来所有的变更信息，能够帮助重现这之间的所有变化。MySQL引入binlog主要有两个目的：一是为了主从复制；二是某些备份还原操作后需要重新应用binlog。

有三种可选的binlog格式，各有优缺点：

statement：基于SQL语句的模式，binlog数据量小，但是某些语句和函数在复制过程可能导致数据不一致甚至出错；
row：基于行的模式，记录的是行的完整变化。很安全，但是binlog会比其他两种模式大很多；
mixed：混合模式，根据语句来选用是statement还是row模式；

利用binlog闪回，需要将binlog格式设置为row、binlog_row_image = full。row模式下，一条使用InnoDB的INSERT会产生如下格式的binlog：

cd usr/local/mysql3306/bin./mysqlbinlog --database=test_binlog2sql mysql/mysql3306/mysql-bin.000023

row模式下，一条使用InnoDB的DELETE会产生如下格式的binlog：

./mysqlbinlog -v --base64-output=decode-rows --start-position=4002 --stop-position=4217 mysql/mysql3306/mysql-bin.000023

既然binlog以event形式记录了所有的变更信息，那么我们把需要回滚的event，从后往前回滚回去即可。

对于DELETE操作，我们从binlog提取出DELETE信息，生成的回滚语句是INSERT。（注：为了方便解释，我们用binlog2sql将原始binlog转化成了可读SQL）：

原始：DELETE FROM `test_binlog2sql`.`user` WHERE `id`=1 AND `name`='小赵';回滚：INSERT INTO `test_binlog2sql`.`user`(`id`, `name`) VALUES (1, '小赵');

对于INSERT操作，回滚SQL是DELETE：

原始：INSERT INTO `test_binlog2sql`.`user`(`id`, `name`) VALUES (2, '小钱');回滚：DELETE FROM `test_binlog2sql`.`user` WHERE `id`=2 AND `name`='小钱';

对于UPDATE操作，回滚SQL应该交换SET和WHERE的值：

原始：UPDATE `test_binlog2sql`.`user` SET `id`=3, `name`='小李' WHERE `id`=3 AND `name`='小孙';回滚：UPDATE `test_binlog2sql`.`user` SET `id`=3, `name`='小孙' WHERE `id`=3 AND `name`='小李';