binlog2sql

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

用途

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

项目状态

正常维护。应用于部分公司线上环境。

已测试环境
- Python 2.7, 3.4+
- MySQL 5.6, 5.7

安装

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

git与pip的安装问题请自行搜索解决。

使用

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

基本用法

解析出标准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 `data`='中文' AND `id`=3 LIMIT 1; #start 763 end 954

选项

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

应用案例

误删整张表数据，需要紧急回滚

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)

恢复数据步骤：

登录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，方便理解、筛选
代码容易改造，可以支持更多个性化解析

贡献者

danfengcao 作者，维护者 [https://github.com/danfengcao]
大众点评DBA团队想法交流，使用体验
赵承勇 pymysqlreplication权限bug #2
陈路炳 bug报告(字段值为空时的处理)，使用体验
dba-jane pymysqlreplication时间字段浮点数bug #29
lujinke bug报告(set字段的处理 #32)

声明： 本站所有文章，如无特殊说明或标注，均为本站原创发布。任何个人或组织，在未征得本站同意时，禁止复制、盗用、采集、发布本站内容到任何网站、书籍等各类媒体平台。如若本站内容侵犯了原著者的合法权益，可联系我们进行处理。

{{userData.name}}已认证

一款从MySQL binlog获取原始SQL、回滚SQL的binlog2sql工具

binlog2sql

用途

项目状态

安装

使用

MySQL server必须设置以下参数:

user需要的最小权限集合：

基本用法

选项

应用案例

误删整张表数据，需要紧急回滚

限制（对比mysqlbinlog）

优点（对比mysqlbinlog）

贡献者

夜莺监控MySQL教程

MySQL 8.0字符集新纪元：utf8mb3与utf8mb4全面解析，utf8成历史

搭建VMware Horizon8私有云桌面虚拟化(五)Horizon连接vCenter Server

搭建VMware Horizon8私有云桌面虚拟化(一)安装Horizon Connection Server8

Horizon Connection Server 外部登入提示登录失败

搭建VMware Horizon8私有云桌面虚拟化(六)vCenter Server 创建模板虚拟机

搭建VMware Horizon8私有云桌面虚拟化(七)Horizon 创建桌面池连接SqlServer数据库

搭建VMware Horizon8私有云桌面虚拟化(二)安装SQL数据库与View Composer(可跳过此步骤)

{{userData.name}}已认证

binlog2sql

用途

项目状态

安装

使用

MySQL server必须设置以下参数:

user需要的最小权限集合：

基本用法

选项

应用案例

误删整张表数据，需要紧急回滚

限制（对比mysqlbinlog）

优点（对比mysqlbinlog）

贡献者

相关文章：

夜莺监控MySQL教程

MySQL 8.0字符集新纪元：utf8mb3与utf8mb4全面解析，utf8成历史

在Jenkins Pipeline中优雅地处理上游与下游Job结果

从k8s的事件聊聊for循环

Linux 系统故障排除最常用的 10 个命令

[kubectl-resource-view]: 一款用于查看k8s资源使用情况的插件