MySQL备份与恢复策略
MySQL 数据库
MySQL
发布日期:   2024-07-28
作者:   小乐
文章字数:   2.2k
阅读时长:   9 分
阅读次数:  

MySQL备份与恢复策略

MySQL 是业务系统中最常见的数据库，但用好它并不容易。索引设计、SQL 优化、事务处理都是日常开发中需要关注的点。本文从实际场景出发，讲常见问题和解决思路。

一、备份类型

#

1.1 按备份方式

类型 说明 工具
逻辑备份 导出SQL语句 mysqldump, mydumper
物理备份 复制数据文件 XtraBackup, mysqlbackup

#

1.2 按备份范围

类型 说明 适用场景
全量备份 备份所有数据 基础备份
增量备份 备份变化的数据 减少备份时间和空间
差异备份 备份自上次全备以来的变化 平衡全备和增备

#

1.3 按备份状态

类型 说明 特点
热备 不停止服务 对业务影响小，需要工具支持
温备 只读备份 影响写入
冷备 停止服务 最简单，影响最大

二、mysqldump逻辑备份

#

2.1 基本用法

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

# 备份多个数据库
mysqldump -u root -p --databases db1 db2 > databases.sql

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

# 备份指定表
mysqldump -u root -p mydb table1 table2 > tables.sql

#

2.2 常用参数

mysqldump -u root -p \
    --single-transaction \    # 开启事务，保证一致性（InnoDB）
    --master-data=2 \        # 记录binlog位置（用于主从复制）
    --triggers \             # 导出触发器
    --routines \             # 导出存储过程和函数
    --events \               # 导出定时事件
    --databases mydb > mydb.sql

#

2.3 压缩备份

# 直接压缩
mysqldump -u root -p mydb | gzip > mydb.sql.gz

# 解压恢复
gunzip < mydb.sql.gz | mysql -u root -p mydb

#

2.4 恢复

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

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

# 大文件恢复（显示进度）
pv mydb.sql | mysql -u root -p mydb

三、XtraBackup物理备份

#

3.1 为什么选择XtraBackup

特性 mysqldump XtraBackup
备份速度 慢（逐行导出） 快（文件复制）
恢复速度 慢（逐行插入） 快（文件复制）
对业务影响 大（锁表或长事务） 小（热备份）
文件大小 大（文本SQL） 小（数据文件）
使用场景 小库、部分表 大库、全库

#

3.2 安装

# CentOS/RHEL
yum install percona-xtrabackup-80

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

#

3.3 全量备份

# 全量备份
xtrabackup --backup \
    --target-dir=/backup/full/$(date +%Y%m%d) \
    --user=root --password=password

# 备份过程输出：
# 1. 复制InnoDB数据文件（不锁表）
# 2. 执行LOCK TABLES FOR BACKUP（短暂锁）
# 3. 复制非InnoDB文件
# 4. 获取binlog位置
# 5. 释放锁

#

3.4 准备备份（Prepare）

# 准备备份（应用redo log，使备份一致）
xtrabackup --prepare --target-dir=/backup/full/20240728

# 可多次准备，不会破坏备份

#

3.5 增量备份

# 基于全备的增量备份
xtrabackup --backup \
    --target-dir=/backup/inc1 \
    --incremental-basedir=/backup/full/20240728 \
    --user=root --password=password

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

#

3.6 恢复

# 1. 停止MySQL
systemctl stop mysqld

# 2. 清理数据目录
mv /var/lib/mysql /var/lib/mysql_bak
mkdir /var/lib/mysql

# 3. 恢复全备
xtrabackup --copy-back --target-dir=/backup/full/20240728

# 4. 应用增量备份（如果有）
xtrabackup --prepare \
    --target-dir=/backup/full/20240728 \
    --incremental-dir=/backup/inc1

xtrabackup --prepare \
    --target-dir=/backup/full/20240728 \
    --incremental-dir=/backup/inc2

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

# 6. 启动MySQL
systemctl start mysqld

四、备份策略设计

#

4.1 3-2-1备份原则

  • 3份数据副本
  • 2种不同存储介质
  • 1份异地备份

#

4.2 备份周期设计

备份类型 频率 保留时间
全量备份 每周一次 1个月
增量备份 每天一次 1周
binlog备份 实时 7-14天

#

4.3 自动化备份脚本

#!/bin/bash
# backup.sh

BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d)
WEEK_DAY=$(date +%u)
MYSQL_USER="backup"
MYSQL_PASS="password"

# 周日全量，其他增量
if [ "$WEEK_DAY" -eq 7 ]; then
    # 全量备份
    BACKUP_TYPE="full"
    TARGET_DIR="$BACKUP_DIR/full/$DATE"
    mkdir -p $TARGET_DIR
    
    xtrabackup --backup \
        --target-dir=$TARGET_DIR \
        --user=$MYSQL_USER --password=$MYSQL_PASS \
        2>$TARGET_DIR/backup.log
    
    # 记录全备位置
    echo $TARGET_DIR > $BACKUP_DIR/last_full.txt
else
    # 增量备份
    BACKUP_TYPE="incremental"
    TARGET_DIR="$BACKUP_DIR/inc/$DATE"
    mkdir -p $TARGET_DIR
    
    FULL_DIR=$(cat $BACKUP_DIR/last_full.txt)
    
    # 确定增量基准
    if [ "$WEEK_DAY" -eq 1 ]; then
        BASE_DIR=$FULL_DIR
    else
        BASE_DIR=$(ls -td $BACKUP_DIR/inc/*/ | head -1)
    fi
    
    xtrabackup --backup \
        --target-dir=$TARGET_DIR \
        --incremental-basedir=$BASE_DIR \
        --user=$MYSQL_USER --password=$MYSQL_PASS \
        2>$TARGET_DIR/backup.log
fi

# 压缩备份
tar czf $TARGET_DIR.tar.gz -C $(dirname $TARGET_DIR) $(basename $TARGET_DIR)
rm -rf $TARGET_DIR

# 上传OSS
aliyun oss cp $TARGET_DIR.tar.gz oss://mybucket/mysql-backup/

# 清理旧备份
find $BACKUP_DIR -name "*.tar.gz" -mtime +30 -delete

echo "备份完成: $TARGET_DIR.tar.gz"

五、基于binlog的时点恢复

#

5.1 为什么需要binlog恢复

全量备份只能恢复到备份时刻。binlog记录了备份后的所有变更，可以实现任意时间点的恢复。

#

5.2 备份binlog

# 实时备份binlog到远程服务器
mysqlbinlog --read-from-remote-server \
    --host=master --user=repl --password=password \
    --raw --stop-never mysql-bin.000001 &

#

5.3 恢复流程

# 场景：误删表，需要恢复到删除前

# 1. 找到删除操作的位置
mysqlbinlog --start-datetime='2024-07-28 10:00:00' \
    --stop-datetime='2024-07-28 12:00:00' \
    /var/lib/mysql/mysql-bin.000005 | grep -i "DROP TABLE"

# 输出：
# at 1234567
# DROP TABLE `important_table`

# 2. 恢复全备
xtrabackup --copy-back --target-dir=/backup/full/20240728

# 3. 应用binlog到删除前
mysqlbinlog --start-position=4 \
    --stop-position=1234567 \
    /var/lib/mysql/mysql-bin.000005 | mysql -u root -p

# 4. 应用后续binlog（跳过删除操作）
mysqlbinlog --start-position=1234580 \
    /var/lib/mysql/mysql-bin.000005 | mysql -u root -p

#

5.4 基于时间恢复

# 恢复到指定时间点
mysqlbinlog --start-datetime='2024-07-28 00:00:00' \
    --stop-datetime='2024-07-28 10:30:00' \
    mysql-bin.000001 mysql-bin.000002 | mysql -u root -p

六、mydumper多线程备份

#

6.1 安装

# 编译安装
git clone https://github.com/mydumper/mydumper.git
cd mydumper
cmake .
make && make install

#

6.2 备份

# 多线程备份（比mysqldump快数倍）
mydumper -u root -p password \
    -B mydb \
    -o /backup/mydumper/$(date +%Y%m%d) \
    -t 4 \              # 4个线程
    -c \                # 压缩
    -G \                # 导出触发器、存储过程等
    -E \                # 导出事件
    -R                  # 导出路由

#

6.3 恢复

# 多线程恢复
myloader -u root -p password \
    -d /backup/mydumper/20240728 \
    -t 4 \              # 4个线程
    -B mydb             # 恢复到指定数据库

七、备份验证

#

7.1 定期恢复演练

# 自动化恢复验证脚本
#!/bin/bash

FULL_BACKUP="/backup/full/latest"
TEST_DIR="/var/lib/mysql_test"

# 1. 准备备份
xtrabackup --prepare --target-dir=$FULL_BACKUP

# 2. 恢复到测试目录
rm -rf $TEST_DIR
mkdir $TEST_DIR
xtrabackup --copy-back --target-dir=$FULL_BACKUP --datadir=$TEST_DIR

# 3. 启动测试MySQL
mysqld --datadir=$TEST_DIR --port=3307 --socket=/tmp/mysql_test.sock &

# 4. 验证数据完整性
mysql -S /tmp/mysql_test.sock -e "CHECKSUM TABLE mydb.important_table"

# 5. 关闭测试实例
mysqladmin -S /tmp/mysql_test.sock shutdown

echo "备份验证完成"

#

7.2 监控备份状态

# 检查备份文件大小
BACKUP_SIZE=$(du -sm /backup/full/latest | awk '{print $1}')
if [ $BACKUP_SIZE -lt 100 ]; then
    echo "备份文件异常小，可能备份失败" | mail -s "备份告警" dba@company.com
fi

# 检查备份日志
if grep -q "completed OK" /backup/full/latest/backup.log; then
    echo "备份成功"
else
    echo "备份失败" | mail -s "备份告警" dba@company.com
fi

八、云数据库备份

#

8.1 阿里云RDS

  • 自动全量备份（每天）
  • 自动增量备份（binlog，5分钟）
  • 支持恢复到任意时间点
  • 支持跨地域备份

#

8.2 腾讯云CDB

  • 自动备份策略可配置
  • 支持物理备份和逻辑备份
  • 支持备份下载

九、灾难恢复计划

#

9.1 RPO和RTO

指标 说明 目标
RPO 恢复点目标，可接受的数据丢失量 < 5分钟
RTO 恢复时间目标，业务恢复所需时间 < 30分钟

#

9.2 恢复流程

灾难发生
    │
    ├── 评估损失范围
    │       ├── 数据损坏 → 从备份恢复
    │       └── 硬件故障 → 切换到备库
    │
    ├── 确定恢复时间点
    │       └── 根据RPO选择binlog恢复点
    │
    ├── 执行恢复
    │       ├── 恢复全备
    │       ├── 应用增量备
    │       └── 应用binlog
    │
    ├── 验证数据完整性
    │       └── 关键表校验
    │
    └── 切换业务流量

十、总结

备份方式 适用场景 优点 缺点
mysqldump 小库、部分表、迁移 简单、可读 慢、大
XtraBackup 大库、生产环境 快、热备 需要安装
mydumper 大库并行备份 多线程快 需安装
binlog 时点恢复 精确恢复 依赖全备
备份策略 频率 保留
全量备份 每周 1月
增量备份 每天 1周
binlog 实时 2周

备份的核心原则：

  1. 定期备份，自动化执行
  2. 备份加密，安全存储
  3. 异地容灾，防止单点
  4. 定期演练，验证可用
  5. 监控告警，及时发现问题

核心要点

  1. 索引设计的原则：最左前缀原则、避免索引列参与计算、选择合适的索引类型

  2. SQL 优化的方法：使用 EXPLAIN 分析执行计划、避免 SELECT *、优化 JOIN

  3. 事务隔离级别的选择：根据业务需求选择合适的级别

  4. 常见的锁问题：行锁、表锁、死锁的处理

总结

MySQL 优化是一个持续的过程，需要结合业务特点和数据量来调整。在实际项目中，定期分析慢查询日志、优化索引、调整配置参数，都能提升数据库性能。

赏

   转载规则


《MySQL备份与恢复策略》 由 小乐 采用 知识共享署名 4.0 国际许可协议 进行许可。
  目录