mysqlbinlog_flashback | myflash | binlog2.sql mysql闪回工具对比

Myflash

项目地址https://github.com/Meituan-Dianping/MyFlash

MyFlash是由美团点评公司技术工程部开发维护的一个回滚DML操作的工具。该工具通过解析v4版本的binlog，完成回滚操作。相对已有的回滚工具，其增加了更多的过滤选项，让回滚更加容易。 该工具已经在美团点评内部使用

限制

1. binlog格式必须为row,且binlog_row_image=full
2. 仅支持5.6与5.7
3. 只能回滚DML（增、删、改）

安装

[root@centos7-mysql5 MyFlash-master]# gcc -w  `pkg-config --cflags --libs glib-2.0` source/binlogParseGlib.c  -o binary/flashback

可能会报glib-2.0没安装，装上这个 libgnomeui-devel 包组即可

[root@centos7-mysql5 MyFlash-master]# yum install libgnomeui-devel

 

• 1.databaseNames

  指定需要回滚的数据库名。多个数据库可以用“,”隔开。如果不指定该参数，相当于指定了所有数据库。

• 2.tableNames

  指定需要回滚的表名。多个表可以用“,”隔开。如果不指定该参数，相当于指定了所有表。

• 3.start-position

  指定回滚开始的位置。如不指定，从文件的开始处回滚。请指定正确的有效的位置，否则无法回滚

• 4.stop-position

  指定回滚结束的位置。如不指定，回滚到文件结尾。请指定正确的有效的位置，否则无法回滚

• 5.start-datetime

  指定回滚的开始时间。注意格式必须是 %Y-%m-%d %H:%M:%S。 如不指定，则不限定时间

• 6.stop-datetime

  指定回滚的结束时间。注意格式必须是 %Y-%m-%d %H:%M:%S。 如不指定，则不限定时间

• 7.sqlTypes

  指定需要回滚的sql类型。目前支持的过滤类型是INSERT, UPDATE ,DELETE。多个类型可以用“,”隔开。

• 8.maxSplitSize

  一旦指定该参数，对文件进行固定尺寸的分割（单位为M），过滤条件有效，但不进行回滚操作。该参数主要用来将大的binlog文件切割，防止单次应用的binlog尺寸过大，对线上造成压力

• 9.binlogFileNames

  指定需要回滚的binlog文件，目前只支持单个文件，后续会增加多个文件支持

• 10.outBinlogFileNameBase

  指定输出的binlog文件前缀，如不指定，则默认为binlog_output_base.flashback

• 11.logLevel

  仅供开发者使用，默认级别为error级别。在生产环境中不要修改这个级别，否则输出过多

• 12.include-gtids

  指定需要回滚的gtid,支持gtid的单个和范围两种形式。

• 13.exclude-gtids

  指定不需要回滚的gtid，用法同include-gtids

3.example

1.回滚整个文件

./flashback --binlogFileNames=haha.000041
mysqlbinlog binlog_output_base.flashback | mysql -h<host> -u<user> -p

2.回滚该文件中的所有insert语句

./flashback  --sqlTypes='INSERT' --binlogFileNames=haha.000041
mysqlbinlog binlog_output_base.flashback | mysql -h<host> -u<user> -p

 

3.回滚大文件

回滚
./flashback --binlogFileNames=haha.000042
切割大文件
./flashback --maxSplitSize=1 --binlogFileNames=binlog_output_base.flashback
应用
mysqlbinlog binlog_output_base.flashback.000001 | mysql -h<host> -u<user> -p
...
mysqlbinlog binlog_output_base.flashback.<N> | mysql -h<host> -u<user> -p

 

 

测试

 

 

 

 

[root@centos7-mysql5 binary]# ./flashback --databaseNames='test' --tableNames='t3' --sqlTypes='DELETE' --binlogFileNames=/mysql/mysql5.7/binlog/mysql-bin.000004

[root@centos7-mysql5 binary]# ls -lrt
total 7364
-rwxr-xr-x. 1 root root 7463125 Nov 5 2020 mysqlbinlog20160408
-rwxr-xr-x. 1 root root 58720 Apr 26 20:13 flashback
-rw-r--r--. 1 root root 9873 Apr 27 00:42 binlog_output_base.flashback <<<<<<生成的回滚文件

 

回滚

[root@centos7-mysql5 binary]# mysqlbinlog binlog_output_base.flashback --skip-gtids | mysql -uroot -p
Enter password:

 数据已经回来了

 

 

 

 

mysqlbinlog_flashback

项目地址：https://github.com/58daojia-dba/mysqlbinlog_flashback

工具下载：https://www.modb.pro/download/536707

阿里的工具

需要有python才能解析

是在线读取row格式的mysqld的binlog,然后生成反向的sql语句的工具。一般用于数据恢复的目的。

所谓反向的sql语句就是如果是insert，则反向的sql为delete。如果delete,反向的sql是insert,如果是update, 反向的sql还是update,但是update的值是原来的值。

 

#使用限制

1.支持mysql版本为MySQL 5.5 and 5.6.因为底层使用的是python-mysql-replication包。

2.数据库必须是row格式的。原因看这个链接

3.反向生成的表必须有主键。

4.日志必须在主库存在

5.反向生成的mysql数据类型列出在下面。没有列出的类型没有经过严格的测试，也许有问题

6.支持的类型

允许解析的字段类型，不在里面的会报错

ALLOW_TYPE={ "varchar":True, "char":True, "datetime":True, "date":True, "time":True, "timestamp":True, "bigint":True, "mediumint":True, "smallint":True, "tinyint":True, "int":True, "smallint":True, "decimal":True, "float":True, "double":True, "longtext":True, "tinytext":True, "text":True, "mediumtext":True }

 

对mysql字符集的支持什么

utf8测试通过。gbk方式没有测试，应该问题不大。

导入mysql时一定指定，mysql ... --default-character-set=utf8mb4

##参数说明 python mysqlbinlog_back.py --help 看在线的帮助

##安装依赖的包PyMySQL和环境 python2.6

python --version有的话安装pip

curl  https://bootstrap.pypa.io/pip/2.7/get-pip.py -o get-pip.py

python get-pip.py

pip install PyMySQL

pymysql版本装的高了会报错  “ImportError: cannot import name charset_to_encoding”

 pip uninstall pymysql卸载后重新装下低版本的

 pip install pymysql~=0.9.3

  python mysqlbinlog_back.py --help命令可以正常显示说明安装成功

 

 

最简单的例子为 python mysqlbinlog_back.py --host="127.0.0.1" --username="root" --port=43306 --password="" --schema=test --table="test5"

下面是程序输出结果 ls -l log/*

-rw-r--r-- 1 root root 2592 Nov 9 15:44 log/save_data_dml_test_20161109_154434.sql
-rw-r--r-- 1 root root 1315 Nov 9 15:44 log/flashback_test_20161109_154434.sql <--- 反向sq文件
-rw-r--r-- 1 root root 441 Nov 9 15:44 log/save_data_create_table_test_20161109_154434.sql

它会在线连接参数指定mysql,读取binlog,仅仅抽取对schema为test 表名test5的binlog，生成反向sq文件保存在log目录下,其中flash_开头的文件是反向的sql语句。

用mysql命令导入数据是一定指定字符集为utf8mb4,比如

mysql ... --default-character-set=utf8mb4 test < flashback_test_20161109_154434.sql

##详细描述

mysqlbinlog_back.py在线连接参数指定mysql,读取binlog,如果缺省，它通过show binary logs命令找到最近的binlog文件，从文件开头开始解析，一直解析到当前时间退出。

如果指定开始binary log文件名和位置（BINLOG_START_FILE_NAME，BINLOG_START_FILE_POSITION），会从指定binary log文件名和位置开始解析，一直BINLOG_END_TIME结束，中间会自动扫描跨多个binlog.

生成文件目录可以通过OUTPUT_FILE_PATH来指定。目录下有2个类： 一类是反向解析的文件,格式为flashback_schema名_当前时间.sql . 另一类用于审查数据的sql,审查数据的sql用于记录操作类型，sql的老、新值。其中, save_data_create_table_开头的文件用于生成建表语句，save_data_dml用于插入到新的表中。

 

测试：

 生成回滚文件

python  mysqlbinlog_back.py --host="192.168.0.135" --username="root" --password="123456" --schema=test --table="t" --binlog_start_time="2022-05-08 19:26:00"

会在.log目录下生成回滚文件flashback_*****.sql

 回退delete

 mysql -uroot -p123456 --default-character-set=utf8mb4 test < flashback_test_20220508_193024.sql

 

 

 

 

binlog2.sql

也需要用到python

项目地址https://github.com/danfengcao/binlog2sql

安装  这个需要连接外网

没有外网比较麻烦需要先下载安装包再安装pymsyql

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

下载解压后

shell> pip install -r requirements.txt

使用

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需要的最小权限集合：

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); #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

解析模式

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

 

测试：

 

解析出标准SQL

python binlog2sql.py -h192.168.0.135 -uroot -p123456 -P3306 -dtest2 -t r --start-file mysql-bin.000017 --start-datetime="2022-05-08 19:55:00"

解析出回滚SQL

 python binlog2sql.py --flashback -h192.168.0.135 -uroot -p123456 -P3306 -dtest2 -t r --start-file mysql-bin.000017 --start-datetime="2022-05-08 19:55:00" > rollback.sql

 

回滚

mysql -uroot -p123456 test2 < rollback.sql

 

限制（对比mysqlbinlog）

• mysql server必须开启，离线模式下不能解析
• 参数 binlog_row_image 必须为FULL，暂不支持MINIMAL
• 解析速度不如mysqlbinlog

优点（对比mysqlbinlog）

• 纯Python开发，安装与使用都很简单
• 自带flashback、no-primary-key解析模式，无需再装补丁
• flashback模式下，更适合闪回实战
• 解析为标准SQL，方便理解、筛选
• 代码容易改造，可以支持更多个性化解析

 

ddl闪回工具 

Percona Data Recovery Tool for InnoDB

https://launchpad.net/percona-data-recovery-tool-for-innodb

 

下载解压后

工具包工具用途：

• page_parser- 该工具采用一些 InnoDB 表空间并将其拆分为按表空间 ID 和表 ID 分组的单独页面集。
• constraints_parser- 该工具需要一些表空间或任何其他文件，并尝试在此文件中查找您的数据，将其视为真正的 innodb 表空间的一部分。
• create_defs.pl- 此工具使用您的数据库服务器登录名/密码为您的数据库创建表定义，因此这些定义可以与约束解析器一起使用。
• split_dump.pl- 此工具将 constraints_parser 工具输出拆分为一组单独的 CSV 文件。
• show_data_dictionary（在 v.0.2+ 中完成） - 此工具尝试读取和显示指定表空间文件的 innodb 全局数据字典。
• checksum_tablespace（将在 v.0.2+ 中完成） - 此工具尝试读取 innodb 表空间并计算其校验和以检查文件是否损坏。

测试实验

 

https://dwj999.github.io/%E4%BD%BF%E7%94%A8Percona-Data-Recovery-Tool-for-InnoDB%E6%81%A2%E5%A4%8D%E6%95%B0%E6%8D%AE.html

https://ubin.top/2021/01/08/Percona-Data-Recovery-Tool-for-InnoDB/