登录/ 注册
 找回密码
 立即注册
关闭

CSDN热搜

程序园 精品问答 技术交流 资源下载
返回列表 发新帖
首页 › 业界区 › 安全 ›误操作后快速恢复数据 binlog 解析为反向 SQL ...

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

巫雪艷 6 天前
误操作后快速恢复数据 binlog 解析为反向 SQL

1.前言

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

reverse_sql 是一个用于解析和转换二进制日志（binlog）的工具。它可以将二进制日志文件中记录的数据库更改操作（如插入、更新、删除）转换为反向的 SQL 语句，以便进行数据恢复。其运行模式需二进制日志设置为 ROW 格式。
下载地址：
  1. 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'），如果不同时满足这两个条件，程序直接退出。
  1. greatsql> SHOW VARIABLES LIKE '%binlog%';
  2. +------------------------------------------------+----------------------+
  3. | Variable_name                                  | Value                |
  4. +------------------------------------------------+----------------------+
  5. | binlog_cache_size                              | 32768                |
  6. | binlog_checksum                                | CRC32                |
  7. | binlog_ddl_skip_rewrite                        | OFF                  |
  8. | binlog_direct_non_transactional_updates        | OFF                  |
  9. | binlog_encryption                              | OFF                  |
  10. | binlog_error_action                            | ABORT_SERVER         |
  11. | binlog_expire_logs_auto_purge                  | ON                   |
  12. | binlog_expire_logs_seconds                     | 2592000              |
  13. | binlog_format                                  | ROW                  |
  14. | binlog_group_commit_sync_delay                 | 0                    |
  15. | binlog_group_commit_sync_no_delay_count        | 0                    |
  16. | binlog_gtid_simple_recovery                    | ON                   |
  17. | binlog_max_flush_queue_time                    | 0                    |
  18. | binlog_order_commits                           | ON                   |
  19. | binlog_rotate_encryption_master_key_at_startup | OFF                  |
  20. | binlog_row_event_max_size                      | 8192                 |
  21. | binlog_row_image                               | FULL                 |
  22. | binlog_row_metadata                            | MINIMAL              |
  23. | binlog_row_value_options                       |                      |
  24. | binlog_rows_query_log_events                   | OFF                  |
  25. | binlog_skip_flush_commands                     | OFF                  |
  26. | binlog_space_limit                             | 0                    |
  27. | binlog_stmt_cache_size                         | 32768                |
  28. | binlog_transaction_compression                 | OFF                  |
  29. | binlog_transaction_compression_level_zstd      | 3                    |
  30. | binlog_transaction_dependency_history_size     | 25000                |
  31. | binlog_transaction_dependency_tracking         | WRITESET             |
  32. | have_backup_safe_binlog_info                   | YES                  |
  33. | innodb_api_enable_binlog                       | OFF                  |
  34. | log_statements_unsafe_for_binlog               | ON                   |
  35. | max_binlog_cache_size                          | 4294967296           |
  36. | max_binlog_size                                | 1073741824           |
  37. | max_binlog_stmt_cache_size                     | 18446744073709547520 |
  38. | rpl_read_binlog_speed_limit                    | 0                    |
  39. | sync_binlog                                    | 1                    |
  40. +------------------------------------------------+----------------------+
  41. 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 工具赋予执行权限

在服务器上解压后，授权：
  1. $ ll rev*
  2. -rwxr-xr-x 1 root root 50780824 Apr 28 14:23 reverse_sql_mysql8
  3. $ chmod 755 reverse_sql_mysql8
复制代码
5.2 数据库用户赋权(最小化用户权限)
  1. greatsql> CREATE USER 'test'@'%' identified BY 'test';
  2. Query OK, 0 rows affected (0.15 sec)
  4. greatsql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO `test`@`%`;
  5. Query OK, 0 rows affected (0.01 sec)
  7. greatsql> GRANT SELECT ON `test`.* TO `test`@`%`;
  8. Query OK, 0 rows affected (0.01 sec)
  10. greatsql> SHOW grants FOR test;
  11. +------------------------------------------------------------------+
  12. | Grants FOR test@%                                                |
  13. +------------------------------------------------------------------+
  14. | GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO `test`@`%` |
  15. | GRANT SELECT ON `test`.* TO `test`@`%`                           |
  16. +------------------------------------------------------------------+
  17. 2 rows in set (0.00 sec)
复制代码
5.3 查看使用帮助信息
  1. $ ./reverse_sql_mysql8 --help
  2. 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
  3.                           [--binlog-pos BINLOG_POS] --start-time ST --end-time ET [--max-workers MAX_WORKERS] [--print] [--replace] [-v]
  5. Binlog数据恢复，生成反向SQL语句。
  7. options:
  8.   -h, --help            show this help message and exit
  9.   -ot ONLY_TABLES [ONLY_TABLES ...], --only-tables ONLY_TABLES [ONLY_TABLES ...]
  10.                         设置要恢复的表，多张表用,逗号分隔
  11.   -op ONLY_OPERATION, --only-operation ONLY_OPERATION
  12.                         设置误操作时的命令（insert/update/delete）
  13.   -H MYSQL_HOST, --mysql-host MYSQL_HOST
  14.                         MySQL主机名
  15.   -P MYSQL_PORT, --mysql-port MYSQL_PORT
  16.                         MySQL端口号
  17.   -u MYSQL_USER, --mysql-user MYSQL_USER
  18.                         MySQL用户名
  19.   -p MYSQL_PASSWD, --mysql-passwd MYSQL_PASSWD
  20.                         MySQL密码
  21.   -d MYSQL_DATABASE, --mysql-database MYSQL_DATABASE
  22.                         MySQL数据库名
  23.   -c MYSQL_CHARSET, --mysql-charset MYSQL_CHARSET
  24.                         MySQL字符集，默认utf8
  25.   --binlog-file BINLOG_FILE
  26.                         Binlog文件
  27.   --binlog-pos BINLOG_POS
  28.                         Binlog位置，默认4
  29.   --start-time ST       起始时间
  30.   --end-time ET         结束时间
  31.   --max-workers MAX_WORKERS
  32.                         线程数，默认4（并发越高，锁的开销就越大，适当调整并发数）
  33.   --print               将解析后的SQL输出到终端
  34.   --replace             将update转换为replace操作
  35.   -v, --version         show program's version number and exit
  37. Example usage:
  38.     shell> ./reverse_sql -ot table1 -op delete -H 192.168.198.239 -P 3336 -u admin -p hechunyang -d hcy \
  39.             --binlog-file mysql-bin.000124 --start-time "2023-07-06 10:00:00" --end-time "2023-07-06 22:00:00"
复制代码
6.案例演示

6.1 创建测试数据，模拟误更新
  1. greatsql> CREATE TABLE t1 (  
  2.          id INT AUTO_INCREMENT PRIMARY KEY,  
  3.          name VARCHAR(50),  
  4.          age INT,  
  5.          email VARCHAR(100),  
  6.          gender ENUM('Male', 'Female', 'Other'),  
  7.          salary DECIMAL(10,2),  
  8.          join_date DATE,  
  9.          is_active BOOLEAN  
  10.      );
  11. Query OK, 0 rows affected (0.04 sec)
  13. greatsql> INSERT INTO t1 (name, age, email, gender, salary, join_date, is_active) VALUES  
  14.      ('张三', 28, 'zhangsan@example.com', 'Male', 8500.00, '2020-05-15', TRUE),  
  15.      ('李四', 32, 'lisi@example.com', 'Male', 9200.50, '2019-08-22', TRUE),  
  16.      ('王五', 25, 'wangwu@example.com', 'Male', 7800.00, '2021-03-10', TRUE),  
  17.      ('赵六', 29, 'zhaoliu@example.com', 'Female', 8800.75, '2020-11-05', TRUE),  
  18.      ('肖七', 35, 'xiaoqi@example.com', 'Female', 10500.00, '2018-06-18', FALSE),  
  19.      ('孙八', 27, 'sunba@example.com', 'Male', 8100.00, '2021-01-30', TRUE),  
  20.      ('周九', 31, 'zhoujiu@example.com', 'Other', 9500.25, '2019-09-12', TRUE),  
  21.      ('吴十', 24, 'wushi@example.com', 'Female', 7600.50, '2022-02-14', TRUE),  
  22.      ('郑十一', 30, 'zhengshiyi@example.com', 'Male', 8900.00, '2020-07-25', FALSE),  
  23.      ('王十二', 33, 'wangshier@example.com', 'Female', 10000.00, '2019-04-08', TRUE);
  24. Query OK, 10 rows affected (0.01 sec)
  25. Records: 10  Duplicates: 0  Warnings: 0
  27. greatsql> SELECT * FROM t1;
  28. +----+-----------+------+------------------------+--------+----------+------------+-----------+
  29. | id | name      | age  | email                  | gender | salary   | join_date  | is_active |
  30. +----+-----------+------+------------------------+--------+----------+------------+-----------+
  31. |  1 | 张三      |   28 | zhangsan@example.com   | Male   |  8500.00 | 2020-05-15 |         1 |
  32. |  2 | 李四      |   32 | lisi@example.com       | Male   |  9200.50 | 2019-08-22 |         1 |
  33. |  3 | 王五      |   25 | wangwu@example.com     | Male   |  7800.00 | 2021-03-10 |         1 |
  34. |  4 | 赵六      |   29 | zhaoliu@example.com    | Female |  8800.75 | 2020-11-05 |         1 |
  35. |  5 | 肖七      |   35 | xiaoqi@example.com     | Female | 10500.00 | 2018-06-18 |         0 |
  36. |  6 | 孙八      |   27 | sunba@example.com      | Male   |  8100.00 | 2021-01-30 |         1 |
  37. |  7 | 周九      |   31 | zhoujiu@example.com    | Other  |  9500.25 | 2019-09-12 |         1 |
  38. |  8 | 吴十      |   24 | wushi@example.com      | Female |  7600.50 | 2022-02-14 |         1 |
  39. |  9 | 郑十一    |   30 | zhengshiyi@example.com | Male   |  8900.00 | 2020-07-25 |         0 |
  40. | 10 | 王十二    |   33 | wangshier@example.com  | Female | 10000.00 | 2019-04-08 |         1 |
  41. +----+-----------+------+------------------------+--------+----------+------------+-----------+
  42. 10 rows in set (0.00 sec)
  44. greatsql> UPDATE t1   
  45.      SET salary = 9800.00, is_active = TRUE   
  46.      WHERE id = 5;
  47. Query OK, 1 row affected (0.01 sec)
  48. Rows matched: 1  Changed: 1  Warnings: 0
  50. greatsql> SELECT * FROM t1;
  51. +----+-----------+------+------------------------+--------+----------+------------+-----------+
  52. | id | name      | age  | email                  | gender | salary   | join_date  | is_active |
  53. +----+-----------+------+------------------------+--------+----------+------------+-----------+
  54. |  1 | 张三      |   28 | zhangsan@example.com   | Male   |  8500.00 | 2020-05-15 |         1 |
  55. |  2 | 李四      |   32 | lisi@example.com       | Male   |  9200.50 | 2019-08-22 |         1 |
  56. |  3 | 王五      |   25 | wangwu@example.com     | Male   |  7800.00 | 2021-03-10 |         1 |
  57. |  4 | 赵六      |   29 | zhaoliu@example.com    | Female |  8800.75 | 2020-11-05 |         1 |
  58. |  5 | 肖七      |   35 | xiaoqi@example.com     | Female |  9800.00 | 2018-06-18 |         1 |
  59. |  6 | 孙八      |   27 | sunba@example.com      | Male   |  8100.00 | 2021-01-30 |         1 |
  60. |  7 | 周九      |   31 | zhoujiu@example.com    | Other  |  9500.25 | 2019-09-12 |         1 |
  61. |  8 | 吴十      |   24 | wushi@example.com      | Female |  7600.50 | 2022-02-14 |         1 |
  62. |  9 | 郑十一    |   30 | zhengshiyi@example.com | Male   |  8900.00 | 2020-07-25 |         0 |
  63. | 10 | 王十二    |   33 | wangshier@example.com  | Female | 10000.00 | 2019-04-08 |         1 |
  64. +----+-----------+------+------------------------+--------+----------+------------+-----------+
  65. 10 rows in set (0.00 sec)
复制代码
6.2 解析binlog

查看 binlog 当前信息
  1. greatsql> SHOW MASTER STATUS \G
  2. *************************** 1. row ***************************
  3.              File: binlog.000002
  4.          Position: 2918
  5.      Binlog_Do_DB:
  6. Binlog_Ignore_DB:
  7. Executed_Gtid_Set: 2a3248f7-e762-11ef-ae09-00163e11ac96:1,
  8. 3837053e-e762-11ef-ade8-00163e2cc6be:1-4008,
  9. 615fadb3-234d-11f0-ab29-00163e2cc6be:1-5,
  10. aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1:1-1067166
  11. 1 row in set (0.00 sec)
复制代码
执行解析命令
[code] ./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

使用道具 举报
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 立即注册
发帖
巫雪艷
6 天前

0

粉丝关注

13

主题发布

板块介绍填写区域，请于后台编辑
微信扫一扫