误操作后快速恢复数据 binlog 解析为反向 SQL
作者：GreatSQL
- 2025-07-21 福建
本文字数：17327 字
阅读完需：约 57 分钟
误操作后快速恢复数据 binlog 解析为反向 SQL
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 VARIABLES LIKE '%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 支持三种二进制日志格式：
STATEMENT (基于语句的复制 - SBR)
记录实际执行的 SQL 语句
优点：日志文件较小，记录的是语句而非数据变更
缺点：某些非确定性函数(如 NOW(), UUID(), RAND())可能导致主从数据不一致
ROW (基于行的复制 - RBR) GreatSQL 默认使用 ROW 作为二进制日志格式。
记录每行数据的变化情况
优点：最安全的复制方式，能准确复制数据变更
缺点：日志文件较大，特别是批量操作时
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'@'%' identified BY 'test';Query OK, 0 rows affected (0.15 sec)
greatsql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO `test`@`%`;Query OK, 0 rows affected (0.01 sec)
greatsql> GRANT SELECT ON `test`.* TO `test`@`%`;Query OK, 0 rows affected (0.01 sec)
greatsql> SHOW grants FOR test;+------------------------------------------------------------------+| Grants FOR test@% |+------------------------------------------------------------------+| GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO `test`@`%` || GRANT SELECT ON `test`.* TO `test`@`%` |+------------------------------------------------------------------+2 rows in set (0.00 sec)
复制代码
5.3 查看使用帮助信息
$ ./reverse_sql_mysql8 --helpusage: 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 ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(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> INSERT INTO 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 WHERE id = 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 MASTER STATUS \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-10671661 row in set (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: 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-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: 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;-- ------------------------------------------------------------ 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: DELETE FROM `test_111`.`t1` WHERE `id`=3 AND `name`='王五' AND `age`=25 AND `email`='wangwu@example.com' AND `gender`='Male' AND `salary`=7800.00 AND `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: DELETE FROM `test_111`.`t1` WHERE `id`=4 AND `name`='赵六' AND `age`=29 AND `email`='zhaoliu@example.com' AND `gender`='Female' AND `salary`=8800.75 AND `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: DELETE FROM `test_111`.`t1` 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执行时间: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: DELETE FROM `test_111`.`t1` WHERE `id`=6 AND `name`='孙八' AND `age`=27 AND `email`='sunba@example.com' AND `gender`='Male' AND `salary`=8100.00 AND `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: DELETE FROM `test_111`.`t1` WHERE `id`=7 AND `name`='周九' AND `age`=31 AND `email`='zhoujiu@example.com' AND `gender`='Other' AND `salary`=9500.25 AND `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: DELETE FROM `test_111`.`t1` WHERE `id`=8 AND `name`='吴十' AND `age`=24 AND `email`='wushi@example.com' AND `gender`='Female' AND `salary`=7600.50 AND `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: DELETE FROM `test_111`.`t1` WHERE `id`=9 AND `name`='郑十一' AND `age`=30 AND `email`='zhengshiyi@example.com' AND `gender`='Male' AND `salary`=8900.00 AND `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: DELETE FROM `test_111`.`t1` WHERE `id`=10 AND `name`='王十二' AND `age`=33 AND `email`='wangshier@example.com' AND `gender`='Female' AND `salary`=10000.00 AND `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`=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;-- ----------------------------------------------------------$
复制代码
工具运行后，会在当前目录下生成一个 {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" --printProcessing 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-21 11: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-21 11: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" --replaceProcessing 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: REPLACE 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 后几行不会被注释，需要手动处理。
6.4 拿到反向 SQL 恢复数据
在{db}_{table}_recover.sql 文件中找到刚才误操作的 DML 语句，然后在 GreatSQL 数据库中执行逆向工程后的 SQL 以恢复数据。
-- 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`=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 in set (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 28 16:22 output10.sql-rw-r--r-- 1 root root 802 Apr 28 16:22 output11.sql-rw-r--r-- 1 root root 508 Apr 28 16:22 output1.sql-rw-r--r-- 1 root root 500 Apr 28 16:22 output2.sql-rw-r--r-- 1 root root 504 Apr 28 16:22 output3.sql-rw-r--r-- 1 root root 510 Apr 28 16:22 output4.sql-rw-r--r-- 1 root root 510 Apr 28 16:22 output5.sql-rw-r--r-- 1 root root 502 Apr 28 16:22 output6.sql-rw-r--r-- 1 root root 508 Apr 28 16:22 output7.sql-rw-r--r-- 1 root root 506 Apr 28 16:22 output8.sql-rw-r--r-- 1 root root 518 Apr 28 16:22 output9.sql$ cat output1.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: 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 --helpusage: 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：专门用于生成反向 SQL（UNDO SQL），主要用于数据回滚/修复场景binlog2sql：默认生成正向 SQL，也可通过参数生成反向 SQL实现方式
reverse_sql基于 Python 实现binlog2sql也是 Python 实现但更早出现使用场景
选
reverse_sql：✅ 需要快速生成精准回滚 SQL（需明确操作类型和时间范围）✅ 处理大型事务（自动分组优化）选
binlog2sql：✅ 需要分析 binlog 详细内容（支持实时解析）✅持续监控或复杂过滤条件（多操作类型组合过滤，如 GTID / 多库多表）✅ 大 binlog 文件处理，优先binlog2sql -B（内存优化更可靠）
8.总结
reverse_sql 在生成回滚 SQL 时更加灵活和高效。reverse_sql 数据闪回工具为数据库管理和维护提供了极大的便利，特别是在数据恢复方面。它允许用户在发生错误时能够迅速采取行动，减少数据丢失带来的损失。然而，为了有效使用此工具，必须注意其实施条件，如确保日志记录功能正常运行，以及维护良好的备份习惯。此外，了解工具的具体操作方法及其限制也非常重要，这有助于更安全、高效地管理数据库资源。
划线
评论
复制
发布于: 刚刚阅读数: 3
版权声明: 本文为 InfoQ 作者【GreatSQL】的原创文章。
原文链接:【http://xie.infoq.cn/article/a663a9a3e670615263c42f8fc】。文章转载请联系作者。
GreatSQL
关注
GreatSQL社区 2023-01-31 加入
GreatSQL是由万里数据库维护的MySQL分支，专注于提升MGR可靠性及性能，支持InnoDB并行查询特性，是适用于金融级应用的MySQL分支版本。 社区：https://greatsql.cn/ Gitee: https://gitee.com/GreatSQL/GreatSQL
评论