书籍 / MySQL 性能优化完全指南
第 22 章

备份与恢复

MySQL 备份与恢复完全指南

备份和恢复是关键的运维能力。本指南涵盖备份策略、工具、恢复程序和RPO/RTO规划。

1. 备份类型


逻辑备份（SQL导出）：

mysqldump:
├─ 导出为SQL语句
├─ CREATE TABLE、INSERT语句
├─ 可读、便携
└─ 大数据库慢（I/O密集）

优点：
├─ 易于理解
├─ 版本独立
├─ 可恢复特定表
└─ 可版本控制

缺点：
├─ 大数据库极慢
├─ 阻塞性（锁定表）
├─ 文件大
└─ 恢复慢

物理备份（二进制文件）：

Percona XtraBackup:
├─ 直接复制InnoDB数据文件
├─ 非阻塞（后台进程）
├─ 非常快
└─ 二进制格式

优点：
├─ 极快
├─ 非阻塞
├─ 文件小
└─ 恢复快

缺点：
├─ 版本特定
├─ 需要xtrabackup工具
└─ 可移植性低

全量备份 vs 增量备份：

全量备份:
├─ 复制整个数据库
├─ 大小：全部数据
└─ 恢复快（单个备份）

增量备份:
├─ 仅复制上次备份后更改的块
├─ 小（仅更改）
└─ 恢复慢（需要多个备份）

最佳策略：
每周全量 + 每日增量：
└─ 100GB + (6 × ~5GB) = 130GB/周
└─ 每月全量 + 每周增量 + 每日日志 = 最大灵活性（支持PITR）

2. 工具对比


mysqldump（内置）：
├─ 费用：免费
├─ 速度：慢（分钟到小时）
├─ 阻塞性：是（表锁）
├─ 可移植性：高
└─ 适用：小数据库、逻辑备份

Percona XtraBackup（开源）：
├─ 费用：免费
├─ 速度：快（分钟）
├─ 阻塞性：否（后台）
├─ 可移植性：中（二进制）
└─ 适用：生产、大数据库

MySQL Enterprise Backup（商业）：
├─ 费用：$$$（MySQL Enterprise套餐）
├─ 速度：非常快
├─ 阻塞性：否
└─ 适用：高可用性企业

AWS RDS（云）：
├─ 费用：含在RDS中
├─ 速度：自动
├─ 阻塞性：否（自动）
└─ 适用：云环境

推荐：
├─ 开发：mysqldump
├─ 小型生产：mysqldump + cron
├─ 大型生产：Percona XtraBackup
└─ 云：原生备份（RDS等）

3. 恢复程序


完全恢复（XtraBackup）：

场景：服务器崩溃，数据损坏

1. 停止 MySQL
   systemctl stop mysql

2. 备份损坏数据
   mv /var/lib/mysql /var/lib/mysql.backup

3. 准备备份
   xtrabackup --prepare --target-dir=/backup/base/

4. 复制备份到MySQL目录
   xtrabackup --copy-back --target-dir=/backup/base/

5. 修复权限
   chown -R mysql:mysql /var/lib/mysql

6. 启动 MySQL
   systemctl start mysql

7. 验证数据
   SELECT COUNT(*) FROM tables...;

恢复时间：2-7 分钟

时间点恢复（PITR）：

场景：有人执行了无WHERE的DELETE

1. 从备份恢复
   xtrabackup --copy-back ...

2. 查找DELETE事件时间戳
   mysqlbinlog /var/log/mysql/binlog.000001 | grep -A5 "DELETE"

3. 创建临时数据库
   CREATE DATABASE recovery_db;

4. 恢复表到临时库（到指定时间点）
   mysqlbinlog /var/log/mysql/binlog.000001 \
     --stop-datetime='2024-04-24 15:00:00' | \
     mysql -u root -p recovery_db

5. 从临时库复制表
   INSERT INTO mydb.mytable
   SELECT * FROM recovery_db.mytable;

6. 验证数据
   SELECT COUNT(*) FROM mytable;

关键：
├─ 保留二进制日志：binlog_expire_logs_days = 30
├─ 启用二进制日志：log_bin = ON
└─ 定期测试恢复

4. 备份策略规划


高可用（RPO < 15分钟，RTO < 1分钟）：
├─ 多主同步复制
├─ 每小时备份
└─ 自动故障转移

标准生产（RPO < 1小时，RTO < 15分钟）：
├─ 日全量备份（XtraBackup）
├─ 每小时增量备份
└─ 保留30天二进制日志

成本优化（RPO < 1天，RTO < 1小时）：
├─ 每日mysqldump（凌晨2点）
├─ 周全量+日增量
└─ 便宜存储（S3 Glacier）

存储估算（数据库500GB）：
全量备份：150GB（3:1压缩比）
每周策略：150GB + (6 × 5GB) = 180GB
保留3个月：约26TB

5. 测试恢复


永远不要依赖未经测试的备份！

每月恢复测试脚本：

#!/bin/bash
# 测试备份恢复

# 1. 恢复到测试服务器
xtrabackup --copy-back \
  --target-dir=/backup/latest \
  --datadir=/data/test/

# 2. 启动MySQL
systemctl start mysql@test

# 3. 运行验证查询
mysql -u root < /test/validation.sql

# 4. 与生产比较
mysqldump -u root prod_db > /tmp/prod.sql
mysqldump -u root test_db > /tmp/test.sql
diff /tmp/prod.sql /tmp/test.sql

# 5. 检查数据完整性
# - 行数是否匹配？
# - 校验和是否匹配？
# - 外键是否有效？

告警：
├─ 恢复失败 → 严重
├─ 恢复时间 > 预期RTO → 警告
├─ 数据不匹配 → 严重
└─ 缺少备份 → 严重

6. 最佳实践

总结

备份和恢复不是可选的。根据RPO/RTO需求规划备份策略，定期测试，监控备份作业成功。未测试过的备份不是备份。

本章评分
4.5  / 5  (8 评分)

💬 留言讨论