案例误删 98 张表后靠 binlog 恢复：从定位 end_log_pos 到资金对账的完整复盘

2026-09-18 21:03:27

MySQL 生产库误删 98 张表后的时间点恢复实战：从 binlog 解析到资金对账

原文来源：https://jishuzhan.net/article/2100104842006679554

环境：MySQL 9.0.1（Community），innodb_file_per_table=ON，binlog_format=MIXED，sync_binlog=1，binlog_expire_logs_seconds=600000，无全量备份，事故库约 100 张表。

TL;DR：

止损：停应用 → cp -a mysql-bin.* → systemctl stop mysqld → dd 整盘镜像
定位：mysqlbinlog -v --base64-output=DECODE-ROWS → grep -n "DROP TABLE IF EXISTS" → 取 --stop-position
恢复：基线（测试库快照，按时间切口裁剪）+ mysqlbinlog --skip-gtids --start-datetime --stop-position
验收：外键孤儿检查、余额=最后一条流水、业务键去重、字符集双重编码修正
底线：没有备份就别谈恢复；binlog 是唯一的时间机器，且只保留 6.9 天

一、事故现场与约束条件

执行的文件是从测试库导出的 Navicat 结构导出：98 张表、2229 行 DDL、0 条 INSERT。文件头部：

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS `admin`;
CREATE TABLE `admin` ( ... ) ENGINE = InnoDB ...;

97 秒内 98 张表全部 DROP 后重建（information_schema.TABLES.CREATE_TIME 落在 16:06:45 ~ 16:08:22）。

环境约束决定了可选方案：

log_bin=ON、binlog_format=MIXED、sync_binlog=1 → binlog 可用于 PITR；
binlog_expire_logs_seconds=600000（≈6.9 天）→ 窗口有限，每次重启都可能触发清理；
innodb_file_per_table=ON → DROP 会 unlink 每张表的 .ibd；
secure_file_priv=NULL → 无法用 LOAD_FILE() 从 SQL 侧读文件；
业务账号只有 ALL ON db.*，没有 REPLICATION CLIENT / SUPER → SHOW BINARY LOGS 直接 1227；
无任何全量备份（宝塔未配置计划任务）。

二、第一性原理：DROP TABLE 之后数据还在不在？

2.1 文件系统层

innodb_file_per_table=ON 时 DROP TABLE 的行为：删除数据字典定义 → InnoDB unlink .ibd → inode 与数据块进入空闲列表，但块内容在被复用前不会被擦除。结论：在磁盘块被覆盖之前，页级数据仍然存在，敌人只有时间和写入量。（innodb_file_per_table=OFF 时数据都在 ibdata1，DROP 只是把页放回 free list，可用 undrop-for-innodb 做页解析。）

2.2 binlog 层

DDL 永远以 statement（Query event）记录。事故当天那条：

# at 124490158
#260914 16:06:43 server id 1 end_log_pos 124490314 CRC32 0x06712dd0 Query thread_id=25486 exec_time=0 error_code=0
SET TIMESTAMP=1789373203/*!*/;
SET @@session.foreign_key_checks=0/*!*/;
DROP TABLE IF EXISTS `admin` /* generated by server */

两个细节：foreign_key_checks=0 说明执行方（Navicat）先关外键再批量 drop；# at 124490158 与 end_log_pos 124490314 给出该事件的起止偏移，这就是后面 PITR 的停止坐标。

2.3 为什么 MIXED 模式下不能只靠 binlog 重建全库

实测同一份 binlog 的事件分布：

事件类型	数量	说明
statement INSERT INTO	45,577	可重放，含完整列值
statement UPDATE	40,382	增量语义（x = x + 1 类会重复累加）
row ### INSERT INTO	18,566	带完整行镜像
row ### UPDATE	10,909	带 before/after 镜像（`binlog_row_image=FULL`）

关键结论：binlog 只有“窗口内的变更”，没有窗口开始之前就存在的历史行 —— 恢复整库必须有基线。

三、取证：把“时间机器”完整保存下来

mkdir -p /data/rescue && cd /data/rescue
cp -a /www/server/data/mysql-bin.* .
cp -a /www/server/data/ibdata1 /www/server/data/ib_logfile* . 2>/dev/null
ls -l --time-style=long-iso mysql-bin.*
mysqlbinlog mysql-bin.000119 2>/dev/null | head -40
mysqldump --single-transaction --no-tablespaces --routines --events --triggers --databases  > prod_current_$(date +%F_%H%M).sql
systemctl stop mysqld
df -T /www/server/data
dd if=/dev/vdb1 of=/data/rescue/disk.img bs=4M conv=noerror,sync status=progress

坑：dd 的镜像必须放另一块物理盘；停库后不要重启（crash recovery、doublewrite、purge 都会覆盖被删页）；不要 OPTIMIZE/REPAIR/重跑 DDL。有从库则 STOP REPLICA; 后立刻全量备份——那是最理想的基线。

四、binlog 解析：从 2.7 万行输出里定位那 97 秒

4.1 两种输出模式

# 可读：row event 解码为 ### INSERT / ### UPDATE 伪注释
mysqlbinlog --base64-output=DECODE-ROWS -v mysql-bin.000119 > decoded.sql

# 回放：保留 BINLOG '...' base64，可直接管道给 mysql
mysqlbinlog --skip-gtids --stop-position=... mysql-bin.000119 > replay.sql

⚠️ DECODE-ROWS 的输出不能直接回放（row 事件变成注释了）。

4.2 定位事故坐标

grep -n "DROP TABLE IF EXISTS" decoded.sql | head -5
# 1959548:DROP TABLE IF EXISTS `admin` /* generated by server */

往上找最近的 # at 得 124490158：

事故时间：2026-09-14 16:06:43
停止坐标：--stop-position=124490158

--stop-datetime 也能用，但精度只到秒、且事务原子性会让跨秒事务被整体包含或丢弃——能用 position 就别用 datetime。

4.3 权限

SHOW BINARY LOGS 要 REPLICATION CLIENT，mysqlbinlog --read-from-remote-server 要 REPLICATION SLAVE。只有业务账号两条都走不通。平时就该准备一个带 REPLICATION CLIENT 的只读账号。

五、没有全量备份时，如何构造“可信基线”

来源优先级：从库 → 云/虚拟机快照、宝塔备份 → 测试库中从生产同步的快照（本次靠它）→ 生产里的表级 *_bak_YYYYMMDD → 开发机副本。

拿到候选源后必须做三件事：

5.1 定时间切口

对比候选源与生产残留备份表的最后一致时间点。

5.2 校验 id 空间

SELECT COUNT(*) FROM prod_bak t LEFT JOIN baseline b ON b.id = t.id WHERE b.id IS NULL OR b.amount <> t.amount OR b.created_at <> t.created_at;

本次实测：基线与生产 8/31 的 aicreation_task_files_bak 在 id = '2026-08-25 00:00:00'; -- datetime 列 DELETE FROM t2WHEREcreated_at` >= 1787587200; -- int 时间戳列（易踩）


裁剪后复核主键最大值是否符合“同步时刻应有的规模”。

## 六、PITR 执行：先沙箱，再决定上生产

### 6.1 生成增量脚本

```bash
mysqlbinlog --skip-gtids --database= --start-datetime="2026-08-25 00:00:00" --stop-position=124490158 mysql-bin.000119 > incr.sql

--skip-gtids 去掉 SET @@SESSION.GTID_NEXT（目标实例 gtid_mode=OFF 会报错）；起点选基线时间而非 binlog 起点，避免累加型 UPDATE 被重复应用。

6.2 沙箱验证与错误分类

mysql -h127.0.0.1 -uroot -p sandbox  incr_err.log
grep -o 'ERROR [0-9]*' incr_err.log | sort | uniq -c | sort -rn

错误码	含义	数量	处置
1054	Unknown column（目标表结构旧）	2,717	必须修：补列后重放（本例补 operation_key/note）
1452	外键失败（父行缺失）	359	补父行（缺用户）后重放
1032	找不到要更新的行	4,000	基线缺行，登记为基线不完整
1062	重复键	634	无害（幂等回放）
1449	触发器 DEFINER 账号不存在	2,798	导入前 DROP TRIGGER
1419	建触发器缺 SUPER	2	权限问题，与数据无关

经验：先修 schema 再回放。第一轮因缺一列，资金流水 2,717 条整批被拒；补列后单独重放那批语句一次成功。

6.3 字符集前提

mysql --default-character-set=utf8mb4 ...。含中文的字段（备注、提示词）客户端字符集没对齐时，轻则乱码，重则解析失败。本次踩过：2,717 条余额备注变成双重编码（UTF-8 被按 Latin-1 解释）。

七、恢复后的数据工程（真正花时间的地方）

7.1 按外键自动生成孤儿检查

SELECT CONCAT('SELECT ''', TABLE_NAME, '.', COLUMN_NAME, ''' AS col, COUNT(*) c FROM `', TABLE_NAME, '` t LEFT JOIN `', REFERENCED_TABLE_NAME, '` r ON r.`', REFERENCED_COLUMN_NAME, '` = t.`', COLUMN_NAME, '` WHERE t.`', COLUMN_NAME, '` IS NOT NULL AND r.`', REFERENCED_COLUMN_NAME, '` IS NULL HAVING c > 0;') AS chk FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = '' AND REFERENCED_TABLE_NAME IS NOT NULL;

本次跑出 178 条任务无对应用户 → 补回 4 个用户 → 孤儿归零。

7.2 用账本重建余额（不信任何快照里的 balance）

CREATE TABLE tmp_last AS
SELECT user_id, balance_after, frozen_after, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY id DESC) rn FROM user_balance_logs;

UPDATE users u JOIN tmp_last t ON t.user_id = u.id AND t.rn = 1 SET u.balance = t.balance_after, u.frozen_balance = t.frozen_after;

-- ① 账实一致
SELECT COUNT(*) FROM users u WHERE ABS(u.balance - IFNULL((SELECT balance_after FROM user_balance_logs l WHERE l.user_id = u.id ORDER BY id DESC LIMIT 1), u.balance)) > 0.01;

-- ② 账本连续性（窗口函数版）
WITH seq AS (SELECT id, user_id, balance_before, balance_after, LAG(balance_after) OVER (PARTITION BY user_id ORDER BY id) AS prev_after FROM user_balance_logs) SELECT * FROM seq WHERE prev_after IS NOT NULL AND ABS(prev_after - balance_before) > 0.01;

-- ③ 扣费勾稽
SELECT (SELECT ROUND(SUM(amount),2) FROM user_balance_logs WHERE type IN ('settle','consume')) AS 流水, (SELECT ROUND(SUM(amount),2) FROM users_costs_log) AS 费用日志;

7.3 去重必须用业务键

同一笔业务在不同副本里会落在两套自增 id 空间，合并后表现为“流水翻倍”，直接导致余额被重复扣减：

DELETE l1 FROM user_balance_logs l1 JOIN user_balance_logs l2 ON l1.user_id = l2.user_id AND l1.created_at = l2.created_at AND l1.amount = l2.amount AND IFNULL(l1.task_id,0) = IFNULL(l2.task_id,0) AND l1.id > l2.id;

本次删掉 2,717 条重复（9,908 → 7,191），去重后重跑 7.2 的校验。

7.4 双重编码的判别与修复

判别（Python）：能“用 cp1252 编码再按 UTF-8 解码”成功、且结果含中文的即双重编码：

def is_mojibake(v: str) -> bool:
try:
fixed = v.encode("cp1252").decode("utf-8")
except Exception:
return False
return fixed != v and any("\u4e00" /dev/null || true
ls -l --time-style=long-iso "$RESCUE"/mysql-bin.*
systemctl stop mysqld
DEV=$(df -T "$DATADIR" | awk 'NR==2{print $1}')
dd if="$DEV" of="$RESCUE/disk.img" bs=4M conv=noerror,sync status=progress
mysqlbinlog --skip-gtids --database="$DB" --start-datetime="$START_TIME" --stop-position="$STOP_POS" "$RESCUE"/mysql-bin.* > "$RESCUE/incr.sql"
echo "取证完成：$RESCUE"; echo "回放脚本：$RESCUE/incr.sql（请在沙箱执行）"

十、复盘：技术层面的五条结论

binlog 是逻辑时间机器，不是备份：只有窗口内变更，恢复整库必须有基线；窗口默认 7 天，且重启可能触发清理。
DROP 是可逆的物理操作：.ibd 被 unlink 后数据块仍在，前提是停写得足够早。
MIXED 下 statement 与 row 混用，决定了“能不能只靠 binlog 重建”——先统计事件类型分布。
恢复 ≠ 导数据：外键、去重、字符集、触发器、配置表引用，每一项都能让“行数对了但业务错了”。
权限与流程是技术方案的一部分：没有 REPLICATION CLIENT 就取不到 binlog；没有备份，任何方案都是赌博。

附录：事故后 60 分钟 checklist

停应用（含定时任务、队列 worker）
导出当前库现状留证（mysqldump --single-transaction --no-tablespaces）
拷贝 binlog + ibdata1 + redo 到另一块盘
记录最早 binlog 时间与事件起点
systemctl stop mysqld，且不再重启
数据盘整盘镜像（dd / 虚拟机快照）
解析 binlog 定位事故坐标（grep DROP TABLE → 取 end_log_pos）
列出候选基线（从库 / 快照 / 测试库同步件 / 表级 bak / 本地副本）

复制全文生成海报 MySQL 数据库运维故障排查数据恢复

案例 误删 98 张表后靠 binlog 恢复：从定位 end_log_pos 到资金对账的完整复盘