Mysql Reverse_SQL数据闪回工具

原创

DBA实战 2024-04-17 17:43:28 博主文章分类：Mysql数据库工具 ©著作权

文章标签 mysql SQL linux 文章分类 MySQL 数据库 在51CTO的第一篇博文

©著作权归作者所有：来自51CTO博客作者DBA实战的原创作品，请联系作者获取转载授权，否则将追究法律责任

作者：吴守阳 2024-04-17 17:35:36

Mysql Reverse_SQL数据闪回工具_SQL

简介

Reverse_SQL 是一个用于解析和转换 MySQL 二进制日志（binlog）的工具。它可以将二进制日志文件中记录的数据库更改操作（如插入、更新、删除）转换为反向的 SQL 语句，以便进行数据恢复。其运行模式需二进制日志设置为 ROW 格式。

功能与特点

1、解析二进制日志：reverse_sql 能够解析 MySQL 的二进制日志文件，并还原出其中的 SQL 语句。

2、生成可读的 SQL：生成原始 SQL 和反向 SQL。

3、支持过滤和筛选：可以根据时间范围、表、DML操作等条件来过滤出具体的误操作 SQL 语句。

4、支持多线程并发解析binlog事件。

请注意！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。

安装版本

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

用户权限

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

闪回操作

Delete数据闪回

[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 NULL AND `env_code` IS NULL;

闪回语句

[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"