正文

程序员深夜误删MySQL生产库核心表后运维团队靠binlog与全量备份四十分钟抢救数据的完整复盘

/0 浏览量
0720

程序员深夜误删MySQL生产库核心表后运维团队靠binlog与全量备份四十分钟抢救数据的完整复盘

凌晨2点17分，企业微信的告警群突然炸了。不是那种常见的CPU飙到90%的例行提醒，而是业务方直接@了值班开发和DBA：“订单查询接口返回空了，用户付款成功但查不到记录。”

那一刻，我连拖鞋都没穿稳就坐到了工位前。登录生产库，跑了一句 SELECT COUNT(*) FROM t_order_main WHERE create_time >= '2024-03-15 00:00:00';，屏幕回了一行 0。再查表结构：ERROR 1146 (42S02): Table 'prod_db.t_order_main' doesn't exist。

核心交易表，没了。

说实话，那一瞬间脑子里闪过的是“背锅、赔偿、凌晨三点写事故报告”。但值班组长老张只说了一句：“别慌，先止血。MySQL有binlog，备份是昨晚两点打的，我们有四十五分钟窗口。”

下面我把这次抢救的完整过程拆开来讲。没有玄学，全是工程纪律。

第一刀：先让生产环境“别动”

误删之后，最危险的不是表没了，而是还在继续写数据。新产生的binlog会不断覆盖旧日志，如果此时有人重试写入，恢复出来的数据会和线上业务产生撕裂。

我们做了三步隔离：

-- 1. 开启只读模式，阻止所有普通写入
SET GLOBAL read_only = ON;

-- 2. 查看当前活跃连接，手动踢掉可疑会话
SHOW FULL PROCESSLIST;
-- 假设查到连接ID为 18492 的脚本在疯狂重试写入
KILL 18492;

-- 3. 如果网关层支持，先把这台实例摘掉负载均衡
-- （这一步由运维同事在SLB控制台操作，DBA侧不再碰流量）

这里有个细节：read_only 只能拦住普通账号，SUPER 权限的账号默认还能写。所以当时我们紧急把应用账号降级成了只读，真正需要写操作的账号全部冻结。这一步大概花了5分钟。

止血完成，接下来才是找“后悔药”。

第二刀：在binlog里扒出那条要命的SQL

MySQL的binlog本质上是一份按时间顺序记录的所有数据变更日志。只要开过 log_bin=ON，哪怕你把表删了、把库清了，它都会在binlog里留下痕迹。

先确认备份时间点对应的binlog文件：

SHOW BINARY LOG STATUS\G
-- 重点关注 File 和 Position
-- 昨晚2点的冷备文件头里写着：
-- -- Position to start replication or point-in-time recovery from : 123456789

然后我们用 mysqlbinlog 把凌晨1:50到2:20之间的日志dump出来，过滤DDL和关键事务：

mysqlbinlog \
  --start-datetime="2024-03-15 01:50:00" \
  --stop-datetime="2024-03-15 02:20:00" \
  /var/log/mysql/mysql-bin.000123 \
  /var/log/mysql/mysql-bin.000124 \
  > /tmp/recovery_debug.log

grep -nE "DROP|CREATE|DELETE|t_order_main" /tmp/recovery_debug.log

输出里很快定位到了罪魁祸首：

# at 123460215
#240315  2:17:03 server id 1  end_log_pos 123460282  Query   thread_id=8821 exec_time=0 error_code=0
SET TIMESTAMP=1710469023;\
DROP TABLE `t_order_main` /* generated by server */;

thread_id=8821，对应的是开发同学阿杰早上在本地Navicat连生产库时，手滑把一条 DROP TABLE 拖进了执行窗口。

我们需要的不是删掉之后的位置，而是删除事件之前的那个 end_log_pos：123460215。这就是我们的“存档点”。

💡 给刚入行的小朋友打个比方：binlog就像学校走廊里的监控录像。你不小心把作业本扔进垃圾桶了，但录像还清楚记录着你扔之前的每一秒。我们要做的，就是把作业本从垃圾桶里捞出来，再把录像快进到垃圾桶打开前的那一帧。

第三刀：把昨天的世界搬回来

昨晚凌晨2点的物理备份是 XtraBackup 全量备份，文件在 /data/backup/full_20240314/。恢复流程分两步：预准备和回拷。

# 1. 预准备：合并事务日志，保证数据文件一致
xtrabackup --prepare --target-dir=/data/backup/full_20240314

# 2. 停库，清空当前数据目录，回拷备份
systemctl stop mysqld
rm -rf /var/lib/mysql/*
xtrabackup --copy-back --target-dir=/data/backup/full_20240314

# 3. 修正权限，重启
chown -R mysql:mysql /var/lib/mysql
systemctl start mysqld

启动后，表回来了，但数据停留在昨晚2点。也就是说，从昨晚2点到今早2:17之间产生的所有新订单、支付回调、状态更新，暂时都不见。

这时候千万别急着接流量。我们先把备份恢复到一台测试机，跑一遍验证脚本，确认主键连续、外键正常、金额汇总和业务报表对得上。测试机验证通过，才敢往生产库回放binlog。

第四刀：用binlog把时间往前拨

这是最关键的一步。我们要把 mysql-bin.000123 从备份位置 123456789 开始回放，一直回放到删除事件之前的 123460215。

生产环境绝对禁止直接管道写入，正确姿势是先导出成SQL文件，人工或脚本核对后再导入：

mysqlbinlog \
  --start-position=123456789 \
  --stop-position=123460215 \
  --database=prod_db \
  /var/log/mysql/mysql-bin.000123 \
  > /tmp/binlog_incremental.sql

# 检查文件大小和关键字段，确认没有异常DDL
wc -l /tmp/binlog_incremental.sql
grep -c "INSERT INTO t_order_main" /tmp/binlog_incremental.sql

导出后，老张在测试库先跑了一遍模拟恢复，确认 t_order_main 的数据量和业务侧报表一致，才在生产库执行：

mysql -u recovery_user -p < /tmp/binlog_incremental.sql

导入完成后，立刻跑一致性校验：

-- 1. 核对今日订单总数
SELECT COUNT(*) AS order_cnt FROM t_order_main WHERE create_time >= '2024-03-15 00:00:00';
-- 预期：与支付网关回调数一致，约 12,407 条

-- 2. 核对金额汇总
SELECT SUM(pay_amount) AS total_pay FROM t_order_main 
WHERE create_time >= '2024-03-15 00:00:00' AND status = 'PAID';
-- 预期：与财务系统对账单误差在 0.01 元以内

-- 3. 抽查最近一笔订单的完整链路
SELECT id, user_id, pay_amount, status, create_time 
FROM t_order_main ORDER BY create_time DESC LIMIT 1;

三组数据全部对齐。表结构完整，索引正常，binlog位点已经推进到删除事件之前。

40分钟后，订单接口重新跳动

从告警响起到流量切回，总共用了38分钟。时间分配大致如下：

阶段 耗时 关键动作
告警响应+止血 5分钟 设只读、踢连接、摘流量
binlog定位 8分钟 mysqlbinlog 过滤时间窗、抓DDL事件
全量备份恢复 10分钟 XtraBackup prepare→copy-back→启库
增量回放+校验 12分钟 导出binlog→测试库验证→生产导入→对账
流量切回 3分钟 逐步放开白名单、观察错误率

业务侧没有感知到超过4秒的查询失败。支付通道那边甚至没收到退票通知，因为我们在写操作停止前就已经完成了数据缝合。

事后我们改了什么，以及给新人的真心话

事故定级P1，复盘会开了整整两个小时。骂人没用，我们只改了四件事：

  1. 生产库权限彻底收口。开发人员不再拥有生产库直连权限。所有变更走工单，由DBA在跳板机上执行。Navicat直连生产库的规则写进了堡垒机策略，违规连接直接断网。
  2. binlog保留期从24小时拉到7天，格式统一为 binlog_format=ROW。STATEMENT 格式在部分DDL和函数调用下容易丢数据，ROW是最稳妥的恢复底座。
  3. 每周做一次“盲恢复演练”。不提前通知，随机挑一个库，从全量备份+binlog恢复到测试环境，计时并记录偏差。演练不是为了表演，是为了让团队肌肉记忆在真出事的时候不手抖。
  4. DDL变更加前置校验脚本。任何 DROP、TRUNCATE、ALTER 语句，必须经过 pt-osc 或 gh-ost 在线变更工具包装，或者先在测试库 EXPLAIN 和 SHOW CREATE TABLE 比对通过后才能上生产。

最后说句实在话：数据丢失从来不是技术问题，是习惯问题。 很多程序员觉得“备份有运维管”“binlog应该开着吧”，结果真出事的时候才发现备份是三个月前的、binlog被轮转掉了、恢复文档还停留在纸质笔记本里。

如果你刚接触数据库，记住三件事就够了：

  • 别在生产库直接跑 DROP、DELETE、UPDATE，尤其是没有 WHERE 的；
  • 每次备份做完，顺手看一眼备份文件大小和最近一次成功时间；
  • 把恢复流程写成可执行的脚本，而不是写在脑子里的“大概知道”。

MySQL不会原谅手滑，但它会奖励那些每天认真写备份脚本的人。这次40分钟能救回来，不是运气好，是我们平时把“最坏的情况”当成了“必须准备好的情况”。

如果你身边有小朋友或者刚入职的同事，你可以这样告诉他：想象你在玩一个存档游戏。全量备份是你昨天下午存的档，binlog是你从存档到现在每一秒的操作录像。角色不小心把装备扔进了岩浆，没关系，加载昨天的存档，然后把录像倒回到扔装备之前的一帧。数据就回来了。

技术这东西，听起来吓人，拆开看就是：备份、日志、流程、演练。做到位了，深夜的告警群也不会真的把你送走。

-- 展开阅读全文 --