第14课: 备份与恢复

mysqldump、备份策略、binlog、数据恢复

备份的重要性

数据备份是数据库管理的核心任务，可以防止数据丢失、支持灾难恢复、满足合规要求。

逻辑备份 (mysqldump)

-- 示例1: 备份单个数据库
mysqldump -u root -p mydb > mydb_backup.sql

-- 示例2: 备份多个数据库
mysqldump -u root -p --databases db1 db2 db3 > multiple_backup.sql

-- 示例3: 备份所有数据库
mysqldump -u root -p --all-databases > all_databases_backup.sql

-- 示例4: 备份单个表
mysqldump -u root -p mydb users > users_backup.sql
mysqldump -u root -p mydb users orders > tables_backup.sql

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

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

高级备份选项

-- 示例7: 压缩备份
mysqldump -u root -p mydb | gzip > mydb_backup.sql.gz

-- 示例8: 带时间戳的备份
mysqldump -u root -p mydb > mydb_backup_$(date +%Y%m%d_%H%M%S).sql

-- 示例9: 大数据库分表备份
for table in $(mysql -u root -p -e "SHOW TABLES FROM mydb" | tail -n +2); do
    mysqldump -u root -p mydb $table > mydb_${table}_backup.sql
done

-- 示例10: 完整的生产环境备份脚本
mysqldump -u backup_user -p \
    --single-transaction \      # InnoDB 一致性备份
    --routines \                # 备份存储过程和函数
    --triggers \                # 备份触发器
    --events \                  # 备份事件
    --master-data=2 \           # 记录 binlog 位置
    --flush-logs \              # 刷新日志
    --databases mydb \
    | gzip > /backup/mydb_$(date +%Y%m%d).sql.gz

数据恢复

-- 恢复数据库
mysql -u root -p mydb < mydb_backup.sql

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

-- 恢复所有数据库
mysql -u root -p < all_databases_backup.sql

-- 恢复单个表
mysql -u root -p mydb < users_backup.sql

二进制日志 (binlog)

binlog 记录所有修改数据的操作，用于主从复制和增量恢复。

-- 查看 binlog 配置
SHOW VARIABLES LIKE 'log_bin';
SHOW VARIABLES LIKE 'binlog_format';

-- 查看所有 binlog 文件
SHOW BINARY LOGS;

-- 查看当前使用的 binlog
SHOW MASTER STATUS;

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

-- 刷新 binlog（生成新文件）
FLUSH LOGS;

使用 binlog 恢复数据

-- 导出 binlog 为 SQL
mysqlbinlog mysql-bin.000001 > binlog.sql

-- 按时间范围恢复
mysqlbinlog --start-datetime="2024-01-01 00:00:00" \
            --stop-datetime="2024-01-01 23:59:59" \
            mysql-bin.000001 | mysql -u root -p

-- 按位置恢复
mysqlbinlog --start-position=1000 \
            --stop-position=2000 \
            mysql-bin.000001 | mysql -u root -p

备份策略

MySQL备份与恢复流程图

备份类型	频率	保留时间	说明
完全备份	每周	4周	备份所有数据
增量备份	每天	7天	备份变化的数据
binlog 备份	实时	7天	用于时间点恢复

完整的备份脚本

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

# 配置
DB_USER="backup_user"
DB_PASS="backup_password"
DB_NAME="mydb"
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 \
    $DB_NAME | gzip > $BACKUP_DIR/${DB_NAME}_${DATE}.sql.gz

# 检查备份是否成功
if [ $? -eq 0 ]; then
    echo "备份成功: ${DB_NAME}_${DATE}.sql.gz"

    # 删除旧备份
    find $BACKUP_DIR -name "${DB_NAME}_*.sql.gz" -mtime +$RETENTION_DAYS -delete
    echo "已删除 ${RETENTION_DAYS} 天前的备份"
else
    echo "备份失败！"
    exit 1
fi

灾难恢复流程

-- 1. 停止应用访问数据库

-- 2. 恢复最近的完全备份
mysql -u root -p mydb < /backup/mydb_20240101.sql

-- 3. 应用增量备份（binlog）
mysqlbinlog --start-datetime="2024-01-01 00:00:00" \
            --stop-datetime="2024-01-07 10:30:00" \
            /var/log/mysql/mysql-bin.* | mysql -u root -p mydb

-- 4. 验证数据完整性
SELECT COUNT(*) FROM users;
SELECT MAX(created_at) FROM orders;

-- 5. 恢复应用访问

物理备份（高级）

-- 使用 XtraBackup 进行物理备份
xtrabackup --backup --target-dir=/backup/full

-- 准备备份
xtrabackup --prepare --target-dir=/backup/full

-- 恢复备份
xtrabackup --copy-back --target-dir=/backup/full

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