binlog2sql

binsql2log是大众点评开源的用于解析MySQL binlog的工具, 项目地址: https://github.com/danfengcao/binlog2sql

从MySQL binlog解析出你要的SQL。根据不同选项，你可以得到原始SQL、回滚SQL、去除主键的INSERT SQL等。

0x00 用途

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

开发团队已经测试过的环境:

• Python 2.7, 3.4+
• MySQL 5.6, 5.7

0x01 安装

安装binlog2sql前先安装pip:

这里没有安装git,直接下载了压缩包

yum -y install epel-release
yum install python-pip
# 下载binlog2sql并解压,安装
wget -O binlog2sql-master.zip https://github.com/danfengcao/binlog2sql/archive/refs/heads/master.zip
unzip binlog2sql-master.zip
cd binlog2sql-master
pip install -r requirements.txt

安装之后, 可以看一下目录结构. 可执行的pyhton文件在 binlog2sql/binlog2sql.py.

0x02 配置

mysql服务端配置

MySQL server必须:

• 开启 binlog
• 设置 binlog_format 为 row
• 设置 binlog_row_image 为 full 或 noblog (默认为full)

[mysqld]
server_id = 1
log_bin = /var/log/mysql/mysql-bin.log
max_binlog_size = 1G
binlog_format = row
binlog_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 USER `binlog2sql@localhost` IDENTIFIED BY 'password';
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO your_user_name;
FLUSH privileges;

0x03 基本用法

show master status可查看binlog文件列表

解析出标准SQL

python binlog2sql.py -h host -P port -u username -p 'your_password' -d database_name -t table_name1 table_name2 --start-file='mysql-bin.000002'

解析出回滚SQL

python binlog2sql.py --flashback -h host -P port -u username -p 'your_password' -d database_name -t table_name1 --start-file='mysql-bin.000002' --start-position=763 --stop-position=1147

选项说明:

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

实例操作

USE test;
delimiter $$
CREATE TABLE `students`(
	`id` int NOT NULL AUTO_INCREMENT,
	`name` varchar(20) NOT NULL,
	`age` tinyint NOT NULL,
	`create_time` int NOT NULL,
	`update_time` int NULL DEFAULT 0,
	PRIMARY KEY(`id`)
)engine=InnoDB;
INSERT INTO `students`(`id`,`name`,`age`,`create_time`)VALUES
(1,'aben',18,UNIX_TIMESTAMP('2021-4-19 22:00')),
(2,'sky',20,UNIX_TIMESTAMP('2021-4-19 22:02')),
(3,'array',16,UNIX_TIMESTAMP('2021-4-19 22:03')),
(4,'sunny',17,UNIX_TIMESTAMP('2021-4-19 22:04'));
$$
delimiter ;

我们来删除一笔数据:

DELETE FROM test.students WHERE name='aben';

1. 查看binlog状态, 使用mysql的命令: show master status查看当前正在写入的log文件.

如果要查看所有的binlog文件, 使用show binary logs

2. 我们可以估计一下大概是在哪一个log文件里面, 我们就用最新的log文件来查找:

python binlog2sql/binlog2sql.py -h 127.0.0.1 -P 3306 -u binlog2sql -p 'password' -d test -t students --start-file='mysql-bin.000006' --start-datetime='2021-4-19 22:45:00' --stop-datetime='2021-4-19 22:48:00'> 1.sql

得到的文件1.sql的内容:

INSERT INTO `test`.`students`(`update_time`, `age`, `create_time`, `id`, `name`) VALUES (0, 18, 1618840800, 1, 'aben'); #start 867 end 1181 time 2021-04-19 22:45:51
INSERT INTO `test`.`students`(`update_time`, `age`, `create_time`, `id`, `name`) VALUES (0, 20, 1618840920, 2, 'sky'); #start 867 end 1181 time 2021-04-19 22:45:51
INSERT INTO `test`.`students`(`update_time`, `age`, `create_time`, `id`, `name`) VALUES (0, 16, 1618840980, 3, 'array'); #start 867 end 1181 time 2021-04-19 22:45:51
INSERT INTO `test`.`students`(`update_time`, `age`, `create_time`, `id`, `name`) VALUES (0, 17, 1618841040, 4, 'sunny'); #start 867 end 1181 time 2021-04-19 22:45:51
DELETE FROM `test`.`students` WHERE `update_time`=0 AND `age`=18 AND `create_time`=1618840800 AND `id`=1 AND `name`='aben' LIMIT 1; #start 1212 end 1460 time 2021-04-19 22:46:36

3. 从上面的结果得知, 删除操作的位置在1212 ~ 1460之间, 再根据位置进一步过滤，使用flashback模式生成回滚sql，检查回滚sql是否正确(注：真实环境下，此步经常会进一步筛选出需要的sql。结合grep、编辑器等)

python binlog2sql/binlog2sql.py -h 127.0.0.1 -P 3306 -u binlog2sql -p 'password' -d test -t students --start-file='mysql-bin.000006' --start-position=1212 --stop-position=1460 -B > rollback.sql

得到的结果:

INSERT INTO `test`.`students`(`update_time`, `age`, `create_time`, `id`, `name`) VALUES (0, 18, 1618840800, 1, 'aben'); #start 1212 end 1460 time 2021-04-19 22:46:36

4. 我们现在就可以使用这个sql来了回滚数据了

mysql -u root -p < rollback.sql

闪回详细介绍可参见example目录下《闪回原理与实战》

0x04 对比mysqlbinlog

• mysql server必须开启，离线模式下不能解析
• 参数 binlog_row_image 必须为FULL，暂不支持MINIMAL
• 解析速度不如mysqlbinlog

0x05 一段来自他人的总结

1. 它本身的核心代码比较少，主要是在pymysqlreplication的基础上进行了二次开发。

   pymysqlreplication实现了MySQL复制协议，可捕捉不同类型的EVENT事件。

   具体可参考：https://github.com/noplay/python-mysql-replication

2. 个人感觉，直接解析文本格式的binlog，也未尝不是一个好办法。

   理由如下：

   1> binlog2sql强烈依赖于MySQL复制协议，如果复制协议发生改变，则该工具将不可用。

    虽然，复制协议发生改变的可能性很小（一般都会保持向前兼容），但相对而言，自带的mysqlbinlog肯定更懂binlog，基于mysqlbinlog解析后的结果进行处理，
   
    可完全屏蔽复制协议等底层细节。
   

   2> 用python来解析文本格式的binlog，本身也不是件难事。

     譬如，update语句在binlog中的对应的文本
   
     在得到表结构的情况下，基本上可离线解析。