binlog2sql 安装使用

binlog2sql 安装使用

1、安装所需依赖

#所需依赖
setuptools-0.6c11.tar.gz
pip-9.0.1.tar.gz
argparse-1.4.0-py2.py3-none-any.whl
linecache2-1.0.0-py2.py3-none-any.whl
PyMySQL-0.7.8-py2-none-any.whl
six-1.10.0-py2.py3-none-any.whl
traceback2-1.4.0-py2.py3-none-any.whl
unittest2-1.1.0-py2.py3-none-any.whl
wheel-0.24.0-py2.py3-none-any.whl
mysql-replication-0.9.tar.gz


#安装依赖
tar -zxvf setuptools-0.6c11.tar.gz
cd setuptools-0.6c11
python setup.py install

tar -zxvf pip-9.0.1.tar.gz
cd pip-9.0.1
python setup.py install

pip install *.whl mysql-replication-0.9.tar.gz

2、使用

​ 1) 根据大致时间过滤数据

python binlog2sql.py -h192.168.136.137 -P3306 -urootmha -p'rootmha' -d sakila -t tmpactor --start-file='mysql-binlog.000013' --start-datetime='2020-06-17 12:44:28' --stop-datetime='2020-06-17 13:20:00'

[root@ip137 binlog2sql]# python binlog2sql.py -h192.168.136.137 -P3306 -urootmha -p'rootmha' -d sakila -t tmpactor --start-file='mysql-binlog.000013' --start-datetime='2020-06-17 12:44:28' --stop-datetime='2020-06-17 13:20:00'
DELETE FROM `sakila`.`tmpactor` WHERE `first_name`='GREGORY' AND `last_name`='GOODING' AND `actor_id`=191 AND `last_update`='2006-02-15 04:34:33' LIMIT 1; #start 371 end 731 time 2020-06-17 12:44:28
DELETE FROM `sakila`.`tmpactor` WHERE `first_name`='JOHN' AND `last_name`='SUVARI' AND `actor_id`=192 AND `last_update`='2006-02-15 04:34:33' LIMIT 1; #start 371 end 731 time 2020-06-17 12:44:28
DELETE FROM `sakila`.`tmpactor` WHERE `first_name`='BURT' AND `last_name`='TEMPLE' AND `actor_id`=193 AND `last_update`='2006-02-15 04:34:33' LIMIT 1; #start 371 end 731 time 2020-06-17 12:44:28
DELETE FROM `sakila`.`tmpactor` WHERE `first_name`='MERYL' AND `last_name`='ALLEN' AND `actor_id`=194 AND `last_update`='2006-02-15 04:34:33' LIMIT 1; #start 371 end 731 time 2020-06-17 12:44:28
DELETE FROM `sakila`.`tmpactor` WHERE `first_name`='JAYNE' AND `last_name`='SILVERSTONE' AND `actor_id`=195 AND `last_update`='2006-02-15 04:34:33' LIMIT 1; #start 371 end 731 time 2020-06-17 12:44:28
DELETE FROM `sakila`.`tmpactor` WHERE `first_name`='BELA' AND `last_name`='WALKEN' AND `actor_id`=196 AND `last_update`='2006-02-15 04:34:33' LIMIT 1; #start 371 end 731 time 2020-06-17 12:44:28
DELETE FROM `sakila`.`tmpactor` WHERE `first_name`='REESE' AND `last_name`='WEST' AND `actor_id`=197 AND `last_update`='2006-02-15 04:34:33' LIMIT 1; #start 371 end 731 time 2020-06-17 12:44:28
DELETE FROM `sakila`.`tmpactor` WHERE `first_name`='MARY' AND `last_name`='KEITEL' AND `actor_id`=198 AND `last_update`='2006-02-15 04:34:33' LIMIT 1; #start 371 end 731 time 2020-06-17 12:44:28
DELETE FROM `sakila`.`tmpactor` WHERE `first_name`='JULIA' AND `last_name`='FAWCETT' AND `actor_id`=199 AND `last_update`='2006-02-15 04:34:33' LIMIT 1; #start 371 end 731 time 2020-06-17 12:44:28
DELETE FROM `sakila`.`tmpactor` WHERE `first_name`='THORA' AND `last_name`='TEMPLE' AND `actor_id`=200 AND `last_update`='2006-02-15 04:34:33' LIMIT 1; #start 371 end 731 time 2020-06-17 12:44:28
DELETE FROM `sakila`.`tmpactor` WHERE `first_name`='MATTHEW' AND `last_name`='CARREY' AND `actor_id`=181 AND `last_update`='2006-02-15 04:34:33' LIMIT 1; #start 819 end 1187 time 2020-06-17 13:12:58
DELETE FROM `sakila`.`tmpactor` WHERE `first_name`='DEBBIE' AND `last_name`='AKROYD' AND `actor_id`=182 AND `last_update`='2006-02-15 04:34:33' LIMIT 1; #start 819 end 1187 time 2020-06-17 13:12:58
DELETE FROM `sakila`.`tmpactor` WHERE `first_name`='RUSSELL' AND `last_name`='CLOSE' AND `actor_id`=183 AND `last_update`='2006-02-15 04:34:33' LIMIT 1; #start 819 end 1187 time 2020-06-17 13:12:58
DELETE FROM `sakila`.`tmpactor` WHERE `first_name`='HUMPHREY' AND `last_name`='GARLAND' AND `actor_id`=184 AND `last_update`='2006-02-15 04:34:33' LIMIT 1; #start 819 end 1187 time 2020-06-17 13:12:58
DELETE FROM `sakila`.`tmpactor` WHERE `first_name`='MICHAEL' AND `last_name`='BOLGER' AND `actor_id`=185 AND `last_update`='2006-02-15 04:34:33' LIMIT 1; #start 819 end 1187 time 2020-06-17 13:12:58
DELETE FROM `sakila`.`tmpactor` WHERE `first_name`='JULIA' AND `last_name`='ZELLWEGER' AND `actor_id`=186 AND `last_update`='2006-02-15 04:34:33' LIMIT 1; #start 819 end 1187 time 2020-06-17 13:12:58
DELETE FROM `sakila`.`tmpactor` WHERE `first_name`='RENEE' AND `last_name`='BALL' AND `actor_id`=187 AND `last_update`='2006-02-15 04:34:33' LIMIT 1; #start 819 end 1187 time 2020-06-17 13:12:58
DELETE FROM `sakila`.`tmpactor` WHERE `first_name`='ROCK' AND `last_name`='DUKAKIS' AND `actor_id`=188 AND `last_update`='2006-02-15 04:34:33' LIMIT 1; #start 819 end 1187 time 2020-06-17 13:12:58
DELETE FROM `sakila`.`tmpactor` WHERE `first_name`='CUBA' AND `last_name`='BIRCH' AND `actor_id`=189 AND `last_update`='2006-02-15 04:34:33' LIMIT 1; #start 819 end 1187 time 2020-06-17 13:12:58
DELETE FROM `sakila`.`tmpactor` WHERE `first_name`='AUDREY' AND `last_name`='BAILEY' AND `actor_id`=190 AND `last_update`='2006-02-15 04:34:33' LIMIT 1; #start 819 end 1187 time 2020-06-17 13:12:58

​ 2) 定位误操作sql的位置在819-1187之间，使用flashback模式生成回滚sql

python binlog2sql.py --flashback -h192.168.136.137 -P3306 -urootmha -p'rootmha' -d sakila -t tmpactor --start-file='mysql-binlog.000013' --start-position=819 --stop-position=1187

[root@ip137 binlog2sql]# python binlog2sql.py --flashback -h192.168.136.137 -P3306 -urootmha -p'rootmha' -d sakila -t tmpactor --start-file='mysql-binlog.000013' --start-position=819 --stop-position=1187
INSERT INTO `sakila`.`tmpactor`(`first_name`, `last_name`, `actor_id`, `last_update`) VALUES ('AUDREY', 'BAILEY', 190, '2006-02-15 04:34:33'); #start 819 end 1187 time 2020-06-17 13:12:58
INSERT INTO `sakila`.`tmpactor`(`first_name`, `last_name`, `actor_id`, `last_update`) VALUES ('CUBA', 'BIRCH', 189, '2006-02-15 04:34:33'); #start 819 end 1187 time 2020-06-17 13:12:58
INSERT INTO `sakila`.`tmpactor`(`first_name`, `last_name`, `actor_id`, `last_update`) VALUES ('ROCK', 'DUKAKIS', 188, '2006-02-15 04:34:33'); #start 819 end 1187 time 2020-06-17 13:12:58
INSERT INTO `sakila`.`tmpactor`(`first_name`, `last_name`, `actor_id`, `last_update`) VALUES ('RENEE', 'BALL', 187, '2006-02-15 04:34:33'); #start 819 end 1187 time 2020-06-17 13:12:58
INSERT INTO `sakila`.`tmpactor`(`first_name`, `last_name`, `actor_id`, `last_update`) VALUES ('JULIA', 'ZELLWEGER', 186, '2006-02-15 04:34:33'); #start 819 end 1187 time 2020-06-17 13:12:58
INSERT INTO `sakila`.`tmpactor`(`first_name`, `last_name`, `actor_id`, `last_update`) VALUES ('MICHAEL', 'BOLGER', 185, '2006-02-15 04:34:33'); #start 819 end 1187 time 2020-06-17 13:12:58
INSERT INTO `sakila`.`tmpactor`(`first_name`, `last_name`, `actor_id`, `last_update`) VALUES ('HUMPHREY', 'GARLAND', 184, '2006-02-15 04:34:33'); #start 819 end 1187 time 2020-06-17 13:12:58
INSERT INTO `sakila`.`tmpactor`(`first_name`, `last_name`, `actor_id`, `last_update`) VALUES ('RUSSELL', 'CLOSE', 183, '2006-02-15 04:34:33'); #start 819 end 1187 time 2020-06-17 13:12:58
INSERT INTO `sakila`.`tmpactor`(`first_name`, `last_name`, `actor_id`, `last_update`) VALUES ('DEBBIE', 'AKROYD', 182, '2006-02-15 04:34:33'); #start 819 end 1187 time 2020-06-17 13:12:58
INSERT INTO `sakila`.`tmpactor`(`first_name`, `last_name`, `actor_id`, `last_update`) VALUES ('MATTHEW', 'CARREY', 181, '2006-02-15 04:34:33'); #start 819 end 1187 time 2020-06-17 13:12:58

常用参数说明

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

3、参考链接

https://github.com/danfengcao/binlog2sql
https://www.cnblogs.com/paul8339/p/8505964.html