MySQL备份恢复全攻略：mysqldump与xtrabackup实战（DBA救命指南）

对于DBA来说，数据库备份就像程序员的「Ctrl+S」——平时不起眼，出事了能救命，而且是最重要的救命手段，没有之一。
相信很多DBA都有过这样的惊魂时刻：开发手滑，一条没有WHERE条件的DELETE语句下去，用户表瞬间清空；服务器宕机，数据目录损坏，眼看业务停摆，领导在身后催得焦头烂额……
我至今还记得2018年的那次事故：开发误删用户全量数据，当时只有每天凌晨1次的全量备份，事故发生在下午5点，我们硬生生靠binlog恢复了4个小时，业务停摆半天，损失惨重。
从那以后，我们彻底重构了备份策略——从“裸奔式”每日全备，升级为「全量备份+增量备份+binlog归档」的黄金组合，RTO（恢复时间目标）从4小时压缩到30分钟，RPO（恢复点目标）从24小时缩减到5分钟，再也不用为数据丢失夜不能寐。
今天，就把这份沉淀了多年的MySQL备份恢复实战攻略，毫无保留分享给大家——涵盖mysqldump和xtrabackup两大核心工具，从基础用法到生产实战，从故障排查到最佳实践，新手能上手，老手能避坑。
一、先搞懂：两大备份工具，该选谁？

MySQL备份工具千千万，但生产环境最常用、最靠谱的，就两个：mysqldump 和 Percona XtraBackup。它们就像两把“双刃剑”，各有专攻，选对了能省90%的麻烦。
核心结论：小型数据库选mysqldump（简单省心），大型数据库选XtraBackup（高效低影响），跨版本迁移必用mysqldump。
1.1 mysqldump：MySQL官方“轻量选手”

mysqldump是MySQL自带的逻辑备份工具，说白了就是“把数据库里的数据，导出成SQL语句”，就像把一本书的内容逐字抄下来，可读性强，操作简单。
特性详细说明备份类型逻辑备份（导出SQL语句，肉眼可看）锁机制默认锁表，可用--single-transaction实现InnoDB一致性读（不锁表）速度较慢（要逐行查询、生成SQL），大数据量会“卡壳”适用场景小型数据库（50GB）、生产环境低影响备份优点快速、低影响、支持增量备份，恢复速度快缺点只支持同版本恢复，配置比mysqldump复杂，需要额外安装1.3 场景对应表（直接抄作业）

不用纠结，对照下面的场景选工具，准没错：
应用场景推荐工具备份策略开发测试环境mysqldump每日全备（简单省事）小型生产（500GB）XtraBackup每周全备 + 每日增量 + 流式备份跨版本迁移mysqldump逻辑导出导入（兼容性强）表级恢复mysqldump单表导出（精准恢复，不影响其他表）时间点恢复（PITR）XtraBackup + binlog全备 + binlog应用（恢复到任意时间点）1.4 环境要求（必看，避免踩坑）

环境不匹配，备份必失败！提前核对以下版本和配置：
组件版本要求关键说明MySQL Server8.0.35+ 或 8.4 LTS稳定版优先，避免使用测试版Percona XtraBackup8.0.35+版本必须与MySQL匹配（比如MySQL 8.0.35，XtraBackup也用8.0.35）操作系统Rocky 9 / Ubuntu 24.04生产环境推荐，兼容性最好磁盘空间数据量的2-3倍预留备份、压缩、恢复的空间，避免空间不足导致备份失败qpress最新版XtraBackup压缩备份必需工具二、实战操作：从准备到备份，一步到位

铺垫再多，不如动手操作。这部分是核心，每一步都有详细命令，直接复制粘贴就能用（记得替换自己的密码和目录）。
2.1 准备工作（3步搞定，避免后续踩坑）

2.1.1 安装备份工具

分两种操作系统，按需选择：
✅ Rocky Linux 9 / CentOS Stream 9

1. # 安装Percona仓库（XtraBackup需要）
2. sudo dnf install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm
3. sudo percona-release setup pxb-80
5. # 安装XtraBackup
6. sudo dnf install -y percona-xtrabackup-80
8. # 安装压缩工具（备份压缩必需）
9. sudo dnf install -y qpress lz4
11. # 验证安装（出现版本号即成功）
12. xtrabackup --version
13. # 正常输出：xtrabackup version 8.0.35-30 based on MySQL server 8.0.35

复制代码

✅ Ubuntu 24.04

1. sudo apt-get update
2. sudo apt-get install -y wget gnupg2 lsb-release
3. wget https://repo.percona.com/apt/percona-release_latest.generic_all.deb
4. sudo dpkg -i percona-release_latest.generic_all.deb
5. sudo percona-release setup pxb-80
6. sudo apt-get install -y percona-xtrabackup-80 qpress

复制代码

2.1.2 创建备份专用用户

不要用root用户备份！创建专用备份用户，分配最小权限，更安全：

1. -- 登录MySQL，执行以下SQL
2. CREATE USER 'backup'@'localhost' IDENTIFIED BY 'BackupPass@2024';  -- 密码替换成自己的
4. -- mysqldump所需权限（复制粘贴即可）
5. GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES, EVENT ON *.* TO 'backup'@'localhost';
7. -- XtraBackup所需权限（复制粘贴即可）
8. GRANT RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'backup'@'localhost';
9. GRANT BACKUP_ADMIN ON *.* TO 'backup'@'localhost';  -- MySQL 8.0+ 必需
10. GRANT SELECT ON performance_schema.log_status TO 'backup'@'localhost';
11. GRANT SELECT ON performance_schema.keyring_component_status TO 'backup'@'localhost';
13. FLUSH PRIVILEGES;  -- 刷新权限，立即生效

复制代码

2.1.3 规划备份目录

目录混乱会导致后续恢复找不到文件，建议按以下结构创建，一目了然：

1. # 创建备份目录结构（一次性执行）
2. sudo mkdir -p /backup/mysql/{full,incremental,binlog,scripts,logs}
3. sudo chown -R mysql:mysql /backup/mysql  # 授权mysql用户，避免权限不足
4. sudo chmod 750 /backup/mysql  # 限制访问权限，更安全
6. # 目录说明（记好，后续用得到）：
7. # /backup/mysql/full        - 存放全量备份
8. # /backup/mysql/incremental - 存放增量备份
9. # /backup/mysql/binlog      - 存放binlog归档
10. # /backup/mysql/scripts     - 存放备份脚本
11. # /backup/mysql/logs        - 存放备份日志
13. # 重要提醒：备份目录建议用独立磁盘或NFS挂载，避免和数据目录在同一磁盘（防止磁盘损坏一起丢）

复制代码

2.2 核心操作：mysqldump实战用法

适合小型数据库，操作简单，以下是最常用的几种场景，覆盖90%的需求：
✅ 备份单个数据库

1. mysqldump -u backup -p'BackupPass@2024' \
2.     --single-transaction \  # InnoDB不锁表，关键参数
3.     --routines \            # 备份存储过程和函数
4.     --triggers \            # 备份触发器
5.     --events \              # 备份事件调度器
6.     mydb > /backup/mysql/full/mydb_$(date +%Y%m%d).sql  # 备份到指定目录，带日期

复制代码

✅ 备份多个数据库

1. mysqldump -u backup -p'BackupPass@2024' \
2.     --single-transaction \
3.     --routines \
4.     --triggers \
5.     --databases db1 db2 db3 > /backup/mysql/full/multi_db_$(date +%Y%m%d).sql

复制代码

✅ 备份所有数据库（最常用）

1. mysqldump -u backup -p'BackupPass@2024' \
2.     --single-transaction \
3.     --routines \
4.     --triggers \
5.     --events \
6.     --all-databases > /backup/mysql/full/all_db_$(date +%Y%m%d).sql

复制代码

✅ 备份单个表（精准恢复，不影响其他表）

1. mysqldump -u backup -p'BackupPass@2024' \
2.     --single-transaction \
3.     mydb users > /backup/mysql/full/mydb_users_$(date +%Y%m%d).sql  # mydb是库名，users是表名

复制代码

✅ 生产环境推荐参数组合（带压缩，更省空间）

这是我平时用的配置，兼顾安全和效率，直接复制用：

1. mysqldump -u backup -p'BackupPass@2024' \
2.     --single-transaction \          # InnoDB一致性读，不锁表（必需）
3.     --source-data=2 \               # 记录binlog位置（注释形式，方便恢复）
4.     --routines \                    # 包含存储过程和函数
5.     --triggers \                    # 包含触发器
6.     --events \                      # 包含事件调度器
7.     --set-gtid-purged=AUTO \        # GTID自动处理（MySQL 8.0+ 推荐）
8.     --hex-blob \                    # 二进制数据用十六进制，避免乱码
9.     --quick \                       # 逐行读取，减少内存占用
10.     --max-allowed-packet=512M \     # 支持大数据包，避免导出失败
11.     --default-character-set=utf8mb4 \  # 字符集，避免乱码
12.     --all-databases \
13.     | gzip > /backup/mysql/full/all_db_$(date +%Y%m%d).sql.gz  # 压缩备份，省空间

复制代码

注意：MySQL 8.0.26+ 版本，--master-data 参数已改为 --source-data，别用错了！
2.3 核心操作：XtraBackup实战用法

适合大型数据库，热备份、速度快，重点掌握全量和增量备份（生产环境最常用）。
✅ 全量备份（基础，增量备份的前提）

1. # 基础全量备份
2. xtrabackup --backup \
3.     --user=backup \
4.     --password='BackupPass@2024' \
5.     --target-dir=/backup/mysql/full/$(date +%Y%m%d)  # 备份目录带日期，方便区分
7. # 备份并压缩（推荐，省空间）
8. xtrabackup --backup \
9.     --user=backup \
10.     --password='BackupPass@2024' \
11.     --target-dir=/backup/mysql/full/$(date +%Y%m%d) \
12.     --compress \                  # 开启压缩
13.     --compress-threads=4          # 4线程压缩，提升速度
15. # 流式备份到远程（大型库推荐，避免本地磁盘不够）
16. xtrabackup --backup \
17.     --user=backup \
18.     --password='BackupPass@2024' \
19.     --stream=xbstream \           # 流式输出
20.     --compress \
21.     | ssh backup@remote-server "cat > /backup/mysql/full_$(date +%Y%m%d).xbstream"  # 传输到远程服务器

复制代码

✅ 增量备份（省空间，适合大型库）

增量备份只备份上次备份后变化的数据，比全量备份小很多，步骤如下（必须基于全量备份）：

1. # 第一步：创建全量备份（基准备份，只做一次）
2. xtrabackup --backup \
3.     --user=backup \
4.     --password='BackupPass@2024' \
5.     --target-dir=/backup/mysql/full/base
7. # 第二步：创建第一次增量备份（基于全量备份）
8. xtrabackup --backup \
9.     --user=backup \
10.     --password='BackupPass@2024' \
11.     --target-dir=/backup/mysql/incremental/inc1 \
12.     --incremental-basedir=/backup/mysql/full/base  # 指定基准备份目录
14. # 第三步：创建第二次增量备份（基于第一次增量）
15. xtrabackup --backup \
16.     --user=backup \
17.     --password='BackupPass@2024' \
18.     --target-dir=/backup/mysql/incremental/inc2 \
19.     --incremental-basedir=/backup/mysql/incremental/inc1  # 指定上一次增量备份目录

复制代码

✅ 备份准备与恢复（关键，备份了不能恢复等于白做）

XtraBackup备份后不能直接恢复，必须先“准备”（应用redo log，确保数据一致性）：

1. # 1. 准备全量备份（基准备份）
2. xtrabackup --prepare \
3.     --apply-log-only \  # 只应用redo log，不做其他操作（增量备份必需）
4.     --target-dir=/backup/mysql/full/base
6. # 2. 应用第一次增量备份到基准备份
7. xtrabackup --prepare \
8.     --apply-log-only \
9.     --target-dir=/backup/mysql/full/base \
10.     --incremental-dir=/backup/mysql/incremental/inc1
12. # 3. 应用第二次增量备份（最后一次增量，不用加--apply-log-only）
13. xtrabackup --prepare \
14.     --target-dir=/backup/mysql/full/base \
15.     --incremental-dir=/backup/mysql/incremental/inc2
17. # 4. 最终准备（确保数据完整）
18. xtrabackup --prepare \
19.     --target-dir=/backup/mysql/full/base
21. # 恢复备份（停止MySQL后执行）
22. xtrabackup --copy-back \
23.     --target-dir=/backup/mysql/full/base
25. # 恢复后设置权限（必须，否则MySQL启动失败）
26. chown -R mysql:mysql /data/mysql/data  # /data/mysql/data是你的MySQL数据目录

复制代码

三、生产实战：脚本+案例，直接落地

手动执行备份太麻烦，而且容易忘，生产环境一定要用脚本+定时任务，实现自动化备份。另外，附上3个高频故障恢复案例，帮你应对突发情况。
3.1 生产环境备份脚本（直接复制可用）

✅ mysqldump备份脚本（小型数据库）

文件路径：/backup/mysql/scripts/mysqldump_backup.sh，记得替换配置中的密码和目录：

1. #!/bin/bash
2. # 文件：/backup/mysql/scripts/mysqldump_backup.sh
3. # 功能：生产环境mysqldump自动化备份脚本
4. # 用法：./mysqldump_backup.sh [full|single] [database_name]
6. set -e  # 执行出错立即退出
8. # 配置（按需修改）
9. MYSQL_USER="backup"
10. MYSQL_PASS="BackupPass@2024"  # 替换成你的备份用户密码
11. MYSQL_HOST="localhost"
12. BACKUP_DIR="/backup/mysql/full"
13. LOG_DIR="/backup/mysql/logs"
14. RETENTION_DAYS=7  # 备份保留7天，自动清理
15. DATE=$(date +%Y%m%d_%H%M%S)
16. LOG_FILE="${LOG_DIR}/mysqldump_${DATE}.log"
18. # 备份元数据库（记录备份信息，可选）
19. META_DB="backup_meta"
20. RECORD_BACKUP=true
22. # 初始化目录
23. mkdir -p ${BACKUP_DIR} ${LOG_DIR}
25. # 日志函数
26. log() {
27.     echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a ${LOG_FILE}
28. }
30. # 记录备份结果到元数据库
31. record_backup() {
32.     local status=$1
33.     local error_msg=$2
34.     local end_time=$(date '+%Y-%m-%d %H:%M:%S')
35.     local backup_size=$(stat -c%s ${BACKUP_FILE} 2>/dev/null || echo 0)
36.     local compressed_size=$(stat -c%s ${BACKUP_FILE}.gz 2>/dev/null || echo 0)
38.     if [ "$RECORD_BACKUP" = true ]; then
39.         mysql -u${MYSQL_USER} -p${MYSQL_PASS} ${META_DB} << EOF
40. UPDATE backup_history SET
41.     end_time = '${end_time}',
42.     duration_seconds = TIMESTAMPDIFF(SECOND, start_time, '${end_time}'),
43.     status = '${status}',
44.     backup_size_bytes = ${backup_size},
45.     compressed_size_bytes = ${compressed_size},
46.     error_message = '${error_msg}'
47. WHERE id = ${BACKUP_ID};
48. EOF
49.     fi
50. }
52. # 全量备份
53. do_full_backup() {
54.     log "Starting full backup..."
55.     BACKUP_FILE="${BACKUP_DIR}/all_databases_${DATE}.sql"
57.     # 获取binlog位置（用于时间点恢复）
58.     BINLOG_INFO=$(mysql -u${MYSQL_USER} -p${MYSQL_PASS} -N -e "SHOW MASTER STATUS")
59.     BINLOG_FILE=$(echo $BINLOG_INFO | awk '{print $1}')
60.     BINLOG_POS=$(echo $BINLOG_INFO | awk '{print $2}')
61.     GTID_EXECUTED=$(mysql -u${MYSQL_USER} -p${MYSQL_PASS} -N -e "SELECT @@global.gtid_executed" | tr '\n' ' ')
63.     # 记录备份开始
64.     if [ "$RECORD_BACKUP" = true ]; then
65.         BACKUP_ID=$(mysql -u${MYSQL_USER} -p${MYSQL_PASS} ${META_DB} -N -e "
66.             INSERT INTO backup_history (backup_type, backup_tool, backup_path, start_time, binlog_file, binlog_position, gtid_executed)
67.             VALUES ('full', 'mysqldump', '${BACKUP_FILE}', NOW(), '${BINLOG_FILE}', ${BINLOG_POS}, '${GTID_EXECUTED}');
68.             SELECT LAST_INSERT_ID();
69.         ")
70.     fi
72.     # 执行备份
73.     log "Backup file: ${BACKUP_FILE}"
74.     log "Binlog: ${BINLOG_FILE}:${BINLOG_POS}"
76.     mysqldump -u${MYSQL_USER} -p${MYSQL_PASS} -h${MYSQL_HOST} \
77.         --single-transaction \
78.         --source-data=2 \
79.         --routines \
80.         --triggers \
81.         --events \
82.         --set-gtid-purged=AUTO \
83.         --hex-blob \
84.         --quick \
85.         --max-allowed-packet=512M \
86.         --default-character-set=utf8mb4 \
87.         --all-databases > ${BACKUP_FILE} 2>>${LOG_FILE}
89.     if [ $? -eq 0 ]; then
90.         log "Backup completed successfully"
91.         # 压缩备份
92.         log "Compressing backup..."
93.         gzip ${BACKUP_FILE}
94.         log "Compressed size: $(du -h ${BACKUP_FILE}.gz | cut -f1)"
95.         record_backup "success" ""
96.     else
97.         log "Backup FAILED!"
98.         record_backup "failed" "mysqldump failed"
99.         exit 1
100.     fi
101. }
103. # 单库备份
104. do_single_backup() {
105.     local DB_NAME=$1
106.     log "Starting single database backup: ${DB_NAME}"
107.     BACKUP_FILE="${BACKUP_DIR}/${DB_NAME}_${DATE}.sql"
109.     mysqldump -u${MYSQL_USER} -p${MYSQL_PASS} -h${MYSQL_HOST} \
110.         --single-transaction \
111.         --source-data=2 \
112.         --routines \
113.         --triggers \
114.         --set-gtid-purged=AUTO \
115.         --hex-blob \
116.         --quick \
117.         ${DB_NAME} > ${BACKUP_FILE} 2>>${LOG_FILE}
119.     if [ $? -eq 0 ]; then
120.         log "Single database backup completed: ${DB_NAME}"
121.         gzip ${BACKUP_FILE}
122.     else
123.         log "Single database backup FAILED: ${DB_NAME}"
124.         exit 1
125.     fi
126. }
128. # 清理旧备份（保留7天）
129. cleanup_old_backups() {
130.     log "Cleaning up backups older than ${RETENTION_DAYS} days..."
131.     find ${BACKUP_DIR} -name "*.sql.gz" -mtime +${RETENTION_DAYS} -delete
132.     find ${LOG_DIR} -name "*.log" -mtime +${RETENTION_DAYS} -delete
133.     log "Cleanup completed"
134. }
136. # 主程序
137. case "$1" in
138.     full)
139.         do_full_backup
140.         ;;
141.     single)
142.         if [ -z "$2" ]; then
143.             log "Error: Database name required"
144.             exit 1
145.         fi
146.         do_single_backup $2
147.         ;;
148.     *)
149.         do_full_backup  # 默认执行全量备份
150.         ;;
151. esac
153. cleanup_old_backups
154. log "Backup script completed"

复制代码

✅ XtraBackup备份脚本（大型数据库）

文件路径：/backup/mysql/scripts/xtrabackup_backup.sh，支持全量和增量备份：

1. #!/bin/bash
2. # 文件：/backup/mysql/scripts/xtrabackup_backup.sh
3. # 功能：生产环境XtraBackup自动化备份脚本
4. # 用法：./xtrabackup_backup.sh [full|incremental]
6. set -e
8. # 配置（按需修改）
9. MYSQL_USER="backup"
10. MYSQL_PASS="BackupPass@2024"
11. BACKUP_BASE="/backup/mysql"
12. FULL_BACKUP_DIR="${BACKUP_BASE}/full"
13. INCR_BACKUP_DIR="${BACKUP_BASE}/incremental"
14. LOG_DIR="${BACKUP_BASE}/logs"
15. DATE=$(date +%Y%m%d_%H%M%S)
16. LOG_FILE="${LOG_DIR}/xtrabackup_${DATE}.log"
17. COMPRESS_THREADS=4  # 压缩线程数
18. PARALLEL_THREADS=4  # 并行备份线程数
20. # 保留策略
21. FULL_RETENTION_DAYS=30  # 全量备份保留30天
22. INCR_RETENTION_DAYS=7   # 增量备份保留7天
24. # 初始化目录
25. mkdir -p ${FULL_BACKUP_DIR} ${INCR_BACKUP_DIR} ${LOG_DIR}
27. # 日志函数
28. log() {
29.     echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a ${LOG_FILE}
30. }
32. # 获取最新全量备份
33. get_latest_full_backup() {
34.     ls -td ${FULL_BACKUP_DIR}/*/ 2>/dev/null | head -1
35. }
37. # 获取最新增量备份
38. get_latest_incremental() {
39.     local base_dir=$1
40.     local base_name=$(basename $base_dir)
41.     ls -td ${INCR_BACKUP_DIR}/${base_name}_inc*/ 2>/dev/null | head -1
42. }
44. # 全量备份
45. do_full_backup() {
46.     log "Starting full backup..."
47.     local BACKUP_PATH="${FULL_BACKUP_DIR}/${DATE}"
49.     xtrabackup --backup \
50.         --user=${MYSQL_USER} \
51.         --password=${MYSQL_PASS} \
52.         --target-dir=${BACKUP_PATH} \
53.         --parallel=${PARALLEL_THREADS} \
54.         --compress \
55.         --compress-threads=${COMPRESS_THREADS} \
56.         2>>${LOG_FILE}
58.     if [ $? -eq 0 ]; then
59.         log "Full backup completed: ${BACKUP_PATH}"
60.         cat ${BACKUP_PATH}/xtrabackup_checkpoints | tee -a ${LOG_FILE}
61.         local SIZE=$(du -sh ${BACKUP_PATH} | cut -f1)
62.         log "Backup size: ${SIZE}"
63.     else
64.         log "Full backup FAILED!"
65.         exit 1
66.     fi
67. }
69. # 增量备份
70. do_incremental_backup() {
71.     log "Starting incremental backup..."
73.     # 获取基准目录（没有全量备份则自动执行全量）
74.     local LATEST_FULL=$(get_latest_full_backup)
75.     if [ -z "$LATEST_FULL" ]; then
76.         log "No full backup found, performing full backup instead"
77.         do_full_backup
78.         return
79.     fi
81.     local FULL_NAME=$(basename $LATEST_FULL)
82.     log "Base full backup: ${LATEST_FULL}"
84.     # 检查是否有之前的增量备份
85.     local LATEST_INCR=$(get_latest_incremental $LATEST_FULL)
86.     local BASE_DIR=$LATEST_FULL
87.     if [ -n "$LATEST_INCR" ]; then
88.         BASE_DIR=$LATEST_INCR
89.         log "Base incremental backup: ${LATEST_INCR}"
90.     fi
92.     # 生成增量备份名称
93.     local INCR_COUNT=$(ls -d ${INCR_BACKUP_DIR}/${FULL_NAME}_inc*/ 2>/dev/null | wc -l)
94.     local INCR_NAME="${FULL_NAME}_inc$((INCR_COUNT + 1))_${DATE}"
95.     local BACKUP_PATH="${INCR_BACKUP_DIR}/${INCR_NAME}"
97.     xtrabackup --backup \
98.         --user=${MYSQL_USER} \
99.         --password=${MYSQL_PASS} \
100.         --target-dir=${BACKUP_PATH} \
101.         --incremental-basedir=${BASE_DIR} \
102.         --parallel=${PARALLEL_THREADS} \
103.         --compress \
104.         --compress-threads=${COMPRESS_THREADS} \
105.         2>>${LOG_FILE}
107.     if [ $? -eq 0 ]; then
108.         log "Incremental backup completed: ${BACKUP_PATH}"
109.         cat ${BACKUP_PATH}/xtrabackup_checkpoints | tee -a ${LOG_FILE}
110.     else
111.         log "Incremental backup FAILED!"
112.         exit 1
113.     fi
114. }
116. # 清理旧备份
117. cleanup_old_backups() {
118.     log "Cleaning up old backups..."
119.     # 清理旧全量备份
120.     find ${FULL_BACKUP_DIR} -maxdepth 1 -type d -mtime +${FULL_RETENTION_DAYS} -exec rm -rf {} \;
121.     # 清理旧增量备份
122.     find ${INCR_BACKUP_DIR} -maxdepth 1 -type d -mtime +${INCR_RETENTION_DAYS} -exec rm -rf {} \;
123.     # 清理旧日志
124.     find ${LOG_DIR} -name "*.log" -mtime +${FULL_RETENTION_DAYS} -delete
125.     log "Cleanup completed"
126. }
128. # 主程序
129. case "$1" in
130.     full)
131.         do_full_backup
132.         ;;
133.     incremental|incr)
134.         do_incremental_backup
135.         ;;
136.     *)
137.         log "Usage: $0 [full|incremental]"
138.         exit 1
139.         ;;
140. esac
142. cleanup_old_backups
143. log "Backup script completed"

复制代码

✅ 定时任务配置（自动化备份关键）

用crontab设置定时任务，让脚本自动执行，不用手动操作：

1. # 编辑crontab（执行命令）
2. crontab -e
4. # -------------- 小型数据库（mysqldump）--------------
5. # 每天凌晨2点执行全备，日志写入cron.log
6. 0 2 * * * /backup/mysql/scripts/mysqldump_backup.sh full >> /backup/mysql/logs/cron.log 2>&1
8. # -------------- 大型数据库（XtraBackup）--------------
9. # 每周日凌晨2点执行全备
10. 0 2 * * 0 /backup/mysql/scripts/xtrabackup_backup.sh full >> /backup/mysql/logs/cron.log 2>&1
11. # 周一到周六凌晨2点执行增量备份
12. 0 2 * * 1-6 /backup/mysql/scripts/xtrabackup_backup.sh incremental >> /backup/mysql/logs/cron.log 2>&1
14. # -------------- binlog归档（每小时一次）--------------
15. 0 * * * * /backup/mysql/scripts/binlog_archive.sh >> /backup/mysql/logs/cron.log 2>&1

复制代码

3.2 高频故障恢复案例（实战必备）

案例一：误删数据恢复（最常见）

场景：开发误执行DELETE语句，删除了users表中当天的数据（本想删除无效用户，忘了加WHERE条件）。

1. -- 误操作SQL（噩梦开始）
2. DELETE FROM users WHERE created_at >= '2024-01-15';
3. -- 正确SQL（应该加条件）
4. DELETE FROM users WHERE created_at >= '2024-01-15' AND status = 'inactive';

复制代码

恢复步骤（关键：立即记录binlog位置，避免数据覆盖）：

1. # 第一步：立即记录当前binlog位置（重中之重，防止binlog滚动）
2. mysql -uroot -p -e "SHOW MASTER STATUS"
3. # 记录结果：File: mysql-bin.000150, Position: 123456789
5. # 第二步：找到最近的全量备份
6. ls -la /backup/mysql/full/
7. # 假设最新备份：all_databases_20240115_020000.sql.gz
9. # 第三步：解压备份，恢复到临时库（避免影响生产库）
10. # 解压备份
11. gunzip -c /backup/mysql/full/all_databases_20240115_020000.sql.gz > /tmp/restore.sql
13. # 创建临时库
14. mysql -uroot -p -e "CREATE DATABASE users_recovery"
16. # 只恢复users表（精准恢复，节省时间）
17. # 方法1：用sed提取users表
18. sed -n '/^-- Table structure for table `users`/,/^-- Table structure for table/p' /tmp/restore.sql > /tmp/users_table.sql
20. # 方法2：用awk提取（更精准）
21. awk '/^-- Table structure for table `users`/{f=1} f; /^UNLOCK TABLES;/{if(f) exit}' /tmp/restore.sql > /tmp/users_table.sql
23. # 导入到临时库
24. mysql -uroot -p users_recovery < /tmp/users_table.sql
26. # 第四步：确认备份的binlog位置（从备份文件中提取）
27. grep "CHANGE MASTER TO" /tmp/restore.sql | head -1
28. # 输出：-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000145', MASTER_LOG_POS=789012;
30. # 第五步：应用binlog，恢复到误删之前
31. # 先找到误删操作的binlog位置
32. mysqlbinlog /data/mysql/binlog/mysql-bin.000145 mysql-bin.000146 \
33.     --start-position=789012 \
34.     --database=mydb \
35.     -v | grep -B5 -A5 "DELETE FROM.*users"
37. # 找到误删位置后，应用binlog到误删前
38. mysqlbinlog /data/mysql/binlog/mysql-bin.000145 mysql-bin.000146 \
39.     --start-position=789012 \
40.     --stop-position=误删操作位置 \  # 替换成实际误删位置
41.     --database=mydb | mysql -uroot -p users_recovery
43. # 第六步：将恢复的数据导回生产库
44. mysqldump -uroot -p users_recovery users | mysql -uroot -p mydb

复制代码

案例二：时间点恢复（PITR）

场景：数据库因误操作、病毒攻击等，需要恢复到指定时间点（比如2024-01-15 14:30:00）。

1. #!/bin/bash
2. # 文件：point_in_time_recovery.sh
3. # 功能：MySQL时间点恢复脚本
4. # 用法：直接执行，修改配置中的恢复时间和目录
6. RECOVERY_TIME="2024-01-15 14:30:00"  # 目标恢复时间
7. BACKUP_DIR="/backup/mysql/full/20240115"  # 最近的全量备份目录
8. BINLOG_DIR="/data/mysql/binlog"  # binlog目录
9. RECOVERY_DIR="/data/mysql_recovery"  # 恢复临时目录
11. # 第一步：准备备份（解压+应用redo log）
12. echo "Preparing backup..."
13. cp -r ${BACKUP_DIR} ${RECOVERY_DIR}
15. # 解压压缩的备份文件（如果是压缩备份）
16. cd ${RECOVERY_DIR}
17. for f in *.qp; do
18.     qpress -d $f ./
19. done
21. # 准备备份，确保数据一致性
22. xtrabackup --prepare --target-dir=${RECOVERY_DIR}
24. # 第二步：获取备份的binlog位置（从备份文件中提取）
25. BINLOG_INFO=$(cat ${RECOVERY_DIR}/xtrabackup_binlog_info)
26. BINLOG_FILE=$(echo $BINLOG_INFO | awk '{print $1}')
27. BINLOG_POS=$(echo $BINLOG_INFO | awk '{print $2}')
29. echo "Backup binlog position: ${BINLOG_FILE}:${BINLOG_POS}"
31. # 第三步：找到需要应用的binlog文件
32. BINLOG_FILES=$(ls ${BINLOG_DIR}/mysql-bin.* | sort | awk -v start="${BINLOG_FILE}" '$0 >= start')
34. # 第四步：应用binlog到指定时间点
35. echo "Applying binlog until ${RECOVERY_TIME}..."
36. mysqlbinlog ${BINLOG_FILES} \
37.     --start-position=${BINLOG_POS} \
38.     --stop-datetime="${RECOVERY_TIME}" \
39.     > ${RECOVERY_DIR}/binlog_recovery.sql
41. # 第五步：恢复数据到生产环境
42. echo "Stopping MySQL..."
43. systemctl stop mysqld
45. # 备份当前生产数据目录（安全起见，防止恢复失败）
46. mv /data/mysql/data /data/mysql/data_old_$(date +%Y%m%d_%H%M%S)
48. # 恢复备份
49. xtrabackup --copy-back --target-dir=${RECOVERY_DIR}
50. chown -R mysql:mysql /data/mysql/data  # 设置权限
52. # 启动MySQL
53. systemctl start mysqld
55. # 应用binlog增量，完成时间点恢复
56. echo "Applying binlog recovery..."
57. mysql -uroot -p < ${RECOVERY_DIR}/binlog_recovery.sql
59. echo "Point-in-time recovery completed to ${RECOVERY_TIME}"

复制代码

案例三：大表备份与恢复（500GB+）

场景：orders表有5亿条数据，单表500GB，直接备份/恢复会卡死，用分区导出导入，提升效率。
[code]# 备份脚本：large_table_backup.sh（分区导出）#!/bin/bashTABLE="orders"DB="ecommerce"BACKUP_DIR="/backup/mysql/large_tables"DATE=$(date +%Y%m%d)PARALLEL_JOBS=4  # 4线程并行，按需调整mkdir -p ${BACKUP_DIR}/${DATE}# 获取表的主键范围（按主键分区）MAX_ID=$(mysql -ubackup -p'BackupPass@2024' -N -e "SELECT MAX(id) FROM ${DB}.${TABLE}")BATCH_SIZE=$((MAX_ID / PARALLEL_JOBS))# 并行导出（按主键分段，避免单线程卡死）for i in $(seq 1 ${PARALLEL_JOBS}); do    START_ID=$(( (i-1) * BATCH_SIZE + 1 ))    END_ID=$(( i * BATCH_SIZE ))    if [ $i -eq ${PARALLEL_JOBS} ]; then        END_ID=${MAX_ID}  # 最后一段包含剩余数据    fi    echo "Exporting ${TABLE} rows ${START_ID} to ${END_ID}..."    mysqldump -ubackup -p'BackupPass@2024' \        --single-transaction \        --no-create-info \  # 只导出数据，表结构单独导出        --where="id >= ${START_ID} AND id