MySQL 备份与恢复

数据备份是数据库管理中最关键的任务之一。本章将详细介绍 MySQL 的备份策略和恢复方法。

备份概述

备份类型

MySQL 支持多种备份类型，各有优缺点：

备份类型	说明	优点	缺点
逻辑备份	导出 SQL 语句	可读性强、跨版本兼容	速度慢、恢复时间长
物理备份	复制数据文件	速度快、恢复快	不可读、版本敏感
全量备份	备份所有数据	完整性强	占用空间大
增量备份	只备份变化数据	节省空间	恢复复杂
热备份	在线备份	不影响业务	需要特定工具
冷备份	停机备份	简单可靠	影响业务

备份策略选择

┌─────────────────────────────────────────────────────────────┐
│                    备份策略决策树                            │
├─────────────────────────────────────────────────────────────┤
│                                                            │
│  数据量 < 10GB？                                            │
│      ├── 是 → 逻辑备份（mysqldump）                         │
│      └── 否 → 数据量 < 100GB？                              │
│                  ├── 是 → 物理备份或逻辑备份均可             │
│                  └── 否 → 物理备份（MySQL Enterprise Backup）│
│                                                            │
│  需要增量备份？                                              │
│      ├── 是 → 开启二进制日志 + 物理备份                      │
│      └── 否 → 全量备份即可                                  │
│                                                            │
│  允许停机？                                                  │
│      ├── 是 → 冷备份（简单可靠）                            │
│      └── 否 → 热备份（需要专业工具）                        │
│                                                            │
└─────────────────────────────────────────────────────────────┘

mysqldump 逻辑备份

mysqldump 是 MySQL 自带的逻辑备份工具，适合中小型数据库。

基本用法

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

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

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

# 备份单个表
mysqldump -u root -p mydb users > users_backup.sql

# 备份多个表
mysqldump -u root -p mydb users orders > tables_backup.sql

常用参数

# 完整备份参数说明
mysqldump -u root -p \
  --single-transaction \      # InnoDB 一致性备份（推荐）
  --routines \                # 包含存储过程和函数
  --triggers \                # 包含触发器
  --events \                  # 包含事件
  --master-data=2 \           # 记录 binlog 位置
  --flush-logs \              # 刷新日志
  --hex-blob \                # 十六进制导出 BLOB
  mydb > mydb_full_backup.sql

重要参数详解：

参数	说明
--single-transaction	使用事务保证一致性，适合 InnoDB
--lock-tables	锁定所有表，适合 MyISAM
--lock-all-tables	全局锁定，保证所有数据库一致性
--master-data[=value]	记录 binlog 位置，用于主从复制
--flush-logs	刷新日志，开始新的 binlog
--routines	备份存储过程和函数
--triggers	备份触发器
--events	备份事件
--hex-blob	以十六进制导出二进制数据
--no-data	只备份表结构
--no-create-info	只备份数据

只备份结构

# 只备份表结构（不含数据）
mysqldump -u root -p --no-data mydb > mydb_schema.sql

# 只备份存储过程和函数
mysqldump -u root -p --routines --no-create-info --no-data mydb > mydb_routines.sql

只备份数据

# 只备份数据（不含表结构）
mysqldump -u root -p --no-create-info mydb > mydb_data.sql

# 使用 INSERT IGNORE 避免重复键错误
mysqldump -u root -p --insert-ignore mydb > mydb_data.sql

远程备份

# 从远程服务器备份
mysqldump -h 192.168.1.100 -P 3306 -u root -p mydb > remote_backup.sql

# 通过 SSH 隧道备份
ssh user@remote "mysqldump -u root -p mydb" > mydb_backup.sql

# 直接通过网络传输并压缩
mysqldump -h remote_host -u root -p mydb | gzip > mydb_backup.sql.gz

压缩备份

# 使用 gzip 压缩
mysqldump -u root -p mydb | gzip > mydb_backup.sql.gz

# 使用 bzip2 压缩（压缩率更高但更慢）
mysqldump -u root -p mydb | bzip2 > mydb_backup.sql.bz2

# 使用 xz 压缩（压缩率最高）
mysqldump -u root -p mydb | xz > mydb_backup.sql.xz

恢复数据

使用 mysql 恢复

# 从备份文件恢复
mysql -u root -p mydb < mydb_backup.sql

# 恢复压缩备份
gunzip < mydb_backup.sql.gz | mysql -u root -p mydb

# 恢复到指定数据库
mysql -u root -p -D mydb < mydb_backup.sql

使用 source 命令

-- 在 MySQL 命令行中恢复
mysql> USE mydb;
mysql> SOURCE /path/to/backup.sql;

恢复注意事项

-- 1. 恢复前先关闭外键检查（加快速度）
SET FOREIGN_KEY_CHECKS = 0;
SET UNIQUE_CHECKS = 0;
SET AUTOCOMMIT = 0;

-- 执行恢复
SOURCE /path/to/backup.sql;

-- 恢复后重新启用
SET FOREIGN_KEY_CHECKS = 1;
SET UNIQUE_CHECKS = 1;
COMMIT;

二进制日志备份与恢复

二进制日志（binlog）记录了所有数据修改操作，可用于增量备份和点恢复。

启用二进制日志

[mysqld]
# 启用二进制日志
log-bin = mysql-bin

# 日志格式（ROW 推荐）
binlog_format = ROW

# 日志过期时间（天）
expire_logs_days = 7

# 单个日志文件大小
max_binlog_size = 100M

# 服务器 ID（用于复制）
server-id = 1

查看二进制日志

-- 查看二进制日志列表
SHOW BINARY LOGS;

-- 查看当前日志信息
SHOW MASTER STATUS;

-- 查看日志内容
SHOW BINLOG EVENTS IN 'mysql-bin.000001';

-- 查看指定位置的日志
SHOW BINLOG EVENTS IN 'mysql-bin.000001' FROM 100 LIMIT 10;

使用 mysqlbinlog 工具

# 查看日志内容
mysqlbinlog mysql-bin.000001

# 查看指定时间范围的日志
mysqlbinlog --start-datetime="2024-01-01 00:00:00" \
            --stop-datetime="2024-01-02 00:00:00" \
            mysql-bin.000001

# 查看指定位置的日志
mysqlbinlog --start-position=100 --stop-position=500 mysql-bin.000001

# 恢复到指定时间点
mysqlbinlog --stop-datetime="2024-01-01 12:00:00" mysql-bin.000001 | mysql -u root -p

# 恢复指定数据库的日志
mysqlbinlog --database=mydb mysql-bin.000001 | mysql -u root -p

点恢复示例

# 场景：今天 14:00 误删了数据，需要恢复到 14:00 之前

# 1. 先恢复全量备份
mysql -u root -p mydb < mydb_backup_20240101.sql

# 2. 应用二进制日志到误操作前
mysqlbinlog --start-datetime="2024-01-01 00:00:00" \
            --stop-datetime="2024-01-01 14:00:00" \
            mysql-bin.000002 mysql-bin.000003 | mysql -u root -p

# 3. 数据恢复完成

备份二进制日志

# 手动刷新日志（开始新的 binlog）
mysqladmin -u root -p flush-logs

# 复制旧的 binlog 到备份位置
cp /var/lib/mysql/mysql-bin.000001 /backup/binlog/

# 定期备份脚本
#!/bin/bash
# 备份 binlog 脚本
BACKUP_DIR="/backup/binlog"
MYSQL_DATA="/var/lib/mysql"

# 刷新日志
mysqladmin -u root -p'password' flush-logs

# 复制除了最后一个之外的所有日志
cd $MYSQL_DATA
for f in $(ls mysql-bin.[0-9]* | head -n -1); do
    cp $f $BACKUP_DIR/
done

物理备份

物理备份直接复制数据文件，速度更快。

冷备份

# 1. 停止 MySQL 服务
sudo systemctl stop mysql

# 2. 复制数据目录
cp -r /var/lib/mysql /backup/mysql_cold_backup

# 3. 启动 MySQL 服务
sudo systemctl start mysql

# 恢复：
# 1. 停止 MySQL
# 2. 删除或移动当前数据目录
# 3. 复制备份到数据目录
# 4. 修改权限
# 5. 启动 MySQL

使用 Percona XtraBackup

Percona XtraBackup 是开源的热备份工具，支持 InnoDB 在线备份：

# 安装 Percona XtraBackup
sudo apt-get install percona-xtrabackup-80

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

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

# 恢复数据
# 1. 停止 MySQL
sudo systemctl stop mysql

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

# 3. 恢复
xtrabackup --copy-back --target-dir=/backup/full

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

# 5. 启动 MySQL
sudo systemctl start mysql

增量备份（XtraBackup）

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

# 第一次增量备份
xtrabackup --backup --target-dir=/backup/inc1 \
  --incremental-basedir=/backup/full --user=root --password

# 第二次增量备份
xtrabackup --backup --target-dir=/backup/inc2 \
  --incremental-basedir=/backup/inc1 --user=root --password

# 恢复增量备份
# 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

备份策略实践

定期全量备份脚本

#!/bin/bash
# MySQL 每日全量备份脚本

# 配置
DB_USER="root"
DB_PASS="password"
BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
RETENTION_DAYS=7

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

# 执行备份
mysqldump -u$DB_USER -p$DB_PASS \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  --master-data=2 \
  --flush-logs \
  --all-databases | gzip > $BACKUP_DIR/all_db_$DATE.sql.gz

# 检查备份是否成功
if [ $? -eq 0 ]; then
    echo "[$(date)] Backup completed: all_db_$DATE.sql.gz" >> $BACKUP_DIR/backup.log
else
    echo "[$(date)] Backup failed!" >> $BACKUP_DIR/backup.log
    exit 1
fi

# 删除旧备份
find $BACKUP_DIR -name "*.sql.gz" -mtime +$RETENTION_DAYS -delete
echo "[$(date)] Old backups cleaned" >> $BACKUP_DIR/backup.log

定时任务配置

# 编辑 crontab
crontab -e

# 每天凌晨 2 点执行全量备份
0 2 * * * /scripts/mysql_backup.sh

# 每小时备份 binlog
0 * * * * /scripts/mysql_binlog_backup.sh

备份验证

#!/bin/bash
# 验证备份完整性

BACKUP_FILE=$1

# 检查文件是否存在
if [ ! -f "$BACKUP_FILE" ]; then
    echo "Error: Backup file not found"
    exit 1
fi

# 检查是否可以解压
if [[ $BACKUP_FILE == *.gz ]]; then
    gunzip -t $BACKUP_FILE
    if [ $? -ne 0 ]; then
        echo "Error: Corrupted gzip file"
        exit 1
    fi
fi

# 检查 SQL 文件结构
zcat $BACKUP_FILE | grep -q "CREATE TABLE"
if [ $? -eq 0 ]; then
    echo "Backup validation passed"
else
    echo "Warning: No CREATE TABLE statements found"
fi

恢复测试

定期进行恢复测试是确保备份有效性的关键：

#!/bin/bash
# 恢复测试脚本

# 创建测试数据库
mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS test_restore"

# 恢复备份
mysql -u root -p test_restore < /backup/mysql/all_db_latest.sql

# 验证数据完整性
mysql -u root -p test_restore -e "
    SELECT 
        COUNT(*) AS table_count 
    FROM information_schema.tables 
    WHERE table_schema = 'test_restore'
"

# 清理测试数据库
mysql -u root -p -e "DROP DATABASE test_restore"

echo "Restore test completed"

小结

本章我们学习了：

1. 备份类型：逻辑备份、物理备份、增量备份
2. mysqldump：逻辑备份工具的使用
3. 二进制日志：增量备份和点恢复
4. 物理备份：冷备份和 Percona XtraBackup
5. 备份策略：定期备份和脚本自动化
6. 恢复测试：确保备份有效性

练习

1. 使用 mysqldump 备份一个数据库并验证备份文件
2. 配置二进制日志并进行点恢复测试
3. 编写自动备份脚本并设置定时任务
4. 进行一次完整的备份恢复测试

参考资源

• MySQL 官方文档 - 备份与恢复
• MySQL 官方文档 - mysqldump
• Percona XtraBackup 文档