文章/答案/技术大牛

发布

社区首页 >专栏 >DML 误操作？MySQL 闪回工具大盘点

DML 误操作？MySQL 闪回工具大盘点

爱可生开源社区

发布于 2025-04-09 08:24:36

14700

代码可运行

文章被收录于专栏：爱可生开源社区爱可生开源社区

运行总次数：0

代码可运行

作者：雷文霆，爱可生华东交付服务部 DBA 成员，主要负责 MySQL 故障处理及相关技术支持。爱好看书，电影。座右铭，每一个不曾起舞的日子，都是对生命的辜负。

操作数据库时，如果不慎执行了 DML 误操作，闪回工具可以回滚这类 DML 误操作。

闪回之前

在使用闪回工具之前，需要明确几个小问题：

哪些 SQL 类型可以使用闪回工具？

可以使用闪回工具回滚的 DML 类型包括：DELETE、INSERT、UPDATE，不支持 DDL。

需要满足哪些条件才能使用？

闪回前还有一些条件，相关参数满足条件（binlog_row_image=full，binlog_format=row），且误操作 DML 后，表结构没有增删列（因为 binlog 中不含表字段名）。

误操作一定要用工具么？

如果 DML 操作涉及的数据量较小，则推荐直接在应用侧执行闪回语句效率更高，但闪回语句需要经过应用侧确认之后，在应用层执行。

有哪些闪回工具推荐？

目前比较主流的 MySQL 闪回工具有 binlog2sql[1]、my2sql[2]、MyFlash[3] 三款。

基本信息

	binlog2sql	my2sql	MyFlash
MySQL支持版本	5.7	5.7 / 8.0	5.7
离线解析	否	是	是
语言	Python	Go	C
最后更新时间	2018.10	2022.11	2020.11

功能特点

binlog2sql：回滚 DML，解析出回滚或原始 SQL。

my2sql：回滚 DML，解析出回滚或原始 SQL。还可以通过 binlog 统计 DML、长事务与大事务分析。

MyFlash：回滚 DML，生成 binlog 类型的回滚文件。

综合以上，推荐使用 my2sql 回滚 DML。下面我们着重介绍 MyFlash 和 my2sql 两款工具的使用。

MyFlash

MyFlash 是由美团点评公司技术工程部开发维护的一个回滚 DML 操作的工具。该工具通过解析 v4 版本的 binlog，完成回滚操作。相对已有的回滚工具，其增加了更多的过滤选项，让回滚更加容易。该工具已经在美团点评内部使用。

MyFlash 可以完全离线，因为只是生成回滚 binlog 文件。但只支持单个文件解析。

推荐方式：指定 GTID 范围，等同于时间点和 Pos 点。

使用方式

show master status\G
File: mysql-bin.000014
Executed_Gtid_Set: 52875cf3-e9d3-11ef-88ab-02000aba3d09:120764

checksum table test_table;
+-------------------+------------+
| Table             | Checksum   |
+-------------------+------------+
| test01.test_table | 1479091061 |
+-------------------+------------+

模拟误操作，事务号范围 120765-120766。

update test_table set blob_field='Tom' where id <900;
update test_table set blob_field='Jreey' where id >900 and id<1000;

执行回滚，有事务号使用 --include-gtids。默认生成的回滚文件名为：binlog_output_base.flashback。

# cd  MyFlash-master/binary/
./flashback --binlogFileNames=mysql-bin.000014 --databaseNames=test01 --tableNames=test_table --sqlTypes='delete,insert,update' --include-gtids='52875cf3-e9d3-11ef-88ab-02000aba3d09:120765-120766'

应用回滚的 binlog 文件，会记录本地 gtid 事务号（skip-gtids不能避免记录事务号，只是为了防止 SQL_MODE=ON 报错）。

mysqlbinlog -vv binlog_output_base.flashback --skip-gtids|mysql -uroot -p

验证结果一致！

checksum table test_table;
+-------------------+------------+
| Table             | Checksum   |
+-------------------+------------+
| test01.test_table | 1479091061 |
+-------------------+------------+

⚠️ 注意事项

binlogFileNames 指定需要回滚的 binlog 文件，目前只支持单个文件。
sqlTypes 支持写一个类型，如 --sqlTypes='delete'。
include-gtids 指定需要回滚的 gtid，支持 gtid 的单个和范围两种形式。
exclude-gtids 指定不需要回滚的 gtid，用法同 include-gtids。

my2sql

my2sql 是 Go 版的 MySQL binlog 解析工具，通过解析 MySQL binlog ，可以生成原始 SQL、回滚 SQL、去除主键的 INSERT SQL 等，也可以生成 DML 统计信息。基于 my2fback、binlog_rollback 工具二次开发而来。2.0 版本开始支持 caching_sha2_password 插件，推荐使用此版本。

my2sql 根据需要原始 SQL 或回滚 SQL，对应 work-type 参数，支持指定时间点（效果等同于 GTID 范围）。

生成原始 SQL：-work-type 2sql
生成回滚 SQL：-work-type rollback

推荐方式：明确指定开始时间和结束时间，配合 file 模式时，只会读取表信息，对库入侵性最小。

核心参数

-start-file：指定开始读取的 binlog 文件名称。虽然也有 -stop-file 参数，指定停止读取的 binlog 文件名称，但一般不用，因为 start-file 配合时间就可以定位出误操作执行的区间。
-work-type：工作类型。
- 2sql：生成原始 SQL。
- rollback：生成回滚 SQL。
- stats：只统计 DML、事务信息。
-mode：获取 binlog 文件的模式。
- repl：伪装成从库解析 binlog 文件（需要拉取）（默认）。
- file：离线解析 binlog 文件。推荐使用，因为以下信息可以证明 file 模式只是查看了表信息。

不同模式连接数据库后的操作信息

unsetunsetrepl 模式unsetunset

admin@127.0.0.1 on  using TCP/IP
SET NAMES utf8mb4
SET autocommit=true
admin@127.0.0.1 on  using TCP/IP
SHOW GLOBAL VARIABLES LIKE 'BINLOG_CHECKSUM'
SET @master_binlog_checksum='NONE'
SET @slave_uuid = 'ff2f78c5-fb2c-11ef-a0eb-02000aba3d09', @replica_uuid = 'ff2f78c5-fb2c-11ef-a0eb-02000aba3d09'
 Binlog Dump       Log: 'mysql-bin.000004'  Pos: 4
Query     SHOW COLUMNS FROM `test01`.`t01`
Query     SHOW INDEX FROM `test01`.`t01`

unsetunsetfile 模式unsetunset

admin@127.0.0.1 on  using TCP/IP
SET NAMES utf8mb4
SET autocommit=true
SHOW COLUMNS FROM `test01`.`t01`
SHOW INDEX FROM `test01`.`t01`

-local-binlog-file：binlog 文件路径。当指定 -mode file 参数时，需要指定 -local-binlog-file binlog 文件的相对路径或绝对路径，可以连续解析多个 binlog 文件，只需要指定起始文件名，工具会自动持续解析下个文件。

下面我们将先后介绍生成回滚 SQL 和原始 SQL 两种工作类型。

生成回滚 SQL

生成的 SQL 文件，导入会更新 GTID。

⚠️ 注意事项 可使用 set+source 避免更新 GTID。

show masterstatus\G
File: mysql-bin.000006
Position: 255561
52875cf3-e9d3-11ef-88ab-02000aba3d09:1-34

selectcount(*) from t01;checksumtable t01;
+----------+
| count(*) |
+----------+
|     1000 |
+----------+
+------------+------------+
| Table      | Checksum   |
+------------+------------+
| test01.t01 | 1658863531 |
+------------+------------+

模拟数据误操作 DELETE，删除了 499 行。

delete from t01 where id < 500;

找到误删除的开始的时间，如 250307 14:12:06，根据误操作时间定位到 binlog 文件，直接搜索 🔍 误操作的语句也可以。

mysqlbinlog --base64-output=decode-rows -vv --database test01 mysql-bin.000006 |grep  -C 10 -i "Rows_query" |grep -C 10    -i "t01" |grep -C 10 -i "delete" > t01_detele.log
#250307 14:12:06 server id 110  end_log_pos 255759      Rows_query
# delete from t01 where id < 500

结束的时间，如 250307 14:38:11（需要找到下一个事务的开始时间），往下搜索 🔍 COMMIT 或是 SESSION.GTID_NEXT 或是 GTID 信息。

#250307 14:12:06 server id 110  end_log_pos 265277      Xid = 445
COMMIT/*!*/;
# at 265277
#250307 14:38:11 server id 110  end_log_pos 265350

以下方案二选一。

方案一

直接读取 binlog 文件解析时（-mode file），binlog 文件相对路径或绝对路径。

./my2sql -user admin -password 123456 -host 127.0.0.1  -port 3313 -mode file -local-binlog-file ./mysql-bin.000004  -work-type rollback  -start-file mysql-bin.000004 -start-datetime "2025-03-07 14:12:06" -stop-datetime "2025-03-07 14:38:11" -add-extraInfo -output-dir ./tmpdir

方案二

伪装成从库解析 binlog。

./my2sql -user admin -password 123456 -host 127.0.0.1  -port 3313  -work-type rollback  -start-file mysql-bin.000004 -start-datetime "2025-03-07 14:12:06" -stop-datetime "2025-03-07 14:38:11" -add-extraInfo -output-dir ./tmpdir01

两种方案执行结果一样，共生成了 3 个文件。

rollback.6.sql：回滚 SQL 文件
biglong_trx.txt：大事务信息
binlog_status.txt：DML 的统计信息

验证与误删除数据量是否相等。导入验证结果一致。

cat rollback.6.sql |grep "INSERT INTO"|wc -l
499

重定向方式 < 导入，会记录到 binlog 并增加事务号。可通过 set sql_log_bin=0; source 的方式不记录。

set sql_log_bin=0;
source /tools/my2sql_test/tmpdir/rollback.6.sql;
select count(*) from t01;checksum table t01;
+----------+
| count(*) |
+----------+
|     1000 |
+----------+
+------------+------------+
| Table      | Checksum   |
+------------+------------+
| test01.t01 | 1658863531 |
+------------+------------+

⚠️ 注意事项

mysqlbinlog 命令解析参数 base64-output=decode-rows (可选)，作用是将二进制日志中以 Base64 编码存储的行数据解码为可读的文本格式。这样，你在查看二进制日志内容时，就可以直接看到具体的行数据更改，而不是一串 Base64 编码的字符串。
闪回 SQL 文件中的 # datetime=... 这行的内容，就是加了参数 -add-extrainfo 后加入的附加信息，可以获取每个语句执行的具体时间和点位。
my2sql 工具可以增加 -databases 参数，实现只过滤指定库，默认为全部库。

生成原始 SQL

除了参数 work-type=2sql，其他参数和生成回滚 SQL 命令一样。

方案一

直接读取 binlog 文件解析。

./my2sql -user admin -password 123456 -host 127.0.0.1  -port 3313 -mode file -local-binlog-file ./mysql-bin.000004  -work-type 2sql  -start-file mysql-bin.000004 -start-datetime "2025-03-07 14:12:06" -stop-datetime "2025-03-07 14:38:11" -add-extraInfo -output-dir ./tmpdir

方案二

伪装成从库解析 binlog。

./my2sql -user admin -password 123456 -host 127.0.0.1  -port 3313  -work-type 2sql  -start-file mysql-bin.000004 -start-datetime "2025-03-07 14:12:06" -stop-datetime "2025-03-07 14:38:11" --add-extraInfo -output-dir ./tmpdir01

两种方案执行结果一样，共生成了 3 个文件。

forward.6.sql：原始 SQL 文件（原始误操作语句）
biglong_trx.txt：大事务信息
binlog_status.txt：DML 的统计信息

查看生成的 biglong_trx.txt 文件。

cat biglong_trx.txt 
binlog            starttime           stoptime            startpos   stoppos    rows     duration   tables
mysql-bin.000006  2025-03-07_14:12:06 2025-03-07_14:12:06 255636     265277     499      0     [test01.t01(inserts=0, updates=0, deletes=499)]

查看生成的 binlog_status.txt 文件，会统计每个时间段区间内相关库表所产生的 DML 次数。

cat binlog_status.txt 
binlog            starttime           stoptime            startpos   stoppos    inserts  updates  deletes  database        table               
mysql-bin.000006  2025-03-07_14:12:06 2025-03-07_14:12:06 255759     265250     0        0        499      test01          t01