binlog2sql

从MySQL binlog解析出你要的SQL。根据不同选项，你可以得到原始SQL、回滚SQL、去除主键的INSERT SQL等。 (Parse out the SQL you want from MySQL binlog. According to different options, you can get original SQL, rollback SQL, INSERT SQL with primary key removed, etc.)

用途

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

use Fast data rollback (flashback) Repair of lost data of new master after master-slave switch Derivative functions brought by standard SQL generated from binlog project status Normal maintenance. Applied in the online environment of some companies.

项目状态

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

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

Tested environment:

Python 2.7, 3.4+
MySQL 5.6, 5.7

安装

installation

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

git与pip的安装问题请自行搜索解决。 Please search for and solve the problem of git and pip installation.

使用

MySQL server必须设置以下参数:

use The following parameters must be set for the 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需要的最小权限集合 (Recommended authorization)：

select, super/replication client, replication slave

建议授权
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO

权限说明 (Permission Description)

select：需要读取server端information_schema.COLUMNS表，获取表结构的元信息，拼接成可视化的sql语句
super/replication client：两个权限都可以，需要执行'SHOW MASTER STATUS', 获取server端的binlog列表
replication slave：通过BINLOG_DUMP协议获取binlog内容的权限
select: need to read the information_schema.COLUMNS table on the server side to obtain the meta-information of the table structure and stitch it into a visual SQL statement
super/replication client: Both permissions are ok, you need to execute'SHOW MASTER STATUS' to get the binlog list on the server side
Replication slave: access to binlog content through BINLOG_DUMP protocol

基本用法 (Basic usage)

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

解析模式(Analysis Mode)

--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。

--stop-never Continue to parse binlog. Optional. The default is False, sync to the latest binlog location when the command is executed.

-K, --no-primary-key Remove the primary key from the INSERT statement. Optional. Default False

-B, --flashback Generate rollback SQL, which can parse large files without memory limitation. Optional. The default is False. It cannot be added at the same time as stop-never or no-primary-key.

--back-interval -B mode, for every thousand lines of SQL to be rolled back, how many seconds to add a SLEEP, if you do not want to add SLEEP, please set it to 0. Optional. The default is 1.0.

解析范围控制 (Resolution range control)

--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'。可选。默认不过滤。

--start-file Start parsing the file, only the file name is required, not the full path. have to.

--start-position/--start-pos start parsing position. Optional. The default is the starting position of start-file.

--stop-file/--end-file Stop parsing the file. Optional. The default is the same file as start-file. If the resolution mode is stop-never, this option is invalid.

--stop-position/--end-pos stop parsing position. Optional. The default is the last position of stop-file; if the parsing mode is stop-never, this option is invalid.

--start-datetime Start parsing time, format'%Y-%m-%d %H:%M:%S'. Optional. No filtering by default.

--stop-datetime stop parsing time, format'%Y-%m-%d %H:%M:%S'. Optional. No filtering by default.

对象过滤(Object filtering)

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

-d, --databases Only parse the sql of the target db, multiple libraries are separated by spaces, such as -d db1 db2. Optional. The default is empty.

-t, --tables Only parse the sql of the target table, multiple tables are separated by spaces, such as -t tbl1 tbl2. Optional. The default is empty.

--only-dml Only parse dml, ignore ddl. Optional. The default is False.

--sql-type Only parse the specified type, support INSERT, UPDATE, DELETE. Multiple types are separated by spaces, such as --sql-type INSERT DELETE. Optional. The default is to resolve additions, deletions and changes. If this parameter is used but no type is filled, the three will not be resolved.

应用案例 (Applications)

误删整张表数据，需要紧急回滚 (Delete the entire table data by mistake and need an emergency rollback)

闪回详细介绍可参见example目录下《闪回原理与实战》(For a detailed introduction of flashback, please refer to "Flashback Principles and Practices" in the example directory) example/mysql-flashback-priciple-and-practice.md

test库tbl表原有数据 (Original data of test library tbl table)
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)

恢复数据步骤 (Steps to recover data)：

登录mysql，查看目前的binlog文件 (Log in to mysql, view the current binlog file)

mysql> show master status;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000051 |       967 |
| mysql-bin.000052 |       965 |
+------------------+-----------+

最新的binlog文件是mysql-bin.000052，我们再定位误操作SQL的binlog位置。误操作人只能知道大致的误操作时间，我们根据大致时间过滤数据。 (The latest binlog file is mysql-bin.000052, we then locate the binlog location of the misoperation SQL. The misoperator can only know the approximate misoperation time, and we filter the data based on the approximate time.)

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、编辑器等) (We got the exact position of the misoperation sql between 728-938, and then further filter according to the position, use the flashback mode to generate the rollback sql, and check whether the rollback sql is correct (Note: In a real environment, this step is often further filtered out The required sql. Combine grep, editor, etc.))

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确认，数据回滚成功。 (Confirm that the rollback SQL is correct and execute the rollback statement. Log in to mysql to confirm that the data rollback is successful.)

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)

联系我

有任何问题，请与我联系。邮箱：danfengcao.info@gmail.com

欢迎提问题提需求，欢迎pull requests！

Name		Name	Last commit message	Last commit date
Latest commit History 69 Commits
binlog2sql		binlog2sql
example		example
tests		tests
.gitignore		.gitignore
LICENSE		LICENSE
README.md		README.md
requirements.txt		requirements.txt

Navigation Menu

Repository files navigation

binlog2sql

用途

项目状态

安装

使用

MySQL server必须设置以下参数:

user需要的最小权限集合 (Recommended authorization)：

基本用法 (Basic usage)

选项

应用案例 (Applications)

误删整张表数据，需要紧急回滚 (Delete the entire table data by mistake and need an emergency rollback)

限制（对比mysqlbinlog）

优点（对比mysqlbinlog）

贡献者

联系我

About

Releases

Packages

Contributors

Languages

Folders and files

Latest commit

History

Repository files navigation

binlog2sql

用途

项目状态

安装

使用

MySQL server必须设置以下参数:

user需要的最小权限集合 (Recommended authorization)：

基本用法 (Basic usage)

选项

应用案例 (Applications)

误删整张表数据，需要紧急回滚 (Delete the entire table data by mistake and need an emergency rollback)

限制（对比mysqlbinlog）

优点（对比mysqlbinlog）

贡献者

联系我

About

Resources

Stars

Watchers

Forks

Releases

Packages

Contributors

Languages