误操作后快速恢复数据 binlog 解析为反向 SQL
<h1 id="误操作后快速恢复数据-binlog-解析为反向-sql">误操作后快速恢复数据 binlog 解析为反向 SQL</h1><h2 id="1前言">1.前言</h2>
<p>本文将介绍使用 <strong>reverse_sql</strong> 工具将 GreatSQL 数据库的 binlog 解析为反向 SQL 语句。模拟误操作后，恢复数据。该工具可以帮助客户在发生事故时快速恢复数据，避免进一步的损失。使用 <code>reverse_sql</code> 工具非常简单，客户只需要指定肇事时间和表名即可。该工具会根据指定的时间点，在数据库中查找并还原该表在该时间点之前的数据状态。这样客户就能轻松地实现数据恢复，防止因意外操作或其他问题导致的数据丢失。</p>
<h2 id="2reverse_sql-工具简介">2.reverse_sql 工具简介</h2>
<p><strong>reverse_sql</strong> 是一个用于解析和转换二进制日志（binlog）的工具。它可以将二进制日志文件中记录的数据库更改操作（如插入、更新、删除）转换为反向的 SQL 语句，以便进行数据恢复。其运行模式需二进制日志设置为 ROW 格式。</p>
<p><strong>下载地址：</strong></p>
<pre><code class="language-HTTP">https://github.com/hcymysql/reverse_sql
</code></pre>
<h2 id="3工具特点">3.工具特点</h2>
<p>该工具的主要功能和特点包括(针对 GreatSQL)：</p>
<p>1、解析二进制日志：<code>reverse_sql</code> 能够解析 GreatSQL 的二进制日志文件，并还原出其中的 SQL 语句。</p>
<p>2、生成可读的 SQL：生成原始 SQL 和反向 SQL。</p>
<p>3、支持过滤和筛选：可以根据时间范围、表、DML操作等条件来过滤出具体的误操作 SQL 语句。</p>
<p>4、支持多线程并发解析 binlog 事件。</p>
<p>注意：<code>reverse_sql</code> 只是将二进制日志还原为 SQL 语句，而不会执行这些 SQL 语句来修改数据库。</p>
<h2 id="4使用前检查">4.使用前检查</h2>
<h3 id="41-greatsql-环境检查">4.1 GreatSQL 环境检查</h3>
<p>首先需要确认二进制日志设置是 ROW 格式以及 row_image 是 FULL。</p>
<p>工具运行时，首先会进行GreatSQL的环境检测（<code>if binlog_format != 'ROW' and binlog_row_image != 'FULL'</code>），如果不同时满足这两个条件，程序直接退出。</p>
<pre><code class="language-sql">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)
</code></pre>
<h3 id="42-参数binlog_format解析">4.2 参数binlog_format解析</h3>
<p>GreatSQL 支持三种二进制日志格式：</p>
<ol>
<li>
<p>STATEMENT (基于语句的复制 - SBR)</p>
<ul>
<li>
<p>记录实际执行的 SQL 语句</p>
</li>
<li>
<p>优点：日志文件较小，记录的是语句而非数据变更</p>
</li>
<li>
<p>缺点：某些非确定性函数(如 NOW(), UUID(), RAND())可能导致主从数据不一致</p>
</li>
</ul>
</li>
<li>
<p>ROW (基于行的复制 - RBR) GreatSQL 默认使用 ROW 作为二进制日志格式。</p>
<ul>
<li>
<p>记录每行数据的变化情况</p>
</li>
<li>
<p>优点：最安全的复制方式，能准确复制数据变更</p>
</li>
<li>
<p>缺点：日志文件较大，特别是批量操作时</p>
</li>
</ul>
</li>
<li>
<p>MIXED (混合模式)</p>
<ul>
<li>默认使用 STATEMENT 格式，但在某些情况下自动切换到 ROW 格式</li>
</ul>
</li>
</ol>
<h3 id="43-参数binlog_row_image解析">4.3 参数<strong>binlog_row_image解析</strong></h3>
<p><strong>binlog_row_image</strong> 是 GreatSQL 中控制二进制日志记录的参数，它决定了在使用基于行的复制时，二进制日志中的行镜像如何被记录。</p>
<p><strong>参数选项</strong></p>
<p><strong>binlog_row_image</strong> 参数有三个可选值：</p>
<ul>
<li><strong>FULL</strong>：记录每一行的变更，包括所有列的前后镜像。--生产环境建议设置为 FULL 最佳。</li>
<li><strong>MINIMAL</strong>：binlog日志的前镜像只记录唯一识别列(唯一索引列、主键列)，后镜像只记录修改列。只记录必要的列，即在更新操作中只记录变更的列和用于识别行的最小列集。</li>
<li><strong>NOBLOB</strong>：类似于 FULL，但不包括 BLOB 和 TEXT 类型的列，除非它们是必要的。</li>
</ul>
<p><strong>使用场景</strong></p>
<ul>
<li>当设置为 <strong>FULL</strong> 时，GreatSQL 记录所有列的变更，这可以确保数据的完整性，但可能会导致二进制日志的大小增加。</li>
<li>设置为 <strong>MINIMAL</strong> 可以减少日志的大小，因为它只记录变更的列和必要的列，这对于减少磁盘 I/O 和网络传输是有益的。</li>
<li><strong>NOBLOB</strong> 选项适用于那些不希望记录大型 BLOB 或 TEXT 数据的场景，但仍然需要记录其他类型列的变更。</li>
</ul>
<h2 id="5工具与数据库用户赋权">5.工具与数据库用户赋权</h2>
<h3 id="51-工具赋予执行权限">5.1 工具赋予执行权限</h3>
<p>在服务器上解压后，授权：</p>
<pre><code class="language-SQL">$ ll rev*
-rwxr-xr-x 1 root root 50780824 Apr 28 14:23 reverse_sql_mysql8
$ chmod 755 reverse_sql_mysql8
</code></pre>
<h3 id="52-数据库用户赋权最小化用户权限">5.2 数据库用户赋权(最小化用户权限)</h3>
<pre><code class="language-SQL">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)
</code></pre>
<h3 id="53-查看使用帮助信息">5.3 查看使用帮助信息</h3>
<pre><code class="language-sql">$ ./reverse_sql_mysql8 --help
usage: reverse_sql_mysql8 [-h] [-ot 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
设置要恢复的表，多张表用,逗号分隔
-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"
</code></pre>
<h2 id="6案例演示">6.案例演示</h2>
<h3 id="61-创建测试数据模拟误更新">6.1 创建测试数据，模拟误更新</h3>
<pre><code class="language-sql">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: 10Duplicates: 0Warnings: 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: 1Changed: 1Warnings: 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)
</code></pre>
<h3 id="62-解析binlog">6.2 解析binlog</h3>
<p>查看 binlog 当前信息</p>
<pre><code class="language-SQL">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-1067166
1 row in set (0.00 sec)
</code></pre>
<p>执行解析命令</p>
<pre><code class="language-Plain"> ./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
Processing binlogevents: 100%|█████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████| 2/2
Processing binlogevents: 0event
Processing binlogevents: 0event
</code></pre>
<p>当出现误操作时，只需指定误操作的时间段，其对应的 binlog 文件（通常可以通过 show master status 得到当前的 binlog 文件名）以及刚才误操作的表，和具体的 DML 命令，比如 update 或者 delete。【不支持一次解析多个 binlog】</p>
<h3 id="63-查看解析文件">6.3 查看解析文件</h3>
<pre><code class="language-SQL">$ 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;
-- ----------------------------------------------------------
$
</code></pre>
<p>工具运行后，会在当前目录下生成一个 {db}_{table}_recover.sql 文件，保存着原生 SQL（原生 SQL 会加注释） 和反向 SQL，如果想将结果输出到前台终端，可以指定 --print 选项。</p>
<pre><code class="language-Go">$ ./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
Processing binlogevents: 100%|█████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████| 4/4
Processing binlogevents: 100%|█████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████| 5/5
Processing binlogevents: 0event
-- 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;
-- ----------------------------------------------------------
</code></pre>
<p>如果你想把 update 操作转换为 replace，指定 --replace 选项即可，同时会在当前目录下生成一个{db}_{table}_recover_replace.sql 文件。</p>
<pre><code class="language-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
Processing binlogevents: 100%|█████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████| 4/4
Processing binlogevents: 100%|█████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████| 5/5
Processing binlogevents: 0event
$ 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);
-- ----------------------------------------------------------
</code></pre>
<p>但注意，如果字段值中包含换行符，则原生 SQL 后几行不会被注释，需要手动处理。</p>
<h3 id="64-拿到反向sql恢复数据">6.4 拿到反向SQL恢复数据</h3>
<p>在{db}_{table}_recover.sql 文件中找到刚才误操作的 DML 语句，然后在 GreatSQL 数据库中执行逆向工程后的 SQL 以恢复数据。</p>
<pre><code class="language-JavaScript">-- 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: 1Changed: 1Warnings: 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)
</code></pre>
<p>可以看到这条误更新的数据已经恢复到最初状态。</p>
<p>如果 {db}_{table}_recover.sql 文件的内容过多，也可以通过 awk 命令进行分割，以便更容易进行排查。</p>
<pre><code class="language-JavaScript">$ 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;
-- ----------------------------------------------------------
$
</code></pre>
<p>不支持 drop 和 truncate 操作，因为这两个操作属于物理性删除，需要通过历史备份进行恢复。</p>
<p>可能存在的问题：</p>
<ul>
<li>数据库密码含特殊字符：需要加单引号圈起来；</li>
<li>数据库字段中如有换行符或者其他特殊字符，则原生sql显示的时候后面可能不会注释，导致回滚 sql 错乱。因此需要我们将原生 SQL 用正则替换，即“-- 原生sql:”和“-- 回滚sql:”之间的部分（-- 原生sql:.*?-- 回滚sql:）</li>
</ul>
<h2 id="7reverse_sql与binlog2sql主要区别">7.reverse_sql与binlog2sql主要区别</h2>
<h3 id="71-reverse_sql工具解析">7.1 reverse_sql工具解析</h3>
<pre><code class="language-JavaScript">./reverse_sql_mysql8 --help
usage: reverse_sql_mysql8 [-h] [-ot 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
设置要恢复的表，多张表用,逗号分隔
-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"
</code></pre>
<h3 id="72-binlog2sql-工具解析">7.2 binlog2sql 工具解析</h3>
<pre><code class="language-Python">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。可选。默认为增删改都解析。用了此参数但没填任何类型，则三者都不解析。
</code></pre>
<h3 id="73-两个工具的区别">7.3 两个工具的区别</h3>
<p><strong>核心功能对比</strong></p>
<table>
<thead>
<tr>
<th>特性</th>
<th>reverse_sql</th>
<th>binlog2sql</th>
</tr>
</thead>
<tbody>
<tr>
<td>生成方向</td>
<td>专注反向SQL (UNDO)</td>
<td>默认正向SQL，需加 -B 参数生成反向SQL</td>
</tr>
<tr>
<td>输出格式</td>
<td>直接生成可执行的回滚SQL</td>
<td>可选原始SQL/回滚SQL/带注释的SQL</td>
</tr>
<tr>
<td>过滤条件</td>
<td>基础过滤（时间/表名）</td>
<td>更灵活（GTID/position/库名等）</td>
</tr>
<tr>
<td>大文件处理</td>
<td>未明确说明</td>
<td>明确支持大文件（-B 模式不受内存限制）</td>
</tr>
<tr>
<td>时间范围</td>
<td>必须指定 --start-time 和 --end-time</td>
<td>可选（支持按时间或位置过滤）</td>
</tr>
<tr>
<td>并发控制</td>
<td>支持线程数调节（--max-workers）</td>
<td>无并发控制选项</td>
</tr>
<tr>
<td>断点续传</td>
<td>依赖binlog位置（--binlog-pos）</td>
<td>支持更灵活的起止位置（--start-pos/--end-pos）</td>
</tr>
<tr>
<td>表级过滤</td>
<td>支持（-ot 多表逗号分隔）</td>
<td>支持（-t 多表空格分隔）</td>
</tr>
<tr>
<td>操作类型过滤</td>
<td>必须指定（-op 仅限一种操作）</td>
<td>灵活过滤（--sql-type 可多选或留空）</td>
</tr>
<tr>
<td>DDL忽略</td>
<td>不支持</td>
<td>支持（--only-dml 忽略DDL）</td>
</tr>
<tr>
<td>输出控制</td>
<td>终端打印（--print）或直接执行</td>
<td>默认打印，支持回滚SLEEP间隔（--back-interval）</td>
</tr>
<tr>
<td>主键处理</td>
<td>无相关选项</td>
<td>支持去除主键（-K 模式）</td>
</tr>
<tr>
<td>UPDATE转换</td>
<td>支持转REPLACE（--replace）</td>
<td>无此功能</td>
</tr>
</tbody>
</table>
<ol>
<li><strong>数据恢复方向</strong>
<ol>
<li><code>reverse_sql</code>：专门用于生成反向 SQL（UNDO SQL），主要用于数据回滚/修复场景</li>
<li><code>binlog2sql</code>：默认生成正向 SQL，也可通过参数生成反向 SQL</li>
</ol>
</li>
<li><strong>实现方式</strong>
<ol>
<li><code>reverse_sql</code> 基于 Python 实现</li>
<li><code>binlog2sql</code> 也是 Python 实现但更早出现</li>
</ol>
</li>
<li><strong>使用场景</strong>
<ol>
<li><strong>选</strong> <strong><code>reverse_sql</code></strong> ：<br>
✅ 需要快速生成精准回滚 SQL（需明确操作类型和时间范围）<br>
✅ 处理大型事务（自动分组优化）</li>
<li><strong>选</strong> <strong><code>binlog2sql</code></strong> ：<br>
✅ 需要分析 binlog 详细内容（支持实时解析）<br>
✅持续监控或复杂过滤条件（多操作类型组合过滤，如 GTID / 多库多表）<br>
✅ 大 binlog 文件处理，优先 <code>binlog2sql -B</code>（内存优化更可靠）</li>
</ol>
</li>
</ol>
<h2 id="8总结">8.总结</h2>
<p><code>reverse_sql</code> 在生成回滚 SQL 时更加灵活和高效。<code>reverse_sql</code> 数据闪回工具为数据库管理和维护提供了极大的便利，特别是在数据恢复方面。它允许用户在发生错误时能够迅速采取行动，减少数据丢失带来的损失。然而，为了有效使用此工具，必须注意其实施条件，如确保日志记录功能正常运行，以及维护良好的备份习惯。此外，了解工具的具体操作方法及其限制也非常重要，这有助于更安全、高效地管理数据库资源。</p>
<hr>
<p>Enjoy GreatSQL 😃</p>
<h2 id="关于-greatsql">关于 GreatSQL</h2>
<p>GreatSQL是适用于金融级应用的国内自主开源数据库，具备高性能、高可靠、高易用性、高安全等多个核心特性，可以作为MySQL或Percona Server的可选替换，用于线上生产环境，且完全免费并兼容MySQL或Percona Server。</p>
<p>相关链接： GreatSQL社区 Gitee GitHub Bilibili</p>
<h2 id="greatsql社区">GreatSQL社区：</h2>
<blockquote>
<p>社区博客有奖征稿详情：https://greatsql.cn/thread-100-1-1.html</p>
</blockquote>
<p><img src="https://img2024.cnblogs.com/other/2630741/202507/2630741-20250721101455510-507640943.png"></p>
<h2 id="技术交流群">技术交流群：</h2>
<blockquote>
<p>微信：扫码添加<code>GreatSQL社区助手</code>微信好友，发送验证信息<code>加群</code>。</p>
</blockquote>
<p><img src="https://img2024.cnblogs.com/other/2630741/202507/2630741-20250721101455797-2107057387.png"></p><br><br>
来源：https://www.cnblogs.com/greatsql/p/18995301
頁:
[1]