binlog2sql 安装使用

github：

https://github.com/danfengcao/binlog2sql

1、安装

git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql

root_D-v024@:~# git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql
Cloning into 'binlog2sql'...
remote: Enumerating objects: 323, done.
remote: Total 323 (delta 0), reused 0 (delta 0), pack-reused 323
Receiving objects: 100% (323/323), 153.26 KiB | 133.00 KiB/s, done.
Resolving deltas: 100% (170/170), done.

2、执行：pip install -r requirements.txt

root_D-v024@:~/binlog2sql# pip install -r requirements.txt
-bash: pip: command not found

报错没有pip命令，手动安装一下。

安装pip

 wget "https://pypi.python.org/packages/source/p/pip/pip-1.5.4.tar.gz#md5=834b2904f92d46aaa333267fb1c922bb"  --no-check-certificate

tar xvf pip-1.5.4.tar.gz 

cd pip-1.5.4

python setup.py install

再次安装：pip install -r requirements.txt

root_D-v024@:~/binlog2sql# pip install -r requirements.txt
Downloading/unpacking PyMySQL==0.7.11 (from -r requirements.txt (line 1))
  Downloading PyMySQL-0.7.11-py2.py3-none-any.whl (78kB): 78kB downloaded
Downloading/unpacking wheel==0.29.0 (from -r requirements.txt (line 2))
  Downloading wheel-0.29.0-py2.py3-none-any.whl (66kB): 66kB downloaded
Downloading/unpacking mysql-replication==0.13 (from -r requirements.txt (line 3))
  Downloading mysql-replication-0.13.tar.gz
  Running setup.py (path:/tmp/pip_build_root/mysql-replication/setup.py) egg_info for package mysql-replication
    
Installing collected packages: PyMySQL, wheel, mysql-replication
  Running setup.py install for mysql-replication
    
Successfully installed PyMySQL wheel mysql-replication
Cleaning up...
root_D-v024@:~/binlog2sql# 

3、

制作测试数据

drop table if exists tt;
create table tt(id int primary key,name varchar(10));
insert into tt(id,name) values(1,'张三');
insert into tt(id,name) values(2,'李四');
insert into tt(id,name) values(3,'王五');

4、模拟误删除一条记录

delete from tt where id=1;

5、使用binlog2sql分析binlog，生成执行过的SQL

root_D-v024@:~/binlog2sql/binlog2sql# python binlog2sql.py -h106.60.14.126 -P3357 -uroot -p'123456' -dceshi -t tt --start-file='mysql-bin.000022'
DELETE FROM `ceshi`.`tt` WHERE `id`=1 AND `name`='张三' LIMIT 1; #start 4 end 388 time 2021-04-13 18:42:39

6、生成回滚SQL，加入 --flashback 参数

root_D-v024@:~/binlog2sql/binlog2sql# python binlog2sql.py --flashback -h106.60.14.126 -P3357 -uroot -p'123456' -dceshi -t tt --start-file='mysql-bin.000022'
INSERT INTO `ceshi`.`tt`(`id`, `name`) VALUES (1, '张三'); #start 4 end 388 time 2021-04-13 18:42:39

7、指定时间段分析binlog，生成回滚SQL

root_D-v024@:~/binlog2sql/binlog2sql# python binlog2sql.py --flashback -h106.60.14.126 -P3357 -uroot -p'123456' -dceshi -t tt --start-file='mysql-bin.000022' --start-datetime='2021-04-13 18:00:00' --stop-datetime='2021-04-13 19:00:00'
INSERT INTO `ceshi`.`tt`(`id`, `name`) VALUES (1, '张三'); #start 4 end 388 time 2021-04-13 18:42:39