暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / 误操作后快速恢复数据 binlog 解析为反向 SQL

误操作后快速恢复数据 binlog 解析为反向 SQL

GreatSQL社区 2025-07-21
85

* GreatSQL社区原创内容未经授权不得随意使用，转载请联系小编并注明来源。

作者简介：烂笔头，万里数据库DBA，好记性不如烂笔头，技术之路在于沉淀与分享。

1.前言

本文将介绍使用 reverse_sql 工具将 GreatSQL 数据库的 binlog 解析为反向 SQL 语句。模拟误操作后，恢复数据。该工具可以帮助客户在发生事故时快速恢复数据，避免进一步的损失。使用 reverse_sql
 工具非常简单，客户只需要指定肇事时间和表名即可。该工具会根据指定的时间点，在数据库中查找并还原该表在该时间点之前的数据状态。这样客户就能轻松地实现数据恢复，防止因意外操作或其他问题导致的数据丢失。

2.reverse_sql 工具简介

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

下载地址：

https://github.com/hcymysql/reverse_sql

3.工具特点

该工具的主要功能和特点包括(针对 GreatSQL)：

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

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

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

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

注意：reverse_sql
 只是将二进制日志还原为 SQL 语句，而不会执行这些 SQL 语句来修改数据库。

4.使用前检查

4.1 GreatSQL 环境检查

首先需要确认二进制日志设置是 ROW 格式以及 row_image 是 FULL。

工具运行时，首先会进行GreatSQL的环境检测（if binlog_format != 'ROW' and binlog_row_image != 'FULL'
），如果不同时满足这两个条件，程序直接退出。

greatsql> SHOW VARIABLESLIKE'%binlog%';
+------------------------------------------------+----------------------+
| Variable_name                                  | Value                |
+------------------------------------------------+----------------------+
| binlog_cache_size                              | 32768                |
| binlog_checksum                                | CRC32                |
| binlog_ddl_skip_rewrite                        | OFF                  |
| binlog_direct_non_transactional_updates        | OFF                  |
| binlog_encryption                              | OFF                  |
| binlog_error_action                            | ABORT_SERVER         |
| binlog_expire_logs_auto_purge                  | ON                   |
| binlog_expire_logs_seconds                     | 2592000              |
| binlog_format                                  | ROW                  |
| binlog_group_commit_sync_delay                 | 0                    |
| binlog_group_commit_sync_no_delay_count        | 0                    |
| binlog_gtid_simple_recovery                    | ON                   |
| binlog_max_flush_queue_time                    | 0                    |
| binlog_order_commits                           | ON                   |
| binlog_rotate_encryption_master_key_at_startup | OFF                  |
| binlog_row_event_max_size                      | 8192                 |
| binlog_row_image                               | FULL                 |
| binlog_row_metadata                            | MINIMAL              |
| binlog_row_value_options                       |                      |
| binlog_rows_query_log_events                   | OFF                  |
| binlog_skip_flush_commands                     | OFF                  |
| binlog_space_limit                             | 0                    |
| binlog_stmt_cache_size                         | 32768                |
| binlog_transaction_compression                 | OFF                  |
| binlog_transaction_compression_level_zstd      | 3                    |
| binlog_transaction_dependency_history_size     | 25000                |
| binlog_transaction_dependency_tracking         | WRITESET             |
| have_backup_safe_binlog_info                   | YES                  |
| innodb_api_enable_binlog                       | OFF                  |
| log_statements_unsafe_for_binlog               | ON                   |
| max_binlog_cache_size                          | 4294967296           |
| max_binlog_size                                | 1073741824           |
| max_binlog_stmt_cache_size                     | 18446744073709547520 |
| rpl_read_binlog_speed_limit                    | 0                    |
| sync_binlog                                    | 1                    |
+------------------------------------------------+----------------------+
35 rows in set (0.01 sec)

4.2 参数binlog_format解析

GreatSQL 支持三种二进制日志格式：

  1. STATEMENT (基于语句的复制 - SBR)

    • 记录实际执行的 SQL 语句

    • 优点：日志文件较小，记录的是语句而非数据变更

    • 缺点：某些非确定性函数(如 NOW(), UUID(), RAND())可能导致主从数据不一致

  2. ROW (基于行的复制 - RBR) GreatSQL 默认使用 ROW 作为二进制日志格式。

    • 记录每行数据的变化情况

    • 优点：最安全的复制方式，能准确复制数据变更

    • 缺点：日志文件较大，特别是批量操作时

  3. MIXED (混合模式)

    • 默认使用 STATEMENT 格式，但在某些情况下自动切换到 ROW 格式

4.3 参数binlog_row_image解析

binlog_row_image 是 GreatSQL 中控制二进制日志记录的参数，它决定了在使用基于行的复制时，二进制日志中的行镜像如何被记录。

参数选项

binlog_row_image 参数有三个可选值：

  • FULL：记录每一行的变更，包括所有列的前后镜像。-- 生产环境建议设置为 FULL 最佳。
  • MINIMAL：binlog日志的前镜像只记录唯一识别列(唯一索引列、主键列)，后镜像只记录修改列。只记录必要的列，即在更新操作中只记录变更的列和用于识别行的最小列集。
  • NOBLOB：类似于 FULL，但不包括 BLOB 和 TEXT 类型的列，除非它们是必要的。

使用场景

  • 当设置为 FULL 时，GreatSQL 记录所有列的变更，这可以确保数据的完整性，但可能会导致二进制日志的大小增加。
  • 设置为 MINIMAL 可以减少日志的大小，因为它只记录变更的列和必要的列，这对于减少磁盘 I/O 和网络传输是有益的。
  • NOBLOB 选项适用于那些不希望记录大型 BLOB 或 TEXT 数据的场景，但仍然需要记录其他类型列的变更。

5.工具与数据库用户赋权

5.1 工具赋予执行权限

在服务器上解压后，授权：

$ ll rev*
-rwxr-xr-x 1 root root 50780824 Apr 28 14:23 reverse_sql_mysql8
$ chmod 755 reverse_sql_mysql8 

5.2 数据库用户赋权(最小化用户权限)

greatsql> CREATE USER'test'@'%'identifiedBY'test';
Query OK, 0 rows affected (0.15 sec)

greatsql> GRANTREPLICATIONSLAVE, REPLICATIONCLIENTON *.* TO`test`@`%`;
Query OK, 0 rows affected (0.01 sec)

greatsql> GRANTSELECTON`test`.* TO`test`@`%`;
Query OK, 0 rows affected (0.01 sec)

greatsql> SHOWgrantsFORtest;
+------------------------------------------------------------------+
| Grants FOR test@%                                                |
+------------------------------------------------------------------+
| GRANTREPLICATIONSLAVE, REPLICATIONCLIENTON *.* TO`test`@`%` |
| GRANTSELECTON`test`.* TO`test`@`%`                           |
+------------------------------------------------------------------+
2rowsinset (0.00 sec)

5.3 查看使用帮助信息

$ ./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" 

6.案例演示

6.1 创建测试数据，模拟误更新

greatsql> CREATE TABLE t1 (  
         idINT AUTO_INCREMENT PRIMARY KEY,  
         nameVARCHAR(50),  
         age INT,  
         email VARCHAR(100),  
         gender ENUM('Male', 'Female', 'Other'),  
         salary DECIMAL(10,2),  
         join_date DATE,  
         is_active BOOLEAN
     );
Query OK, 0 rows affected (0.04 sec)

greatsql> INSERTINTO t1 (name, age, email, gender, salary, join_date, is_active) VALUES
     ('张三', 28, 'zhangsan@example.com', 'Male', 8500.00, '2020-05-15', TRUE),  
     ('李四', 32, 'lisi@example.com', 'Male', 9200.50, '2019-08-22', TRUE),  
     ('王五', 25, 'wangwu@example.com', 'Male', 7800.00, '2021-03-10', TRUE),  
     ('赵六', 29, 'zhaoliu@example.com', 'Female', 8800.75, '2020-11-05', TRUE),  
     ('肖七', 35, 'xiaoqi@example.com', 'Female', 10500.00, '2018-06-18', FALSE),  
     ('孙八', 27, 'sunba@example.com', 'Male', 8100.00, '2021-01-30', TRUE),  
     ('周九', 31, 'zhoujiu@example.com', 'Other', 9500.25, '2019-09-12', TRUE),  
     ('吴十', 24, 'wushi@example.com', 'Female', 7600.50, '2022-02-14', TRUE),  
     ('郑十一', 30, 'zhengshiyi@example.com', 'Male', 8900.00, '2020-07-25', FALSE),  
     ('王十二', 33, 'wangshier@example.com', 'Female', 10000.00, '2019-04-08', TRUE);
Query OK, 10 rows affected (0.01 sec)
Records: 10  Duplicates: 0  Warnings: 0

greatsql> SELECT * FROM t1;
+----+-----------+------+------------------------+--------+----------+------------+-----------+
| id | name      | age  | email                  | gender | salary   | join_date  | is_active |
+----+-----------+------+------------------------+--------+----------+------------+-----------+
|  1 | 张三      |   28 | zhangsan@example.com   | Male   |  8500.00 | 2020-05-15 |         1 |
|  2 | 李四      |   32 | lisi@example.com       | Male   |  9200.50 | 2019-08-22 |         1 |
|  3 | 王五      |   25 | wangwu@example.com     | Male   |  7800.00 | 2021-03-10 |         1 |
|  4 | 赵六      |   29 | zhaoliu@example.com    | Female |  8800.75 | 2020-11-05 |         1 |
|  5 | 肖七      |   35 | xiaoqi@example.com     | Female | 10500.00 | 2018-06-18 |         0 |
|  6 | 孙八      |   27 | sunba@example.com      | Male   |  8100.00 | 2021-01-30 |         1 |
|  7 | 周九      |   31 | zhoujiu@example.com    | Other  |  9500.25 | 2019-09-12 |         1 |
|  8 | 吴十      |   24 | wushi@example.com      | Female |  7600.50 | 2022-02-14 |         1 |
|  9 | 郑十一    |   30 | zhengshiyi@example.com | Male   |  8900.00 | 2020-07-25 |         0 |
| 10 | 王十二    |   33 | wangshier@example.com  | Female | 10000.00 | 2019-04-08 |         1 |
+----+-----------+------+------------------------+--------+----------+------------+-----------+
10 rows in set (0.00 sec)

greatsql> UPDATE t1   
     SET salary = 9800.00, is_active = TRUE   
     WHEREid = 5;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

greatsql> SELECT * FROM t1;
+----+-----------+------+------------------------+--------+----------+------------+-----------+
| id | name      | age  | email                  | gender | salary   | join_date  | is_active |
+----+-----------+------+------------------------+--------+----------+------------+-----------+
|  1 | 张三      |   28 | zhangsan@example.com   | Male   |  8500.00 | 2020-05-15 |         1 |
|  2 | 李四      |   32 | lisi@example.com       | Male   |  9200.50 | 2019-08-22 |         1 |
|  3 | 王五      |   25 | wangwu@example.com     | Male   |  7800.00 | 2021-03-10 |         1 |
|  4 | 赵六      |   29 | zhaoliu@example.com    | Female |  8800.75 | 2020-11-05 |         1 |
|  5 | 肖七      |   35 | xiaoqi@example.com     | Female |  9800.00 | 2018-06-18 |         1 |
|  6 | 孙八      |   27 | sunba@example.com      | Male   |  8100.00 | 2021-01-30 |         1 |
|  7 | 周九      |   31 | zhoujiu@example.com    | Other  |  9500.25 | 2019-09-12 |         1 |
|  8 | 吴十      |   24 | wushi@example.com      | Female |  7600.50 | 2022-02-14 |         1 |
|  9 | 郑十一    |   30 | zhengshiyi@example.com | Male   |  8900.00 | 2020-07-25 |         0 |
| 10 | 王十二    |   33 | wangshier@example.com  | Female | 10000.00 | 2019-04-08 |         1 |
+----+-----------+------+------------------------+--------+----------+------------+-----------+
10 rows in set (0.00 sec)

6.2 解析binlog

查看 binlog 当前信息

greatsql> SHOW MASTERSTATUS \G
*************************** 1.row ***************************
             File: binlog.000002
         Position: 2918
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 2a3248f7-e762-11ef-ae09-00163e11ac96:1,
3837053e-e762-11ef-ade8-00163e2cc6be:1-4008,
615fadb3-234d-11f0-ab29-00163e2cc6be:1-5,
aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1:1-1067166
1rowinset (0.00 sec)

执行解析命令

 ./reverse_sql_mysql8 -u 'test' -p 'test' -H 172.17.136.70 -P 3301 -d test_111 -ot t1 --binlog-file greatsql/dbdata/3301/log/binlog.000009 --start-time "2025-05-22 16:30:00" --end-time "2025-05-22 16:40:00"
 Processing binlogevents: 100%|██████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████| 1/1 [00:00<00:00, 50.32event/s]
Processing binlogevents: 100%|█████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████| 2/2 [00:00<00:00, 111.72event/s]
Processing binlogevents: 0event [00:00, ?event/s]
Processing binlogevents: 0event [00:00, ?event/s]

当出现误操作时，只需指定误操作的时间段，其对应的 binlog 文件（通常可以通过 show master status 得到当前的 binlog 文件名）以及刚才误操作的表，和具体的 DML 命令，比如 update 或者 delete。【不支持一次解析多个 binlog】

6.3 查看解析文件

$ ll test*
-rw-r--r-- 1 root root 5892 Apr 28 15:47 test_111_t1_recover_2025-04-28_15:47:17.sql
$ 
$ cat test_111_t1_recover_2025-04-28_15:47:17.sql 
-- SQL执行时间:2025-04-28 15:41:32
-- 原生sql:
         -- INSERT INTO `test_111`.`t1`(`id`,`name`,`age`,`email`,`gender`,`salary`,`join_date`,`is_active`) VALUES (1,'张三',28,'zhangsan@example.com','Male',8500.00,'2020-05-15',1);
-- 回滚sql:
         DELETEFROM`test_111`.`t1`WHERE`id`=1AND`name`='张三'AND`age`=28AND`email`='zhangsan@example.com'AND`gender`='Male'AND`salary`=8500.00AND`join_date`='2020-05-15'AND`is_active`=1;
-- ----------------------------------------------------------
-- SQL执行时间:2025-04-28 15:41:32
-- 原生sql:
         -- INSERT INTO `test_111`.`t1`(`id`,`name`,`age`,`email`,`gender`,`salary`,`join_date`,`is_active`) VALUES (2,'李四',32,'lisi@example.com','Male',9200.50,'2019-08-22',1);
-- 回滚sql:
         DELETEFROM`test_111`.`t1`WHERE`id`=2AND`name`='李四'AND`age`=32AND`email`='lisi@example.com'AND`gender`='Male'AND`salary`=9200.50AND`join_date`='2019-08-22'AND`is_active`=1;
-- ----------------------------------------------------------
-- SQL执行时间:2025-04-28 15:41:32
-- 原生sql:
         -- INSERT INTO `test_111`.`t1`(`id`,`name`,`age`,`email`,`gender`,`salary`,`join_date`,`is_active`) VALUES (3,'王五',25,'wangwu@example.com','Male',7800.00,'2021-03-10',1);
-- 回滚sql:
         DELETEFROM`test_111`.`t1`WHERE`id`=3AND`name`='王五'AND`age`=25AND`email`='wangwu@example.com'AND`gender`='Male'AND`salary`=7800.00AND`join_date`='2021-03-10'AND`is_active`=1;
-- ----------------------------------------------------------
-- SQL执行时间:2025-04-28 15:41:32
-- 原生sql:
         -- INSERT INTO `test_111`.`t1`(`id`,`name`,`age`,`email`,`gender`,`salary`,`join_date`,`is_active`) VALUES (4,'赵六',29,'zhaoliu@example.com','Female',8800.75,'2020-11-05',1);
-- 回滚sql:
         DELETEFROM`test_111`.`t1`WHERE`id`=4AND`name`='赵六'AND`age`=29AND`email`='zhaoliu@example.com'AND`gender`='Female'AND`salary`=8800.75AND`join_date`='2020-11-05'AND`is_active`=1;
-- ----------------------------------------------------------
-- SQL执行时间:2025-04-28 15:41:32
-- 原生sql:
         -- INSERT INTO `test_111`.`t1`(`id`,`name`,`age`,`email`,`gender`,`salary`,`join_date`,`is_active`) VALUES (5,'肖七',35,'xiaoqi@example.com','Female',10500.00,'2018-06-18',0);
-- 回滚sql:
         DELETEFROM`test_111`.`t1`WHERE`id`=5AND`name`='肖七'AND`age`=35AND`email`='xiaoqi@example.com'AND`gender`='Female'AND`salary`=10500.00AND`join_date`='2018-06-18'AND`is_active`=0;
-- ----------------------------------------------------------
-- SQL执行时间:2025-04-28 15:41:32
-- 原生sql:
         -- INSERT INTO `test_111`.`t1`(`id`,`name`,`age`,`email`,`gender`,`salary`,`join_date`,`is_active`) VALUES (6,'孙八',27,'sunba@example.com','Male',8100.00,'2021-01-30',1);
-- 回滚sql:
         DELETEFROM`test_111`.`t1`WHERE`id`=6AND`name`='孙八'AND`age`=27AND`email`='sunba@example.com'AND`gender`='Male'AND`salary`=8100.00AND`join_date`='2021-01-30'AND`is_active`=1;
-- ----------------------------------------------------------
-- SQL执行时间:2025-04-28 15:41:32
-- 原生sql:
         -- INSERT INTO `test_111`.`t1`(`id`,`name`,`age`,`email`,`gender`,`salary`,`join_date`,`is_active`) VALUES (7,'周九',31,'zhoujiu@example.com','Other',9500.25,'2019-09-12',1);
-- 回滚sql:
         DELETEFROM`test_111`.`t1`WHERE`id`=7AND`name`='周九'AND`age`=31AND`email`='zhoujiu@example.com'AND`gender`='Other'AND`salary`=9500.25AND`join_date`='2019-09-12'AND`is_active`=1;
-- ----------------------------------------------------------
-- SQL执行时间:2025-04-28 15:41:32
-- 原生sql:
         -- INSERT INTO `test_111`.`t1`(`id`,`name`,`age`,`email`,`gender`,`salary`,`join_date`,`is_active`) VALUES (8,'吴十',24,'wushi@example.com','Female',7600.50,'2022-02-14',1);
-- 回滚sql:
         DELETEFROM`test_111`.`t1`WHERE`id`=8AND`name`='吴十'AND`age`=24AND`email`='wushi@example.com'AND`gender`='Female'AND`salary`=7600.50AND`join_date`='2022-02-14'AND`is_active`=1;
-- ----------------------------------------------------------
-- SQL执行时间:2025-04-28 15:41:32
-- 原生sql:
         -- INSERT INTO `test_111`.`t1`(`id`,`name`,`age`,`email`,`gender`,`salary`,`join_date`,`is_active`) VALUES (9,'郑十一',30,'zhengshiyi@example.com','Male',8900.00,'2020-07-25',0);
-- 回滚sql:
         DELETEFROM`test_111`.`t1`WHERE`id`=9AND`name`='郑十一'AND`age`=30AND`email`='zhengshiyi@example.com'AND`gender`='Male'AND`salary`=8900.00AND`join_date`='2020-07-25'AND`is_active`=0;
-- ----------------------------------------------------------
-- SQL执行时间:2025-04-28 15:41:32
-- 原生sql:
         -- INSERT INTO `test_111`.`t1`(`id`,`name`,`age`,`email`,`gender`,`salary`,`join_date`,`is_active`) VALUES (10,'王十二',33,'wangshier@example.com','Female',10000.00,'2019-04-08',1);
-- 回滚sql:
         DELETEFROM`test_111`.`t1`WHERE`id`=10AND`name`='王十二'AND`age`=33AND`email`='wangshier@example.com'AND`gender`='Female'AND`salary`=10000.00AND`join_date`='2019-04-08'AND`is_active`=1;
-- ----------------------------------------------------------
-- SQL执行时间:2025-04-28 15:41:57
-- 原生sql:
         -- UPDATE `test_111`.`t1` SET `id`=5,`name`='肖七',`age`=35,`email`='xiaoqi@example.com',`gender`='Female',`salary`=9800.00,`join_date`='2018-06-18',`is_active`=1 WHERE `id`=5 AND `name`='肖七' AND `age`=35 AND `email`='xiaoqi@example.com' AND `gender`='Female' AND `salary`=10500.00 AND `join_date`='2018-06-18' AND `is_active`=0;
-- 回滚sql:
         UPDATE`test_111`.`t1`SET`id`=5,`name`='肖七',`age`=35,`email`='xiaoqi@example.com',`gender`='Female',`salary`=10500.00,`join_date`='2018-06-18',`is_active`=0WHERE`id`=5AND`name`='肖七'AND`age`=35AND`email`='xiaoqi@example.com'AND`gender`='Female'AND`salary`=9800.00AND`join_date`='2018-06-18'AND`is_active`=1;
-- ----------------------------------------------------------
$ 

工具运行后，会在当前目录下生成一个 {db}_{table}_recover.sql 文件，保存着原生 SQL（原生 SQL 会加注释） 和反向 SQL，如果想将结果输出到前台终端，可以指定 --print 选项。

$ ./reverse_sql_mysql8 -u 'test' -p 'test' -H 172.17.136.70 -P 3301 -d test_111 -ot t1 --binlog-file greatsql/dbdata/3301/log/binlog.000008 --start-time "2025-05-21 11:10:00" --end-time "2025-05-21 11:18:00" --print
Processing binlogevents: 100%|█████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████| 4/4 [00:00<00:00, 258.00event/s]
Processing binlogevents: 100%|█████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████| 4/4 [00:00<00:00, 292.57event/s]
Processing binlogevents: 100%|█████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████| 5/5 [00:00<00:00, 277.56event/s]
Processing binlogevents: 0event [00:00, ?event/s]
-- SQL执行时间:2025-05-2111:14:01
-- 原生sql:
         -- INSERT INTO `test_111`.`t1`(`id`,`name`,`age`,`email`,`gender`,`salary`,`join_date`,`is_active`) VALUES (1,'张三',28,'zhangsan@example.com','Male',8500.00,'2020-05-15',1); 
-- 回滚sql:
         DELETE FROM `test_111`.`t1` WHERE `id`=1 AND `name`='张三' AND `age`=28 AND `email`='zhangsan@example.com' AND `gender`='Male' AND `salary`=8500.00 AND `join_date`='2020-05-15' AND `is_active`=1;
-- ----------------------------------------------------------

-- SQL执行时间:2025-05-2111:14:01
-- 原生sql:
         -- INSERT INTO `test_111`.`t1`(`id`,`name`,`age`,`email`,`gender`,`salary`,`join_date`,`is_active`) VALUES (2,'李四',32,'lisi@example.com','Male',9200.50,'2019-08-22',1); 
-- 回滚sql:
         DELETE FROM `test_111`.`t1` WHERE `id`=2 AND `name`='李四' AND `age`=32 AND `email`='lisi@example.com' AND `gender`='Male' AND `salary`=9200.50 AND `join_date`='2019-08-22' AND `is_active`=1;
-- ----------------------------------------------------------

如果你想把 update 操作转换为 replace，指定 --replace 选项即可，同时会在当前目录下生成一个{db}_{table}_recover_replace.sql 文件。

$ ./reverse_sql_mysql8 -u 'test' -p 'test' -H 172.17.136.70 -P 3301 -d test_111 -ot t1 --binlog-file greatsql/dbdata/3301/log/binlog.000008 --start-time "2025-05-21 11:10:00" --end-time "2025-05-21 11:18:00" --replace
Processing binlogevents: 100%|██████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████| 4/4 [00:00<00:00, 83.25event/s]
Processing binlogevents: 100%|█████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████| 4/4 [00:00<00:00, 112.54event/s]
Processing binlogevents: 100%|█████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████| 5/5 [00:00<00:00, 172.61event/s]
Processing binlogevents: 0event [00:00, ?event/s]
$ ll
-rwxr-xr-x 1 root    root    50780824 May 21 10:28 reverse_sql_mysql8
-rw-r--r-- 1 root    root         646 May 21 11:19 test_111_t1_recover_2025-05-21_11:19:28_replace.sql
-rw-r--r-- 1 root    root        5892 May 21 11:19 test_111_t1_recover_2025-05-21_11:19:28.sql
$ cat test_111_t1_recover_2025-05-21_11:19:28_replace.sql 
-- SQL执行时间:2025-05-21 11:14:36
-- 原生sql:
         -- UPDATE `test_111`.`t1` SET `id`=5,`name`='钱七',`age`=35,`email`='xiaoqi@example.com',`gender`='Female',`salary`=9800.00,`join_date`='2018-06-18',`is_active`=1 WHERE `id`=5 AND `name`='钱七' AND `age`=35 AND `email`='xiaoqi@example.com' AND `gender`='Female' AND `salary`=10500.00 AND `join_date`='2018-06-18' AND `is_active`=0;
-- 回滚sql:
         REPLACEINTO`test_111`.`t1` (`id`,`name`,`age`,`email`,`gender`,`salary`,`join_date`,`is_active`) VALUES (5,'钱七',35,'xiaoqi@example.com','Female',10500.00,'2018-06-18',0);
-- ----------------------------------------------------------

但注意，如果字段值中包含换行符，则原生 SQL 后几行不会被注释，需要手动处理。

6.4 拿到反向SQL恢复数据

在{db}_{table}_recover.sql 文件中找到刚才误操作的 DML 语句，然后在 GreatSQL 数据库中执行逆向工程后的 SQL 以恢复数据。

-- SQL执行时间:2025-04-2815:41:57
-- 原生sql:
         -- UPDATE `test_111`.`t1` SET `id`=5,`name`='肖七',`age`=35,`email`='xiaoqi@example.com',`gender`='Female',`salary`=9800.00,`join_date`='2018-06-18',`is_active`=1 WHERE `id`=5 AND `name`='肖七' AND `age`=35 AND `email`='xiaoqi@example.com' AND `gender`='Female' AND `salary`=10500.00 AND `join_date`='2018-06-18' AND `is_active`=0;
-- 回滚sql:
         UPDATE `test_111`.`t1` SET `id`=5,`name`='肖七',`age`=35,`email`='xiaoqi@example.com',`gender`='Female',`salary`=10500.00,`join_date`='2018-06-18',`is_active`=0 WHERE `id`=5 AND `name`='肖七' AND `age`=35 AND `email`='xiaoqi@example.com' AND `gender`='Female' AND `salary`=9800.00 AND `join_date`='2018-06-18' AND `is_active`=1;
greatsql> UPDATE `test_111`.`t1` SET `id`=5,`name`='肖七',`age`=35,`email`='xiaoqi@example.com',`gender`='Female',`salary`=10500.00,`join_date`='2018-06-18',`is_active`=0 WHERE `id`=5 AND `name`='肖七' AND `age`=35 AND `email`='xiaoqi@example.com' AND `gender`='Female' AND `salary`=9800.00 AND `join_date`='2018-06-18' AND `is_active`=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

greatsql> SELECT * FROM t1;
+----+-----------+------+------------------------+--------+----------+------------+-----------+
| id | name      | age  | email                  | gender | salary   | join_date  | is_active |
+----+-----------+------+------------------------+--------+----------+------------+-----------+
|  1 | 张三      |   28 | zhangsan@example.com   | Male   |  8500.00 | 2020-05-15 |         1 |
|  2 | 李四      |   32 | lisi@example.com       | Male   |  9200.50 | 2019-08-22 |         1 |
|  3 | 王五      |   25 | wangwu@example.com     | Male   |  7800.00 | 2021-03-10 |         1 |
|  4 | 赵六      |   29 | zhaoliu@example.com    | Female |  8800.75 | 2020-11-05 |         1 |
|  5 | 肖七      |   35 | xiaoqi@example.com     | Female | 10500.00 | 2018-06-18 |         0 |
|  6 | 孙八      |   27 | sunba@example.com      | Male   |  8100.00 | 2021-01-30 |         1 |
|  7 | 周九      |   31 | zhoujiu@example.com    | Other  |  9500.25 | 2019-09-12 |         1 |
|  8 | 吴十      |   24 | wushi@example.com      | Female |  7600.50 | 2022-02-14 |         1 |
|  9 | 郑十一    |   30 | zhengshiyi@example.com | Male   |  8900.00 | 2020-07-25 |         0 |
| 10 | 王十二    |   33 | wangshier@example.com  | Female | 10000.00 | 2019-04-08 |         1 |
+----+-----------+------+------------------------+--------+----------+------------+-----------+
10 rows inset (0.00 sec)

可以看到这条误更新的数据已经恢复到最初状态。

如果 {db}_{table}_recover.sql 文件的内容过多，也可以通过 awk 命令进行分割，以便更容易进行排查。

$ awk '/^-- SQL执行时间/{filename = "output" ++count ".sql"; print > filename; next} {print > filename}' test_111_t1_recover_2025-04-28_15:47:17.sql
$ ll output*
-rw-r--r-- 1 root root 524 Apr 2816:22 output10.sql
-rw-r--r-- 1 root root 802 Apr 2816:22 output11.sql
-rw-r--r-- 1 root root 508 Apr 2816:22 output1.sql
-rw-r--r-- 1 root root 500 Apr 2816:22 output2.sql
-rw-r--r-- 1 root root 504 Apr 2816:22 output3.sql
-rw-r--r-- 1 root root 510 Apr 2816:22 output4.sql
-rw-r--r-- 1 root root 510 Apr 2816:22 output5.sql
-rw-r--r-- 1 root root 502 Apr 2816:22 output6.sql
-rw-r--r-- 1 root root 508 Apr 2816:22 output7.sql
-rw-r--r-- 1 root root 506 Apr 2816:22 output8.sql
-rw-r--r-- 1 root root 518 Apr 2816:22 output9.sql
$ cat output1.sql
-- SQL执行时间:2025-04-2815:41:32
-- 原生sql:
         -- INSERT INTO `test_111`.`t1`(`id`,`name`,`age`,`email`,`gender`,`salary`,`join_date`,`is_active`) VALUES (1,'张三',28,'zhangsan@example.com','Male',8500.00,'2020-05-15',1);
-- 回滚sql:
         DELETE FROM `test_111`.`t1` WHERE `id`=1 AND `name`='张三' AND `age`=28 AND `email`='zhangsan@example.com' AND `gender`='Male' AND `salary`=8500.00 AND `join_date`='2020-05-15' AND `is_active`=1;
-- ----------------------------------------------------------
$ 

不支持 drop 和 truncate 操作，因为这两个操作属于物理性删除，需要通过历史备份进行恢复。

可能存在的问题：

  • 数据库密码含特殊字符：需要加单引号圈起来；
  • 数据库字段中如有换行符或者其他特殊字符，则原生sql显示的时候后面可能不会注释，导致回滚 sql 错乱。因此需要我们将原生 SQL 用正则替换，即“-- 原生sql:”和“-- 回滚sql:”之间的部分（-- 原生sql:.*?-- 回滚sql:）

7.reverse_sql与binlog2sql主要区别

7.1 reverse_sql工具解析

./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" 

7.2 binlog2sql 工具解析

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

7.3 两个工具的区别

核心功能对比

特性
reverse_sql
binlog2sql
生成方向
专注反向SQL (UNDO)
默认正向SQL，需加 -B 参数生成反向SQL
输出格式
直接生成可执行的回滚SQL
可选原始SQL/回滚SQL/带注释的SQL
过滤条件
基础过滤（时间/表名）
更灵活（GTID/position/库名等）
大文件处理
未明确说明
明确支持大文件（-B 模式不受内存限制）
时间范围
必须指定 --start-time 和 --end-time
可选（支持按时间或位置过滤）
并发控制
支持线程数调节（--max-workers）
无并发控制选项
断点续传
依赖binlog位置（--binlog-pos）
支持更灵活的起止位置（--start-pos/--end-pos）
表级过滤
支持（-ot 多表逗号分隔）
支持（-t 多表空格分隔）
操作类型过滤
必须指定（-op 仅限一种操作）
灵活过滤（--sql-type 可多选或留空）
DDL忽略
不支持
支持（--only-dml 忽略DDL）
输出控制
终端打印（--print）或直接执行
默认打印，支持回滚SLEEP间隔（--back-interval）
主键处理
无相关选项
支持去除主键（-K 模式）
UPDATE转换
支持转REPLACE（--replace）
无此功能
  1. 数据恢复方向
    1. reverse_sql
      ：专门用于生成反向 SQL（UNDO SQL），主要用于数据回滚/修复场景
    2. binlog2sql
      ：默认生成正向 SQL，也可通过参数生成反向 SQL
  2. 实现方式
    1. reverse_sql
      基于 Python 实现
    2. binlog2sql
      也是 Python 实现但更早出现
  3. 使用场景
    1. 选reverse_sql
       ： 
      ✅ 需要快速生成精准回滚 SQL（需明确操作类型和时间范围） 
      ✅ 处理大型事务（自动分组优化）
    2. 选binlog2sql
       ：
      ✅ 需要分析 binlog 详细内容（支持实时解析） 
      ✅持续监控或复杂过滤条件（多操作类型组合过滤，如 GTID 多库多表） 
      ✅ 大 binlog 文件处理，优先 binlog2sql -B
      （内存优化更可靠）

8.总结

reverse_sql
 在生成回滚 SQL 时更加灵活和高效。reverse_sql
 数据闪回工具为数据库管理和维护提供了极大的便利，特别是在数据恢复方面。它允许用户在发生错误时能够迅速采取行动，减少数据丢失带来的损失。然而，为了有效使用此工具，必须注意其实施条件，如确保日志记录功能正常运行，以及维护良好的备份习惯。此外，了解bao工具的具体操作方法及其限制也非常重要，这有助于更安全、高效地管理数据库资源。

Enjoy GreatSQL :)
<往 期 推 荐>
JDBC游标读不生效导致OOM问题排查分析
GreatSQL优化技巧：使用 FUNCTION 代替标量子查询
诚邀莅临 | GreatSQL邀您参加2025开放原子开源生态大会 共筑开源产业新势能

《用三分钟学会一个MySQL知识》

<关于 GreatSQL>

GreatSQL数据库是一款开源免费数据库，可在普通硬件上满足金融级应用场景，具有高可用、高性能、高兼容、高安全等特性，可作为MySQL或Percona Server for MySQL的理想可选替换。

💻社区官网: https://greatsql.cn/ 
⏩Gitee  : https://gitee.com/GreatSQL/GreatSQL
⏩GitHub  : https://github.com/GreatSQL/

🆙BiliBili  : https://space.bilibili.com/1363850082

（对文章有疑问或见解可去社区官网提出哦~）

加入微信交流群
加入QQ交流群
想看更多技术好文，点个"在看"吧！

mysql创建数据库sql优化testsqlmysql update语句
文章转载自GreatSQL社区，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论