【MySQL运维】使用binlog2sql闪回工具实现MySQL数据回滚

TangLu MySQL 2021-10-19 2798 0

一、binlog2sql介绍

binlog2sql可以从MySQL的binlog中解析出需要的SQL，并且可以生成原始执行SQL、回滚SQL、去除主键的INSERT SQL等，使用这个工具就不用再人工去对binlog进行解析，闪回方便。但是需要注意的是仅能针对DML进行操作，因为DDL语句并不会在binlog中记录每行数据的变化。除了binlog2sql可以实现闪回外，美团也有开源一款myflush工具实现DML语句的回滚，有兴趣的可以自行了解。

二、安装binlog2sql

#安装pip工具，等下会通过pip来安装binlog2sql
yum  install  python-pip 
#拉取binlog2sql源码，如果服务器网络有限制可能会拉不下来
git clone https://github.com/danfengcao/binlog2sql.git 
cd binlog2sql
#使用pip安装binlog2sql和依赖，相关依赖都在安装包里写好的requirements.txt中
pip install -r requirements.txt

附binlog2sql安装包

binlog2sql.zip

三、binlog2sql常用选项

1、MySQL连接配置选项
-h：指定数据库地址

-P：指定数据库端口

-u：指定数据库用户

-p：数据库用户密码

-S：使用socket登陆的话sock文件路径

2、binlog解析模式选项
--stop-never：对binlog进行持续解析，直到binlog停止更新，默认为false，通常进行闪回时也不会启用这个选项
-K, --no-primary-key：去除binlog中INSERT语句的主键，默认为False

-B, --flashback：生成回滚SQL，该选项与stop-never、no-primary-key不能同时使用
--back-interval：指定-B | --flashback时，每一千行回滚SQL后需要SLEEP的秒数，0为不等待

3、解析范围控制选项
--start-file：指定从哪个binlog开始进行解析
--start-position：--start-file所指定的binlog的起始解析位置，默认从start-file文件的起始进行解析
--stop-file：指定最后一个需要解析的binlog，默认与start-file为同个文件，若解析模式为stop-never，此选项不可用
--stop-position：--stop-file所指定的binlog的结束解析位置，默认为stop-file的结尾，若解析模式为stop-never，此选项失效
--start-datetime：以时间维度进行解析，格式'%Y-%m-%d %H:%M:%S'，默认不过滤
--stop-datetime：同上，默认不过滤

4、过滤条件
-d | --databases：指定需要解析的库，多个库用空格隔开，如-d db1 db2
-t | --tables：只解析指定的表，用于恢复表级别的数据，多张表用空格隔开，如-t tbl1 tbl2
--only-dml：只解析DML语句，忽略DDL，默认False
--sql-type：只解析指定类型的DML语句，如INSERT, UPDATE, DELETE，默认为全部解析，多个类型用空格隔开，如--sql-type INSERT DELETE

四、binlog2sql使用示例

1、在使用binlog2sql之前需要确定好误操作时间、需要恢复的库表，然后根据时间定位到具体的binlog进行解析。这次先不加--flashback，看看解析出来的binlog是啥

python /usr/local/src/binlog2sql/binlog2sql/binlog2sql.py -h 172.20.0.140 -udba -p123456 -P 3306 -d tesst_tanglu --start-file mysql-bin.001598 > /tmp/mysql-bin.001598.sql
INSERT INTO `tesst_tanglu`.`test1`(`id`) VALUES (9); #start 1016376 end 1016617 time 2021-10-19 18:34:27
INSERT INTO `tesst_tanglu`.`test1`(`id`) VALUES (10); #start 1174535 end 1174776 time 2021-10-19 18:34:29
DELETE FROM `tesst_tanglu`.`test1` WHERE `id`=1 LIMIT 1; #start 1478501 end 1478742 time 2021-10-19 18:34:33

2、确定SQL后生成回滚SQL，可以看到生成的回滚SQL将上面的SQL语句做了反向操作

python /usr/local/src/binlog2sql/binlog2sql/binlog2sql.py -h 172.20.0.140 -udba -p123456 -P 3306 -d tesst_tanglu --start-file mysql-bin.001598 --flashback > /tmp/flash_mysql-bin.001598.sql
INSERT INTO `tesst_tanglu`.`test1`(`id`) VALUES (1); #start 1478501 end 1478742 time 2021-10-19 18:34:33
DELETE FROM `tesst_tanglu`.`test1` WHERE `id`=10 LIMIT 1; #start 1174535 end 1174776 time 2021-10-19 18:34:29
DELETE FROM `tesst_tanglu`.`test1` WHERE `id`=9 LIMIT 1; #start 1016376 end 1016617 time 2021-10-19 18:34:27

3、导入回滚SQL实现数据恢复