数据备份与恢复

一、备份概述

1.1 备份重要性

1.1.1 为什么需要备份

数据丢失原因：

• 硬件故障
• 软件错误
• 人为误操作
• 恶意攻击
• 自然灾害

东巴文理解：

备份 = 保险
买了保险不一定用得上
但一旦出事就很重要
数据备份是数据安全的保障

1.2 备份类型

1.2.1 按备份方式分类

1.2.2 按备份内容分类

1.2.3 按服务状态分类

二、mysqldump备份

2.1 mysqldump概述

2.1.1 什么是mysqldump

mysqldump：MySQL自带的逻辑备份工具，导出SQL语句。

2.1.2 基本语法

语法格式：

mysqldump [选项] 数据库名 [表名] > 备份文件.sql
mysqldump [选项] --databases 数据库名1 [数据库名2...] > 备份文件.sql
mysqldump [选项] --all-databases > 备份文件.sql

2.2 完全备份

2.2.1 备份单个数据库

示例：

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

# 备份单个数据库的特定表
mysqldump -u root -p mydb users orders > mydb_tables_backup.sql

# 备份多个数据库
mysqldump -u root -p --databases mydb test > multi_db_backup.sql

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

2.2.2 备份选项

常用选项：

示例：

# 完整备份选项
mysqldump -u root -p \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  --master-data=2 \
  --flush-logs \
  mydb > mydb_full_backup.sql

2.3 增量备份

2.3.1 使用二进制日志

步骤：

1. 开启二进制日志
2. 完全备份
3. 定期备份二进制日志

开启二进制日志：

[mysqld]
log-bin=mysql-bin
binlog_format=ROW
server-id=1

2.3.2 备份二进制日志

示例：

# 查看二进制日志列表
mysqlbinlog --list

# 备份二进制日志
mysqlbinlog mysql-bin.000001 > binlog_backup.sql

# 备份多个二进制日志
mysqlbinlog mysql-bin.000001 mysql-bin.000002 > binlog_backup.sql

# 备份指定时间范围的日志
mysqlbinlog --start-datetime="2024-01-01 00:00:00" \
            --stop-datetime="2024-01-02 00:00:00" \
            mysql-bin.000001 > binlog_time_backup.sql

2.4 备份脚本

2.4.1 自动备份脚本

Shell脚本：

#!/bin/bash

# 备份配置
BACKUP_DIR="/backup/mysql"
MYSQL_USER="root"
MYSQL_PASSWORD="password"
DATABASES="mydb test"
DATE=$(date +%Y%m%d_%H%M%S)

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

# 备份数据库
for db in $DATABASES; do
    mysqldump -u $MYSQL_USER -p$MYSQL_PASSWORD \
        --single-transaction \
        --routines \
        --triggers \
        --events \
        $db > $BACKUP_DIR/${db}_${DATE}.sql
    
    # 压缩备份文件
    gzip $BACKUP_DIR/${db}_${DATE}.sql
done

# 删除7天前的备份
find $BACKUP_DIR -name "*.sql.gz" -mtime +7 -delete

echo "Backup completed at $(date)"

2.4.2 定时备份

Crontab配置：

# 每天凌晨2点备份
0 2 * * * /backup/mysql_backup.sh >> /backup/mysql_backup.log 2>&1

# 每周日凌晨3点备份
0 3 * * 0 /backup/mysql_backup.sh >> /backup/mysql_backup.log 2>&1

# 每月1号凌晨4点备份
0 4 1 * * /backup/mysql_backup.sh >> /backup/mysql_backup.log 2>&1

三、物理备份

3.1 冷备份

3.1.1 冷备份步骤

步骤：

1. 停止MySQL服务
2. 复制数据目录
3. 启动MySQL服务

示例：

# 停止MySQL服务
systemctl stop mysql

# 备份数据目录
cp -r /var/lib/mysql /backup/mysql_cold_backup_$(date +%Y%m%d)

# 启动MySQL服务
systemctl start mysql

3.1.2 冷备份优缺点

优点：

• 数据一致性好
• 操作简单
• 恢复速度快

缺点：

• 服务中断
• 不适合大型数据库

3.2 热备份

3.2.1 使用Percona XtraBackup

安装XtraBackup：

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

# CentOS/RHEL
yum install percona-xtrabackup-80

3.2.2 完全备份

示例：

# 完全备份
xtrabackup --backup --target-dir=/backup/full

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

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

3.2.3 增量备份

示例：

# 完全备份
xtrabackup --backup --target-dir=/backup/full

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

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

# 准备备份
xtrabackup --prepare --apply-log-only --target-dir=/backup/full
xtrabackup --prepare --apply-log-only --target-dir=/backup/full \
  --incremental-dir=/backup/inc1
xtrabackup --prepare --target-dir=/backup/full \
  --incremental-dir=/backup/inc2

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

四、数据恢复

4.1 恢复mysqldump备份

4.1.1 恢复数据库

示例：

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

# 恢复多个数据库
mysql -u root -p < multi_db_backup.sql

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

4.1.2 恢复特定表

示例：

# 从备份文件中提取特定表
sed -n '/CREATE TABLE `users`/,/UNLOCK TABLES/p' mydb_backup.sql > users.sql

# 恢复特定表
mysql -u root -p mydb < users.sql

4.2 恢复二进制日志

4.2.1 基于时间点恢复

示例：

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

# 恢复指定时间范围
mysqlbinlog --start-datetime="2024-01-01 10:00:00" \
            --stop-datetime="2024-01-01 12:00:00" \
            mysql-bin.000001 | mysql -u root -p

4.2.2 基于位置恢复

示例：

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

# 恢复到指定位置
mysqlbinlog --stop-position=1000 \
  mysql-bin.000001 | mysql -u root -p

# 恢复指定位置范围
mysqlbinlog --start-position=500 \
            --stop-position=1000 \
            mysql-bin.000001 | mysql -u root -p

4.3 恢复物理备份

4.3.1 恢复冷备份

示例：

# 停止MySQL服务
systemctl stop mysql

# 恢复数据目录
rm -rf /var/lib/mysql
cp -r /backup/mysql_cold_backup /var/lib/mysql

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

# 启动MySQL服务
systemctl start mysql

4.3.2 恢复XtraBackup备份

示例：

# 停止MySQL服务
systemctl stop mysql

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

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

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

# 启动MySQL服务
systemctl start mysql

五、备份策略

5.1 备份策略设计

5.1.1 小型数据库策略

策略：

• 每天完全备份
• 保留7天备份

实施：

#!/bin/bash
BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d)

mysqldump -u root -p --all-databases \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  | gzip > $BACKUP_DIR/all_db_${DATE}.sql.gz

find $BACKUP_DIR -name "*.sql.gz" -mtime +7 -delete

5.1.2 中型数据库策略

策略：

• 每周完全备份
• 每天增量备份
• 保留4周备份

实施：

#!/bin/bash
BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d)
DAY_OF_WEEK=$(date +%u)

if [ $DAY_OF_WEEK -eq 7 ]; then
    # 周日完全备份
    mysqldump -u root -p --all-databases \
      --single-transaction \
      --master-data=2 \
      --flush-logs \
      | gzip > $BACKUP_DIR/full_${DATE}.sql.gz
else
    # 其他天增量备份（备份二进制日志）
    mysqladmin -u root -p flush-logs
    cp /var/lib/mysql/mysql-bin.* $BACKUP_DIR/
fi

find $BACKUP_DIR -name "*.sql.gz" -mtime +28 -delete

5.1.3 大型数据库策略

策略：

• 每月完全备份
• 每周差异备份
• 每天增量备份
• 保留3个月备份

实施：

#!/bin/bash
BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d)
DAY_OF_MONTH=$(date +%d)
DAY_OF_WEEK=$(date +%u)

if [ $DAY_OF_MONTH -eq 1 ]; then
    # 每月1号完全备份
    xtrabackup --backup --target-dir=$BACKUP_DIR/full_${DATE}
elif [ $DAY_OF_WEEK -eq 7 ]; then
    # 每周日差异备份
    xtrabackup --backup --target-dir=$BACKUP_DIR/diff_${DATE} \
      --incremental-basedir=$BACKUP_DIR/full_latest
else
    # 其他天增量备份
    xtrabackup --backup --target-dir=$BACKUP_DIR/inc_${DATE} \
      --incremental-basedir=$BACKUP_DIR/inc_latest
fi

find $BACKUP_DIR -name "full_*" -mtime +90 -exec rm -rf {} \;

5.2 备份验证

5.2.1 验证备份完整性

示例：

#!/bin/bash

BACKUP_FILE="/backup/mysql/mydb_20240101.sql.gz"

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

# 检查文件大小
FILE_SIZE=$(stat -c%s $BACKUP_FILE)
if [ $FILE_SIZE -lt 1000 ]; then
    echo "Backup file too small"
    exit 1
fi

# 检查文件内容
zcat $BACKUP_FILE | grep "CREATE TABLE" > /dev/null
if [ $? -ne 0 ]; then
    echo "Backup file corrupted"
    exit 1
fi

echo "Backup file is valid"

5.2.2 测试恢复

示例：

#!/bin/bash

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

# 恢复备份到测试数据库
mysql -u root -p test_restore < /backup/mysql/mydb_backup.sql

# 验证数据
TABLE_COUNT=$(mysql -u root -p -N -e \
  "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema='test_restore'")

if [ $TABLE_COUNT -gt 0 ]; then
    echo "Restore test successful"
else
    echo "Restore test failed"
fi

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

六、实战案例

6.1 完整备份恢复流程

#!/bin/bash

# 1. 完全备份
echo "Starting full backup..."
mysqldump -u root -p --all-databases \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  --master-data=2 \
  --flush-logs \
  | gzip > /backup/mysql/full_$(date +%Y%m%d).sql.gz

# 2. 备份二进制日志
echo "Backing up binary logs..."
mysqladmin -u root -p flush-logs
cp /var/lib/mysql/mysql-bin.* /backup/mysql/binlog/

# 3. 验证备份
echo "Validating backup..."
zcat /backup/mysql/full_$(date +%Y%m%d).sql.gz | grep "CREATE TABLE" > /dev/null
if [ $? -eq 0 ]; then
    echo "Backup validation successful"
else
    echo "Backup validation failed"
    exit 1
fi

# 4. 模拟数据丢失
echo "Simulating data loss..."
mysql -u root -p -e "DROP DATABASE mydb"

# 5. 恢复完全备份
echo "Restoring full backup..."
zcat /backup/mysql/full_$(date +%Y%m%d).sql.gz | mysql -u root -p

# 6. 恢复二进制日志
echo "Restoring binary logs..."
mysqlbinlog /backup/mysql/binlog/mysql-bin.000002 | mysql -u root -p

echo "Recovery completed"

6.2 自动化备份系统

#!/bin/bash

# 配置文件
CONFIG_FILE="/etc/mysql_backup.conf"

# 读取配置
source $CONFIG_FILE

# 函数：发送通知
send_notification() {
    local subject=$1
    local message=$2
    echo "$message" | mail -s "$subject" $ADMIN_EMAIL
}

# 函数：备份单个数据库
backup_database() {
    local db=$1
    local backup_file="${BACKUP_DIR}/${db}_$(date +%Y%m%d_%H%M%S).sql.gz"
    
    mysqldump -u $MYSQL_USER -p$MYSQL_PASSWORD \
        --single-transaction \
        --routines \
        --triggers \
        --events \
        $db | gzip > $backup_file
    
    if [ $? -eq 0 ]; then
        echo "$(date): Backup of $db completed successfully"
        send_notification "Backup Success" "Backup of $db completed"
    else
        echo "$(date): Backup of $db failed"
        send_notification "Backup Failed" "Backup of $db failed"
    fi
}

# 函数：清理旧备份
cleanup_old_backups() {
    find $BACKUP_DIR -name "*.sql.gz" -mtime +$RETENTION_DAYS -delete
    echo "$(date): Old backups cleaned up"
}

# 主程序
main() {
    echo "$(date): Starting backup process"
    
    # 创建备份目录
    mkdir -p $BACKUP_DIR
    
    # 备份每个数据库
    for db in $DATABASES; do
        backup_database $db
    done
    
    # 清理旧备份
    cleanup_old_backups
    
    echo "$(date): Backup process completed"
}

# 执行主程序
main >> $LOG_FILE 2>&1

配置文件：

# MySQL配置
MYSQL_USER="root"
MYSQL_PASSWORD="password"

# 备份配置
BACKUP_DIR="/backup/mysql"
DATABASES="mydb test production"
RETENTION_DAYS=30

# 通知配置
ADMIN_EMAIL="admin@example.com"

# 日志配置
LOG_FILE="/var/log/mysql_backup.log"

七、本章小结

7.1 核心要点

✅ 备份是数据安全的保障 ✅ mysqldump是常用的逻辑备份工具 ✅ 物理备份速度更快 ✅ 增量备份节省空间和时间 ✅ 定期验证备份有效性 ✅ 制定合适的备份策略

7.2 验证清单

完成本章学习后，请确认您能够：

• 使用mysqldump进行备份
• 实施增量备份策略
• 恢复数据库备份
• 使用二进制日志恢复
• 编写自动化备份脚本
• 验证备份有效性