Gavin 的点滴记录

菜单

Administrator
发布于 2025-03-01 / 59 阅读
0
0

MySQL 5.7 binlog 闪回：误删数据恢复实战

本文介绍的闪回操作只适用于小范围“精准手术”，大规模“重建”请使用备份恢复。

一、背景说明

  • 数据库版本：MySQL 5.7.44

  • binlog 配置：binlog_format = ROW，binlog_row_image = FULL

  • 问题描述：jc-iot-connection_prod 库中 l_cmp_internal_order 表误删了一条记录（时间大概是下午 4点到 4点半）

  • 恢复目标：利用 binlog 闪回技术，将误删数据恢复至删除前的完整状态

二、恢复实操

1、定位 binlog 日志文件

方法一：通过 strings 快速检索时间戳

strings /www/server/data/mysql-bin.034683 | grep "26-08-18" | head -20

方法二：根据文件修改时间推断（推荐）

ll -h /www/server/data/mysql-bin.0346*

# 输出示例:
#-rw-r----- 1 mysql mysql 1.1G Aug 18 15:45 mysql-bin.034681
#-rw-r----- 1 mysql mysql 1.1G Aug 18 16:14 mysql-bin.034682
#-rw-r----- 1 mysql mysql 1.1G Aug 18 16:45 mysql-bin.034683

说明：文件的修改时间约等于 binlog 记录的最后写入时间。通过对比可知，mysql-bin.034682 覆盖了 15:45 ~ 16:14 的日志段，与误删时间（约 16:12）最为接近。

如果不能确定误删时间，可通过下述的【定位提取 SQL 操作】确定。

2、导出目标时间段日志

/www/server/mysql/bin/mysqlbinlog \
--start-datetime="2026-08-18 16:00:00" \
--stop-datetime="2026-08-18 16:15:00" \
/www/server/data/mysql-bin.034682 \
-v --base64-output=decode-rows > gavin_binlog.txt

参数说明：

  • --start-datetime：包含 ≥ 该时间点的事件

  • --stop-datetime：包含 < 该时间点的事件（不包含等于）

  • 时间格式的精度是秒级。16:13 会自动补齐为 16:13:00

  • -v：显示行数据详情

  • --base64-output=decode-rows：将 binlog 中的行事件解码为可读的 SQL 注释格式

（进阶）同时扫描两个文件，确保覆盖 binlog 切换边界：

/www/server/mysql/bin/mysqlbinlog \
    --start-datetime="2026-08-18 16:12:00" \
    --stop-datetime="2026-08-18 16:15:00" \
    /www/server/data/mysql-bin.034682 \
    /www/server/data/mysql-bin.034683 \
    -v --base64-output=decode-rows \
    > gavin_binlog_merged.txt

时间窗口覆盖 16:12:00 ~ 16:14:59.999

两个文件按顺序自动合并

不会漏数据，不会重复

3、定位并提取 DELETE 操作

（1）搜索 DELETE 语句位置

# 搜索 DELETE FROM 语句（包含前后几行上下文）
grep -n -i "DELETE FROM" gavin_binlog.txt -B 5 -A 20


# 搜索包含你的表名和 DELETE 的内容
grep -n -i "你的表名" gavin_binlog.txt | grep -i "DELETE" -B 5 -A 20

# 扩展
grep -n "### DELETE FROM.*你的表名" gavin_binlog.txt
grep -n "### UPDATE.*你的表名" gavin_binlog.txt
grep -n "### INSERT INTO.*你的表名" gavin_binlog.txt

  • -i：--ignore-case

  • -n：--line-number

  • -B 5 -A 20：匹配行及前 5 行、后 20 行

实战操作：

grep -n -i "l_cmp_internal_order" gavin_binlog.txt | grep -i "DELETE" -B 5 -A 20

# 输出示例:
46016803:#260818 16:12:38 server id 1  end_log_pos 990837208 CRC32 0xf3001a30   Table_map: `jc-iot-connection_prod`.`l_cmp_internal_order` mapped to number 4379
46016806:### DELETE FROM `jc-iot-connection_prod`.`l_cmp_internal_order`

关键信息：DELETE 语句位于 第 46016806 行。

（2）提取 DELETE 的完整行数据

本表共有 120+ 个字段，因此需提取足够多的行以覆盖所有 @N 字段：

# 从 DELETE 行开始，向后提取 200 行（46016806 + 200 = 46017006）
sed -n '46016806,46017006p' gavin_binlog.txt

输出内容格式如下：

### DELETE FROM `db`.`table`
### WHERE
###   @1=9285
###   @2=NULL
###   @3=20260818
###   @4='2026-08-18 10:21:47'
###   ...
###   @86='2026-08-18 10:21:47'   -- creation_datetime
###   @87='2026-08-18 10:21:57'   -- last_update_datetime
###   ...
###   @122=NULL
# at 990839029

# 可见，共有 122 个字段，理论最小行数是 124 行

注意：@1 ~ @N 按顺序对应表字段（从左到右），需结合表结构进行映射。

（3）获取事务点位（可选）

方式一（less 分页查看）：

less gavin_binlog.txt

在 less 中按 / 输入 # at 990839029（或直接搜索 DELETE 行号附近），定位到该事务区域，可看到完整的事务日志：

BEGIN
/*!*/;
# at 990836788
#260818 16:12:38 server id 1  end_log_pos 990837208 CRC32 0xf3001a30    Table_map: `jc-iot-connection_prod`.`l_cmp_internal_order` mapped to numb
er 4379
# at 990837208
#260818 16:12:38 server id 1  end_log_pos 990839029 CRC32 0x79d6d762    Delete_rows: table id 4379 flags: STMT_END_F
### DELETE FROM `jc-iot-connection_prod`.`l_cmp_internal_order`
### WHERE
###   @1=9285
...
###   @122=NULL
# at 990839029
#260818 16:12:38 server id 1  end_log_pos 990839060 CRC32 0x8d99f40b    Xid = 59813553884
COMMIT/*!*/;
# at 990839060

点位解析：

  • 990836788：Table_map 映射表结构，准备操作 l_cmp_internal_order

  • 990837208：Delete_rows 实际执行 DELETE FROM ... WHERE @1=9285 ...

  • 990839029：Xid COMMIT，事务提交确认

  • 990839060：GTID / 下一个事件 下一个事务的开始，当前事务结束边界

综上可得：

  • 起始位点（--start-position）：BEGIN 后面的 # at N，即 990836788

  • 结束位点（--stop-position）：COMMIT 后面的 # at N，即 990839060

方式二（sed + grep 快速提取）：

# sed 前后各加几行，可简单判断的得到点位
sed -n '3209350,3209600p' gavin_binlog.txt | grep -E "BEGIN|COMMIT|# at"

4、生成恢复 INSERT 语句

（1）获取表结构

SHOW CREATE TABLE `jc-iot-connection_prod`.`l_cmp_internal_order`;

（2）映射字段并拼接 INSERT

将 @N 按表字段顺序一一对应，重点关注：

  • bit 类型：b'0' → 0，b'1' → 1

  • 字符串类型：保持引号包裹

  • NULL 值：直接写 NULL

推荐：可将表结构和 DELETE 数据发给 AI 辅助生成 INSERT 语句，效率更高且不易出错。

（3）安全执行恢复

BEGIN;

INSERT INTO `jc-iot-connection_prod`.`l_cmp_internal_order` (...) VALUES (...);

-- 验证
SELECT * FROM `jc-iot-connection_prod`.`l_cmp_internal_order` WHERE `uid` = 9285;

COMMIT;   -- 确认无误后提交
-- ROLLBACK;  -- 如有问题则回滚

三、总结

通过 mysqlbinlog 解析 ROW 格式的 binlog，并结合 grep / sed 精准定位 DELETE 操作，再反向生成 INSERT 语句，即可实现误删数据的精确恢复。此方法无需额外工具，适用于所有 MySQL 5.7+ 环境，掌握后可快速应对各类误操作事故。

核心逻辑：定位 → 提取 → 映射 → 回写，四步完成一次标准闪回。

附录：备选工具 binlog2sql 与 my2sql

当误删数据涉及批量操作（如无 WHERE 条件的 DELETE），用 mysqlbinlog 手动整理成百上千条 INSERT 语句将非常低效。此时，闪回工具是更优选择。它们能自动解析 binlog，直接生成反向 SQL，大幅提高恢复效率。

使用前提：无论选择哪个工具，均需满足以下条件：

  • binlog_format = ROW

  • binlog_row_image = FULL

  • 只能回滚 DML（INSERT / UPDATE / DELETE），无法闪回 DDL（如 DROP TABLE / TRUNCATE）

1、binlog2sql

binlog2sql是一款经典的 Python 工具，由大众点评开源。它安装简单，命令直观，适合处理中小规模数据的快速回滚。

适用场景：中小规模数据恢复、Python 环境熟悉的应急场景。

（1）安装与权限准备

# 下载
git clone https://github.com/danfengcao/binlog2sql.git
cd binlog2sql

# 安装依赖
pip install -r requirements.txt

MySQL 账号需具备以下权限：

-- IDENTIFIED BY:
-- 如果用户不存在，会自动创建该用户并设置密码
-- 如果用户已存在，会更新该用户的密码
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'rec_user'@'%' IDENTIFIED BY 'your_password';

FLUSH PRIVILEGES;

-- 查看 rec_user 的权限
SHOW GRANTS FOR 'rec_user'@'%';

注意：binlog2sql 项目最后一次更新在 2018 年，对 MySQL 8.0 及以上版本可能存在兼容性问题，建议在 5.6/5.7 环境中使用。

（2）使用示例

python binlog2sql.py -h127.0.0.1 -P3306 -urec_user -p'your_password' \
    -d jc-iot-connection_prod \
    -t l_cmp_internal_order \
    --start-file='mysql-bin.034682' \
    --start-datetime='2026-08-18 16:12:00' \
    --stop-datetime='2026-08-18 16:13:00' \
    -B > rollback.sql

执行后，rollback.sql中即包含自动生成的反向 SQL，审核后即可执行恢复。

参数解析：

  • --start-file：起始解析文件（必须指定）

  • --stop-file / --end-file：末尾解析文件，默认为 --start-file 同一个文件

  • --start-position / --start-pos：起始解析位置，默认为文件开头

  • --stop-position / --end-pos：末尾解析位置，默认为文件最末位置

  • -B：反向 SQL 模式（DELETE → INSERT，UPDATE → 反向 UPDATE）

2、my2sql

my2sql 采用 Go 语言编写，提供静态编译的二进制文件，无需依赖 Python 环境。它对大文件解析速度快、内存占用低，且支持 MySQL 8.0，是当前生产环境批量恢复的更优选择。

适用场景：大文件（GB 级以上）、批量数据恢复、MySQL 8.0 环境、需要 DML 统计等高级功能的场景。

（1）安装与权限准备

# 下载二进制文件（以 CentOS 7 / Linux amd64 为例，单个可执行文件，8M 左右）
# 下载极慢，建议开代理下载，下载完成再上传到 CentOS 7
wget https://raw.githubusercontent.com/liuhr/my2sql/master/releases/centOS_release_7.x/my2sql

# 赋予执行权限
chmod +x my2sql

# 可选
mv my2sql /usr/local/bin/

MySQL 账号权限要求与 binlog2sql 相同：

GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'rec_user'@'%';

（2）使用示例

方式一：按位点生成回滚 SQL（推荐，最精准）

先用 mysqlbinlog 定位误操作事务的起止位点，再交由 my2sql 精准闪回：

# 1. 确保输出目录存在
mkdir -p ./recover_01

# 2. 生成回滚 sql
./my2sql -user rec_user -password 'your_password' -host 127.0.0.1 \
    -work-type rollback \
    -start-file mysql-bin.034682 \
    -start-pos 990836788 -stop-pos 990839060\
    -databases jc-iot-connection_prod \
    -tables l_cmp_internal_order \
    -output-dir ./recover_01

执行后，回滚 SQL 生成在 ./recover_01/rollback.sql 中。

方式二：按时间范围生成回滚 SQL

mkdir -p ./recover_by_time

./my2sql -user root -password 'your_password' -host 127.0.0.1 \
    -work-type rollback \
    -start-file mysql-bin.034682 \
    -start-datetime "2026-08-18 16:12:00" \
    -stop-datetime "2026-08-18 16:13:00" \
    -databases jc-iot-connection_prod \
    -tables l_cmp_internal_order \
    -sql delete \
    -output-dir ./recover_by_time

参数解析：

  • -sql delete：只解析 DELETE 操作

  • -add-extraInfo：添加位点、时间等注释（犯罪现场）。

方式三：跨文件解析

my2sql 支持同时传入多个 binlog 文件，自动处理跨文件边界：

mkdir -p ./recover_by_time

./my2sql -user rec_user -password 'your_password' -host 127.0.0.1 \
    -work-type rollback \
    -start-file mysql-bin.034682,mysql-bin.034683 \
    -start-datetime "2026-08-18 16:12:00" \
    -stop-datetime "2026-08-18 16:15:00" \
    -databases jc-iot-connection_prod \
    -tables l_cmp_internal_order \
    -output-dir ./recover_cross_file

linux 配置环境变量 PATH
Ollama 本地部署 Deepseek

评论