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）、跨版本迁移、表级恢复 |
| 优点 | 简单、可读、跨平台、跨版本，不用额外安装 |
| 缺点 | 大数据量时速度慢，恢复时要执行大量SQL，耗时久 |
1.2 Percona XtraBackup：大型库“重型利器”
XtraBackup是Percona公司推出的物理热备份工具，直接复制数据库的数据文件，就像给硬盘做“镜像”，速度快、对业务影响极小，是大型数据库的首选。
| 特性 | 详细说明 |
|---|---|
| 备份类型 | 物理备份（直接复制数据文件，二进制格式） |
| 锁机制 | 几乎无锁（InnoDB热备），备份时不影响业务读写 |
| 速度 | 极快（直接复制文件），大数据量优势明显 |
| 适用场景 | 大型数据库（>50GB）、生产环境低影响备份 |
| 优点 | 快速、低影响、支持增量备份，恢复速度快 |
| 缺点 | 只支持同版本恢复，配置比mysqldump复杂，需要额外安装 |
1.3 场景对应表（直接抄作业）
不用纠结，对照下面的场景选工具，准没错：
| 应用场景 | 推荐工具 | 备份策略 |
|---|---|---|
| 开发测试环境 | mysqldump | 每日全备（简单省事） |
| 小型生产（<50GB） | mysqldump | 每日全备 + binlog归档 |
| 中型生产（50-500GB） | XtraBackup | 每周全备 + 每日增量 |
| 大型生产（>500GB） | XtraBackup | 每周全备 + 每日增量 + 流式备份 |
| 跨版本迁移 | mysqldump | 逻辑导出导入（兼容性强） |
| 表级恢复 | mysqldump | 单表导出（精准恢复，不影响其他表） |
| 时间点恢复（PITR） | XtraBackup + binlog | 全备 + binlog应用（恢复到任意时间点） |
1.4 环境要求（必看，避免踩坑）
环境不匹配，备份必失败！提前核对以下版本和配置：
| 组件 | 版本要求 | 关键说明 |
|---|---|---|
| MySQL Server | 8.0.35+ 或 8.4 LTS | 稳定版优先，避免使用测试版 |
| Percona XtraBackup | 8.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
# 安装Percona仓库（XtraBackup需要）
sudo dnf install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm
sudo percona-release setup pxb-80
# 安装XtraBackup
sudo dnf install -y percona-xtrabackup-80
# 安装压缩工具（备份压缩必需）
sudo dnf install -y qpress lz4
# 验证安装（出现版本号即成功）
xtrabackup --version
# 正常输出：xtrabackup version 8.0.35-30 based on MySQL server 8.0.35
✅ Ubuntu 24.04
sudo apt-get update
sudo apt-get install -y wget gnupg2 lsb-release
wget https://repo.percona.com/apt/percona-release_latest.generic_all.deb
sudo dpkg -i percona-release_latest.generic_all.deb
sudo percona-release setup pxb-80
sudo apt-get install -y percona-xtrabackup-80 qpress
2.1.2 创建备份专用用户
不要用root用户备份！创建专用备份用户，分配最小权限，更安全：
-- 登录MySQL，执行以下SQL
CREATE USER 'backup'@'localhost' IDENTIFIED BY 'BackupPass@2024'; -- 密码替换成自己的
-- mysqldump所需权限（复制粘贴即可）
GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES, EVENT ON *.* TO 'backup'@'localhost';
-- XtraBackup所需权限（复制粘贴即可）
GRANT RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'backup'@'localhost';
GRANT BACKUP_ADMIN ON *.* TO 'backup'@'localhost'; -- MySQL 8.0+ 必需
GRANT SELECT ON performance_schema.log_status TO 'backup'@'localhost';
GRANT SELECT ON performance_schema.keyring_component_status TO 'backup'@'localhost';
FLUSH PRIVILEGES; -- 刷新权限，立即生效
2.1.3 规划备份目录
目录混乱会导致后续恢复找不到文件，建议按以下结构创建，一目了然：
# 创建备份目录结构（一次性执行）
sudo mkdir -p /backup/mysql/{full,incremental,binlog,scripts,logs}
sudo chown -R mysql:mysql /backup/mysql # 授权mysql用户，避免权限不足
sudo chmod 750 /backup/mysql # 限制访问权限，更安全
# 目录说明（记好，后续用得到）：
# /backup/mysql/full - 存放全量备份
# /backup/mysql/incremental - 存放增量备份
# /backup/mysql/binlog - 存放binlog归档
# /backup/mysql/scripts - 存放备份脚本
# /backup/mysql/logs - 存放备份日志
# 重要提醒：备份目录建议用独立磁盘或NFS挂载，避免和数据目录在同一磁盘（防止磁盘损坏一起丢）
2.2 核心操作：mysqldump实战用法
适合小型数据库，操作简单，以下是最常用的几种场景，覆盖90%的需求：
✅ 备份单个数据库
mysqldump -u backup -p'BackupPass@2024' \
--single-transaction \ # InnoDB不锁表，关键参数
--routines \ # 备份存储过程和函数
--triggers \ # 备份触发器
--events \ # 备份事件调度器
mydb > /backup/mysql/full/mydb_$(date +%Y%m%d).sql # 备份到指定目录，带日期
✅ 备份多个数据库
mysqldump -u backup -p'BackupPass@2024' \
--single-transaction \
--routines \
--triggers \
--databases db1 db2 db3 > /backup/mysql/full/multi_db_$(date +%Y%m%d).sql
✅ 备份所有数据库（最常用）
mysqldump -u backup -p'BackupPass@2024' \
--single-transaction \
--routines \
--triggers \
--events \
--all-databases > /backup/mysql/full/all_db_$(date +%Y%m%d).sql
✅ 备份单个表（精准恢复，不影响其他表）
mysqldump -u backup -p'BackupPass@2024' \
--single-transaction \
mydb users > /backup/mysql/full/mydb_users_$(date +%Y%m%d).sql # mydb是库名，users是表名
✅ 生产环境推荐参数组合（带压缩，更省空间）
这是我平时用的配置，兼顾安全和效率，直接复制用：
mysqldump -u backup -p'BackupPass@2024' \
--single-transaction \ # InnoDB一致性读，不锁表（必需）
--source-data=2 \ # 记录binlog位置（注释形式，方便恢复）
--routines \ # 包含存储过程和函数
--triggers \ # 包含触发器
--events \ # 包含事件调度器
--set-gtid-purged=AUTO \ # GTID自动处理（MySQL 8.0+ 推荐）
--hex-blob \ # 二进制数据用十六进制，避免乱码
--quick \ # 逐行读取，减少内存占用
--max-allowed-packet=512M \ # 支持大数据包，避免导出失败
--default-character-set=utf8mb4 \ # 字符集，避免乱码
--all-databases \
| gzip > /backup/mysql/full/all_db_$(date +%Y%m%d).sql.gz # 压缩备份，省空间
注意：MySQL 8.0.26+ 版本，--master-data 参数已改为 --source-data，别用错了！
2.3 核心操作：XtraBackup实战用法
适合大型数据库，热备份、速度快，重点掌握全量和增量备份（生产环境最常用）。
✅ 全量备份（基础，增量备份的前提）
# 基础全量备份
xtrabackup --backup \
--user=backup \
--password='BackupPass@2024' \
--target-dir=/backup/mysql/full/$(date +%Y%m%d) # 备份目录带日期，方便区分
# 备份并压缩（推荐，省空间）
xtrabackup --backup \
--user=backup \
--password='BackupPass@2024' \
--target-dir=/backup/mysql/full/$(date +%Y%m%d) \
--compress \ # 开启压缩
--compress-threads=4 # 4线程压缩，提升速度
# 流式备份到远程（大型库推荐，避免本地磁盘不够）
xtrabackup --backup \
--user=backup \
--password='BackupPass@2024' \
--stream=xbstream \ # 流式输出
--compress \
| ssh backup@remote-server "cat > /backup/mysql/full_$(date +%Y%m%d).xbstream" # 传输到远程服务器
✅ 增量备份（省空间，适合大型库）
增量备份只备份上次备份后变化的数据，比全量备份小很多，步骤如下（必须基于全量备份）：
# 第一步：创建全量备份（基准备份，只做一次）
xtrabackup --backup \
--user=backup \
--password='BackupPass@2024' \
--target-dir=/backup/mysql/full/base
# 第二步：创建第一次增量备份（基于全量备份）
xtrabackup --backup \
--user=backup \
--password='BackupPass@2024' \
--target-dir=/backup/mysql/incremental/inc1 \
--incremental-basedir=/backup/mysql/full/base # 指定基准备份目录
# 第三步：创建第二次增量备份（基于第一次增量）
xtrabackup --backup \
--user=backup \
--password='BackupPass@2024' \
--target-dir=/backup/mysql/incremental/inc2 \
--incremental-basedir=/backup/mysql/incremental/inc1 # 指定上一次增量备份目录
✅ 备份准备与恢复（关键，备份了不能恢复等于白做）
XtraBackup备份后不能直接恢复，必须先“准备”（应用redo log，确保数据一致性）：
# 1. 准备全量备份（基准备份）
xtrabackup --prepare \
--apply-log-only \ # 只应用redo log，不做其他操作（增量备份必需）
--target-dir=/backup/mysql/full/base
# 2. 应用第一次增量备份到基准备份
xtrabackup --prepare \
--apply-log-only \
--target-dir=/backup/mysql/full/base \
--incremental-dir=/backup/mysql/incremental/inc1
# 3. 应用第二次增量备份（最后一次增量，不用加--apply-log-only）
xtrabackup --prepare \
--target-dir=/backup/mysql/full/base \
--incremental-dir=/backup/mysql/incremental/inc2
# 4. 最终准备（确保数据完整）
xtrabackup --prepare \
--target-dir=/backup/mysql/full/base
# 恢复备份（停止MySQL后执行）
xtrabackup --copy-back \
--target-dir=/backup/mysql/full/base
# 恢复后设置权限（必须，否则MySQL启动失败）
chown -R mysql:mysql /data/mysql/data # /data/mysql/data是你的MySQL数据目录
三、生产实战：脚本+案例，直接落地
手动执行备份太麻烦，而且容易忘，生产环境一定要用脚本+定时任务，实现自动化备份。另外，附上3个高频故障恢复案例，帮你应对突发情况。
3.1 生产环境备份脚本（直接复制可用）
✅ mysqldump备份脚本（小型数据库）
文件路径：/backup/mysql/scripts/mysqldump_backup.sh，记得替换配置中的密码和目录：
#!/bin/bash
# 文件：/backup/mysql/scripts/mysqldump_backup.sh
# 功能：生产环境mysqldump自动化备份脚本
# 用法：./mysqldump_backup.sh [full|single] [database_name]
set -e # 执行出错立即退出
# 配置（按需修改）
MYSQL_USER="backup"
MYSQL_PASS="BackupPass@2024" # 替换成你的备份用户密码
MYSQL_HOST="localhost"
BACKUP_DIR="/backup/mysql/full"
LOG_DIR="/backup/mysql/logs"
RETENTION_DAYS=7 # 备份保留7天，自动清理
DATE=$(date +%Y%m%d_%H%M%S)
LOG_FILE="${LOG_DIR}/mysqldump_${DATE}.log"
# 备份元数据库（记录备份信息，可选）
META_DB="backup_meta"
RECORD_BACKUP=true
# 初始化目录
mkdir -p ${BACKUP_DIR} ${LOG_DIR}
# 日志函数
log() {
echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a ${LOG_FILE}
}
# 记录备份结果到元数据库
record_backup() {
local status=$1
local error_msg=$2
local end_time=$(date '+%Y-%m-%d %H:%M:%S')
local backup_size=$(stat -c%s ${BACKUP_FILE} 2>/dev/null || echo 0)
local compressed_size=$(stat -c%s ${BACKUP_FILE}.gz 2>/dev/null || echo 0)
if [ "$RECORD_BACKUP" = true ]; then
mysql -u${MYSQL_USER} -p${MYSQL_PASS} ${META_DB} << EOF
UPDATE backup_history SET
end_time = '${end_time}',
duration_seconds = TIMESTAMPDIFF(SECOND, start_time, '${end_time}'),
status = '${status}',
backup_size_bytes = ${backup_size},
compressed_size_bytes = ${compressed_size},
error_message = '${error_msg}'
WHERE id = ${BACKUP_ID};
EOF
fi
}
# 全量备份
do_full_backup() {
log "Starting full backup..."
BACKUP_FILE="${BACKUP_DIR}/all_databases_${DATE}.sql"
# 获取binlog位置（用于时间点恢复）
BINLOG_INFO=$(mysql -u${MYSQL_USER} -p${MYSQL_PASS} -N -e "SHOW MASTER STATUS")
BINLOG_FILE=$(echo $BINLOG_INFO | awk '{print $1}')
BINLOG_POS=$(echo $BINLOG_INFO | awk '{print $2}')
GTID_EXECUTED=$(mysql -u${MYSQL_USER} -p${MYSQL_PASS} -N -e "SELECT @@global.gtid_executed" | tr '\n' ' ')
# 记录备份开始
if [ "$RECORD_BACKUP" = true ]; then
BACKUP_ID=$(mysql -u${MYSQL_USER} -p${MYSQL_PASS} ${META_DB} -N -e "
INSERT INTO backup_history (backup_type, backup_tool, backup_path, start_time, binlog_file, binlog_position, gtid_executed)
VALUES ('full', 'mysqldump', '${BACKUP_FILE}', NOW(), '${BINLOG_FILE}', ${BINLOG_POS}, '${GTID_EXECUTED}');
SELECT LAST_INSERT_ID();
")
fi
# 执行备份
log "Backup file: ${BACKUP_FILE}"
log "Binlog: ${BINLOG_FILE}:${BINLOG_POS}"
mysqldump -u${MYSQL_USER} -p${MYSQL_PASS} -h${MYSQL_HOST} \
--single-transaction \
--source-data=2 \
--routines \
--triggers \
--events \
--set-gtid-purged=AUTO \
--hex-blob \
--quick \
--max-allowed-packet=512M \
--default-character-set=utf8mb4 \
--all-databases > ${BACKUP_FILE} 2>>${LOG_FILE}
if [ $? -eq 0 ]; then
log "Backup completed successfully"
# 压缩备份
log "Compressing backup..."
gzip ${BACKUP_FILE}
log "Compressed size: $(du -h ${BACKUP_FILE}.gz | cut -f1)"
record_backup "success" ""
else
log "Backup FAILED!"
record_backup "failed" "mysqldump failed"
exit 1
fi
}
# 单库备份
do_single_backup() {
local DB_NAME=$1
log "Starting single database backup: ${DB_NAME}"
BACKUP_FILE="${BACKUP_DIR}/${DB_NAME}_${DATE}.sql"
mysqldump -u${MYSQL_USER} -p${MYSQL_PASS} -h${MYSQL_HOST} \
--single-transaction \
--source-data=2 \
--routines \
--triggers \
--set-gtid-purged=AUTO \
--hex-blob \
--quick \
${DB_NAME} > ${BACKUP_FILE} 2>>${LOG_FILE}
if [ $? -eq 0 ]; then
log "Single database backup completed: ${DB_NAME}"
gzip ${BACKUP_FILE}
else
log "Single database backup FAILED: ${DB_NAME}"
exit 1
fi
}
# 清理旧备份（保留7天）
cleanup_old_backups() {
log "Cleaning up backups older than ${RETENTION_DAYS} days..."
find ${BACKUP_DIR} -name "*.sql.gz" -mtime +${RETENTION_DAYS} -delete
find ${LOG_DIR} -name "*.log" -mtime +${RETENTION_DAYS} -delete
log "Cleanup completed"
}
# 主程序
case "$1" in
full)
do_full_backup
;;
single)
if [ -z "$2" ]; then
log "Error: Database name required"
exit 1
fi
do_single_backup $2
;;
*)
do_full_backup # 默认执行全量备份
;;
esac
cleanup_old_backups
log "Backup script completed"
✅ XtraBackup备份脚本（大型数据库）
文件路径：/backup/mysql/scripts/xtrabackup_backup.sh，支持全量和增量备份：
#!/bin/bash
# 文件：/backup/mysql/scripts/xtrabackup_backup.sh
# 功能：生产环境XtraBackup自动化备份脚本
# 用法：./xtrabackup_backup.sh [full|incremental]
set -e
# 配置（按需修改）
MYSQL_USER="backup"
MYSQL_PASS="BackupPass@2024"
BACKUP_BASE="/backup/mysql"
FULL_BACKUP_DIR="${BACKUP_BASE}/full"
INCR_BACKUP_DIR="${BACKUP_BASE}/incremental"
LOG_DIR="${BACKUP_BASE}/logs"
DATE=$(date +%Y%m%d_%H%M%S)
LOG_FILE="${LOG_DIR}/xtrabackup_${DATE}.log"
COMPRESS_THREADS=4 # 压缩线程数
PARALLEL_THREADS=4 # 并行备份线程数
# 保留策略
FULL_RETENTION_DAYS=30 # 全量备份保留30天
INCR_RETENTION_DAYS=7 # 增量备份保留7天
# 初始化目录
mkdir -p ${FULL_BACKUP_DIR} ${INCR_BACKUP_DIR} ${LOG_DIR}
# 日志函数
log() {
echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a ${LOG_FILE}
}
# 获取最新全量备份
get_latest_full_backup() {
ls -td ${FULL_BACKUP_DIR}/*/ 2>/dev/null | head -1
}
# 获取最新增量备份
get_latest_incremental() {
local base_dir=$1
local base_name=$(basename $base_dir)
ls -td ${INCR_BACKUP_DIR}/${base_name}_inc*/ 2>/dev/null | head -1
}
# 全量备份
do_full_backup() {
log "Starting full backup..."
local BACKUP_PATH="${FULL_BACKUP_DIR}/${DATE}"
xtrabackup --backup \
--user=${MYSQL_USER} \
--password=${MYSQL_PASS} \
--target-dir=${BACKUP_PATH} \
--parallel=${PARALLEL_THREADS} \
--compress \
--compress-threads=${COMPRESS_THREADS} \
2>>${LOG_FILE}
if [ $? -eq 0 ]; then
log "Full backup completed: ${BACKUP_PATH}"
cat ${BACKUP_PATH}/xtrabackup_checkpoints | tee -a ${LOG_FILE}
local SIZE=$(du -sh ${BACKUP_PATH} | cut -f1)
log "Backup size: ${SIZE}"
else
log "Full backup FAILED!"
exit 1
fi
}
# 增量备份
do_incremental_backup() {
log "Starting incremental backup..."
# 获取基准目录（没有全量备份则自动执行全量）
local LATEST_FULL=$(get_latest_full_backup)
if [ -z "$LATEST_FULL" ]; then
log "No full backup found, performing full backup instead"
do_full_backup
return
fi
local FULL_NAME=$(basename $LATEST_FULL)
log "Base full backup: ${LATEST_FULL}"
# 检查是否有之前的增量备份
local LATEST_INCR=$(get_latest_incremental $LATEST_FULL)
local BASE_DIR=$LATEST_FULL
if [ -n "$LATEST_INCR" ]; then
BASE_DIR=$LATEST_INCR
log "Base incremental backup: ${LATEST_INCR}"
fi
# 生成增量备份名称
local INCR_COUNT=$(ls -d ${INCR_BACKUP_DIR}/${FULL_NAME}_inc*/ 2>/dev/null | wc -l)
local INCR_NAME="${FULL_NAME}_inc$((INCR_COUNT + 1))_${DATE}"
local BACKUP_PATH="${INCR_BACKUP_DIR}/${INCR_NAME}"
xtrabackup --backup \
--user=${MYSQL_USER} \
--password=${MYSQL_PASS} \
--target-dir=${BACKUP_PATH} \
--incremental-basedir=${BASE_DIR} \
--parallel=${PARALLEL_THREADS} \
--compress \
--compress-threads=${COMPRESS_THREADS} \
2>>${LOG_FILE}
if [ $? -eq 0 ]; then
log "Incremental backup completed: ${BACKUP_PATH}"
cat ${BACKUP_PATH}/xtrabackup_checkpoints | tee -a ${LOG_FILE}
else
log "Incremental backup FAILED!"
exit 1
fi
}
# 清理旧备份
cleanup_old_backups() {
log "Cleaning up old backups..."
# 清理旧全量备份
find ${FULL_BACKUP_DIR} -maxdepth 1 -type d -mtime +${FULL_RETENTION_DAYS} -exec rm -rf {} \;
# 清理旧增量备份
find ${INCR_BACKUP_DIR} -maxdepth 1 -type d -mtime +${INCR_RETENTION_DAYS} -exec rm -rf {} \;
# 清理旧日志
find ${LOG_DIR} -name "*.log" -mtime +${FULL_RETENTION_DAYS} -delete
log "Cleanup completed"
}
# 主程序
case "$1" in
full)
do_full_backup
;;
incremental|incr)
do_incremental_backup
;;
*)
log "Usage: $0 [full|incremental]"
exit 1
;;
esac
cleanup_old_backups
log "Backup script completed"
✅ 定时任务配置（自动化备份关键）
用crontab设置定时任务，让脚本自动执行，不用手动操作：
# 编辑crontab（执行命令）
crontab -e
# -------------- 小型数据库（mysqldump）--------------
# 每天凌晨2点执行全备，日志写入cron.log
0 2 * * * /backup/mysql/scripts/mysqldump_backup.sh full >> /backup/mysql/logs/cron.log 2>&1
# -------------- 大型数据库（XtraBackup）--------------
# 每周日凌晨2点执行全备
0 2 * * 0 /backup/mysql/scripts/xtrabackup_backup.sh full >> /backup/mysql/logs/cron.log 2>&1
# 周一到周六凌晨2点执行增量备份
0 2 * * 1-6 /backup/mysql/scripts/xtrabackup_backup.sh incremental >> /backup/mysql/logs/cron.log 2>&1
# -------------- binlog归档（每小时一次）--------------
0 * * * * /backup/mysql/scripts/binlog_archive.sh >> /backup/mysql/logs/cron.log 2>&1
3.2 高频故障恢复案例（实战必备）
案例一：误删数据恢复（最常见）
场景：开发误执行DELETE语句，删除了users表中当天的数据（本想删除无效用户，忘了加WHERE条件）。
-- 误操作SQL（噩梦开始）
DELETE FROM users WHERE created_at >= '2024-01-15';
-- 正确SQL（应该加条件）
DELETE FROM users WHERE created_at >= '2024-01-15' AND status = 'inactive';
恢复步骤（关键：立即记录binlog位置，避免数据覆盖）：
# 第一步：立即记录当前binlog位置（重中之重，防止binlog滚动）
mysql -uroot -p -e "SHOW MASTER STATUS"
# 记录结果：File: mysql-bin.000150, Position: 123456789
# 第二步：找到最近的全量备份
ls -la /backup/mysql/full/
# 假设最新备份：all_databases_20240115_020000.sql.gz
# 第三步：解压备份，恢复到临时库（避免影响生产库）
# 解压备份
gunzip -c /backup/mysql/full/all_databases_20240115_020000.sql.gz > /tmp/restore.sql
# 创建临时库
mysql -uroot -p -e "CREATE DATABASE users_recovery"
# 只恢复users表（精准恢复，节省时间）
# 方法1：用sed提取users表
sed -n '/^-- Table structure for table `users`/,/^-- Table structure for table/p' /tmp/restore.sql > /tmp/users_table.sql
# 方法2：用awk提取（更精准）
awk '/^-- Table structure for table `users`/{f=1} f; /^UNLOCK TABLES;/{if(f) exit}' /tmp/restore.sql > /tmp/users_table.sql
# 导入到临时库
mysql -uroot -p users_recovery < /tmp/users_table.sql
# 第四步：确认备份的binlog位置（从备份文件中提取）
grep "CHANGE MASTER TO" /tmp/restore.sql | head -1
# 输出：-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000145', MASTER_LOG_POS=789012;
# 第五步：应用binlog，恢复到误删之前
# 先找到误删操作的binlog位置
mysqlbinlog /data/mysql/binlog/mysql-bin.000145 mysql-bin.000146 \
--start-position=789012 \
--database=mydb \
-v | grep -B5 -A5 "DELETE FROM.*users"
# 找到误删位置后，应用binlog到误删前
mysqlbinlog /data/mysql/binlog/mysql-bin.000145 mysql-bin.000146 \
--start-position=789012 \
--stop-position=误删操作位置 \ # 替换成实际误删位置
--database=mydb | mysql -uroot -p users_recovery
# 第六步：将恢复的数据导回生产库
mysqldump -uroot -p users_recovery users | mysql -uroot -p mydb
案例二：时间点恢复（PITR）
场景：数据库因误操作、病毒攻击等，需要恢复到指定时间点（比如2024-01-15 14:30:00）。
#!/bin/bash
# 文件：point_in_time_recovery.sh
# 功能：MySQL时间点恢复脚本
# 用法：直接执行，修改配置中的恢复时间和目录
RECOVERY_TIME="2024-01-15 14:30:00" # 目标恢复时间
BACKUP_DIR="/backup/mysql/full/20240115" # 最近的全量备份目录
BINLOG_DIR="/data/mysql/binlog" # binlog目录
RECOVERY_DIR="/data/mysql_recovery" # 恢复临时目录
# 第一步：准备备份（解压+应用redo log）
echo "Preparing backup..."
cp -r ${BACKUP_DIR} ${RECOVERY_DIR}
# 解压压缩的备份文件（如果是压缩备份）
cd ${RECOVERY_DIR}
for f in *.qp; do
qpress -d $f ./
done
# 准备备份，确保数据一致性
xtrabackup --prepare --target-dir=${RECOVERY_DIR}
# 第二步：获取备份的binlog位置（从备份文件中提取）
BINLOG_INFO=$(cat ${RECOVERY_DIR}/xtrabackup_binlog_info)
BINLOG_FILE=$(echo $BINLOG_INFO | awk '{print $1}')
BINLOG_POS=$(echo $BINLOG_INFO | awk '{print $2}')
echo "Backup binlog position: ${BINLOG_FILE}:${BINLOG_POS}"
# 第三步：找到需要应用的binlog文件
BINLOG_FILES=$(ls ${BINLOG_DIR}/mysql-bin.* | sort | awk -v start="${BINLOG_FILE}" '$0 >= start')
# 第四步：应用binlog到指定时间点
echo "Applying binlog until ${RECOVERY_TIME}..."
mysqlbinlog ${BINLOG_FILES} \
--start-position=${BINLOG_POS} \
--stop-datetime="${RECOVERY_TIME}" \
> ${RECOVERY_DIR}/binlog_recovery.sql
# 第五步：恢复数据到生产环境
echo "Stopping MySQL..."
systemctl stop mysqld
# 备份当前生产数据目录（安全起见，防止恢复失败）
mv /data/mysql/data /data/mysql/data_old_$(date +%Y%m%d_%H%M%S)
# 恢复备份
xtrabackup --copy-back --target-dir=${RECOVERY_DIR}
chown -R mysql:mysql /data/mysql/data # 设置权限
# 启动MySQL
systemctl start mysqld
# 应用binlog增量，完成时间点恢复
echo "Applying binlog recovery..."
mysql -uroot -p < ${RECOVERY_DIR}/binlog_recovery.sql
echo "Point-in-time recovery completed to ${RECOVERY_TIME}"
案例三：大表备份与恢复（500GB+）
场景：orders表有5亿条数据，单表500GB，直接备份/恢复会卡死，用分区导出导入，提升效率。
# 备份脚本：large_table_backup.sh（分区导出）
#!/bin/bash
TABLE="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 <= ${END_ID}" \
${DB} ${TABLE} | gzip > ${BACKUP_DIR}/${DATE}/${TABLE}_part${i}.sql.gz &
done
# 等待所有导出任务完成
wait
# 单独导出表结构
mysqldump -ubackup -p'BackupPass@2024' \
--no-data \
${DB} ${TABLE} > ${BACKUP_DIR}/${DATE}/${TABLE}_schema.sql
echo "Large table backup completed"
# 恢复脚本：large_table_restore.sh（并行导入）
#!/bin/bash
DB="ecommerce"
TABLE="orders"
BACKUP_DIR="/backup/mysql/large_tables/20240115"
PARALLEL_JOBS=4 # 并行导入线程数
# 先导入表结构
mysql -uroot -p ${DB} < ${BACKUP_DIR}/${TABLE}_schema.sql
# 禁用索引（加速导入，导入完成后再重建）
mysql -uroot -p -e "ALTER TABLE ${DB}.${TABLE} DISABLE KEYS"
# 并行导入数据（控制线程数，避免MySQL压力过大）
for f in ${BACKUP_DIR}/${TABLE}_part*.sql.gz; do
echo "Importing ${f}..."
gunzip -c $f | mysql -uroot -p ${DB} &
# 控制
浙公网安备 33010602011771号