强曰为道
与天地相似，故不违。知周乎万物，而道济天下，故不过。旁行而不流，乐天知命，故不忧.
文档目录

MySQL 完全指南 / 第 13 章：备份恢复

第 13 章：备份恢复

没有备份的数据库就像没有安全网的高空走钢丝。本章详解备份策略与恢复方法。

13.1 备份策略总览

类型工具速度锁定适用场景
逻辑备份mysqldump中等可选小中型数据库、跨版本迁移
物理备份xtrabackup快不锁表大型数据库、PITR
快照备份LVM/ZFS 快照极快短暂文件系统支持快照的环境
Binlog 备份mysqlbinlog—不锁表增量备份、PITR

13.2 mysqldump 逻辑备份

13.2.1 基本备份

# 备份单个数据库
mysqldump -uroot -p --single-transaction myapp > myapp_backup.sql

# 备份多个数据库
mysqldump -uroot -p --single-transaction --databases myapp auth > multi_db_backup.sql

# 备份所有数据库
mysqldump -uroot -p --single-transaction --all-databases > all_backup.sql

# 备份指定表
mysqldump -uroot -p --single-transaction myapp users orders > tables_backup.sql

# 只备份表结构（不含数据）
mysqldump -uroot -p --no-data myapp > schema_backup.sql

# 只备份数据（不含结构）
mysqldump -uroot -p --no-create-info myapp > data_backup.sql

13.2.2 推荐参数

mysqldump -uroot -p \
  --single-transaction \          # InnoDB 不锁表（一致性快照）
  --routines \                    # 包含存储过程和函数
  --triggers \                    # 包含触发器
  --events \                      # 包含事件调度器
  --set-gtid-purged=ON \         # GTID 环境下使用
  --hex-blob \                    # BLOB 数据用十六进制
  --default-character-set=utf8mb4 \
  --result-file=/backup/myapp_$(date +%Y%m%d_%H%M%S).sql \
  myapp
参数说明
--single-transactionInnoDB 一致性备份，不锁表
--routines备份存储过程和函数
--triggers备份触发器
--events备份事件调度器
--set-gtid-purgedGTID 环境下控制 GTID 信息
--flush-logs备份前切换 Binlog
--master-data=2记录 Binlog 位置（注释形式）
--hex-blob二进制数据用十六进制
--max-allowed-packet最大包大小

13.2.3 恢复

# 恢复数据库
mysql -uroot -p myapp < myapp_backup.sql

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

# 在 MySQL 命令行中恢复
# mysql> SOURCE /backup/myapp_backup.sql;

13.2.4 压缩备份

# 压缩备份
mysqldump -uroot -p --single-transaction myapp | gzip > myapp_$(date +%Y%m%d).sql.gz

# 压缩恢复
gunzip < myapp_20260510.sql.gz | mysql -uroot -p myapp

13.3 XtraBackup 物理备份

13.3.1 安装

# Ubuntu/Debian
sudo apt install percona-xtrabackup-80

# CentOS/RHEL
sudo yum install percona-xtrabackup-80

13.3.2 全量备份

# 全量备份
xtrabackup --backup \
  --target-dir=/backup/full \
  --user=root --password='MyPassword'

# 准备备份（应用 Redo Log）
xtrabackup --prepare --target-dir=/backup/full

# 恢复
systemctl stop mysql
rm -rf /var/lib/mysql/*
xtrabackup --copy-back --target-dir=/backup/full --datadir=/var/lib/mysql
chown -R mysql:mysql /var/lib/mysql
systemctl start mysql

13.3.3 增量备份

# 第 1 步：全量备份
xtrabackup --backup --target-dir=/backup/full --user=root --password='MyPassword'

# 第 2 步：增量备份（基于全量）
xtrabackup --backup \
  --target-dir=/backup/inc1 \
  --incremental-basedir=/backup/full \
  --user=root --password='MyPassword'

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

# 恢复增量备份
# 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. 应用第二次增量
xtrabackup --prepare --target-dir=/backup/full --incremental-dir=/backup/inc2

# 4. 恢复
xtrabackup --copy-back --target-dir=/backup/full --datadir=/var/lib/mysql

13.4 Binlog 增量备份与 PITR

13.4.1 Binlog 备份

# 备份 Binlog
mysqlbinlog --read-from-remote-server --raw \
  --host=192.168.1.100 --user=root --password='MyPassword' \
  --result-file=/backup/binlog/ \
  mysql-bin.000001 mysql-bin.000002 mysql-bin.000003

# 自动备份脚本
mysqlbinlog --read-from-remote-server --raw \
  --host=192.168.1.100 --user=repl --password='ReplPass' \
  --result-file=/backup/binlog/ \
  --start-position=4 --stop-never mysql-bin.000001 &

13.4.2 时间点恢复（PITR）

# 场景：误删数据，恢复到 2026-05-10 14:30:00

# 1. 恢复最近的全量备份
mysql -uroot -p myapp < /backup/myapp_full_20260510.sql

# 2. 重放 Binlog 到指定时间点（跳过误操作）
mysqlbinlog --start-datetime="2026-05-10 00:00:00" \
            --stop-datetime="2026-05-10 14:29:59" \
            /var/lib/mysql/mysql-bin.000005 | mysql -uroot -p

# 或使用位置
mysqlbinlog --start-position=154 --stop-position=8910 \
            /var/lib/mysql/mysql-bin.000005 | mysql -uroot -p

# 3. 跳过误操作后，继续重放后续 Binlog
mysqlbinlog --start-position=8911 \
            /var/lib/mysql/mysql-bin.000005 \
            /var/lib/mysql/mysql-bin.000006 | mysql -uroot -p

13.4.3 查看 Binlog 内容

# 查看 Binlog 事件
mysqlbinlog --base64-output=DECODE-ROWS -v mysql-bin.000005 | less

# 查找误操作的位置
mysqlbinlog --base64-output=DECODE-ROWS -v mysql-bin.000005 | grep -B5 -A5 "DELETE FROM users"

13.5 备份自动化脚本

#!/bin/bash
# mysql_backup.sh - MySQL 自动备份脚本

BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
RETENTION_DAYS=7
MYSQL_USER="root"
MYSQL_PASS="MyPassword"
DATABASE="myapp"

# 创建备份目录
mkdir -p ${BACKUP_DIR}

# 全量备份
mysqldump -u${MYSQL_USER} -p${MYSQL_PASS} \
  --single-transaction --routines --triggers --events \
  --set-gtid-purged=ON \
  --result-file=${BACKUP_DIR}/${DATABASE}_${DATE}.sql \
  ${DATABASE}

# 压缩
gzip ${BACKUP_DIR}/${DATABASE}_${DATE}.sql

# 删除过期备份
find ${BACKUP_DIR} -name "*.sql.gz" -mtime +${RETENTION_DAYS} -delete

echo "Backup completed: ${BACKUP_DIR}/${DATABASE}_${DATE}.sql.gz"

13.6 备份验证

# 定期验证备份有效性
#!/bin/bash
# verify_backup.sh

# 1. 恢复到测试实例
mysql -h test-host -uroot -ptest_pass < /backup/latest_backup.sql

# 2. 检查表完整性
mysql -h test-host -uroot -ptest_pass -e "
SELECT TABLE_NAME, TABLE_ROWS 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'myapp' 
ORDER BY TABLE_ROWS DESC;"

# 3. 抽样查询验证数据
mysql -h test-host -uroot -ptest_pass -e "
SELECT COUNT(*) FROM myapp.users;
SELECT COUNT(*) FROM myapp.orders;"

⚠️ 备份黄金法则：

  • 3-2-1 原则：至少 3 份备份、2 种介质、1 份异地
  • 定期验证备份可恢复性
  • 恢复演练比备份更重要

业务场景

场景 1：生产环境备份策略

时间操作保留时间
每天凌晨 2:00xtrabackup 全量备份7 天
每小时Binlog 备份3 天
每周日mysqldump 逻辑备份到异地30 天

扩展阅读