跳到主要内容

MySQL 备份与恢复

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

备份概述​

为什么需要备份？​

数据库备份是保障数据安全的重要手段，主要应对以下场景：

  • 硬件故障：磁盘损坏、服务器宕机
  • 人为错误：误删数据、错误更新
  • 软件问题：程序 bug、数据库损坏
  • 安全事件：勒索软件、恶意攻击
  • 灾难恢复：火灾、地震等自然灾害
  • 数据迁移：迁移到新服务器或新环境

备份类型​

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

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

备份策略选择​

选择备份策略时需要考虑以下因素：

mysqldump 逻辑备份​

mysqldump 是 MySQL 自带的逻辑备份工具，适合中小型数据库。它将数据库导出为 SQL 脚本文件，包含创建表和插入数据的语句。

基本用法​

# 备份单个数据库
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以十六进制导出二进制数据有 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 -u root -p --extended-insert 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

# 并行压缩（加快速度）
mysqldump -u root -p mydb | pigz > mydb_backup.sql.gz

恢复数据​

使用 mysql 命令恢复​

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

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

# 使用 zcat 直接读取压缩文件
zcat 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;

恢复优化技巧​

恢复大量数据时，可以通过以下方式提高速度：

-- 恢复前关闭外键检查（加快速度）
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;

注意：关闭这些检查虽然能加快恢复速度，但可能跳过数据完整性验证，请确保备份数据是完整可靠的。

二进制日志备份与恢复​

二进制日志（Binary Log）记录了所有数据修改操作，可用于增量备份和时间点恢复（Point-in-Time Recovery）。

启用二进制日志​

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

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

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

# 或 MySQL 8.0+ 使用
binlog_expire_logs_seconds = 604800

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

# 服务器ID（用于复制，必须唯一）
server-id = 1

二进制日志格式​

格式说明优点缺点
ROW记录行数据变化数据准确、安全日志较大
STATEMENT记录 SQL 语句日志较小某些语句不确定
MIXED混合模式兼顾两者复杂场景可能有问题

查看二进制日志​

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

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

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

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

-- 查看指定时间范围（需要解析）
SHOW BINLOG EVENTS IN 'mysql-bin.000001' 
    FROM 0 LIMIT 100;

使用 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

# 将日志输出为 SQL 文件
mysqlbinlog mysql-bin.000001 > binlog_recovery.sql

时间点恢复示例​

假设场景：今天 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

物理备份​

物理备份直接复制数据文件，速度更快，适合大型数据库。

冷备份​

冷备份需要停止 MySQL 服务，是最简单可靠的备份方式：

# 步骤 1：停止 MySQL 服务
sudo systemctl stop mysql
# 或
sudo service mysql stop

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

# 步骤 3：启动 MySQL 服务
sudo systemctl start mysql

冷备份恢复步骤：

# 1. 停止 MySQL
sudo systemctl stop mysql

# 2. 备份当前数据目录（以防万一）
mv /var/lib/mysql /var/lib/mysql.old

# 3. 复制备份到数据目录
cp -r /backup/mysql_cold_backup /var/lib/mysql

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

# 5. 启动 MySQL
sudo systemctl start mysql

使用 Percona XtraBackup​

Percona XtraBackup 是开源的热备份工具，支持 InnoDB 在线备份，不会阻塞业务。

安装 Percona XtraBackup​

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

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

# 使用 Docker
docker pull percona/percona-xtrabackup:8.0

全量备份​

# 创建全量备份
xtrabackup --backup --target-dir=/backup/full \
  --user=root --password=your_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 --backup --target-dir=/backup/full \
  --user=root --password=your_password

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

# 第二次增量备份
xtrabackup --backup --target-dir=/backup/inc2 \
  --incremental-basedir=/backup/inc1 \
  --user=root --password=your_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：应用第二次增量（最后一次不需要 --apply-log-only）
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
    # 发送告警通知
    # mail -s "MySQL Backup Failed" [email protected] < /dev/null
    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

# 每周日凌晨 3 点执行完整备份（包含所有数据库）
0 3 * * 0 /scripts/mysql_full_backup.sh

备份验证脚本​

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

BACKUP_FILE=$1

if [ -z "$BACKUP_FILE" ]; then
    echo "Usage: $0 <backup_file>"
    exit 1
fi

# 检查文件是否存在
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
    echo "Gzip integrity check: PASSED"
fi

# 检查 SQL 文件结构
if [[ $BACKUP_FILE == *.gz ]]; then
    CHECK_CMD="zcat $BACKUP_FILE"
else
    CHECK_CMD="cat $BACKUP_FILE"
fi

# 检查是否有完整的备份结构
$CHECK_CMD | grep -q "CREATE TABLE"
if [ $? -eq 0 ]; then
    echo "SQL structure check: PASSED"
else
    echo "Warning: No CREATE TABLE statements found"
fi

# 检查备份文件大小
SIZE=$($CHECK_CMD | wc -c)
echo "Backup size: $SIZE bytes"

echo "Backup validation completed"

恢复测试脚本​

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

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

BACKUP_FILE=$1
TEST_DB="test_restore_$(date +%Y%m%d)"

if [ -z "$BACKUP_FILE" ]; then
    echo "Usage: $0 <backup_file>"
    exit 1
fi

echo "Starting restore test..."

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

# 恢复备份
if [[ $BACKUP_FILE == *.gz ]]; then
    zcat $BACKUP_FILE | mysql -u root -p $TEST_DB
else
    mysql -u root -p $TEST_DB < $BACKUP_FILE
fi

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

# 检查关键表
mysql -u root -p $TEST_DB -e "SHOW TABLES"

# 清理测试数据库
read -p "Clean up test database? (y/n) " -n 1 -r
echo
if [[ $REPLY =~ ^[Yy]$ ]]; then
    mysql -u root -p -e "DROP DATABASE $TEST_DB"
    echo "Test database cleaned up"
fi

echo "Restore test completed"

备份最佳实践​

1. 备份策略原则​

  • 3-2-1 原则：至少 3 份备份，存储在 2 种不同介质上，其中 1 份异地存放
  • 定期验证：定期测试恢复流程，确保备份可用
  • 文档记录：记录备份策略、恢复步骤、联系人信息

2. 监控和告警​

# 检查备份是否完成
#!/bin/bash
BACKUP_DIR="/backup/mysql"
TODAY=$(date +%Y%m%d)

if [ ! -f "$BACKUP_DIR/all_db_$TODAY*.sql.gz" ]; then
    echo "ALERT: Daily backup not found for $TODAY"
    # 发送告警
    exit 1
fi

# 检查备份文件大小（应该大于某个阈值）
SIZE=$(stat -f%z "$BACKUP_DIR/all_db_$TODAY"*.sql.gz 2>/dev/null || stat -c%s "$BACKUP_DIR/all_db_$TODAY"*.sql.gz)
if [ $SIZE -lt 1000000 ]; then
    echo "WARNING: Backup file size is suspiciously small: $SIZE bytes"
fi

3. 安全措施​

# 加密备份文件
mysqldump -u root -p mydb | gzip | openssl enc -aes-256-cbc -salt -out mydb_backup.sql.gz.enc

# 解密并恢复
openssl enc -d -aes-256-cbc -in mydb_backup.sql.gz.enc | gunzip | mysql -u root -p mydb

# 设置备份文件权限
chmod 600 /backup/mysql/*.sql.gz
chown mysql:mysql /backup/mysql/*.sql.gz

4. 备份清单​

备份项目频率保留时间备注
全量备份每日7 天所有数据库
binlog实时7 天增量恢复
配置文件每周30 天/etc/mysql/
表结构每次变更永久仅结构

常见问题​

1. 备份速度太慢​

# 使用快速参数
mysqldump --quick --single-transaction --extended-insert mydb > backup.sql

# 使用并行压缩
mysqldump mydb | pigz > backup.sql.gz

# 使用 mydumper（多线程备份）
mydumper -u root -p password -B mydb -o /backup/mydumper/

2. 恢复时外键约束错误​

-- 恢复前禁用外键检查
SET FOREIGN_KEY_CHECKS = 0;
SOURCE backup.sql;
SET FOREIGN_KEY_CHECKS = 1;

3. 字符集问题​

# 指定字符集备份
mysqldump --default-character-set=utf8mb4 mydb > backup.sql

# 恢复时指定字符集
mysql --default-character-set=utf8mb4 mydb < backup.sql

小结​

本章我们学习了：

  1. 备份类型：逻辑备份、物理备份、全量备份、增量备份
  2. mysqldump：逻辑备份工具的详细使用方法
  3. 二进制日志：增量备份和时间点恢复
  4. 物理备份：冷备份和 Percona XtraBackup 热备份
  5. 备份策略：定期备份、脚本自动化、验证测试
  6. 最佳实践：3-2-1 原则、监控告警、安全措施

练习​

  1. 使用 mysqldump 备份一个数据库并验证备份文件完整性
  2. 配置二进制日志并进行时间点恢复测试
  3. 编写自动备份脚本并设置定时任务
  4. 进行一次完整的备份恢复测试
  5. 比较逻辑备份和物理备份的性能差异

参考资料​