跳到主要内容

备份与数据恢复

「误删数据怎么恢复」是场景题的常客，答得好要分清误删行 / 误删表 / 误删库 / 误删实例四种情况，手段完全不同。

备份类型​

类型工具特点
逻辑备份mysqldump、mydumper导出 SQL 文本，跨版本跨平台，恢复慢
物理备份XtraBackup、clone plugin（8.0.17+）直接拷数据文件，快，适合大库，但同版本同平台
binlog—增量，配合全备实现任意时间点恢复（PITR）

mysqldump 的关键参数：

mysqldump --single-transaction \   # 用一致性快照，不锁表（仅 InnoDB 有效）
          --master-data=2 \        # 把备份时的 binlog 位点写进注释，PITR 必需
          --routines --triggers --events \
          --set-gtid-purged=ON \   # GTID 模式必需
          -A > full.sql

--single-transaction 的原理：开启一个 RR 隔离级别的事务，靠 MVCC 拿到一致性视图，所以不需要锁表。代价是备份期间这个长事务会导致 undo 无法回收（见事务与 MVCC的长事务危害）。

不加它就会用 FTWRL（FLUSH TABLES WITH READ LOCK）—— 全库只读，业务直接不可写。这个区别是高频考点。

:::warning --single-transaction 不是万能的 它只对 InnoDB 有效。如果库里混有 MyISAM 表，这些表的备份不在一致性视图内，备份出来的数据可能不一致。混合引擎的库只能用 FTWRL。

另外备份期间执行 DDL 会让这个事务失败（MDL 冲突）—— 所以备份和改表不能同时做。 :::

🔥 四种误删的恢复手段​

1. 误删行（DELETE 删错了）​

用 Flashback 反向生成 SQL。前提是 binlog_format=ROW 且 binlog_row_image=FULL。

原理：row 格式的 binlog 记录了每行变更前后的完整镜像，所以可以反向推导：

原操作binlog 事件反向操作
DELETEDelete_rows改成 Write_rows（插回去）
INSERTWrite_rows改成 Delete_rows
UPDATEUpdate_rows前后镜像对调

多个事务要倒序执行：误操作是 A→B→C，恢复要 C'→B'→A'。

工具：binlog2sql、MyFlash，或 mysqlbinlog -v 解析后手工处理。

:::danger 不要直接在主库上执行恢复 标准做法是先在一个临时库上恢复并确认数据正确，再把数据同步回主库。直接在主库上跑反向 SQL，万一方向搞错（比如把 UPDATE 的前后镜像弄反）会造成二次破坏。 :::

2. 误删表 / 误删库（DROP / TRUNCATE）​

Flashback 救不了 —— DROP TABLE、TRUNCATE TABLE、DROP DATABASE 在 binlog 里都是 statement 格式记录的一条 DDL 语句（即使 binlog_format=ROW 也一样），没有行数据可以反推。

只能靠「全量备份 + binlog 重放」：

# 1. 取最近一次的全量备份，恢复到一个临时实例
mysql -h tmp_instance < full.sql

# 2. 从全备的位点开始，重放 binlog 到误操作之前的那一刻
mysqlbinlog --start-position=<全备位点> \
            --stop-datetime="2026-01-01 10:29:59" \
            binlog.0000xx | mysql -h tmp_instance

# 3. 把需要的表 / 库从临时实例导回生产

关键细节：

  • 重放时要跳过那条误删的 DDL，可以用 --stop-position 精确停在它之前，再用 --start-position 从它之后继续
  • mysqlbinlog 重放不支持 --database 过滤的完整语义（有些语句会被漏掉或误带），精确恢复推荐用 start-position / stop-position 而不是按库过滤
  • 重放是单线程的，几天的 binlog 可能要重放很久。提速办法：把临时实例设为备份库的从库，用并行复制来重放
  • 恢复时间取决于「上次全备到现在的 binlog 量」，所以备份频率决定了 RTO

3. 误删实例（rm 删了数据目录）​

只要还有一个完整的从库就不是灾难 —— 把从库提升为主库即可。所以这是多副本架构本身在提供保护。

HA 系统会自动选出新主库。真正的问题是「误删了整个集群」（如批量运维脚本执行错），这时只能靠跨机房 / 跨云的异地备份。

4. 更隐蔽的：误更新（UPDATE 漏了 WHERE）​

和误删行同样处理（Flashback 反向），但发现得往往更晚 —— 因为数据还在，只是值错了，可能几天后才有人反馈。

🔥 预防比恢复重要​

被问「误删数据怎么办」，如果只答恢复手段，那是答了一半。面试官真正想听的是预防机制：

账号权限​

  • 业务账号不给 DROP / TRUNCATE / ALTER 权限，只给 SELECT / INSERT / UPDATE / DELETE
  • DBA 平时用只读账号，需要写操作时临时提权
  • 删除数据改为先改名再删：RENAME TABLE t TO t_bak_20260101，观察一周确认无影响再真删

SQL 审核​

sql_safe_updates = ON    # DELETE / UPDATE 必须带 WHERE 或 LIMIT，否则报错

这个参数能挡住「DELETE FROM t」这类最低级的事故。注意它要求 WHERE 条件能用上索引，否则同样报错 —— 这也顺带约束了全表扫描式更新。

上线前走 SQL 审核平台（Yearning、Archery、SQLE），自动检查：是否带 WHERE、是否有索引、预估影响行数、是否大事务。

延迟从库​

CHANGE MASTER TO MASTER_DELAY = 3600;   -- 延迟 1 小时

这是专门为误操作设计的时间机器：主库上误删了数据，延迟从库上这条数据还在（只要在 1 小时内发现）。处理方式是停止延迟从库的复制、重放 binlog 到误操作之前、把数据取出来。

比全备 + binlog 重放快得多 —— 不需要恢复整个实例。生产环境建议配一个延迟 1～6 小时的从库，成本只是一台机器。

其他​

  • 操作前先备份：CREATE TABLE t_bak AS SELECT * FROM t WHERE ...
  • 分批执行：DELETE ... LIMIT 1000 循环，既避免大事务也给了中途发现错误的机会（见加锁规则实战的 limit 缩小锁范围）
  • 生产和测试环境的连接配置物理隔离，避免「以为连的是测试库」
  • 定期做恢复演练 —— 没演练过的备份等于没有备份。很多团队发现备份不可用都是在真正需要它的时候

备份的其他考点​

全备应该在主库还是从库做？ 从库。备份会消耗 IO 和 CPU，且 --single-transaction 会产生长事务。在从库上做的代价是「从库在备份期间延迟会增大」。

备份要验证什么？

  1. 能否成功恢复（定期演练）
  2. 恢复后的数据是否完整一致（pt-table-checksum 对比）
  3. 备份文件的校验和（防止存储介质损坏）
  4. RTO（恢复要多久）和 RPO（会丢多少数据）是否满足业务要求

binlog 要保留多久？ binlog_expire_logs_seconds（8.0，旧参数 expire_logs_days）。至少要覆盖两次全备的间隔 + 安全余量，否则「全备 + binlog」链条会断。磁盘空间不够时把 binlog 归档到对象存储。

binlog_row_image 的三个值：

值记录什么后果
FULL（默认）变更前后的所有列binlog 大，但 Flashback 和复制都安全
MINIMAL只记主键 + 被修改的列binlog 小，但无法完整反推原始数据，Flashback 失效
NOBLOB除未修改的 BLOB / TEXT 外都记折中

为了能 Flashback 必须用 FULL —— 这是「省 binlog 空间」和「保留恢复能力」的取舍，生产应该选后者。

最后于 更新