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

参看：https://github.com/danfengcao/binlog2sql/blob/master/README.md

 

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 http://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生成并应用。