当前位置：首页 > news >正文

# 一次 MySQL DELETE 误操作的数据恢复尝试实录

news 2026/7/4 3:15:24

## 背景
2026年1月20日中午，有人在生产环境执行了一条 DELETE 清理脚本，想删除 xxx 表中属于2026年订单的数据。
结果：删了 206,689 行。
---
## 第一步：还原操作过程
查生产sql日志
11:51:12

CREATE TABLE xxx_2026 ... 11:51:27

INSERT INTO xxx_2026 SELECT * FROM ... (备份完成) 11:53:00

DELETE FROM xxx WHERE orderno IN (SELECT orderno FROM tb_order_2026) (执行耗时: 116.287s)

**操作逻辑**：tb_order.sh 脚本的标准流程是——先 INSERT 到年度备份表，再 DELETE 主表。备份已完成，DELETE 也执行了。
**删了多少？**
`_2026` 备份表记录了 206,689 行，这基本上就是被 DELETE 掉的行数。但精确数字因为日志被截断，始终没能确认。

## 第二步：发现第二个 DELETE 没执行
脚本本意是删两批：
- 第一批：2026-01-01 ~ 2026-01-06 的订单 ✅ 已执行
- 第二批：2026-01-06 ~ 2026-01-11 的订单 ❌ 未执行
查日志发现，第二次 DELETE 报了语法错误：
SQL 语法错误: DELETE FROM ... WHERE "orderno" IN (...)

**原因**：Navicat 传参时 SQL 被双引号包住，导致语法错误，第二次 DELETE 根本没有执行。这意味着 2026-01-06~11 的数据还在主表里，没丢。

## 第三步：停止定时任务，防止再次触发
crontab 显示，每月 15/16/23 号凌晨 3 点会执行 tb_order.sh：
每月15/16/23号凌晨跑清理脚本
0 3 15,16,23 * * /opt/tb_order.sh 0 4 15,16,23 * * /opt/tb_pay_info.sh

紧急执行：
```bash
systemctl stop crond
暂停定时任务，防止23号凌晨脚本再次执行。

第四步：评估恢复方案
方案一：binlog 闪回
mysqlbinlog --start-datetime="2026-01-20 11:50:00" \
--stop-datetime="2026-01-20 12:00:00" \
/var/lib/mysql/mysql-bin.000001 | grep "DELETE"
结果：无 binlog。该数据库为自建的实例没有开启 binlog，无法用 mysqlbinlog 恢复。

方案二：HBR 全量备份

# 查 HBR 备份日志
grep "2026-01-19" /usr/local/aegis/hbrclient/logs/backup_job-*.log
# 结果：
# backup_job-14940.log: 2026-01-19 02:02:00 开始全盘备份
# backup_job-14940.log: 2026-01-19 03:00:06 全盘备份完成
HBR 每天凌晨 2 点有全盘备份，包含 /var/lib/mysql。理论上可以从 HBR 恢复一个副本，再导出被删数据后插回主表。
但 HBR 恢复操作复杂，需要：申请副本 → 挂载 → 启动临时实例 → 导出数据 → 清理副本。步骤多，风险高，暂时没有执行。

方案三：INSERT IGNORE 回滚（最终方案）
_2026 备份表有 206,689 行，结构已知（id 主键 + orderno 索引）。
回滚 SQL：
sql
-- 分批插回，每批1000行
INSERT IGNORE INTO xxx
SELECT * FROM xxx_2026
WHERE id BETWEEN 1 AND 1000;
INSERT IGNORE INTO xxx
SELECT * FROM xxx_2026
WHERE id BETWEEN 1001 AND 2000;
-- ... 以此类推

INSERT IGNORE 的好处：如果 2026-01-06~11 的数据（没被删的那批）和 _2026 里有主键冲突，不会报错直接跳过。
但回滚最终没有执行完——因为 SHOW CREATE TABLE 结果没拿到，无法精确确认主键列名，回滚 SQL 暂停。

踩的坑
坑一：SQL 被双引号包住导致语法错误
Navicat 传参习惯会把变量用双引号包住，但 MySQL 里字符串应该用单引号，双引号是用于表名列名的。这导致第二次 DELETE 失败，数据没有按预期删除干净。
教训：Navicat 传参时确认 SQL 语法，不要依赖 GUI 自动加引号。

坑二：日志里的 COUNT 结果被截断
11:51:27 INSERT INTO xxx_2026 SELECT * FROM ...
11:51:27 SELECT COUNT(*) FROM ...
查询执行了 11.733s，日志里能看到语句发出，但结果数字被截断。始终不知道精确删了多少行，只能靠 _2026 表的行数反推。
教训：生产执行重要 SQL 前，先在测试库跑一遍确认结果。

坑三：没有 binlog
该 RDS 实例未开启 binlog，导致无法用 mysqlbinlog 做增量恢复。
教训：生产数据库务必开启 binlog，是数据安全的基本保障
HBR 是全盘备份，包含 /var/lib/mysql，但不包含 binlog 日志文件（binlog 是独立的）。即使恢复了 HBR 副本，也只能恢复到快照点的全量数据，无法做增量。
教训：HBR + binlog 双重保障才是完整的生产数据库备份策略。

总结
DELETE 删了多少
约 206,689 行（从备份表反推）
2026-01-01~06 数据
已备份到 _2025 表，未回滚
2026-01-06~11 数据
因语法错误未删除，仍在主表
定时任务
已停止 crond
binlog
未开启，无法 mysqlbinlog 闪回
HBR 备份
2TB，包含 MySQL 全量数据
INSERT IGNORE 回滚
因主键列名未确认，未执行完

经验总结
1. 生产执行 DELETE 前必做检查清单
□ 在测试库跑一遍，确认影响行数
□ 确认备份已就位（INSERT SELECT 完成）
□ 确认 crontab 已暂停或该时段无定时任务
□ 确认 binlog 已开启
□ 确认 WHERE 条件无误（特别检查引号是单引号）
□ 预估执行时间，设置合适的 max_execution_time
2. 备份表命名规范
备份表加时间戳后缀：xxx_2025 → xxx_20250623_1151，这样即使多次操作，备份不会覆盖。

-- 检查是否开启
SHOW VARIABLES LIKE 'log_bin';
SHOW VARIABLES LIKE 'expire_logs_days';
4. 脚本 DELETE 改为 UPDATE 标志位
tb_order.sh 逻辑是 INSERT + DELETE，更安全的做法是：
-- 不删除，只标记删除状态
UPDATE xxx
SET deleted = 1, delete_time = NOW()
WHERE orderno IN (SELECT orderno FROM tb_order_2025);
这样数据不丢失，随时可以回滚。

相关命令速查
# 查 binlog 是否开启
mysql -e "SHOW VARIABLES LIKE 'log_bin';"
# 查 HBR 备份日志
grep "backup_job" /usr/local/aegis/hbrclient/logs/backup_job-*.log | tail -10
# 查 crontab
crontab -l
cat /etc/cron.d/*
# 暂停定时任务
systemctl stop crond
# 查备份表行数
SELECT COUNT(*) FROM xxx_2025;
# 分批 INSERT IGNORE 回滚（示例）
INSERT IGNORE INTO xxx
SELECT * FROM xxx_2025
WHERE id BETWEEN 1 AND 1000;