MySQL教程 / 第 160 节

第16章：备份策略与实践

数据备份是DBA最重要的工作之一，没有备份就没有安全感

16.1 备份的重要性 ⭐⭐⭐⭐⭐

16.1.1 为什么需要备份？

常见的数据丢失场景：

❌ 硬件故障（磁盘损坏、服务器宕机）
❌ 人为误操作（误删数据、DROP TABLE）
❌ 软件Bug（应用程序错误）
❌ 恶意攻击（SQL注入、勒索病毒）
❌ 自然灾害（火灾、地震、水灾）

备份的作用：

✅ 数据恢复（最重要）
✅ 数据迁移
✅ 搭建从库
✅ 数据分析
✅ 合规要求

血泪教训：

没有备份 = 没有工作
没有测试的备份 = 没有备份

16.2 备份类型

16.2.1 逻辑备份 vs 物理备份

对比项	逻辑备份	物理备份
备份内容	SQL语句	数据文件
备份工具	mysqldump、mysqlpump	XtraBackup、LVM快照
备份速度	慢	快
恢复速度	慢	快
跨平台	✅ 支持	❌ 不支持
跨版本	✅ 支持	⚠️ 有限制
占用空间	小（可压缩）	大
适用场景	小数据库、跨平台	大数据库、快速恢复

16.2.2 全量备份 vs 增量备份

全量备份（Full Backup）：

备份所有数据
恢复简单
占用空间大
备份时间长

增量备份（Incremental Backup）：

只备份变化的数据
节省空间
恢复复杂（需要全量+所有增量）
备份时间短

差异备份（Differential Backup）：

备份自上次全量备份后的所有变化
介于全量和增量之间
恢复需要全量+最后一次差异

16.2.3 热备份 vs 冷备份

热备份（Hot Backup）：

数据库在线备份
不影响业务
推荐使用

温备份（Warm Backup）：

备份时只能读，不能写
影响部分业务

冷备份（Cold Backup）：

停止数据库备份
影响业务
不推荐

16.3 mysqldump逻辑备份 ⭐⭐⭐⭐⭐

16.3.1 mysqldump基本用法

# 备份单个数据库
mysqldump -u root -p database_name > backup.sql

# 备份多个数据库
mysqldump -u root -p --databases db1 db2 db3 > backup.sql

# 备份所有数据库
mysqldump -u root -p --all-databases > all_backup.sql

# 备份单个表
mysqldump -u root -p database_name table_name > table_backup.sql

# 备份多个表
mysqldump -u root -p database_name table1 table2 > tables_backup.sql

16.3.2 mysqldump重要参数

# 完整的备份命令（推荐）
mysqldump -u root -p \
  --single-transaction \      # InnoDB一致性备份（不锁表）
  --master-data=2 \           # 记录binlog位置（注释形式）
  --flush-logs \              # 刷新binlog
  --routines \                # 备份存储过程和函数
  --triggers \                # 备份触发器
  --events \                  # 备份事件
  --hex-blob \                # 二进制数据使用十六进制
  --default-character-set=utf8mb4 \  # 字符集
  database_name > backup.sql

# 参数说明：
# --single-transaction：
#   - 适用于InnoDB
#   - 保证备份的一致性
#   - 不锁表，不影响业务
#   - ⭐ 强烈推荐

# --master-data=2：
#   - 记录备份时的binlog位置
#   - =1：直接写入CHANGE MASTER语句
#   - =2：以注释形式写入（推荐）
#   - 用于搭建主从复制

# --flush-logs：
#   - 备份前刷新binlog
#   - 生成新的binlog文件
#   - 便于增量备份

# --lock-all-tables：
#   - 锁定所有表（MyISAM需要）
#   - 会影响业务
#   - InnoDB不需要

# --quick：
#   - 快速备份
#   - 不缓冲查询结果
#   - 大表必须使用

# --compress：
#   - 压缩传输数据
#   - 减少网络传输

16.3.3 mysqldump备份示例

# 示例1：备份单个数据库（InnoDB）
mysqldump -u root -p \
  --single-transaction \
  --master-data=2 \
  --flush-logs \
  --routines \
  --triggers \
  mydb > mydb_backup_$(date +%Y%m%d).sql

# 示例2：备份所有数据库
mysqldump -u root -p \
  --all-databases \
  --single-transaction \
  --master-data=2 \
  --flush-logs \
  --routines \
  --triggers \
  --events > all_backup_$(date +%Y%m%d).sql

# 示例3：只备份表结构
mysqldump -u root -p \
  --no-data \
  database_name > schema_only.sql

# 示例4：只备份数据
mysqldump -u root -p \
  --no-create-info \
  database_name > data_only.sql

# 示例5：备份并压缩
mysqldump -u root -p \
  --single-transaction \
  database_name | gzip > backup.sql.gz

# 示例6：远程备份
mysqldump -h remote_host -u root -p \
  --single-transaction \
  database_name > remote_backup.sql

16.3.4 mysqldump恢复

# 恢复数据库
mysql -u root -p database_name < backup.sql

# 恢复所有数据库
mysql -u root -p < all_backup.sql

# 恢复压缩的备份
gunzip < backup.sql.gz | mysql -u root -p database_name

# 恢复时显示进度
pv backup.sql | mysql -u root -p database_name

# 恢复前创建数据库
mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS mydb"
mysql -u root -p mydb < backup.sql

16.3.5 mysqldump的优缺点

优点：

✅ 简单易用
✅ 跨平台、跨版本
✅ 可以查看和编辑备份文件
✅ 可以选择性恢复
✅ 适合小数据库

缺点：

❌ 备份速度慢
❌ 恢复速度慢
❌ 占用CPU和内存
❌ 不适合大数据库（>100GB）

16.4 mysqlpump逻辑备份

16.4.1 mysqlpump概述

# mysqlpump是MySQL 5.7新增的备份工具
# 相比mysqldump的改进：
# ✅ 支持并行备份（多线程）
# ✅ 备份速度更快
# ✅ 支持备份用户
# ✅ 支持压缩

# 基本用法
mysqlpump -u root -p database_name > backup.sql

# 并行备份（4个线程）
mysqlpump -u root -p \
  --default-parallelism=4 \
  database_name > backup.sql

# 压缩备份
mysqlpump -u root -p \
  --compress-output=LZ4 \
  database_name > backup.sql.lz4

16.4.2 mysqlpump vs mysqldump

特性	mysqldump	mysqlpump
并行备份	❌	✅
备份速度	慢	快
压缩	需要外部工具	✅ 内置
备份用户	❌	✅
成熟度	高	一般
推荐度	⭐⭐⭐⭐⭐	⭐⭐⭐

16.5 XtraBackup物理备份 ⭐⭐⭐⭐⭐

16.5.1 XtraBackup概述

XtraBackup特点：

✅ Percona开发的开源工具
✅ 物理备份（直接复制数据文件）
✅ 热备份（不锁表）
✅ 支持增量备份
✅ 备份速度快
✅ 恢复速度快
✅ 适合大数据库

版本选择：

MySQL 5.7 → XtraBackup 2.4
MySQL 8.0 → XtraBackup 8.0

16.5.2 XtraBackup安装

# CentOS/RHEL安装
yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
yum install percona-xtrabackup-24

# Ubuntu/Debian安装
wget https://repo.percona.com/apt/percona-release_latest.generic_all.deb
dpkg -i percona-release_latest.generic_all.deb
apt-get update
apt-get install percona-xtrabackup-24

# 验证安装
xtrabackup --version

16.5.3 XtraBackup全量备份

# 全量备份
xtrabackup --backup \
  --user=root \
  --password=your_password \
  --target-dir=/backup/full_backup

# 参数说明：
# --backup：执行备份
# --target-dir：备份目录
# --user：MySQL用户
# --password：MySQL密码

# 备份完成后的目录结构：
/backup/full_backup/
├── ibdata1              # 系统表空间
├── mysql/               # mysql系统库
├── mydb/                # 业务数据库
│   ├── table1.ibd
│   └── table2.ibd
├── xtrabackup_checkpoints  # 检查点信息
├── xtrabackup_info         # 备份信息
└── xtrabackup_logfile      # 事务日志

16.5.4 XtraBackup增量备份

# 第一次全量备份
xtrabackup --backup \
  --user=root \
  --password=your_password \
  --target-dir=/backup/full

# 第一次增量备份（基于全量）
xtrabackup --backup \
  --user=root \
  --password=your_password \
  --target-dir=/backup/inc1 \
  --incremental-basedir=/backup/full

# 第二次增量备份（基于第一次增量）
xtrabackup --backup \
  --user=root \
  --password=your_password \
  --target-dir=/backup/inc2 \
  --incremental-basedir=/backup/inc1

# 增量备份原理：
# 只备份自上次备份后修改的数据页
# 通过LSN（Log Sequence Number）判断

16.5.5 XtraBackup恢复

# 恢复全量备份

# 1. 准备备份（应用事务日志）
xtrabackup --prepare \
  --target-dir=/backup/full

# 2. 停止MySQL
systemctl stop mysqld

# 3. 清空数据目录
rm -rf /var/lib/mysql/*

# 4. 恢复数据
xtrabackup --copy-back \
  --target-dir=/backup/full

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

# 6. 启动MySQL
systemctl start mysqld

# 恢复增量备份

# 1. 准备全量备份（不回滚未提交的事务）
xtrabackup --prepare --apply-log-only \
  --target-dir=/backup/full

# 2. 应用第一次增量
xtrabackup --prepare --apply-log-only \
  --target-dir=/backup/full \
  --incremental-dir=/backup/inc1

# 3. 应用第二次增量（最后一次不加--apply-log-only）
xtrabackup --prepare \
  --target-dir=/backup/full \
  --incremental-dir=/backup/inc2

# 4. 恢复数据（同全量恢复）
xtrabackup --copy-back \
  --target-dir=/backup/full

16.5.6 XtraBackup压缩备份

# 压缩备份
xtrabackup --backup \
  --user=root \
  --password=your_password \
  --compress \
  --compress-threads=4 \
  --target-dir=/backup/compressed

# 解压备份
xtrabackup --decompress \
  --target-dir=/backup/compressed

# 删除压缩文件
find /backup/compressed -name "*.qp" -delete

# 准备和恢复（同普通备份）
xtrabackup --prepare --target-dir=/backup/compressed
xtrabackup --copy-back --target-dir=/backup/compressed

16.6 备份策略设计 ⭐⭐⭐⭐⭐

16.6.1 备份策略原则

3-2-1原则：

3份数据：至少保留3份数据副本
2种介质：使用2种不同的存储介质
1份异地：至少1份存储在异地

备份频率：

全量备份：每周1次
增量备份：每天1次
binlog备份：实时或每小时

保留策略：

每日备份：保留7天
每周备份：保留4周
每月备份：保留12个月
年度备份：永久保留

16.6.2 小型数据库备份策略（<10GB）

# 策略：每天全量备份

# 备份脚本：/backup/scripts/daily_backup.sh
#!/bin/bash

BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d)
MYSQL_USER="root"
MYSQL_PASSWORD="your_password"

# 创建备份目录
mkdir -p $BACKUP_DIR/$DATE

# 全量备份
mysqldump -u$MYSQL_USER -p$MYSQL_PASSWORD \
  --all-databases \
  --single-transaction \
  --master-data=2 \
  --flush-logs \
  --routines \
  --triggers \
  --events | gzip > $BACKUP_DIR/$DATE/all_databases.sql.gz

# 删除7天前的备份
find $BACKUP_DIR -type d -mtime +7 -exec rm -rf {} \;

# 定时任务：每天凌晨2点执行
# crontab -e
# 0 2 * * * /backup/scripts/daily_backup.sh

16.6.3 中型数据库备份策略（10GB-100GB）

# 策略：每周全量 + 每天增量

# 全量备份脚本：/backup/scripts/weekly_full.sh
#!/bin/bash

BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d)

# 全量备份
xtrabackup --backup \
  --user=root \
  --password=your_password \
  --target-dir=$BACKUP_DIR/full_$DATE

# 定时任务：每周日凌晨2点执行
# 0 2 * * 0 /backup/scripts/weekly_full.sh

# 增量备份脚本：/backup/scripts/daily_inc.sh
#!/bin/bash

BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d)

# 查找最新的备份（全量或增量）
LATEST_BACKUP=$(ls -td $BACKUP_DIR/* | head -1)

# 增量备份
xtrabackup --backup \
  --user=root \
  --password=your_password \
  --target-dir=$BACKUP_DIR/inc_$DATE \
  --incremental-basedir=$LATEST_BACKUP

# 定时任务：每天凌晨3点执行（周日除外）
# 0 3 * * 1-6 /backup/scripts/daily_inc.sh

16.6.4 大型数据库备份策略（>100GB）

# 策略：XtraBackup + binlog备份

# 全量备份：每周1次
# 增量备份：每天1次
# binlog备份：每小时1次

# binlog备份脚本：/backup/scripts/binlog_backup.sh
#!/bin/bash

BACKUP_DIR="/backup/binlog"
DATE=$(date +%Y%m%d_%H%M)
MYSQL_DATADIR="/var/lib/mysql"

# 创建备份目录
mkdir -p $BACKUP_DIR/$DATE

# 刷新binlog
mysql -u root -p -e "FLUSH LOGS"

# 复制binlog（除了最新的）
cd $MYSQL_DATADIR
for file in $(ls mysql-bin.* | head -n -1); do
    cp $file $BACKUP_DIR/$DATE/
done

# 定时任务：每小时执行
# 0 * * * * /backup/scripts/binlog_backup.sh

16.7 备份验证与测试 ⭐⭐⭐⭐⭐

16.7.1 为什么要验证备份？

没有测试的备份 = 没有备份

验证内容：

✅ 备份文件完整性
✅ 备份可以成功恢复
✅ 恢复后数据正确性
✅ 恢复时间是否满足RTO

16.7.2 备份验证脚本

# 验证mysqldump备份
#!/bin/bash

BACKUP_FILE="/backup/mysql/backup.sql.gz"
TEST_DB="test_restore"

# 1. 检查备份文件是否存在
if [ ! -f "$BACKUP_FILE" ]; then
    echo "备份文件不存在"
    exit 1
fi

# 2. 检查备份文件大小
FILE_SIZE=$(stat -f%z "$BACKUP_FILE" 2>/dev/null || stat -c%s "$BACKUP_FILE")
if [ $FILE_SIZE -lt 1000 ]; then
    echo "备份文件太小，可能损坏"
    exit 1
fi

# 3. 尝试恢复到测试数据库
mysql -u root -p -e "DROP DATABASE IF EXISTS $TEST_DB"
mysql -u root -p -e "CREATE DATABASE $TEST_DB"
gunzip < $BACKUP_FILE | mysql -u root -p $TEST_DB

# 4. 检查恢复结果
TABLE_COUNT=$(mysql -u root -p -N -e "SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA='$TEST_DB'")
if [ $TABLE_COUNT -eq 0 ]; then
    echo "恢复失败，没有表"
    exit 1
fi

echo "备份验证成功，共恢复 $TABLE_COUNT 个表"

# 5. 清理测试数据库
mysql -u root -p -e "DROP DATABASE $TEST_DB"

16.7.3 定期恢复演练

# 每月进行一次完整的恢复演练

# 1. 准备测试环境
# 2. 恢复最新的全量备份
# 3. 应用增量备份
# 4. 应用binlog
# 5. 验证数据完整性
# 6. 记录恢复时间
# 7. 编写演练报告

16.8 备份最佳实践

16.8.1 备份检查清单

✅ 备份策略已制定
✅ 备份脚本已编写
✅ 定时任务已配置
✅ 备份存储已规划
✅ 备份监控已部署
✅ 恢复流程已文档化
✅ 定期验证备份
✅ 定期恢复演练
✅ 异地备份已配置
✅ 备份加密已启用

16.8.2 常见错误

# ❌ 错误1：不验证备份
# 后果：需要恢复时发现备份损坏

# ❌ 错误2：只有一份备份
# 后果：备份损坏或丢失，无法恢复

# ❌ 错误3：备份和数据在同一磁盘
# 后果：磁盘损坏，备份和数据都丢失

# ❌ 错误4：没有异地备份
# 后果：机房故障，所有数据丢失

# ❌ 错误5：不测试恢复流程
# 后果：紧急恢复时手忙脚乱

# ❌ 错误6：备份没有加密
# 后果：备份泄露，数据安全风险

16.8.3 备份监控

# 监控备份是否成功
# 监控备份文件大小
# 监控备份时间
# 监控磁盘空间

# 告警规则：
# - 备份失败立即告警
# - 备份文件大小异常告警
# - 备份时间超时告警
# - 磁盘空间不足告警

16.9 本章总结

本章学习内容：

✅ 备份的重要性
✅ 备份类型（逻辑/物理、全量/增量、热/冷）
✅ mysqldump逻辑备份 ⭐⭐⭐⭐⭐
✅ mysqlpump逻辑备份
✅ XtraBackup物理备份 ⭐⭐⭐⭐⭐
✅ 备份策略设计 ⭐⭐⭐⭐⭐
✅ 备份验证与测试 ⭐⭐⭐⭐⭐
✅ 备份最佳实践

重点掌握：

没有备份就没有安全感
mysqldump适合小数据库，XtraBackup适合大数据库
备份策略：3-2-1原则
必须定期验证和测试备份
备份和数据不能在同一磁盘
必须有异地备份

备份工具选择：

小数据库（<10GB）：mysqldump
中大型数据库（>10GB）：XtraBackup
跨平台迁移：mysqldump
快速恢复：XtraBackup

备份策略：

小型：每天全量
中型：每周全量 + 每天增量
大型：每周全量 + 每天增量 + 每小时binlog

面试重点：

备份的重要性
mysqldump和XtraBackup的区别
如何设计备份策略
如何验证备份
3-2-1备份原则

下一章预告： 数据恢复实战

练习题

说明逻辑备份和物理备份的区别
mysqldump的–single-transaction参数有什么作用？
如何使用mysqldump备份所有数据库？
XtraBackup相比mysqldump有什么优势？
如何进行增量备份？
什么是3-2-1备份原则？
如何验证备份是否可用？
为什么要定期进行恢复演练？
设计一个中型数据库的备份策略
备份文件应该存储在哪里？

继续学习： 第17章：数据恢复实战