大众点评binlog2sql——二进制日志解析、回滚语句生成

1、简介

功能用途：

数据快速回滚(闪回)

主从切换后新master丢数据的修复

从binlog生成标准sql，带来的衍生功能

使用前提：

###mysql server必须设置以下参数:

[mysqld]

server_id = 1

log_bin = /var/log/mysql/mysql-bin.log

max_binlog_size = 1g

binlog_format = row

binlog_row_image = full

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

2、安装配置

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

shell> pip install -r requirements.txt

附加：

软件包已经下载完成，需要提前安装pip。

报错处理：

importerror: no module named setuptools

解决方法

wget https://pypi.python.org/packages/source/s/setuptools/setuptools-0.6c11.tar.gz

tar zxvf setuptools-0.6c11.tar.gz

cd setuptools-0.6c11

python setup.py install

3、基本用法

解析出标准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

解析出回滚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

4、选项说明

选项：

###mysql连接配置

-h host; -p port; -u user; -p password

###解析模式

--stop-never 持续同步binlog。可选。不加则同步至执行命令时最新的binlog位置。

-k, --no-primary-key 对insert语句去除主键。可选。

-b, --flashback 生成回滚语句，可解析大文件，不受内存限制，每打印一千行加一句sleep select(1)。可选。与stop-never或no-primary-key不能同时添加。

###解析范围控制

--start-file 起始解析文件。必须。

--start-position/--start-pos start-file的起始解析位置。可选。默认为start-file的起始位置。

--stop-file/--end-file 末尾解析文件。可选。默认为start-file同一个文件。若解析模式为stop-never，此选项失效。

--stop-position/--end-pos stop-file的末尾解析位置。可选。默认为stop-file的最末位置;若解析模式为stop-never，此选项失效。

--start-datetime 从哪个时间点的binlog开始解析，格式必须为datetime，如'2016-11-11 11:11:11'。可选。默认不过滤。

--stop-datetime 到哪个时间点的binlog停止解析，格式必须为datetime，如'2016-11-11 11:11:11'。可选。默认不过滤。

###对象过滤

-d, --databases 只输出目标db的sql。可选。默认为空。

-t, --tables 只输出目标tables的sql。可选。默认为空。

5、最终总结

适用于增删改等误操作的闪回。

可用于单表恢复后，后续的二进制日志中的，此表的其他操作语句重新生成执行，减少数据丢失量。

可以生成标准的sql，结合inception实现对开发的sql语句执行标准的制定。

仅针对增删改操作的sql生成，对ddl语句只能查看到，并不能生成回滚。对于已经drop的表应使用备份先进性恢复，之后再根据恢复的位置，从二进制日志再进行sql生成并应用。