第 10 讲：MySQL 高可用、备份恢复、容灾

这一讲是整个 MySQL 学习体系的最后一块重要拼图。

前面已经学了：

表设计、索引、事务、锁、MVCC
慢查询优化
主从复制
分库分表

现在要解决的是：

数据库出了问题怎么办？数据丢了怎么找回？系统怎么设计才能在故障时不中断服务？

一、先建立一个核心认知

很多开发者平时只关注"写代码、查数据"，觉得备份恢复是 DBA 的事。

但实际上：

后端工程师必须理解备份恢复和容灾，因为你的业务代码直接影响数据安全。

比如：

写了一个 bug，导致大批数据被错误更新
没有事务保护，导致数据部分写入
批量删除逻辑有问题，删了不该删的数据

这些情况下，你需要知道怎么恢复数据。

二、MySQL 数据安全的三道防线

第一道防线：业务层保护

事务保证原子性
逻辑删除代替物理删除
操作前校验
权限控制

第二道防线：数据库层保护

binlog 记录所有变更
redo log 保证已提交数据不丢
主从复制提供冗余

第三道防线：备份恢复

定期全量备份
增量备份
异地备份
定期演练恢复

三、备份策略

1. 全量备份

把整个数据库完整备份一份。

工具：mysqldump

bash

mysqldump -u root -p \
  --all-databases \
  --single-transaction \
  --master-data=2 \
  > backup_full_20250407.sql

参数说明

--single-transaction

对 InnoDB 表，开启事务做一致性备份，不锁表。

--master-data=2

在备份文件里记录当前 binlog 文件名和位置。这样恢复后可以继续做增量恢复。

全量备份的问题

数据量大时，备份文件很大
备份耗时长
恢复时也需要很长时间

所以全量备份通常每天做一次或每周做一次。

2. 增量备份

只备份上次备份以来的变更。

依赖 binlog

binlog 记录了所有变更，增量备份本质上就是定期归档 binlog。

怎么做增量备份？

方式 1：定期备份 binlog 文件

把 binlog 文件定期同步到备份存储（比如 OSS）。

方式 2：mysqlbinlog 工具

bash

mysqlbinlog mysql-bin.000123 > binlog_backup.sql

增量备份的好处

体积小
频率可以很高（比如每小时）
可以做到分钟级别的数据恢复

3. 物理备份

直接拷贝数据库的物理文件。

工具：XtraBackup（Percona 开源）

bash

xtrabackup --backup \
  --target-dir=/backup/full \
  --user=root \
  --password=xxx

优点

备份速度快
恢复速度快
不影响线上服务

适合场景

大数据量（几百 GB 以上）
对备份恢复速度要求高

4. 备份策略组合

生产环境通常这样组合：

text

每周一次全量备份（mysqldump 或 XtraBackup）
+
每天增量备份（binlog 归档）
+
实时 binlog 同步到备份存储

四、数据恢复

场景 1：完整恢复（全量）

比如机器彻底故障，需要从备份恢复整个数据库。

bash

mysql -u root -p < backup_full_20250407.sql

场景 2：基于时间点恢复（PITR）

Point In Time Recovery。

这是最常用的恢复方式，可以把数据恢复到某个具体时刻。

场景举例

今天早上 10:00 做了全量备份
下午 14:30 有人误删了一张表
需要恢复到 14:29 的状态

恢复步骤

第一步：恢复全量备份

bash

mysql -u root -p < backup_full_20250407.sql

这时数据库回到了早上 10:00 的状态。

第二步：找到误操作的时间点

用 mysqlbinlog 查看 binlog：

bash

mysqlbinlog mysql-bin.000123 | grep -i "drop table"

找到误操作发生的时间，比如：

text

# 14:30:25 执行了 DROP TABLE order_info;

第三步：重放 binlog 到误操作前

bash

mysqlbinlog \
  --start-datetime="2025-04-07 10:00:00" \
  --stop-datetime="2025-04-07 14:30:00" \
  mysql-bin.000123 | mysql -u root -p

这样就把 10:00 到 14:30 之间的所有变更重放了一遍，但没有包含 14:30 之后的误操作。

恢复完成

数据回到了误操作前 14:30 的状态。

场景 3：单表恢复

如果只是误删了某张表，不想恢复整个数据库。

方法 1：从备份里提取单表

bash

mysqldump -u root -p db_name table_name > table_backup.sql

恢复：

bash

mysql -u root -p db_name < table_backup.sql

方法 2：用 binlog 反向解析

如果是误 UPDATE / DELETE，可以用工具（如 binlog2sql）解析 binlog，生成反向 SQL（把 UPDATE 变成回滚前的 UPDATE）。

五、如何防止误操作？

1. 逻辑删除代替物理删除

加 is_deleted 字段，删除时只改字段，不真正 DELETE。

这样即使业务层出现 bug，数据还在，可以通过 UPDATE 恢复。

2. 操作前做数据快照

对于重要批量操作，先备份数据：

sql

-- 先备份
CREATE TABLE order_info_backup_20250407 AS
SELECT * FROM order_info WHERE ...;

-- 再操作
UPDATE order_info SET ... WHERE ...;

3. 权限控制

开发环境和生产环境分离
生产数据库最小权限原则
不给开发账号 DROP / DELETE 等危险权限

4. SQL 审计

记录所有 DDL 操作
重要 DML 操作需要审批
操作前强制备份

5. 延迟删除

物理删除不是立即执行，而是先标记，定期清理。

这样给了一个"后悔"的窗口期。

六、高可用架构方案

1. 主从架构（基础方案）

text

主库 (写) -> 从库 1 (读)
           -> 从库 2 (读)

主库挂了，手动切换从库为主库
有数据丢失风险（异步复制）

2. 半同步复制（减少数据丢失）

主库写入 binlog 后，至少等一个从库确认收到，才返回成功。

text

主库写 -> 等从库 ACK -> 返回成功

好处：即使主库宕机，至少有一个从库有完整数据。

3. MGR（MySQL Group Replication）

MySQL 官方的组复制方案。

多个节点组成一个复制组
强一致性写入（基于 Paxos 协议）
自动故障切换
最多容忍少数节点故障

4. InnoDB Cluster

基于 MGR 的高可用方案，包含：

MySQL Group Replication
MySQL Shell
MySQL Router

提供完整的高可用管理能力。

5. MHA（Master High Availability）

开源高可用工具。

监控主库状态
主库宕机时自动切换
尽量减少数据丢失

生产常见架构

中小型系统

text

主库 + 2 个从库
+ 半同步复制
+ MHA 自动切换

大型系统

text

多地多中心
+ MGR / InnoDB Cluster
+ 分库分表
+ 异地容灾

七、异地容灾

为什么需要异地容灾？

单机房故障场景：

机房断电
机房网络中断
自然灾害

这些情况下，整个机房的服务都不可用。

两地三中心

常见方案：

text

城市 A：主机房 + 同城备机房
城市 B：异地备机房

主机房故障：切换到同城备机房（延迟低）
整个城市故障：切换到异地备机房（延迟高但能用）

RPO 和 RTO

这是容灾设计的两个核心指标。

RPO（Recovery Point Objective）

恢复点目标。

数据最多丢失多长时间的量？

比如：

RPO = 0：不允许丢任何数据
RPO = 1h：允许丢最近 1 小时的数据

RTO（Recovery Time Objective）

恢复时间目标。

从故障到恢复服务，最多允许多长时间？

比如：

RTO = 0：立刻恢复，不允许停服
RTO = 30min：允许停服 30 分钟

不同场景要求不同

场景	RPO	RTO
金融核心系统	0	秒级
电商核心交易	秒级	分钟级
普通业务	分钟级	小时级
数据分析	小时级	天级

八、线上数据库操作规范

这是大厂里非常重要的工程规范。

1. 大表 DDL 操作要谨慎

对大表加列、改列、加索引，直接执行 ALTER TABLE 可能锁表很长时间。

解决方案：Online DDL

MySQL 5.6+ 支持 Online DDL，大多数 ALTER 操作不再锁全表。

更安全的方案：pt-online-schema-change

bash

pt-online-schema-change \
  --alter "ADD COLUMN remark VARCHAR(255)" \
  D=db_name,t=table_name \
  --execute

原理：

创建一张新表（有新结构）
用触发器同步增量数据
迁移存量数据
原子性切换表名

2. 批量操作要分批

不能：

sql

DELETE FROM order_info WHERE create_time < '2023-01-01';

要：

sql

-- 分批删除
DELETE FROM order_info
WHERE create_time < '2023-01-01'
LIMIT 1000;
-- 循环执行，每次 sleep 一会儿

3. 高危操作要有回滚方案

执行任何重要操作前：

先备份
先在测试环境验证
准备好回滚脚本

4. 不要在业务高峰期做重量级操作

比如备份、大表 DDL、数据迁移，应该在业务低峰期（凌晨）执行。

5. 变更要有审批和记录

所有 DDL 变更要审批
记录操作时间、操作人、操作内容
保留回滚方案

九、面试高频题

1. MySQL 怎么做备份？

全量备份用 mysqldump 或 XtraBackup，增量备份用 binlog 归档，通常组合使用：定期全量 + 实时 binlog 备份。

2. 如果误删了一张表，怎么恢复？

第一步：停止写入，防止数据继续变化；第二步：从最近的全量备份恢复；第三步：用 mysqlbinlog 重放备份到误操作前的时间点；这就是 PITR（基于时间点恢复）。

3. 什么是 RPO 和 RTO？

RPO 是数据最多丢多少，RTO 是最多停服多久。这两个指标决定了你的容灾方案需要多强。

4. 如何防止误删数据？

逻辑删除代替物理删除、操作前备份、权限控制、SQL 审计、延迟删除。

5. 大表怎么做 DDL 变更？

用 Online DDL 或 pt-online-schema-change，避免直接 ALTER TABLE 锁表时间过长影响线上服务。

十、核心结论总结

数据安全有三道防线：业务层、数据库层、备份恢复
全量备份 + 增量备份（binlog）是标准备份策略
PITR 可以把数据恢复到任意时间点
逻辑删除是防止误删的重要手段
半同步复制可以减少主从切换时的数据丢失
MGR / InnoDB Cluster 提供更强的高可用保证
RPO 和 RTO 是容灾设计的核心指标
大表 DDL 要用 Online DDL 或 pt-osc
批量操作要分批，避免大事务
高危操作必须有备份和回滚方案

十一、练习题

练习 1

你们系统今天下午 15:00 有人误执行了：

sql

DELETE FROM user WHERE id > 0;

把用户表全删了。

你有全量备份（今天早上 8:00）和完整 binlog。请描述你的恢复步骤。

练习 2

什么是 RPO 和 RTO？如果你的系统要求 RPO = 5 分钟，你的备份策略应该怎么设计？

练习 3

为什么大表直接 ALTER TABLE 很危险？有哪些解决方案？

十二、整个课程回顾

MySQL 全场景学习路线的 10 讲：

讲次	主题	核心内容
第 1 讲	表结构设计与字段类型选择	字段类型、主键设计、范式与反范式
第 2 讲	索引底层原理与索引设计	B+ 树、聚簇索引、回表、覆盖索引、联合索引、最左匹配
第 3 讲	事务、ACID、redo log、undo log、binlog	事务原理、三种日志、WAL、两阶段提交
第 4 讲	事务隔离级别	脏读、不可重复读、幻读、四种隔离级别
第 5 讲	MVCC、Read View、快照读、当前读	版本链、可见性判断、RC 和 RR 差异
第 6 讲	InnoDB 锁机制	行锁、间隙锁、临键锁、死锁、意向锁
第 7 讲	慢查询优化与性能调优	慢查询定位、深分页、count、join、大事务
第 8 讲	主从复制、读写分离、主从延迟	binlog 格式、主从原理、延迟原因、读写分离一致性
第 9 讲	分库分表	垂直拆分、水平拆分、分片键、全局 ID、分布式事务
第 10 讲	高可用、备份恢复、容灾	备份策略、PITR、防误删、高可用方案、RPO/RTO

第 10 讲：MySQL 高可用、备份恢复、容灾 ​

一、先建立一个核心认知 ​

二、MySQL 数据安全的三道防线 ​

第一道防线：业务层保护 ​

第二道防线：数据库层保护 ​

第三道防线：备份恢复 ​

三、备份策略 ​

1. 全量备份 ​

工具：mysqldump ​

参数说明 ​

全量备份的问题 ​

2. 增量备份 ​

依赖 binlog ​

怎么做增量备份？ ​

增量备份的好处 ​

3. 物理备份 ​

工具：XtraBackup（Percona 开源） ​

优点 ​

适合场景 ​

4. 备份策略组合 ​

四、数据恢复 ​

场景 1：完整恢复（全量） ​

场景 2：基于时间点恢复（PITR） ​

场景举例 ​

恢复步骤 ​

恢复完成 ​

场景 3：单表恢复 ​

方法 1：从备份里提取单表 ​

方法 2：用 binlog 反向解析 ​

五、如何防止误操作？ ​

1. 逻辑删除代替物理删除 ​

2. 操作前做数据快照 ​

3. 权限控制 ​

4. SQL 审计 ​

5. 延迟删除 ​

六、高可用架构方案 ​

1. 主从架构（基础方案） ​

2. 半同步复制（减少数据丢失） ​

3. MGR（MySQL Group Replication） ​

4. InnoDB Cluster ​

5. MHA（Master High Availability） ​

生产常见架构 ​

七、异地容灾 ​

为什么需要异地容灾？ ​

两地三中心 ​

RPO 和 RTO ​

八、线上数据库操作规范 ​

1. 大表 DDL 操作要谨慎 ​

2. 批量操作要分批 ​

3. 高危操作要有回滚方案 ​

4. 不要在业务高峰期做重量级操作 ​

5. 变更要有审批和记录 ​

九、面试高频题 ​

1. MySQL 怎么做备份？ ​

2. 如果误删了一张表，怎么恢复？ ​

3. 什么是 RPO 和 RTO？ ​

4. 如何防止误删数据？ ​

5. 大表怎么做 DDL 变更？ ​

十、核心结论总结 ​

十一、练习题 ​

练习 1 ​

练习 2 ​

练习 3 ​

十二、整个课程回顾 ​

第 10 讲：MySQL 高可用、备份恢复、容灾

一、先建立一个核心认知

二、MySQL 数据安全的三道防线

第一道防线：业务层保护

第二道防线：数据库层保护

第三道防线：备份恢复

三、备份策略

1. 全量备份

工具：mysqldump

参数说明

全量备份的问题

2. 增量备份

依赖 binlog

怎么做增量备份？

增量备份的好处

3. 物理备份

工具：XtraBackup（Percona 开源）

优点

适合场景

4. 备份策略组合

四、数据恢复

场景 1：完整恢复（全量）

场景 2：基于时间点恢复（PITR）

场景举例

恢复步骤

恢复完成

场景 3：单表恢复

方法 1：从备份里提取单表

方法 2：用 binlog 反向解析

五、如何防止误操作？

1. 逻辑删除代替物理删除

2. 操作前做数据快照

3. 权限控制

4. SQL 审计

5. 延迟删除

六、高可用架构方案

1. 主从架构（基础方案）

2. 半同步复制（减少数据丢失）

3. MGR（MySQL Group Replication）

4. InnoDB Cluster

5. MHA（Master High Availability）

生产常见架构

七、异地容灾

为什么需要异地容灾？

两地三中心

RPO 和 RTO

八、线上数据库操作规范

1. 大表 DDL 操作要谨慎

2. 批量操作要分批

3. 高危操作要有回滚方案

4. 不要在业务高峰期做重量级操作

5. 变更要有审批和记录

九、面试高频题

1. MySQL 怎么做备份？

2. 如果误删了一张表，怎么恢复？

3. 什么是 RPO 和 RTO？

4. 如何防止误删数据？

5. 大表怎么做 DDL 变更？

十、核心结论总结

十一、练习题

练习 1

练习 2

练习 3

十二、整个课程回顾