MySQL基于备份库与 binlog 的组合恢复方案

一、背景说明

在一次 MySQL 数据维护过程中，执行了一条未带 WHERE 条件的 UPDATE 语句，导致业务表中多条数据被误更新。

误操作类似如下：

执行结果显示：

也就是说，该表中 379 条记录被全部更新，其中 address_line 和 approval_admin_id 两个字段被错误覆盖。

由于该操作已提交，无法通过 ROLLBACK 回滚，只能通过备份和 binlog 进行恢复。

---

二、故障现象

误操作后，查询发现大量记录的地址字段被统一改成了同一个错误地址：

查询结果显示仍有多条数据异常。

---

三、恢复思路

本次恢复分为两部分：

因为昨天已有全量备份，并且 MySQL 开启了 binlog，所以最终采用：

---

四、第一步：确认 binlog 配置

先确认 MySQL 是否满足基于 binlog 恢复的条件。

结果为：

这两个配置非常关键：

• ROW：binlog 会记录每一行的变更
• FULL：会记录更新前后的完整字段值

这意味着可以从 binlog 中找到误更新前的原始数据。

---

五、第二步：备份当前错误表

在恢复前，先把当前表完整备份成 SQL 文件，避免二次操作失误。

检查备份文件：

这是恢复前的安全兜底，非常重要。

---

六、第三步：通过昨天备份库恢复历史数据

由于昨天的全量备份已经导入到了另一台本地服务器，因此先将备份表导入到生产库的临时恢复库中。

1. 在本地备份库导出表

将该文件传到生产服务器。

2. 生产库创建临时恢复库

3. 导入备份表

4. 预览可恢复的数据

这里发现可匹配到 367 条，说明这 367 条在昨天备份中存在，可以直接从备份库恢复。

5. 执行恢复

---

七、第四步：定位剩余未恢复数据

恢复 367 条后，继续查询仍然异常的数据：

结果发现剩余 12 条。

这些记录的 created_at 都是当天生成的，说明它们是：

因此昨天的备份库中没有这些 ID，不能通过备份库恢复，只能通过 binlog 找回误更新前的值。

---

八、第五步：导出误操作时间段的 binlog

先查询 binlog 文件：

然后导出误操作时间段的 binlog：

示例：

---

九、第六步：从 binlog 中提取 12 条旧值

根据剩余的 12 个主键 ID，在 binlog 文件中搜索：

在 ROW + FULL 模式下，binlog 中会看到类似结构：

这里需要注意：

所以恢复时要取 WHERE 部分 的值。

本次表结构中：

---

十、第七步：生成 12 条恢复 SQL

根据 binlog 的 WHERE 部分生成恢复 SQL。

示例：

如果 approval_admin_id 原值为 NULL，必须写成：

而不是：

最终可以合并成一条 CASE WHEN 形式的批量恢复 SQL：

---

十一、第八步：恢复后核对

恢复完成后，先核对这批 ID：

再检查误更新值是否仍然残留：

如果返回 0，说明误更新数据已全部恢复。

---

十二、为什么备份库只恢复了 367 条，而不是 379 条？

原因是误更新总共影响了 379 条，但其中：

所以恢复策略必须拆开处理。

---

十三、关键经验总结

1. 误执行 UPDATE 后不要继续乱改

一旦发现误操作，第一时间：

切换新 binlog，避免后续操作和误操作混在一起。

2. 立即备份当前错误表

即使当前数据是错的，也要先留现场。

3. 优先使用备份库恢复

如果有全量备份，最稳方式是：

不要直接全表覆盖，避免影响误操作之后新增或正常变化的数据。

4. 当天新增数据要用 binlog 恢复

备份库没有当天新增数据，因此需要从 ROW binlog 中取旧值恢复。

5. binlog 中 WHERE 和 SET 的含义不能搞反

恢复误更新时，一定要取 WHERE 部分。

6. 生产环境批量 UPDATE 必须先 SELECT

执行 UPDATE 前，必须先执行对应 SELECT：

再执行：

7. 高危 SQL 建议强制开启安全模式

客户端或数据库侧建议开启：

或者在 Navicat、DBeaver 等客户端中开启安全更新提醒。

---

十四、推荐的生产操作规范

批量 UPDATE 标准流程

示例：

如果发现不对：

生产环境禁忌

不要直接执行：

不要在没有 WHERE 条件的情况下更新业务表。

---

十五、本次恢复结论

本次误操作共影响 379 条数据。

最终恢复方式：

恢复后再次查询误更新条件，异常数据清零，业务数据恢复成功。

这次事故说明：