最新版Mysql reverse_sql数据闪回工具超强详解，入手即用！！

DBA实战

已于 2024-06-19 11:24:50 修改

阅读量854

点赞数 9

文章标签： mysql sql

于 2024-03-19 16:34:09 首次发布

本文链接：https://blog.csdn.net/qq_34692500/article/details/136846975

版权

功能点：

该工具的主要功能和特点包括：
1、解析二进制日志：reverse_sql 能够解析 MySQL 的二进制日志文件，并还原出其中的 SQL 语句。
2、生成可读的 SQL：生成原始 SQL 和反向 SQL。
3、支持过滤和筛选：可以根据时间范围、表、DML操作等条件来过滤出具体的误操作 SQL 语句。
4、支持多线程并发解析binlog事件。
5、reverse_sql 支持MySQL 5.7/8.0 和 MariaDB，适用于CentOS 7系统。
请注意！reverse_sql 只是将二进制日志还原为 SQL 语句，而不会执行这些 SQL 语句来修改数据库

原理：

调用官方 https://python-mysql-replication.readthedocs.io/ 库来实现，通过指定的时间范围，转换为timestamp时间戳，将整个时间范围平均分配给每个线程。
由于 BinLogStreamReader 并不支持指定时间戳来进行递增解析，固在每个任务开始之前，使用上一个任务处理过的 binlog_file 和 binlog_pos，这样后续的线程就可以获取
到上一个线程处理过的 binlog 文件名和 position，然后进行后续的并发处理。
假设开始时间戳 start_timestamp 是 1625558400，线程数量 num_threads 是 4，整个时间范围被平均分配给每个线程。那么，通过计算可以得到以下结果：
对于第一个线程（i=0），start_time 是 1625558400。

对于第二个线程（i=1），start_time 是 1625558400 + time_range。

对于第三个线程（i=2），start_time 是 1625558400 + 2 * time_range。

对于最后一个线程（i=3），start_time 是 1625558400 + 3 * time_range。
这样，每个线程的开始时间都会有所偏移，确保处理的时间范围没有重叠，并且覆盖了整个时间范围。最后，将结果保存在一个列表里，并对列表做升序排序，取得最终结果。

安装包下载：

reverse_sql工具版本号: 2.1.1，更新日期：2024-01-27 - 增加json格式支持
MySQL5.7版本用 https://github.com/hcymysql/reverse_sql/releases/download/reverse_sql_json/reverse_sql_mysql57
MySQL8.0版本用 https://github.com/hcymysql/reverse_sql/releases/download/reverse_sql_json/reverse_sql_mysql8

[root@test19-server07 jesong]#  chmod 655 reverse_sql_mysql8 
[root@test19-server07 jesong]#  chown  root:root reverse_sql_mysql8 

mysql8要配置这三个参数，否则不显示字段正确名称
BINLOG_ROW_METADATA=full
binlog_row_image=full
binlog_format = row

MySQL 最小化用户权限：

> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO `yourname`@`%`;
> GRANT SELECT ON `test`.* TO `yourname`@`%`;

恢复删除数据delete操作：（演示的是mysql8）

[root@test19-server07 jesong]# /jesong/reverse_sql_mysql8 -ot industry_dict -op delete -H 10.10.10.7 -P 3366 -u dbaadmin -p'n***nEFGF' -d box --binlog-file mysql-bin.000011 --start-time "2024-03-19 14:00:00" --end-time "2024-03-19 14:30:00"
vim wushouyang_industry_dict_recover_2024-03-19_15:52:33.sql
-- SQL执行时间:2024-03-19 15:50:15
-- 原生sql:
        -- DELETE FROM ``wushouyang`.`industry_dict`` WHERE `id`=72 AND `name`='医疗' AND `parent_id`=0 AND `price`=6.00 AND `create_user_id`='50' AND `created_time`='2019-11-08 18:37:25' AND `update_user_id`=NULL AND `update_time`=NULL;
-- 回滚sql:
        INSERT INTO `wushouyang`.`industry_dict`(`id`,`name`,`parent_id`,`price`,`create_user_id`,`created_time`,`update_user_id`,`update_time`) VALUES (72,'医疗',0,6.00,'50','2019-11-08 18:37:25',NULL,NULL);
-- ----------------------------------------------------------
-- ----------------------------------------------------------
-- SQL执行时间:2024-03-19 15:50:15
-- 原生sql:
        -- DELETE FROM ``wushouyang`.`industry_dict`` WHERE `id`=73 AND `name`='医美' AND `parent_id`=72 AND `price`=0.00 AND `create_user_id`='50' AND `created_time`='2019-11-08 18:37:30' AND `update_user_id`=NULL AND `update_time`=NULL;
-- 回滚sql:
        INSERT INTO `wushouyang`.`industry_dict`(`id`,`name`,`parent_id`,`price`,`create_user_id`,`created_time`,`update_user_id`,`update_time`) VALUES (73,'医美',72,0.00,'50','2019-11-08 18:37:30',NULL,NULL);
-- ----------------------------------------------------------

恢复删除数据update操作：

[root@test19-server07 jesong]#  /jesong/reverse_sql_mysql8 -ot company_id_seq -op update -H 10.10.10.7 -P 3306 -u dbaadmin -p'nfg***nEFGF' -d wushouyang --binlog-file mysql-bin.000013 --start-time "2024-03-19 14:50:00" --end-time "2024-03-19 15:59:00" --max-workers 6
[root@test19-server07 mysql]# vim  wushouyang_company_id_seq_recover_2024-03-19_15:56:33.sql
-- SQL执行时间:2024-03-19 15:55:54
-- 原生sql:
        -- UPDATE `wushouyang`.`company_id_seq` SET `id`=1,`name`='默认',`start_id`=110,`end_id`=12500,`current_id`=11217,`remark`= NULL,`env_code`= NULL WHERE `id`=1 AND `name`='默认' AND `start_id`=10500 AND `end_id`=12500 AND `current_id`=11217 AND `remark` IS NULL AND `env_code` IS NULL;
-- 回滚sql:
        UPDATE `wushouyang`.`company_id_seq` SET `id`=1,`name`='默认',`start_id`=10500,`end_id`=12500,`current_id`=11217,`remark`=NULL,`env_code`=NULL WHERE `id`=1 AND `name`='默认' AND `start_id`=110 AND `end_id`=12500 AND `current_id`=11217 AND `remark` IS NULL AND `env_code` IS NULL;
-- ----------------------------------------------------------
-- SQL执行时间:2024-03-19 15:55:54
-- 原生sql:
        -- UPDATE `wushouyang`.`company_id_seq` SET `id`=2,`name`='教育',`start_id`=110,`end_id`=14500,`current_id`=13727,`remark`= NULL,`env_code`= NULL WHERE `id`=2 AND `name`='教育' AND `start_id`=12501 AND `end_id`=14500 AND `current_id`=13727 AND `remark` IS NULL AND `env_code` IS NULL;
-- 回滚sql:
        UPDATE `wushouyang`.`company_id_seq` SET `id`=2,`name`='教育',`start_id`=12501,`end_id`=14500,`current_id`=13727,`remark`=NULL,`env_code`=NULL WHERE `id`=2 AND `name`='教育' AND `start_id`=110 AND `end_id`=14500 AND `current_id`=13727 AND `remark` IS

将update替换为replace：

[root@test19-server07 mysql]# vim  wushouyang_company_id_seq_recover_2024-03-19_15:57:02_replace.sql
-- SQL执行时间:2024-03-19 15:55:54
-- 原生sql:
        -- UPDATE `wushouyang`.`company_id_seq` SET `id`=1,`name`='默认',`start_id`=110,`end_id`=12500,`current_id`=11217,`remark`= NULL,`env_code`= NULL WHERE `id`=1 AND `name`='默认' AND `start_id`=10500 AND `end_id`=12500 AND `current_id`=11217 AND `remark` IS NULL AND `env_code` IS NULL;
-- 回滚sql:
        REPLACE INTO `wushouyang`.`company_id_seq` (`id`,`name`,`start_id`,`end_id`,`current_id`,`remark`,`env_code`) VALUES (1,'默认',10500,12500,11217,NULL,NULL);
-- ----------------------------------------------------------
-- SQL执行时间:2024-03-19 15:55:54
-- 原生sql:
        -- UPDATE `wushouyang`.`company_id_seq` SET `id`=2,`name`='教育',`start_id`=110,`end_id`=14500,`current_id`=13727,`remark`= NULL,`env_code`= NULL WHERE `id`=2 AND `name`='教育' AND `start_id`=12501 AND `end_id`=14500 AND `current_id`=13727 AND `remark` IS NULL AND `env_code` IS NULL;
-- 回滚sql:
        REPLACE INTO `wushouyang`.`company_id_seq` (`id`,`name`,`start_id`,`end_id`,`current_id`,`remark`,`env_code`) VALUES (2,'教育',12501,14500,13727,NULL,NULL);

sql文件拆分：

sql文件的内容过多，也可以通过awk命令进行分割，以便更容易进行排查
[root@test19-server07 mysql]# awk '/^-- SQL执行时间/{filename = "output" ++count ".sql"; print > filename; next} {print > filename}' ./wushouyang_company_id_seq_recover_2024-03-19_15:57:02_replace.sql
[root@test19-server07 mysql]# ls
-rw-r--r-- 1 root  root   740 Mar 19 16:02  output10.sql
-rw-r--r-- 1 root  root   659 Mar 19 16:02  output11.sql
-rw-r--r-- 1 root  root   674 Mar 19 16:02  output12.sql

参数详解：

[root@test19-server07 jesong]# ./reverse_sql_mysql8 --help

usage: reverse_sql_mysql8 [-h] [-ot ONLY_TABLES [ONLY_TABLES ...]] [-op ONLY_OPERATION] -H MYSQL_HOST -P MYSQL_PORT -u MYSQL_USER -p MYSQL_PASSWD -d MYSQL_DATABASE [-c MYSQL_CHARSET] --binlog-file BINLOG_FILE [--binlog-pos BINLOG_POS]
                          --start-time ST --end-time ET [--max-workers MAX_WORKERS] [--print] [--replace] [-v]

Binlog数据恢复，生成反向SQL语句。

options:
  -h, --help            show this help message and exit
  -ot ONLY_TABLES [ONLY_TABLES ...], --only-tables ONLY_TABLES [ONLY_TABLES ...]
                        设置要恢复的表，多张表用,逗号分隔
  -op ONLY_OPERATION, --only-operation ONLY_OPERATION
                        设置误操作时的命令（insert/update/delete）
  -H MYSQL_HOST, --mysql-host MYSQL_HOST
                        MySQL主机名
  -P MYSQL_PORT, --mysql-port MYSQL_PORT
                        MySQL端口号
  -u MYSQL_USER, --mysql-user MYSQL_USER
                        MySQL用户名
  -p MYSQL_PASSWD, --mysql-passwd MYSQL_PASSWD
                        MySQL密码
  -d MYSQL_DATABASE, --mysql-database MYSQL_DATABASE
                        MySQL数据库名
  -c MYSQL_CHARSET, --mysql-charset MYSQL_CHARSET
                        MySQL字符集，默认utf8
  --binlog-file BINLOG_FILE
                        Binlog文件
  --binlog-pos BINLOG_POS
                        Binlog位置，默认4
  --start-time ST       起始时间
  --end-time ET         结束时间
  --max-workers MAX_WORKERS
                        线程数，默认4（并发越高，锁的开销就越大，适当调整并发数）
  --print               将解析后的SQL输出到终端
  --replace             将update转换为replace操作
  -v, --version         show program's version number and exit

Example usage:
    shell> ./reverse_sql -ot table1 -op delete -H 192.168.198.239 -P 3336 -u admin -p hechunyang -d hcy \
            --binlog-file mysql-bin.000124 --start-time "2023-07-06 10:00:00" --end-time "2023-07-06 22:00:00"

数据库技术交流群：