背景:演练被业务误删除的数据恢复场景。
本文介绍通过工具 Lightning 对离线 binlog 生成回滚SQL语句的过程。
lightning 是由贝壳找房 DBA 团队开发和维护的一个 MySQL binlog 转换工具。该工具可以将 MySQL ROW 格式的 binlog 转换为想要的 SQL,如:原始 SQL,闪回 SQL等。也可以对 binlog 进行统计分析,用于数据库异常分析。甚至可以通过定制 lua 插件进行二次开发,发挥无限的想象力。
MySQL数据库前置条件:
- 开启了 binlog
- binlog v4 版本 (MySQL 5.1+)
- binlog_format=row
- binlog_row_image=full
步骤一:确认操作所在的binlog
业务正在运行期间发生了数据误删除
首先要在众多的 mysql-bin 文件中找到包含删除的文件。
mysql>SHOW MASTER LOGS;
将 mysql-bin 文件 下载到本地。
阿里云RDS可以通过手册指引来下载:云数据库RDS MySQL版远程获取Binlog日志并解析Binlog日志 (aliyun.com)
1.确定发生删除时的时间范围
这里简单说下查找思路:有大概删除时间如上午十点左右
通过相邻两个 binlog
日志开头的时间标志来大致确定 binlog 间隔生成的时间,从而推测删除操作发生在哪个 binlog
里。
mysqlbinlog --no-defaults -vv --base64-output=decode-rows mysql-bin.000024 | more
输出示例
# The proper term is pseudo_replica_mode, but we use this compatibility alias
# to make the statement usable on server versions 8.0.24 and older.
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#220811 11:19:56 server id 1 end_log_pos 125 CRC32 0x3b3fb91e Start: binlog v 4, server v 8.0.25 created 220811 11:19:56
BINLOG '
7G/xYg8JV5CJeQAAAH8AAAAAAAQAOC4wLkI1AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEwANAAgAAAAABAAEAAAAYQAEGggAAAAICAgCAAAACgoKKioAEjQA
CigBDrk/Ow==
'/*!*/;
2.核实binlog是否存在删除操作
使用关键字 DELETE
查找
mysqlbinlog --no-defaults -vv --base64-output=decode-rows mysql-bin.000022 | grep "DELETE"
输出示例
### DELETE FROM `test`.`log`
### DELETE FROM `test`.`user`
...
### DELETE FROM `test`.`log`
确认是删除存在于该 mysql-bin.000022 中,或多个相邻里如:mysql-bin.000022、mysql-bin.000023
发生的删除操作包含:log表、user表
3.查看操作点位
操作影响数据量小的情况可以直接使用 grep
命令 增加结果前后行 -C
参数来查看
如数据删除操作结果的前后 50 行
mysqlbinlog --no-defaults -vv --base64-output=decode-rows mysql-bin.000022 | grep -C 50 "DELETE" > result022.txt
影响较多的操作需要生成可查看的binlog
来分析
mysqlbinlog --no-defaults -vv --base64-output=decode-rows mysql-bin.000022 > detail022.txt
步骤二:生成恢复语句(flashback)
这里介绍可以离线生成恢复语句的工具:Lightning
离线使用方法:
(这里仅介绍了命令行操作,更多可看文档使用yaml配置)
1.将数据库结构导出
将数据库结构导出一份,保存为 db_schema.sql
(名字随意)
mysqldump -h127.0.0.1 test -uroot -p123456 --set-gtid-purged=off --default-character-set=utf8 > db_schema.sql
2.执行生成恢复语句命令
使用上面项目链接里,下载好的二进制可执行文件 lightning.linux-amd64
生成
./lightning.linux-amd64 -no-defaults \
-plugin flashback \
-event-types delete \
-tables test.log,test.user \
-schema-file db_schema.sql \
-binlog-file mysql-bin.000022 > flashback022.sql
如果能确定误操作的点位或时间可以增加参数
-start-position 4387 \
-stop-position 52538 \
-start-datetime "2022-08-11 10:00:00" \
-stop-datetime "2022-08-11 10:05:00" \
生成的回滚 sql
保存在了 flashback022.sql
步骤三:恢复
查看生成的回滚语句,没有问题后直接进行导入恢复。
mysql -h127.0.0.1 -uroot -p123456 test < flashback022.sql --default-character-set=utf8
注意我这里仅仅用到了恢复删除的操作,所以不存在顺序问题。请自行查看生成的恢复语句是否符合顺序要求,不符合的需要通过
tac
命令进行反转操作。