误操作后快速恢复数据 binlog 解析为反向 SQL
误操作后快速恢复数据 binlog 解析为反向 SQL1.前言
本文将介绍使用 reverse_sql 工具将 GreatSQL 数据库的 binlog 解析为反向 SQL 语句。模拟误操作后，恢复数据。该工具可以帮助客户在发生事故时快速恢复数据，避免进一步的损失。使用 reverse_sql 工具非常简单，客户只需要指定肇事时间和表名即可。该工具会根据指定的时间点，在数据库中查找并还原该表在该时间点之前的数据状态。这样客户就能轻松地实现数据恢复，防止因意外操作或其他问题导致的数据丢失。
2.reverse_sql 工具简介
reverse_sql 是一个用于解析和转换二进制日志（binlog）的工具。它可以将二进制日志文件中记录的数据库更改操作（如插入、更新、删除）转换为反向的 SQL 语句，以便进行数据恢复。其运行模式需二进制日志设置为 ROW 格式。
下载地址：
https://github.com/hcymysql/reverse_sql3.工具特点
该工具的主要功能和特点包括(针对 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 --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" 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: 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)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-1067166
1 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
页:
[1]