4/28/202690 Views

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

一、背景说明

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

误操作类似如下：

sql
UPDATE biz_address_apply_info SET address_line = '某错误地址', approval_admin_id = 某管理员ID;

执行结果显示：

text
Rows matched: 379 Changed: 379 Warnings: 0

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

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

二、故障现象

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

sql
SELECT id, address_line, approval_admin_id, created_at FROM biz_address_apply_info WHERE address_line = '某错误地址' AND approval_admin_id = 某错误管理员ID ORDER BY created_at, id;

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

三、恢复思路

本次恢复分为两部分：

数据类型恢复方式
昨天备份中已存在的数据使用备份库按主键恢复
当天新增、备份中不存在的数据使用 binlog 恢复误操作前旧值

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

text
备份库恢复大部分历史数据 + binlog 恢复当天新增数据

四、第一步：确认 binlog 配置

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

sql
SHOW VARIABLES LIKE 'binlog_format'; SHOW VARIABLES LIKE 'binlog_row_image';

结果为：

text
binlog_format ROW binlog_row_image FULL

这两个配置非常关键：

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

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

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

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

bash
mysqldump -uroot -p 业务库名 biz_address_apply_info > /tmp/biz_address_apply_info_bak_before_restore.sql

检查备份文件：

bash
ls -lh /tmp/biz_address_apply_info_bak_before_restore.sql head -n 20 /tmp/biz_address_apply_info_bak_before_restore.sql

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

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

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

1. 在本地备份库导出表

bash
mysqldump -uroot -p 备份库名 biz_address_apply_info > /tmp/biz_address_apply_info_restore.sql

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

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

sql
CREATE DATABASE company_address_restore DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

3. 导入备份表

bash
mysql -uroot -p company_address_restore < /tmp/biz_address_apply_info_restore.sql

4. 预览可恢复的数据

sql
SELECT t.id, t.address_line AS 当前_address_line, b.address_line AS 备份_address_line, t.approval_admin_id AS 当前_approval_admin_id, b.approval_admin_id AS 备份_approval_admin_id FROM 生产库.biz_address_apply_info t JOIN company_address_restore.biz_address_apply_info b ON t.id = b.id WHERE t.address_line = '某错误地址' AND t.approval_admin_id = 某错误管理员ID;

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

5. 执行恢复

sql
UPDATE 生产库.biz_address_apply_info t JOIN company_address_restore.biz_address_apply_info b ON t.id = b.id SET t.address_line = b.address_line, t.approval_admin_id = b.approval_admin_id WHERE t.address_line = '某错误地址' AND t.approval_admin_id = 某错误管理员ID;

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

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

sql
SELECT id, address_line, approval_admin_id, created_at FROM 生产库.biz_address_apply_info WHERE address_line = '某错误地址' AND approval_admin_id = 某错误管理员ID ORDER BY created_at, id;

结果发现剩余 12 条。

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

text
备份之后新增的数据

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

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

先查询 binlog 文件：

sql
SHOW BINARY LOG STATUS;

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

bash
mysqlbinlog --base64-output=DECODE-ROWS -vv \ --start-datetime="YYYY-MM-DD HH:MM:SS" \ --stop-datetime="YYYY-MM-DD HH:MM:SS" \ /path/to/binlog.000001 > /tmp/binlog_restore_range.txt

示例：

bash
mysqlbinlog --base64-output=DECODE-ROWS -vv \ --start-datetime="2026-04-27 14:00:00" \ --stop-datetime="2026-04-27 14:02:00" \ /var/lib/mysql/binlog.000001 > /tmp/binlog_restore_range.txt

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

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

bash
grep -n -A80 -B20 "主键ID" /tmp/binlog_restore_range.txt

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

text
### UPDATE `数据库名`.`biz_address_apply_info` ### WHERE ### @1=主键ID ### @2='误更新前的 address_line' ### ... ### @40=误更新前的 approval_admin_id ### SET ### @1=主键ID ### @2='误更新后的 address_line' ### ... ### @40=误更新后的 approval_admin_id

这里需要注意：

text
WHERE 部分 = 更新前旧值 SET 部分 = 更新后新值

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

本次表结构中：

binlog 字段含义
@1主键 ID
@2原 address_line
@40原 approval_admin_id

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

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

示例：

sql
UPDATE 生产库.biz_address_apply_info SET address_line = '原始地址', approval_admin_id = 原始管理员ID WHERE id = 主键ID;

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

sql
approval_admin_id = NULL

而不是：

sql
approval_admin_id = 'NULL'

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

sql
UPDATE 生产库.biz_address_apply_info SET address_line = CASE id WHEN 主键ID1 THEN '原始地址1' WHEN 主键ID2 THEN '原始地址2' WHEN 主键ID3 THEN '原始地址3' END, approval_admin_id = CASE id WHEN 主键ID1 THEN NULL WHEN 主键ID2 THEN 原始管理员ID2 WHEN 主键ID3 THEN 原始管理员ID3 END WHERE id IN ( 主键ID1, 主键ID2, 主键ID3 );

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

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

sql
SELECT id, address_line, approval_admin_id, created_at FROM 生产库.biz_address_apply_info WHERE id IN ( 主键ID1, 主键ID2, 主键ID3 ) ORDER BY id;

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

sql
SELECT COUNT(*) AS remain_wrong_count FROM 生产库.biz_address_apply_info WHERE address_line = '某错误地址' AND approval_admin_id = 某错误管理员ID;

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

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

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

text
367 条：昨天备份中已存在，可以通过备份库按 id 恢复 12 条：当天新增，昨天备份中没有，只能通过 binlog 恢复

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

十三、关键经验总结

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

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

sql
FLUSH LOGS;

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

2. 立即备份当前错误表

bash
mysqldump -uroot -p 数据库名 表名 > /tmp/table_before_restore.sql

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

3. 优先使用备份库恢复

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

text
备份表导入临时库 → 按主键 JOIN → 只恢复误改字段

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

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

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

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

text
WHERE = 更新前 SET = 更新后

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

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

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

sql
SELECT COUNT(*) FROM 表名 WHERE 条件;

再执行：

sql
UPDATE 表名 SET 字段 = 值 WHERE 条件;

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

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

sql
SET SQL_SAFE_UPDATES = 1;

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

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

批量 UPDATE 标准流程

text
1. SELECT 预览影响范围 2. COUNT 确认行数 3. BEGIN 开启事务 4. UPDATE 执行修改 5. SELECT 核对结果 6. COMMIT 提交 7. 异常则 ROLLBACK

示例：

sql
START TRANSACTION; SELECT COUNT(*) FROM biz_address_apply_info WHERE 条件; UPDATE biz_address_apply_info SET 字段 = 新值 WHERE 条件; SELECT * FROM biz_address_apply_info WHERE 条件; COMMIT;

如果发现不对：

sql
ROLLBACK;

生产环境禁忌

不要直接执行：

sql
UPDATE 表名 SET 字段 = 值;

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

十五、本次恢复结论

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

最终恢复方式：

text
367 条通过昨天全量备份恢复 12 条通过 MySQL ROW binlog 恢复

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

这次事故说明：

text
备份是底线，binlog 是救命绳，操作规范是第一道防线。

讨论 0

0 / 100
Supports **Bold**, `Code`
No comments yet.