MySQL备份与恢复完全指南：数据安全保障实战策略

数据备份和恢复是数据库管理的核心任务，关系到业务数据的安全性和连续性。本文将深入探讨MySQL的各种备份策略、恢复方法和最佳实践。

备份策略概述

1. 备份类型分类

# 1. 逻辑备份 vs 物理备份
# 逻辑备份：导出SQL语句
mysqldump -u root -p --single-transaction --routines --triggers database_name > backup.sql

# 物理备份：直接复制数据文件
# 需要停止MySQL服务或使用专业工具如Percona XtraBackup

# 2. 完全备份 vs 增量备份
# 完全备份：备份所有数据
mysqldump -u root -p --all-databases --single-transaction > full_backup.sql

# 增量备份：基于二进制日志
mysqlbinlog --start-datetime="2023-11-01 00:00:00" \
           --stop-datetime="2023-11-02 00:00:00" \
           mysql-bin.000001 > incremental_backup.sql

# 3. 热备份 vs 冷备份
# 热备份：数据库运行时备份
mysqldump -u root -p --single-transaction --master-data=2 database_name > hot_backup.sql

# 冷备份：停止数据库服务后备份
systemctl stop mysql
cp -r /var/lib/mysql /backup/mysql_cold_backup
systemctl start mysql

2. 备份策略设计

-- 查看数据库大小，制定备份策略
SELECT 
    table_schema AS '数据库',
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS '大小(MB)',
    COUNT(*) AS '表数量'
FROM information_schema.tables 
WHERE table_schema NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys')
GROUP BY table_schema
ORDER BY SUM(data_length + index_length) DESC;

-- 查看表的更新频率，确定备份频率
SELECT 
    table_schema,
    table_name,
    update_time,
    TIMESTAMPDIFF(HOUR, update_time, NOW()) AS hours_since_update
FROM information_schema.tables 
WHERE table_schema = 'your_database'
    AND update_time IS NOT NULL
ORDER BY update_time DESC;

-- 启用二进制日志（用于增量备份）
-- 在 my.cnf 中配置：
-- log-bin = mysql-bin
-- server-id = 1
-- binlog_format = ROW
-- expire_logs_days = 7

-- 查看二进制日志状态
SHOW MASTER STATUS;
SHOW BINARY LOGS;

mysqldump 详细使用

1. 基础备份命令

#!/bin/bash
# MySQL 备份脚本

# 配置变量
MYSQL_USER="backup_user"
MYSQL_PASS="backup_password"
MYSQL_HOST="localhost"
BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d_%H%M%S)

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

# 1. 单个数据库备份
mysqldump -h$MYSQL_HOST -u$MYSQL_USER -p$MYSQL_PASS \
    --single-transaction \
    --routines \
    --triggers \
    --events \
    --master-data=2 \
    --flush-logs \
    database_name > $BACKUP_DIR/database_name_$DATE.sql

# 2. 多个数据库备份
mysqldump -h$MYSQL_HOST -u$MYSQL_USER -p$MYSQL_PASS \
    --single-transaction \
    --routines \
    --triggers \
    --events \
    --master-data=2 \
    --databases db1 db2 db3 > $BACKUP_DIR/multiple_db_$DATE.sql

# 3. 所有数据库备份
mysqldump -h$MYSQL_HOST -u$MYSQL_USER -p$MYSQL_PASS \
    --single-transaction \
    --routines \
    --triggers \
    --events \
    --master-data=2 \
    --all-databases > $BACKUP_DIR/all_databases_$DATE.sql

# 4. 只备份表结构
mysqldump -h$MYSQL_HOST -u$MYSQL_USER -p$MYSQL_PASS \
    --no-data \
    --routines \
    --triggers \
    --events \
    database_name > $BACKUP_DIR/schema_only_$DATE.sql

# 5. 只备份数据
mysqldump -h$MYSQL_HOST -u$MYSQL_USER -p$MYSQL_PASS \
    --no-create-info \
    --single-transaction \
    database_name > $BACKUP_DIR/data_only_$DATE.sql

# 6. 备份特定表
mysqldump -h$MYSQL_HOST -u$MYSQL_USER -p$MYSQL_PASS \
    --single-transaction \
    database_name table1 table2 > $BACKUP_DIR/specific_tables_$DATE.sql

# 7. 条件备份（备份部分数据）
mysqldump -h$MYSQL_HOST -u$MYSQL_USER -p$MYSQL_PASS \
    --single-transaction \
    --where="created_at >= '2023-01-01'" \
    database_name users > $BACKUP_DIR/conditional_backup_$DATE.sql

2. 高级备份选项

# 大数据库优化备份
mysqldump -h$MYSQL_HOST -u$MYSQL_USER -p$MYSQL_PASS \
    --single-transaction \
    --quick \
    --lock-tables=false \
    --master-data=2 \
    --flush-logs \
    --hex-blob \
    --default-character-set=utf8mb4 \
    database_name | gzip > $BACKUP_DIR/large_db_$DATE.sql.gz

# 并行备份（使用 mydumper）
mydumper -h $MYSQL_HOST -u $MYSQL_USER -p $MYSQL_PASS \
    --database database_name \
    --outputdir $BACKUP_DIR/mydumper_$DATE \
    --threads 4 \
    --compress \
    --events \
    --routines \
    --triggers

# 一致性备份（主从环境）
mysqldump -h$MYSQL_HOST -u$MYSQL_USER -p$MYSQL_PASS \
    --single-transaction \
    --master-data=2 \
    --dump-slave=2 \
    --include-master-host-port \
    database_name > $BACKUP_DIR/consistent_backup_$DATE.sql

3. 备份验证和压缩

#!/bin/bash
# 备份验证脚本

BACKUP_FILE="$1"

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

# 检查备份文件大小
FILE_SIZE=$(stat -f%z "$BACKUP_FILE" 2>/dev/null || stat -c%s "$BACKUP_FILE" 2>/dev/null)
if [ "$FILE_SIZE" -lt 1024 ]; then
    echo "警告: 备份文件过小: $FILE_SIZE bytes"
fi

# 检查SQL语法
if [[ "$BACKUP_FILE" == *.sql ]]; then
    # 检查是否包含必要的SQL语句
    if ! grep -q "CREATE TABLE\|INSERT INTO" "$BACKUP_FILE"; then
        echo "警告: 备份文件可能不完整，缺少CREATE或INSERT语句"
    fi
    
    # 检查是否有错误信息
    if grep -q "ERROR\|mysqldump: Error" "$BACKUP_FILE"; then
        echo "错误: 备份文件包含错误信息"
        grep "ERROR\|mysqldump: Error" "$BACKUP_FILE"
        exit 1
    fi
fi

# 压缩备份文件
if [[ "$BACKUP_FILE" == *.sql ]] && [[ "$BACKUP_FILE" != *.gz ]]; then
    echo "压缩备份文件..."
    gzip "$BACKUP_FILE"
    echo "压缩完成: ${BACKUP_FILE}.gz"
fi

echo "备份验证通过: $BACKUP_FILE"

物理备份方案

1. Percona XtraBackup

#!/bin/bash
# Percona XtraBackup 备份脚本

MYSQL_USER="backup_user"
MYSQL_PASS="backup_password"
BACKUP_DIR="/backup/xtrabackup"
DATE=$(date +%Y%m%d_%H%M%S)
FULL_BACKUP_DIR="$BACKUP_DIR/full_$DATE"
INCREMENTAL_BACKUP_DIR="$BACKUP_DIR/incremental_$DATE"

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

# 1. 完全备份
xtrabackup --backup \
    --user=$MYSQL_USER \
    --password=$MYSQL_PASS \
    --target-dir=$FULL_BACKUP_DIR \
    --datadir=/var/lib/mysql

# 2. 增量备份（基于上次完全备份）
LAST_FULL_BACKUP=$(ls -1t $BACKUP_DIR/full_* | head -1)
xtrabackup --backup \
    --user=$MYSQL_USER \
    --password=$MYSQL_PASS \
    --target-dir=$INCREMENTAL_BACKUP_DIR \
    --incremental-basedir=$LAST_FULL_BACKUP \
    --datadir=/var/lib/mysql

# 3. 备份准备（恢复前必须执行）
prepare_backup() {
    local backup_dir=$1
    echo "准备备份: $backup_dir"
    
    # 准备完全备份
    xtrabackup --prepare --target-dir=$backup_dir
    
    # 如果有增量备份，需要应用增量
    for inc_backup in $(ls -1t $BACKUP_DIR/incremental_* 2>/dev/null); do
        if [ -d "$inc_backup" ]; then
            echo "应用增量备份: $inc_backup"
            xtrabackup --prepare \
                --target-dir=$backup_dir \
                --incremental-dir=$inc_backup
        fi
    done
}

# 4. 压缩备份
compress_backup() {
    local backup_dir=$1
    echo "压缩备份: $backup_dir"
    tar -czf "${backup_dir}.tar.gz" -C $(dirname $backup_dir) $(basename $backup_dir)
    rm -rf $backup_dir
}

# 执行备份后处理
if [ "$1" = "full" ]; then
    echo "执行完全备份..."
    # 完全备份已在上面执行
    compress_backup $FULL_BACKUP_DIR
elif [ "$1" = "incremental" ]; then
    echo "执行增量备份..."
    # 增量备份已在上面执行
    compress_backup $INCREMENTAL_BACKUP_DIR
fi

2. MySQL Enterprise Backup

# MySQL Enterprise Backup 使用示例
# 注意：这是商业版本功能

# 完全备份
mysqlbackup --user=backup_user --password=backup_pass \
    --backup-dir=/backup/meb \
    --with-timestamp \
    backup-and-apply-log

# 增量备份
mysqlbackup --user=backup_user --password=backup_pass \
    --backup-dir=/backup/meb \
    --incremental \
    --incremental-base=history:last_backup \
    --with-timestamp \
    backup

# 压缩备份
mysqlbackup --user=backup_user --password=backup_pass \
    --backup-dir=/backup/meb \
    --compress \
    --with-timestamp \
    backup-and-apply-log

二进制日志管理

1. 二进制日志配置

-- 查看二进制日志配置
SHOW VARIABLES LIKE 'log_bin%';
SHOW VARIABLES LIKE 'binlog%';
SHOW VARIABLES LIKE 'expire_logs_days';

-- 查看二进制日志文件
SHOW BINARY LOGS;

-- 查看当前二进制日志位置
SHOW MASTER STATUS;

-- 查看二进制日志内容
-- SHOW BINLOG EVENTS IN 'mysql-bin.000001' LIMIT 10;

-- 手动切换二进制日志
FLUSH LOGS;

-- 清理旧的二进制日志
PURGE BINARY LOGS TO 'mysql-bin.000010';
PURGE BINARY LOGS BEFORE '2023-11-01 00:00:00';

2. 二进制日志备份脚本

#!/bin/bash
# 二进制日志备份脚本

MYSQL_USER="backup_user"
MYSQL_PASS="backup_password"
MYSQL_HOST="localhost"
BINLOG_DIR="/var/lib/mysql"
BACKUP_DIR="/backup/binlogs"
DATE=$(date +%Y%m%d)

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

# 获取当前二进制日志文件列表
mysql -h$MYSQL_HOST -u$MYSQL_USER -p$MYSQL_PASS -e "SHOW BINARY LOGS;" | \
awk 'NR>1 {print $1}' > /tmp/binlog_list.txt

# 备份二进制日志文件
while read binlog_file; do
    if [ -f "$BINLOG_DIR/$binlog_file" ]; then
        echo "备份二进制日志: $binlog_file"
        cp "$BINLOG_DIR/$binlog_file" "$BACKUP_DIR/$DATE/"
        
        # 验证备份
        if mysqlbinlog "$BACKUP_DIR/$DATE/$binlog_file" > /dev/null 2>&1; then
            echo "验证成功: $binlog_file"
        else
            echo "验证失败: $binlog_file"
            exit 1
        fi
    fi
done < /tmp/binlog_list.txt

# 压缩备份
cd $BACKUP_DIR
tar -czf "binlogs_$DATE.tar.gz" $DATE/
rm -rf $DATE/

# 清理临时文件
rm -f /tmp/binlog_list.txt

echo "二进制日志备份完成: binlogs_$DATE.tar.gz"

# 可选：清理旧的二进制日志（保留7天）
mysql -h$MYSQL_HOST -u$MYSQL_USER -p$MYSQL_PASS -e \
"PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 7 DAY);"

数据恢复策略

1. 完全恢复

#!/bin/bash
# 完全恢复脚本

MYSQL_USER="root"
MYSQL_PASS="root_password"
MYSQL_HOST="localhost"
BACKUP_FILE="$1"

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

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

# 解压备份文件（如果需要）
if [[ "$BACKUP_FILE" == *.gz ]]; then
    echo "解压备份文件..."
    gunzip -c "$BACKUP_FILE" > /tmp/restore.sql
    RESTORE_FILE="/tmp/restore.sql"
else
    RESTORE_FILE="$BACKUP_FILE"
fi

# 停止应用服务（可选）
echo "建议停止应用服务以避免数据不一致"
read -p "是否继续恢复? (y/N): " confirm
if [[ $confirm != [yY] ]]; then
    echo "恢复已取消"
    exit 0
fi

# 执行恢复
echo "开始恢复数据库..."
mysql -h$MYSQL_HOST -u$MYSQL_USER -p$MYSQL_PASS < "$RESTORE_FILE"

if [ $? -eq 0 ]; then
    echo "数据库恢复成功"
else
    echo "数据库恢复失败"
    exit 1
fi

# 清理临时文件
if [ "$RESTORE_FILE" = "/tmp/restore.sql" ]; then
    rm -f /tmp/restore.sql
fi

echo "恢复完成，请验证数据完整性"

2. 点时间恢复 (PITR)

#!/bin/bash
# 点时间恢复脚本

MYSQL_USER="root"
MYSQL_PASS="root_password"
MYSQL_HOST="localhost"
FULL_BACKUP="$1"
RECOVERY_TIME="$2"
BINLOG_DIR="/backup/binlogs"

if [ -z "$FULL_BACKUP" ] || [ -z "$RECOVERY_TIME" ]; then
    echo "用法: $0 <full_backup_file> <recovery_time>"
    echo "示例: $0 backup.sql '2023-11-08 10:30:00'"
    exit 1
fi

echo "执行点时间恢复到: $RECOVERY_TIME"

# 1. 恢复完全备份
echo "步骤1: 恢复完全备份..."
mysql -h$MYSQL_HOST -u$MYSQL_USER -p$MYSQL_PASS < "$FULL_BACKUP"

# 2. 从备份中获取二进制日志位置
BINLOG_INFO=$(grep "CHANGE MASTER TO" "$FULL_BACKUP" | head -1)
if [ -n "$BINLOG_INFO" ]; then
    BINLOG_FILE=$(echo "$BINLOG_INFO" | sed -n "s/.*MASTER_LOG_FILE='\([^']*\)'.*/\1/p")
    BINLOG_POS=$(echo "$BINLOG_INFO" | sed -n "s/.*MASTER_LOG_POS=\([0-9]*\).*/\1/p")
    echo "从二进制日志恢复: $BINLOG_FILE, 位置: $BINLOG_POS"
else
    echo "警告: 无法从备份中获取二进制日志信息"
    echo "请手动指定起始二进制日志文件和位置"
    exit 1
fi

# 3. 应用二进制日志到指定时间点
echo "步骤2: 应用二进制日志到 $RECOVERY_TIME..."

# 查找需要应用的二进制日志文件
BINLOG_FILES=$(ls $BINLOG_DIR/mysql-bin.* | sort)

for binlog_file in $BINLOG_FILES; do
    binlog_name=$(basename $binlog_file)
    
    # 检查是否是起始文件之后的文件
    if [[ "$binlog_name" > "$BINLOG_FILE" ]] || [[ "$binlog_name" == "$BINLOG_FILE" ]]; then
        echo "处理二进制日志: $binlog_name"
        
        if [[ "$binlog_name" == "$BINLOG_FILE" ]]; then
            # 起始文件，从指定位置开始
            mysqlbinlog --start-position=$BINLOG_POS \
                       --stop-datetime="$RECOVERY_TIME" \
                       "$binlog_file" | \
            mysql -h$MYSQL_HOST -u$MYSQL_USER -p$MYSQL_PASS
        else
            # 后续文件，从头开始到指定时间
            mysqlbinlog --stop-datetime="$RECOVERY_TIME" \
                       "$binlog_file" | \
            mysql -h$MYSQL_HOST -u$MYSQL_USER -p$MYSQL_PASS
        fi
        
        # 检查是否已经超过恢复时间点
        BINLOG_END_TIME=$(mysqlbinlog --start-datetime="$RECOVERY_TIME" \
                                     --stop-datetime="$RECOVERY_TIME" \
                                     "$binlog_file" 2>/dev/null | wc -l)
        if [ $BINLOG_END_TIME -eq 0 ]; then
            echo "已到达恢复时间点，停止处理"
            break
        fi
    fi
done

echo "点时间恢复完成"
echo "请验证数据完整性和一致性"

3. 表级恢复

#!/bin/bash
# 单表恢复脚本

MYSQL_USER="root"
MYSQL_PASS="root_password"
MYSQL_HOST="localhost"
DATABASE="$1"
TABLE="$2"
BACKUP_FILE="$3"

if [ -z "$DATABASE" ] || [ -z "$TABLE" ] || [ -z "$BACKUP_FILE" ]; then
    echo "用法: $0 <database> <table> <backup_file>"
    exit 1
fi

# 创建临时数据库
TEMP_DB="temp_restore_$(date +%s)"
echo "创建临时数据库: $TEMP_DB"

mysql -h$MYSQL_HOST -u$MYSQL_USER -p$MYSQL_PASS -e "CREATE DATABASE $TEMP_DB;"

# 恢复到临时数据库
echo "恢复备份到临时数据库..."
mysql -h$MYSQL_HOST -u$MYSQL_USER -p$MYSQL_PASS $TEMP_DB < "$BACKUP_FILE"

# 检查表是否存在
TABLE_EXISTS=$(mysql -h$MYSQL_HOST -u$MYSQL_USER -p$MYSQL_PASS -e \
"SELECT COUNT(*) FROM information_schema.tables 
WHERE table_schema='$TEMP_DB' AND table_name='$TABLE';" | tail -1)

if [ "$TABLE_EXISTS" -eq 0 ]; then
    echo "错误: 表 $TABLE 在备份中不存在"
    mysql -h$MYSQL_HOST -u$MYSQL_USER -p$MYSQL_PASS -e "DROP DATABASE $TEMP_DB;"
    exit 1
fi

# 备份当前表（如果存在）
CURRENT_TABLE_EXISTS=$(mysql -h$MYSQL_HOST -u$MYSQL_USER -p$MYSQL_PASS -e \
"SELECT COUNT(*) FROM information_schema.tables 
WHERE table_schema='$DATABASE' AND table_name='$TABLE';" | tail -1)

if [ "$CURRENT_TABLE_EXISTS" -eq 1 ]; then
    echo "备份当前表..."
    mysqldump -h$MYSQL_HOST -u$MYSQL_USER -p$MYSQL_PASS \
        $DATABASE $TABLE > "/tmp/${DATABASE}_${TABLE}_backup_$(date +%s).sql"
fi

# 恢复表
echo "恢复表 $DATABASE.$TABLE..."

# 删除当前表
mysql -h$MYSQL_HOST -u$MYSQL_USER -p$MYSQL_PASS -e \
"DROP TABLE IF EXISTS $DATABASE.$TABLE;"

# 从临时数据库复制表结构和数据
mysql -h$MYSQL_HOST -u$MYSQL_USER -p$MYSQL_PASS -e \
"CREATE TABLE $DATABASE.$TABLE LIKE $TEMP_DB.$TABLE;"

mysql -h$MYSQL_HOST -u$MYSQL_USER -p$MYSQL_PASS -e \
"INSERT INTO $DATABASE.$TABLE SELECT * FROM $TEMP_DB.$TABLE;"

# 清理临时数据库
echo "清理临时数据库..."
mysql -h$MYSQL_HOST -u$MYSQL_USER -p$MYSQL_PASS -e "DROP DATABASE $TEMP_DB;"

echo "表恢复完成: $DATABASE.$TABLE"

自动化备份系统

1. 备份调度脚本

#!/bin/bash
# 自动化备份调度脚本

# 配置文件
CONFIG_FILE="/etc/mysql-backup/backup.conf"

# 默认配置
MYSQL_USER="backup_user"
MYSQL_PASS="backup_password"
MYSQL_HOST="localhost"
BACKUP_DIR="/backup/mysql"
RETENTION_DAYS=30
LOG_FILE="/var/log/mysql-backup.log"

# 加载配置文件
if [ -f "$CONFIG_FILE" ]; then
    source "$CONFIG_FILE"
fi

# 日志函数
log_message() {
    echo "$(date '+%Y-%m-%d %H:%M:%S') - $1" | tee -a "$LOG_FILE"
}

# 发送通知
send_notification() {
    local subject="$1"
    local message="$2"
    
    # 邮件通知
    if command -v mail >/dev/null 2>&1; then
        echo "$message" | mail -s "$subject" admin@example.com
    fi
    
    # Slack通知（如果配置了webhook）
    if [ -n "$SLACK_WEBHOOK" ]; then
        curl -X POST -H 'Content-type: application/json' \
            --data "{\"text\":\"$subject: $message\"}" \
            "$SLACK_WEBHOOK"
    fi
}

# 清理旧备份
cleanup_old_backups() {
    log_message "清理 $RETENTION_DAYS 天前的备份文件"
    find "$BACKUP_DIR" -name "*.sql*" -mtime +$RETENTION_DAYS -delete
    find "$BACKUP_DIR" -name "*.tar.gz" -mtime +$RETENTION_DAYS -delete
}

# 完全备份
full_backup() {
    local date_str=$(date +%Y%m%d_%H%M%S)
    local backup_file="$BACKUP_DIR/full_backup_$date_str.sql"
    
    log_message "开始完全备份"
    
    mysqldump -h$MYSQL_HOST -u$MYSQL_USER -p$MYSQL_PASS \
        --single-transaction \
        --routines \
        --triggers \
        --events \
        --master-data=2 \
        --all-databases | gzip > "${backup_file}.gz"
    
    if [ $? -eq 0 ]; then
        local file_size=$(stat -f%z "${backup_file}.gz" 2>/dev/null || stat -c%s "${backup_file}.gz" 2>/dev/null)
        log_message "完全备份成功: ${backup_file}.gz (${file_size} bytes)"
        
        # 验证备份
        if zcat "${backup_file}.gz" | head -20 | grep -q "MySQL dump"; then
            log_message "备份验证通过"
        else
            log_message "备份验证失败"
            send_notification "MySQL备份验证失败" "备份文件: ${backup_file}.gz"
        fi
    else
        log_message "完全备份失败"
        send_notification "MySQL完全备份失败" "请检查数据库连接和权限"
    fi
}

# 增量备份（基于二进制日志）
incremental_backup() {
    local date_str=$(date +%Y%m%d_%H%M%S)
    local backup_file="$BACKUP_DIR/incremental_backup_$date_str.sql"
    
    log_message "开始增量备份"
    
    # 获取上次备份的二进制日志位置
    local last_backup_info="$BACKUP_DIR/last_backup_info.txt"
    
    if [ -f "$last_backup_info" ]; then
        local last_binlog_file=$(grep "BINLOG_FILE" "$last_backup_info" | cut -d'=' -f2)
        local last_binlog_pos=$(grep "BINLOG_POS" "$last_backup_info" | cut -d'=' -f2)
        
        # 获取当前二进制日志位置
        local current_status=$(mysql -h$MYSQL_HOST -u$MYSQL_USER -p$MYSQL_PASS -e "SHOW MASTER STATUS\G")
        local current_binlog_file=$(echo "$current_status" | grep "File:" | awk '{print $2}')
        local current_binlog_pos=$(echo "$current_status" | grep "Position:" | awk '{print $2}')
        
        # 导出增量数据
        mysqlbinlog --start-position=$last_binlog_pos \
                   --stop-position=$current_binlog_pos \
                   /var/lib/mysql/$current_binlog_file > "$backup_file"
        
        if [ $? -eq 0 ]; then
            gzip "$backup_file"
            log_message "增量备份成功: ${backup_file}.gz"
            
            # 更新备份信息
            echo "BINLOG_FILE=$current_binlog_file" > "$last_backup_info"
            echo "BINLOG_POS=$current_binlog_pos" >> "$last_backup_info"
            echo "BACKUP_TIME=$(date)" >> "$last_backup_info"
        else
            log_message "增量备份失败"
            send_notification "MySQL增量备份失败" "请检查二进制日志配置"
        fi
    else
        log_message "未找到上次备份信息，执行完全备份"
        full_backup
    fi
}

# 主函数
main() {
    local backup_type="$1"
    
    # 创建备份目录
    mkdir -p "$BACKUP_DIR"
    
    log_message "开始MySQL备份任务 - 类型: $backup_type"
    
    case "$backup_type" in
        "full")
            full_backup
            ;;
        "incremental")
            incremental_backup
            ;;
        *)
            echo "用法: $0 {full|incremental}"
            echo "  full        - 执行完全备份"
            echo "  incremental - 执行增量备份"
            exit 1
            ;;
    esac
    
    # 清理旧备份
    cleanup_old_backups
    
    log_message "MySQL备份任务完成"
}

# 执行主函数
main "$1"

2. Crontab 配置

# 编辑 crontab
crontab -e

# 添加备份任务
# 每天凌晨2点执行完全备份
0 2 * * * /usr/local/bin/mysql-backup.sh full

# 每4小时执行增量备份
0 */4 * * * /usr/local/bin/mysql-backup.sh incremental

# 每周日凌晨1点清理旧备份
0 1 * * 0 find /backup/mysql -name "*.sql*" -mtime +30 -delete

# 每月1号执行备份验证
0 3 1 * * /usr/local/bin/backup-verify.sh

灾难恢复计划

1. 恢复测试流程

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

TEST_DB="backup_test_$(date +%s)"
MYSQL_USER="root"
MYSQL_PASS="root_password"
BACKUP_FILE="$1"

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

echo "=== 备份恢复测试开始 ==="

# 1. 创建测试数据库
echo "创建测试数据库: $TEST_DB"
mysql -u$MYSQL_USER -p$MYSQL_PASS -e "CREATE DATABASE $TEST_DB;"

# 2. 恢复备份到测试数据库
echo "恢复备份到测试数据库..."
if [[ "$BACKUP_FILE" == *.gz ]]; then
    zcat "$BACKUP_FILE" | sed "s/CREATE DATABASE/-- CREATE DATABASE/g" | \
    mysql -u$MYSQL_USER -p$MYSQL_PASS $TEST_DB
else
    sed "s/CREATE DATABASE/-- CREATE DATABASE/g" "$BACKUP_FILE" | \
    mysql -u$MYSQL_USER -p$MYSQL_PASS $TEST_DB
fi

# 3. 验证数据完整性
echo "验证数据完整性..."
TABLE_COUNT=$(mysql -u$MYSQL_USER -p$MYSQL_PASS -e \
"SELECT COUNT(*) FROM information_schema.tables WHERE table_schema='$TEST_DB';" | tail -1)

echo "恢复的表数量: $TABLE_COUNT"

if [ "$TABLE_COUNT" -gt 0 ]; then
    echo "✓ 备份恢复测试通过"
    
    # 显示表统计信息
    mysql -u$MYSQL_USER -p$MYSQL_PASS -e "
    SELECT 
        table_name,
        table_rows,
        ROUND((data_length + index_length) / 1024 / 1024, 2) AS size_mb
    FROM information_schema.tables 
    WHERE table_schema = '$TEST_DB'
    ORDER BY table_rows DESC
    LIMIT 10;"
else
    echo "✗ 备份恢复测试失败"
fi

# 4. 清理测试数据库
echo "清理测试数据库..."
mysql -u$MYSQL_USER -p$MYSQL_PASS -e "DROP DATABASE $TEST_DB;"

echo "=== 备份恢复测试完成 ==="

2. 灾难恢复手册

# MySQL 灾难恢复手册

## 1. 紧急响应流程

### 步骤1: 评估损坏程度
- 检查MySQL服务状态
- 检查数据文件完整性
- 评估数据丢失范围

### 步骤2: 停止服务
```bash
systemctl stop mysql
systemctl stop application_service

步骤3: 备份当前状态

1	cp -r /var/lib/mysql /var/lib/mysql.damaged

步骤4: 选择恢复策略

完全恢复：使用最新完全备份
点时间恢复：恢复到故障前某个时间点
部分恢复：只恢复特定数据库或表

2. 恢复优先级

高优先级数据库

用户数据库
订单系统数据库
财务数据库

中优先级数据库

日志数据库
统计数据库

低优先级数据库

临时数据库
测试数据库

3. 恢复时间目标 (RTO)

核心业务系统: 2小时内
一般业务系统: 4小时内
非核心系统: 24小时内

4. 恢复点目标 (RPO)

核心业务数据: 最多丢失15分钟数据
一般业务数据: 最多丢失1小时数据
非核心数据: 最多丢失24小时数据


## 最佳实践总结

### 1. 备份策略建议

```sql
-- 备份策略配置检查
SELECT 
    '二进制日志' as 配置项,
    CASE WHEN @@log_bin = 1 THEN '已启用' ELSE '未启用' END as 状态,
    CASE WHEN @@log_bin = 1 THEN '✓' ELSE '✗' END as 建议
UNION ALL
SELECT 
    '二进制日志格式',
    @@binlog_format,
    CASE WHEN @@binlog_format = 'ROW' THEN '✓' ELSE '建议使用ROW' END
UNION ALL
SELECT 
    '事务隔离级别',
    @@transaction_isolation,
    CASE WHEN @@transaction_isolation = 'REPEATABLE-READ' THEN '✓' ELSE '✓' END
UNION ALL
SELECT 
    'InnoDB刷新日志',
    CAST(@@innodb_flush_log_at_trx_commit AS CHAR),
    CASE WHEN @@innodb_flush_log_at_trx_commit = 1 THEN '✓' ELSE '建议设为1' END;

2. 监控和告警

#!/usr/bin/env python3
# MySQL备份监控脚本

import os
import time
import smtplib
from email.mime.text import MIMEText
from datetime import datetime, timedelta

class BackupMonitor:
    def __init__(self, backup_dir, alert_email):
        self.backup_dir = backup_dir
        self.alert_email = alert_email
        
    def check_backup_freshness(self, max_age_hours=25):
        """检查备份文件是否足够新"""
        latest_backup = None
        latest_time = 0
        
        for filename in os.listdir(self.backup_dir):
            if filename.endswith(('.sql', '.sql.gz')):
                filepath = os.path.join(self.backup_dir, filename)
                mtime = os.path.getmtime(filepath)
                if mtime > latest_time:
                    latest_time = mtime
                    latest_backup = filename
        
        if latest_backup:
            age_hours = (time.time() - latest_time) / 3600
            if age_hours > max_age_hours:
                self.send_alert(f"备份文件过旧: {latest_backup}, 已有 {age_hours:.1f} 小时")
                return False
            else:
                print(f"最新备份: {latest_backup}, {age_hours:.1f} 小时前")
                return True
        else:
            self.send_alert("未找到备份文件")
            return False
    
    def check_backup_size(self, min_size_mb=10):
        """检查备份文件大小"""
        for filename in os.listdir(self.backup_dir):
            if filename.endswith(('.sql', '.sql.gz')):
                filepath = os.path.join(self.backup_dir, filename)
                size_mb = os.path.getsize(filepath) / 1024 / 1024
                if size_mb < min_size_mb:
                    self.send_alert(f"备份文件过小: {filename}, 只有 {size_mb:.1f} MB")
                    return False
        return True
    
    def send_alert(self, message):
        """发送告警邮件"""
        try:
            msg = MIMEText(f"MySQL备份告警: {message}")
            msg['Subject'] = 'MySQL备份告警'
            msg['From'] = 'backup-monitor@example.com'
            msg['To'] = self.alert_email
            
            # 这里需要配置SMTP服务器
            # server = smtplib.SMTP('localhost')
            # server.send_message(msg)
            # server.quit()
            
            print(f"告警: {message}")
        except Exception as e:
            print(f"发送告警失败: {e}")
    
    def run_checks(self):
        """运行所有检查"""
        print(f"开始备份监控检查 - {datetime.now()}")
        
        freshness_ok = self.check_backup_freshness()
        size_ok = self.check_backup_size()
        
        if freshness_ok and size_ok:
            print("所有备份检查通过")
        else:
            print("备份检查发现问题")

if __name__ == "__main__":
    monitor = BackupMonitor("/backup/mysql", "admin@example.com")
    monitor.run_checks()

3. 备份最佳实践清单

备份频率
- 核心数据：每日完全备份 + 每小时增量备份
- 一般数据：每日完全备份
- 测试数据：每周备份
备份存储
- 本地存储：快速恢复
- 远程存储：灾难恢复
- 云存储：长期保存
备份验证
- 定期恢复测试
- 备份文件完整性检查
- 自动化验证脚本
安全措施
- 备份文件加密
- 访问权限控制
- 传输加密
文档管理
- 备份策略文档
- 恢复流程手册
- 联系人信息

通过实施完善的备份和恢复策略，可以确保MySQL数据库在各种故障情况下的数据安全和业务连续性。记住，备份策略的有效性需要通过定期的恢复测试来验证。