1.简介
从MySQL binlog解析出你要的SQL。根据不同选项，你可以得到原始SQL、回滚SQL、去除主键的INSERT SQL等
官方文档: https://github.com/danfengcao/binlog2sql
2.用途
- 数据快速回滚(闪回)
- 主从切换后新master丢数据的修复
- 从binlog生成标准SQL，带来的衍生功能
3.适用环境
Python 2.7, 3.4+
MySQL 5.6, 5.7
4.安装
git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql
pip install -r requirements.txt
#安装pip
curl https://bootstrap.pypa.io/pip/2.7/get-pip.py -o get-pip.py
5.使用
使用前提:
#MySQL server必须设置以下参数(值不需要和下面相同，只要开启即可):
[mysqld]
server_id = 1
log_bin = /var/log/mysql/mysql-bin.log
max_binlog_size = 1G
binlog_format = row
#回滚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);
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;
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);
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;
参数选项:
#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。可选。默认为增删改都解析。用了此参数但没填任何类型，则三者都不解析。
6.实际案例操作
误操作
mysql> select * from db_status;
+------+--------+-------------+---------------+----------------------+---------------------+---------------------+
| ID | STATUS | LOCATION_ID | EXTERNAL_IP | DBNAME | CREATED_DATE | MODIFIED_DATE |
+------+--------+-------------+---------------+----------------------+---------------------+---------------------+
| 0 | 0 | ind01 | 1292 | gachar | 2020-11-24 17:15:16 | 1997-01-01 00:00:00 |
| 1808 | 1 | ind01 | 1293 | NULL | 2020-11-25 15:34:09 | 1997-01-01 00:00:00 |
| 1809 | 1 | us01 | 1294 | change | 1970-01-02 00:00:00 | 1997-01-01 00:00:00 |
+------+--------+-------------+---------------+----------------------+---------------------+---------------------+
3 rows in set (0.08 sec)
#误删除全表
mysql> delete from web_db_status;
Query OK, 3 rows affected (0.20 sec)
mysql> select * from web_db_status;
Empty set (0.00 sec)
可以看到表中数据已经被删除
开始恢复
#查看当前binlog的位点
mysql> show master status\G;
*************************** 1. row ***************************
File: mysql_bin.000020
Position: 94967010
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
#最新的binlog文件是mysql_bin.000020，我们再定位误操作SQL的binlog位置。误操作人只能知道大致的误操作时间，我们根据大致时间过滤数据
#注意这里可以根据位点和时间判断，因为binlog文件都会有写入时间
#如果知道位点直接位点方式过滤，如果不知道可以用大概时间解析出标准sql
shell>python binlog2sql/binlog2sql.py -h127.0.0.1 -P 3306 -u root -p -dtest -tweb_db_status --start-file='mysql_bin.000020' --start-datetime='2021-01-05 16:00:00' --stop-datetime='2021-01-05 16:22:00'
输出:
DELETE FROM `test`.`web_db_status` WHERE `STATUS`=0 AND `MODIFIED_DATE`='1997-01-01 00:00:00' AND `EXTERNAL_IP`='1292' AND `DBNAME`='gachar' AND `CREATED_DATE`='2020-11-24 17:15:16' AND `LOCATION_ID`='ind01' AND `ID`=0 LIMIT 1; #start 94966615 end 94966979 time 2021-01-05 16:05:04
DELETE FROM `test`.`web_db_status` WHERE `STATUS`=1 AND `MODIFIED_DATE`='1997-01-01 00:00:00' AND `EXTERNAL_IP`='1293' AND `DBNAME` IS NULL AND `CREATED_DATE`='2020-11-25 15:34:09' AND `LOCATION_ID`='ind01' AND `ID`=1808 LIMIT 1; #start 94966615 end 94966979 time 2021-01-05 16:05:04
DELETE FROM `test`.`web_db_status` WHERE `STATUS`=1 AND `MODIFIED_DATE`='1997-01-01 00:00:00' AND `EXTERNAL_IP`='1294' AND `DBNAME`='change' AND `CREATED_DATE`='1970-01-02 00:00:00' AND `LOCATION_ID`='us01' AND `ID`=1809 LIMIT 1; #start 94966615 end 94966979 time 2021-01-05 16:05:04
#可以看到上面解析出来了我们刚才删除的三条sql语句（注意这里命令行没有加-B参数即解析出标准sql）
#接下来我们直接解析回滚sql（命令中加入-B参数 即--flashback）
shell> python binlog2sql/binlog2sql.py -h127.0.0.1 -P 3306 -u root -p -B -dtest -tweb_db_status --start-file='mysql_bin.000020' --start-datetime='2021-01-05 16:00:00' --stop-datetime='2021-01-05 16:22:00' > rollback.sql
shell> cat rollback.sql
INSERT INTO `test`.`web_db_status`(`STATUS`, `MODIFIED_DATE`, `EXTERNAL_IP`, `DBNAME`, `CREATED_DATE`, `LOCATION_ID`, `ID`) VALUES (1, '1997-01-01 00:00:00', '1294', 'change', '1970-01-02 00:00:00', 'us01', 1809); #start 94966615 end 94966979 time 2021-01-05 16:05:04
INSERT INTO `test`.`web_db_status`(`STATUS`, `MODIFIED_DATE`, `EXTERNAL_IP`, `DBNAME`, `CREATED_DATE`, `LOCATION_ID`, `ID`) VALUES (1, '1997-01-01 00:00:00', '1293', NULL, '2020-11-25 15:34:09', 'ind01', 1808); #start 94966615 end 94966979 time 2021-01-05 16:05:04
INSERT INTO `test`.`web_db_status`(`STATUS`, `MODIFIED_DATE`, `EXTERNAL_IP`, `DBNAME`, `CREATED_DATE`, `LOCATION_ID`, `ID`) VALUES (0, '1997-01-01 00:00:00', '1292', 'gachar', '2020-11-24 17:15:16', 'ind01', 0); #start 94966615 end 94966979 time 2021-01-05 16:05:04
#可以看到已经生成了反向插入sql，直接将这些sql执行到数据库中
#确认一下生成的回滚sql使我们对应的表的反向sql，是否正确，确认无误导入数据库
mysql> source /root/binlog2sql/rollback.sql;
Query OK, 1 row affected (0.04 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.01 sec)
mysql> select * from db_status;
+------+--------+-------------+---------------+----------------------+---------------------+---------------------+
| ID | STATUS | LOCATION_ID | EXTERNAL_IP | DBNAME | CREATED_DATE | MODIFIED_DATE |
+------+--------+-------------+---------------+----------------------+---------------------+---------------------+
| 0 | 0 | ind01 | 1292 | gachar | 2020-11-24 17:15:16 | 1997-01-01 00:00:00 |
| 1808 | 1 | ind01 | 1293 | NULL | 2020-11-25 15:34:09 | 1997-01-01 00:00:00 |
| 1809 | 1 | us01 | 1294 | change | 1970-01-02 00:00:00 | 1997-01-01 00:00:00 |
+------+--------+-------------+---------------+----------------------+---------------------+---------------------+
3 rows in set (0.08 sec)
#对比前后的数据
#数据已经成功恢复
7.优缺点对比(mysqlbinlog)
限制（对比mysqlbinlog）
- mysql server必须开启，离线模式下不能解析
- 参数 binlog_row_image 必须为FULL，暂不支持MINIMAL
- 解析速度不如mysqlbinlog
优点（对比mysqlbinlog）
- 纯Python开发，安装与使用都很简单
- 自带flashback、no-primary-key解析模式，无需再装补丁
- flashback模式下，更适合闪回实战 解析为标准SQL，方便理解、筛选 代码容易改造，可以支持更多个性化解析
扫一扫
1257
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
