MySQL核心-备份与恢复

业务场景引入

某电商公司在双十一期间遭遇了以下数据危机：

• 硬件故障：主数据库服务器硬盘损坏，2小时内需要恢复服务
• 误操作：开发人员错误执行了删除语句，误删了1万条订单数据
• 数据损坏：数据库文件损坏，需要从备份恢复到故障前状态
• 安全事件：黑客攻击导致部分数据被篡改，需要恢复到安全时间点

这些场景都需要完善的备份恢复策略来保障业务连续性。

备份策略设计

备份类型分析

企业级备份策略

#!/bin/bash
# 企业级MySQL备份脚本

# 配置参数
DB_HOST="localhost"
DB_PORT="3306"
DB_USER="backup_user"
DB_PASS="backup_password"
BACKUP_DIR="/data/mysql_backup"
LOG_FILE="/var/log/mysql_backup.log"
RETENTION_DAYS=30

# 创建备份目录
BACKUP_DATE=$(date +%Y%m%d_%H%M%S)
FULL_BACKUP_DIR="${BACKUP_DIR}/full_backup_${BACKUP_DATE}"
mkdir -p ${FULL_BACKUP_DIR}

# 记录开始时间
echo "$(date '+%Y-%m-%d %H:%M:%S') - Starting backup" >> ${LOG_FILE}

# 全量备份
mysqldump \
    --host=${DB_HOST} \
    --port=${DB_PORT} \
    --user=${DB_USER} \
    --password=${DB_PASS} \
    --single-transaction \
    --routines \
    --triggers \
    --events \
    --flush-logs \
    --master-data=2 \
    --delete-master-logs \
    --all-databases \
    --compress \
    > ${FULL_BACKUP_DIR}/full_backup_${BACKUP_DATE}.sql

# 检查备份是否成功
if [ $? -eq 0 ]; then
    echo "$(date '+%Y-%m-%d %H:%M:%S') - Full backup completed successfully" >> ${LOG_FILE}
    
    # 压缩备份文件
    gzip ${FULL_BACKUP_DIR}/full_backup_${BACKUP_DATE}.sql
    
    # 备份二进制日志
    mysql --host=${DB_HOST} --port=${DB_PORT} --user=${DB_USER} --password=${DB_PASS} \
          -e "FLUSH LOGS; SHOW MASTER STATUS;" > ${FULL_BACKUP_DIR}/master_status.txt
    
    # 拷贝配置文件
    cp /etc/my.cnf ${FULL_BACKUP_DIR}/
    
    # 清理过期备份
    find ${BACKUP_DIR} -name "full_backup_*" -type d -mtime +${RETENTION_DAYS} -exec rm -rf {} \;
    
else
    echo "$(date '+%Y-%m-%d %H:%M:%S') - Full backup failed" >> ${LOG_FILE}
    exit 1
fi

逻辑备份详解

mysqldump高级用法

# 基础全库备份
mysqldump -u root -p --all-databases > full_backup.sql

# 单库备份（推荐生产环境参数）
mysqldump \
    --host=192.168.1.100 \
    --port=3306 \
    --user=backup_user \
    --password=secure_password \
    --single-transaction \      # 保证事务一致性
    --routines \               # 包含存储过程和函数
    --triggers \               # 包含触发器
    --events \                 # 包含事件
    --flush-logs \             # 刷新日志
    --master-data=2 \          # 记录binlog位置（注释形式）
    --set-gtid-purged=OFF \    # 适用于GTID环境
    --default-character-set=utf8mb4 \
    --compress \               # 启用压缩传输
    ecommerce > ecommerce_backup_$(date +%Y%m%d_%H%M%S).sql

# 表级备份
mysqldump -u root -p ecommerce orders order_items > orders_backup.sql

# 只备份表结构
mysqldump -u root -p --no-data ecommerce > schema_only.sql

# 只备份数据
mysqldump -u root -p --no-create-info ecommerce > data_only.sql

# 条件备份（备份特定数据）
mysqldump -u root -p ecommerce orders \
    --where="order_date >= '2024-01-01'" > orders_2024.sql

大数据库备份优化

# 并行备份脚本
#!/bin/bash

DATABASE="ecommerce"
BACKUP_DIR="/data/mysql_backup/$(date +%Y%m%d_%H%M%S)"
mkdir -p ${BACKUP_DIR}

# 获取表列表
TABLES=$(mysql -u backup_user -p${DB_PASS} -e "USE ${DATABASE}; SHOW TABLES;" | grep -v Tables_in)

# 并行备份大表
for table in ${TABLES}; do
    echo "Backing up table: ${table}"
    (
        mysqldump \
            -u backup_user -p${DB_PASS} \
            --single-transaction \
            --routines \
            --triggers \
            --compress \
            ${DATABASE} ${table} > ${BACKUP_DIR}/${table}.sql
        
        if [ $? -eq 0 ]; then
            echo "Table ${table} backup completed"
            gzip ${BACKUP_DIR}/${table}.sql
        else
            echo "Table ${table} backup failed"
        fi
    ) &
    
    # 限制并行进程数
    (($(jobs -r | wc -l) >= 4)) && wait
done

wait  # 等待所有备份完成
echo "All table backups completed"

增量备份策略

#!/bin/bash
# 增量备份脚本（基于binlog）

MYSQL_USER="backup_user"
MYSQL_PASS="backup_password"
BACKUP_DIR="/data/mysql_backup/incremental"
LOG_FILE="/var/log/mysql_incremental_backup.log"

# 创建增量备份目录
BACKUP_DATE=$(date +%Y%m%d_%H%M%S)
INCREMENTAL_DIR="${BACKUP_DIR}/incremental_${BACKUP_DATE}"
mkdir -p ${INCREMENTAL_DIR}

# 获取当前binlog位置
CURRENT_BINLOG=$(mysql -u${MYSQL_USER} -p${MYSQL_PASS} -e "SHOW MASTER STATUS\G" | grep File | awk '{print $2}')
CURRENT_POSITION=$(mysql -u${MYSQL_USER} -p${MYSQL_PASS} -e "SHOW MASTER STATUS\G" | grep Position | awk '{print $2}')

echo "$(date '+%Y-%m-%d %H:%M:%S') - Current binlog: ${CURRENT_BINLOG}, Position: ${CURRENT_POSITION}" >> ${LOG_FILE}

# 读取上次备份的位置
LAST_BACKUP_INFO="${BACKUP_DIR}/last_backup_info.txt"
if [ -f ${LAST_BACKUP_INFO} ]; then
    LAST_BINLOG=$(cat ${LAST_BACKUP_INFO} | grep "File:" | awk '{print $2}')
    LAST_POSITION=$(cat ${LAST_BACKUP_INFO} | grep "Position:" | awk '{print $2}')
else
    echo "No previous backup info found, please run full backup first"
    exit 1
fi

# 备份增量binlog
if [ "${CURRENT_BINLOG}" != "${LAST_BINLOG}" ] || [ "${CURRENT_POSITION}" -gt "${LAST_POSITION}" ]; then
    mysqlbinlog \
        --start-position=${LAST_POSITION} \
        --stop-position=${CURRENT_POSITION} \
        /var/lib/mysql/${LAST_BINLOG} > ${INCREMENTAL_DIR}/incremental_${BACKUP_DATE}.sql
    
    # 如果binlog文件已切换，还需要备份新文件
    if [ "${CURRENT_BINLOG}" != "${LAST_BINLOG}" ]; then
        mysqlbinlog \
            --stop-position=${CURRENT_POSITION} \
            /var/lib/mysql/${CURRENT_BINLOG} >> ${INCREMENTAL_DIR}/incremental_${BACKUP_DATE}.sql
    fi
    
    # 更新备份位置信息
    echo "File: ${CURRENT_BINLOG}" > ${LAST_BACKUP_INFO}
    echo "Position: ${CURRENT_POSITION}" >> ${LAST_BACKUP_INFO}
    echo "Backup_Date: ${BACKUP_DATE}" >> ${LAST_BACKUP_INFO}
    
    echo "$(date '+%Y-%m-%d %H:%M:%S') - Incremental backup completed" >> ${LOG_FILE}
else
    echo "$(date '+%Y-%m-%d %H:%M:%S') - No changes since last backup" >> ${LOG_FILE}
fi

物理备份方案

Percona XtraBackup

# 安装Percona XtraBackup
# CentOS/RHEL
yum install percona-xtrabackup-80

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

# 全量物理备份
xtrabackup \
    --user=backup_user \
    --password=backup_password \
    --host=localhost \
    --port=3306 \
    --backup \
    --target-dir=/data/mysql_backup/xtrabackup_$(date +%Y%m%d_%H%M%S) \
    --datadir=/var/lib/mysql \
    --compress \
    --compress-threads=4 \
    --parallel=4

# 增量备份
INCREMENTAL_DIR="/data/mysql_backup/incremental_$(date +%Y%m%d_%H%M%S)"
xtrabackup \
    --user=backup_user \
    --password=backup_password \
    --backup \
    --target-dir=${INCREMENTAL_DIR} \
    --incremental-basedir=/data/mysql_backup/last_full_backup \
    --datadir=/var/lib/mysql

# 备份准备（必须步骤）
xtrabackup --prepare --target-dir=/data/mysql_backup/full_backup_20240301

# 增量备份准备
xtrabackup --prepare --apply-log-only --target-dir=/data/mysql_backup/full_backup_20240301
xtrabackup --prepare --target-dir=/data/mysql_backup/full_backup_20240301 \
           --incremental-dir=/data/mysql_backup/incremental_20240302

文件系统级备份

#!/bin/bash
# 基于LVM快照的物理备份

MYSQL_DATA_DIR="/var/lib/mysql"
LVM_VOLUME="/dev/vg0/mysql_lv"
SNAPSHOT_NAME="mysql_snapshot"
MOUNT_POINT="/mnt/mysql_snapshot"
BACKUP_DIR="/data/mysql_backup/lvm_backup_$(date +%Y%m%d_%H%M%S)"

# 刷新表并加读锁
mysql -u root -p -e "FLUSH TABLES WITH READ LOCK; SYSTEM sleep 30;" &
MYSQL_PID=$!

# 等待锁生效
sleep 5

# 创建LVM快照
lvcreate -L 10G -s -n ${SNAPSHOT_NAME} ${LVM_VOLUME}

# 释放MySQL锁
kill ${MYSQL_PID}

# 挂载快照
mkdir -p ${MOUNT_POINT}
mount /dev/vg0/${SNAPSHOT_NAME} ${MOUNT_POINT}

# 备份数据
mkdir -p ${BACKUP_DIR}
rsync -av ${MOUNT_POINT}/ ${BACKUP_DIR}/

# 清理快照
umount ${MOUNT_POINT}
lvremove -f /dev/vg0/${SNAPSHOT_NAME}

echo "LVM snapshot backup completed: ${BACKUP_DIR}"

数据恢复实战

逻辑备份恢复

# 完整恢复
mysql -u root -p < full_backup.sql

# 指定数据库恢复
mysql -u root -p ecommerce < ecommerce_backup.sql

# 恢复特定表
mysql -u root -p ecommerce < orders_backup.sql

# 跳过错误继续恢复
mysql -u root -p --force < backup_with_errors.sql

点对点时间恢复

-- 场景：需要恢复到2024-03-01 14:30:00的状态

-- 1. 首先从最近的全量备份恢复
mysql -u root -p < full_backup_20240301_080000.sql

-- 2. 应用增量binlog到指定时间点
mysqlbinlog \
    --start-datetime="2024-03-01 08:00:00" \
    --stop-datetime="2024-03-01 14:30:00" \
    /var/lib/mysql/mysql-bin.000050 \
    /var/lib/mysql/mysql-bin.000051 | mysql -u root -p

-- 3. 验证恢复结果
SELECT COUNT(*) FROM orders WHERE order_date <= '2024-03-01 14:30:00';

误删数据恢复

#!/bin/bash
# 误删数据恢复脚本

# 场景：误删了orders表中order_date >= '2024-03-01'的数据
# 需要恢复到删除操作之前的状态

RECOVER_DB="ecommerce"
RECOVER_TABLE="orders"
DELETE_TIME="2024-03-01 15:45:30"  # 误删操作时间
BACKUP_DB="ecommerce_recover"

# 1. 创建恢复数据库
mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS ${BACKUP_DB};"

# 2. 从最近备份恢复到临时数据库
mysql -u root -p ${BACKUP_DB} < full_backup_20240301_080000.sql

# 3. 应用binlog到误删之前
mysqlbinlog \
    --start-datetime="2024-03-01 08:00:00" \
    --stop-datetime="${DELETE_TIME}" \
    /var/lib/mysql/mysql-bin.000052 | mysql -u root -p ${BACKUP_DB}

# 4. 提取被删除的数据
mysql -u root -p -e "
    SELECT * FROM ${BACKUP_DB}.${RECOVER_TABLE} 
    WHERE order_date >= '2024-03-01'
    INTO OUTFILE '/tmp/recovered_orders.csv'
    FIELDS TERMINATED BY ',' 
    ENCLOSED BY '\"' 
    LINES TERMINATED BY '\n';
"

# 5. 恢复数据到原表
mysql -u root -p -e "
    LOAD DATA INFILE '/tmp/recovered_orders.csv'
    INTO TABLE ${RECOVER_DB}.${RECOVER_TABLE}
    FIELDS TERMINATED BY ',' 
    ENCLOSED BY '\"' 
    LINES TERMINATED BY '\n';
"

echo "Data recovery completed. Please verify the results."

物理备份恢复

# XtraBackup恢复流程

# 1. 停止MySQL服务
systemctl stop mysql

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

# 3. 恢复数据
xtrabackup --copy-back --target-dir=/data/mysql_backup/full_backup_prepared

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

# 5. 启动MySQL服务
systemctl start mysql

# 6. 验证恢复
mysql -u root -p -e "SELECT NOW(); SHOW DATABASES;"

备份监控与自动化

备份监控脚本

#!/bin/bash
# 备份状态监控脚本

BACKUP_DIR="/data/mysql_backup"
LOG_FILE="/var/log/backup_monitor.log"
ALERT_EMAIL="admin@company.com"

# 检查今日备份
TODAY=$(date +%Y%m%d)
BACKUP_COUNT=$(find ${BACKUP_DIR} -name "*${TODAY}*" -type f | wc -l)

if [ ${BACKUP_COUNT} -eq 0 ]; then
    echo "$(date '+%Y-%m-%d %H:%M:%S') - ERROR: No backup found for today" >> ${LOG_FILE}
    echo "MySQL backup missing for $(date '+%Y-%m-%d')" | mail -s "Backup Alert" ${ALERT_EMAIL}
else
    echo "$(date '+%Y-%m-%d %H:%M:%S') - INFO: Found ${BACKUP_COUNT} backup(s) for today" >> ${LOG_FILE}
fi

# 检查备份文件完整性
LATEST_BACKUP=$(find ${BACKUP_DIR} -name "*.sql.gz" -type f -mtime -1 | head -1)
if [ -n "${LATEST_BACKUP}" ]; then
    gzip -t ${LATEST_BACKUP}
    if [ $? -eq 0 ]; then
        echo "$(date '+%Y-%m-%d %H:%M:%S') - INFO: Latest backup file integrity OK" >> ${LOG_FILE}
    else
        echo "$(date '+%Y-%m-%d %H:%M:%S') - ERROR: Latest backup file corrupted" >> ${LOG_FILE}
        echo "Backup file corrupted: ${LATEST_BACKUP}" | mail -s "Backup Corruption Alert" ${ALERT_EMAIL}
    fi
fi

# 检查磁盘空间
BACKUP_DISK_USAGE=$(df ${BACKUP_DIR} | tail -1 | awk '{print $5}' | sed 's/%//')
if [ ${BACKUP_DISK_USAGE} -gt 80 ]; then
    echo "$(date '+%Y-%m-%d %H:%M:%S') - WARNING: Backup disk usage is ${BACKUP_DISK_USAGE}%" >> ${LOG_FILE}
    echo "Backup disk usage high: ${BACKUP_DISK_USAGE}%" | mail -s "Disk Space Alert" ${ALERT_EMAIL}
fi

自动化备份恢复测试

#!/bin/bash
# 自动化备份恢复测试脚本

TEST_DB="test_restore_$(date +%Y%m%d_%H%M%S)"
BACKUP_FILE="/data/mysql_backup/latest/full_backup.sql.gz"
LOG_FILE="/var/log/backup_test.log"

echo "$(date '+%Y-%m-%d %H:%M:%S') - Starting backup restore test" >> ${LOG_FILE}

# 1. 创建测试数据库
mysql -u root -p -e "CREATE DATABASE ${TEST_DB};"

# 2. 恢复备份到测试数据库
zcat ${BACKUP_FILE} | mysql -u root -p ${TEST_DB}

if [ $? -eq 0 ]; then
    echo "$(date '+%Y-%m-%d %H:%M:%S') - Backup restore test successful" >> ${LOG_FILE}
    
    # 3. 执行数据完整性检查
    TABLE_COUNT=$(mysql -u root -p -e "SELECT COUNT(*) as count FROM information_schema.tables WHERE table_schema='${TEST_DB}';" | tail -1)
    echo "$(date '+%Y-%m-%d %H:%M:%S') - Restored ${TABLE_COUNT} tables" >> ${LOG_FILE}
    
    # 4. 清理测试数据库
    mysql -u root -p -e "DROP DATABASE ${TEST_DB};"
    
else
    echo "$(date '+%Y-%m-%d %H:%M:%S') - Backup restore test failed" >> ${LOG_FILE}
    echo "Backup restore test failed for $(date '+%Y-%m-%d')" | mail -s "Backup Test Alert" admin@company.com
fi

Cron定时任务配置

# 编辑crontab
crontab -e

# 添加备份任务
# 每天凌晨2点执行全量备份
0 2 * * * /usr/local/bin/mysql_full_backup.sh >> /var/log/mysql_backup.log 2>&1

# 每4小时执行增量备份
0 */4 * * * /usr/local/bin/mysql_incremental_backup.sh >> /var/log/mysql_incremental.log 2>&1

# 每天上午9点执行备份监控检查
0 9 * * * /usr/local/bin/backup_monitor.sh

# 每周日凌晨执行备份恢复测试
0 3 * * 0 /usr/local/bin/backup_restore_test.sh

# 每月1号清理过期备份
0 1 1 * * find /data/mysql_backup -name "*.sql.gz" -mtime +30 -delete

云备份方案

AWS S3备份

#!/bin/bash
# MySQL数据备份到AWS S3

AWS_BUCKET="company-mysql-backup"
AWS_REGION="us-west-2"
LOCAL_BACKUP_DIR="/data/mysql_backup"
S3_PREFIX="mysql-backup/$(date +%Y/%m/%d)"

# 执行本地备份
/usr/local/bin/mysql_full_backup.sh

# 上传到S3
LATEST_BACKUP=$(find ${LOCAL_BACKUP_DIR} -name "*.sql.gz" -mtime -1 | head -1)
if [ -n "${LATEST_BACKUP}" ]; then
    aws s3 cp ${LATEST_BACKUP} s3://${AWS_BUCKET}/${S3_PREFIX}/
    
    # 设置生命周期（30天后删除）
    aws s3api put-object-tagging \
        --bucket ${AWS_BUCKET} \
        --key ${S3_PREFIX}/$(basename ${LATEST_BACKUP}) \
        --tagging 'TagSet=[{Key=Lifecycle,Value=30days}]'
fi

总结与最佳实践

备份策略建议

1. 3-2-1备份原则

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

2. 备份频率规划

   • 全量备份：每日或每周
   • 增量备份：每小时或每4小时
   • 二进制日志：实时备份

3. 恢复测试

   • 定期进行恢复演练
   • 自动化备份完整性检查
   • 记录恢复时间目标（RTO）

常见问题解决

-- 备份过程中的锁等待问题
SET SESSION lock_wait_timeout = 300;
SET SESSION innodb_lock_wait_timeout = 300;

-- 大表备份优化
-- 使用--single-transaction确保一致性
-- 使用--quick避免内存不足
-- 使用--compress减少网络传输

-- 恢复过程优化
SET unique_checks = 0;
SET foreign_key_checks = 0;
-- 执行恢复操作
SET unique_checks = 1;
SET foreign_key_checks = 1;

完善的备份恢复策略是数据库运维的核心，需要根据业务需求制定合适的备份频率、保留策略和恢复流程。