作为开发，我们都知道数据备份的重要性，而数据备份最重要的就是数据库备份，前一段时间由于操作失误，误删过一次数据库，所以特把备份和恢复脚本分享出来，作为笔记。
- MySQL 备份脚本
#!/bin/bash
\# ===================================================================
\# MySQL 分库全量备份脚本（生产级 | 自适应 –source-data / –master-data）
\# 功能：
\# – 自动发现用户数据库
\# – 每库独立压缩备份
\# – 自动选择 –source-data (8.0+) 或 –master-data (5.7)
\# – 智能处理 GTID（仅在启用时设置）
\# – 提取 binlog 位置生成 .info 文件
\# – 清理 N 天前旧备份
\# 作者：BridgeLi
\# 版本：1.0
\# ===================================================================
\# ——————————-
\# 配置区
\# ——————————-
BACKUP_DIR="/project/backup/mysql/dbs"
CNF_FILE="/project/backup/mysql/my.cnf"
RETENTION_DAYS=7
MIN_FREE_SPACE_GB=5
HOSTNAME=$(hostname -s)
DT=$(date +%Y-%m-%d_%H%M%S)
\# 排除系统库
EXCLUDED_DBS="^(mysql|sys|information_schema|performance_schema)$"
\# ——————————-
\# 初始化 & 依赖检查
\# ——————————-
for cmd in mysql mysqldump gzip gunzip df date awk sed; do
command -v "$cmd" >/dev/null || { echo "错误：缺少命令 ‘$cmd’"; exit 1; }
done
mkdir -p "$BACKUP_DIR" || { echo "错误：无法创建目录 $BACKUP_DIR"; exit 1; }
LOG_FILE="$BACKUP_DIR/backup.log"
LOCK_FILE="$BACKUP_DIR/.backup.lock"
if [ -f "$LOCK_FILE" ]; then
echo "[$(date +’%Y-%m-%d %H:%M:%S’)] 错误：锁文件存在，可能已有备份在运行。"
exit 1
fi
trap "rm -f ‘$LOCK_FILE’" EXIT
touch "$LOCK_FILE"
AVAILABLE_GB=$(df -P "$BACKUP_DIR" | tail -1 | awk ‘{print int($4/1024/1024)}’)
if [ "$AVAILABLE_GB" -lt "$MIN_FREE_SPACE_GB" ]; then
echo "[$(date +’%Y-%m-%d %H:%M:%S’)] 错误：磁盘空间不足 (${AVAILABLE_GB}GB < ${MIN_FREE_SPACE_GB}GB)"
exit 1
fi
exec > >(tee -a "$LOG_FILE") 2>&1
echo "\[$(date +’%Y-%m-%d %H:%M:%S’)\] \[$HOSTNAME\] 开始全量分库备份…"
\# ——————————-
\# 检测 MySQL 版本和 GTID 状态
\# ——————————-
MYSQL_CMD="mysql –defaults-extra-file=$CNF_FILE -sN"
\# 获取 MySQL 主版本（57, 80）
MYSQL_VERSION=$($MYSQL_CMD -e "SELECT REPLACE(LEFT(VERSION(), 4), ‘.’, ”);")
if ! [[ "$MYSQL_VERSION" =~ ^[0-9]+$ ]]; then
echo "[$(date +’%Y-%m-%d %H:%M:%S’)] 错误：无法获取 MySQL 版本"
exit 1
fi
\# 选择 source-data / master-data
if [ "$MYSQL_VERSION" -ge 80 ]; then
REPLICATION_OPT="–source-data=2"
else
REPLICATION_OPT="–master-data=2"
fi
\# 检查 GTID 是否启用
GTID_MODE=$($MYSQL_CMD -e "SELECT @@GLOBAL.gtid_mode;" 2>/dev/null || echo "OFF")
if [[ "$GTID_MODE" =~ ^(ON|ON_PERMISSIVE|OFF_PERMISSIVE)$ ]]; then
GTID_PURGED_OPT="–set-gtid-purged=ON"
GTID_ENABLED=true
else
GTID_PURGED_OPT="–set-gtid-purged=OFF"
GTID_ENABLED=false
fi
echo "[$(date +’%Y-%m-%d %H:%M:%S’)] MySQL 版本: $MYSQL_VERSION, 使用: $REPLICATION_OPT, GTID: ${GTID_MODE:-OFF}"
\# ——————————-
\# 获取所有非系统数据库
\# ——————————-
dbs=()
while IFS= read -r db; do
[[ -z "$db" ]] && continue
if [[ ! "$db" =~ $EXCLUDED_DBS ]]; then
dbs+=("$db")
fi
done < <($MYSQL_CMD -e "SHOW DATABASES;" 2>>"$LOG_FILE")
if [ ${#dbs[@]} -eq 0 ]; then
echo "[$(date +’%Y-%m-%d %H:%M:%S’)] 警告：未找到可备份的数据库。"
exit 0
fi
echo "[$(date +’%Y-%m-%d %H:%M:%S’)] 发现 ${#dbs[@]} 个数据库: ${dbs[*]}"
\# ——————————-
\# 执行备份
\# ——————————-
SUCCESS_COUNT=0
FAILURE_COUNT=0
START_TIME=$(date +%s)
for db in "${dbs[@]}"; do
DUMP_FILE="${db}-${DT}.sql.gz"
DUMP_PATH="$BACKUP_DIR/$DUMP_FILE"
INFO_PATH="${DUMP_PATH%.gz}.info"
echo "[$(date +’%Y-%m-%d %H:%M:%S’)] 正在备份: $db -> $DUMP_PATH"
mysqldump –defaults-extra-file="$CNF_FILE" \
–single-transaction \
–routines \
–triggers \
$REPLICATION_OPT \
$GTID_PURGED_OPT \
–databases "$db" 2>>"$LOG_FILE" | gzip -c > "$DUMP_PATH"
if [ $? -eq 0 ] && [ -s "$DUMP_PATH" ] && gunzip -t "$DUMP_PATH" >/dev/null 2>&1; then
\# 提取 binlog 位置（source/master 兼容）
read master_file master_pos < <(gzip -dc "$DUMP_PATH" | sed -n "/^– CHANGE MASTER TO / s/.\*LOG_FILE=’\\([^’]\*\\)’,.\*LOG_POS=\\([0-9]\*\\).*/\\1 \\2/p" | head -1)
\# 提取 GTID（仅当启用）
if [ "$GTID_ENABLED" = true ]; then
gtid_purged=$(gzip -dc "$DUMP_PATH" | sed -n "s/^SET @@GLOBAL.GTID_PURGED=’\\([^’]*\)’;\$/\\1/p" | head -1)
[ -z "$gtid_purged" ] && gtid_purged="NONE"
else
gtid_purged="DISABLED"
fi
\# 写入 .info
{
[ -n "$master_file" ] && echo "File: $master_file"
[ -n "$master_pos" ] && echo "Position: $master_pos"
echo "GTID: $gtid_purged"
} > "$INFO_PATH"
chmod 600 "$DUMP_PATH" "$INFO_PATH" 2>/dev/null || true
echo "[$(date +’%Y-%m-%d %H:%M:%S’)] 成功: $db (binlog: $master_file, pos: $master_pos, gtid: $gtid_purged)"
((SUCCESS_COUNT++))
else
echo "[$(date +’%Y-%m-%d %H:%M:%S’)] 失败: $db"
rm -f "$DUMP_PATH" "$INFO_PATH"
((FAILURE_COUNT++))
fi
done
\# ——————————-
\# 清理 N 天前的旧备份
\# ——————————-
if [ $FAILURE_COUNT -eq 0 ]; then
cleanup_old() {
local pattern="$1"
local now_ts=$(date +%s)
local files=()
mapfile -t files < <(find "$BACKUP_DIR" -name "$pattern" -type f 2>/dev/null)
for file in "${files[@]}"; do
if [[ "$file" =~ -([0-9]{4})-([0-9]{2})-([0-9]{2})_([0-9]{2})([0-9]{2})([0-9]{2})\. ]]; then
\# 提取各部分：年、月、日、时、分、秒
local y=${BASH_REMATCH[1]}
local m=${BASH_REMATCH[2]}
local d=${BASH_REMATCH[3]}
local H=${BASH_REMATCH[4]}
local M=${BASH_REMATCH[5]}
local S=${BASH_REMATCH[6]}
\# 构造合法日期字符串：2025-09-25 02:00:01
local datetime="$y-$m-$d $H:$M:$S"
\# 转换为时间戳
local file_ts=$(date -d "$datetime" +%s 2>/dev/null) || continue
local age_days=$(( (now_ts – file_ts) / 86400 ))
if [ $age_days -ge $RETENTION_DAYS ]; then
echo "[$(date +’%Y-%m-%d %H:%M:%S’)] 清理过期文件: $file (已存在 $age_days 天)"
rm -f "$file"
fi
fi
done
}
cleanup_old "*.sql.gz"
cleanup_old "*.info"
END_TIME=$(date +%s)
echo "[$(date +’%Y-%m-%d %H:%M:%S’)] 备份完成: 成功 $SUCCESS_COUNT，失败 $FAILURE_COUNT，耗时 $((END_TIME – START_TIME)) 秒"
else
echo "[$(date +’%Y-%m-%d %H:%M:%S’)] 警告：有 $FAILURE_COUNT 个数据库备份失败，跳过清理。"
exit 1
fi
exit 0
- MySQL binlog 备份脚本：
#!/bin/bash
\# ===================================================================
\# MySQL Binlog 增量备份脚本（优化版）
\# 配合全量备份实现 PITR
\# 作者：BridgeLi
\# 版本：1.0
\# ===================================================================
\# 配置参数
BINLOG_DIR="/var/lib/mysql"
BACKUP_DIR="/project/backup/mysql/binlogs"
CNF_FILE="/project/backup/mysql/my.cnf"
LOG_FILE="$BACKUP_DIR/binlog_backup.log"
LOCK_FILE="$BACKUP_DIR/.backup.lock"
LAST_COPIED_FILE="$BACKUP_DIR/.last_binlog"
\# 创建备份目录
mkdir -p "$BACKUP_DIR" || { echo "[$(date)] 错误：无法创建备份目录 $BACKUP_DIR" >&2; exit 1; }
\# 使用 flock 防止并发执行
exec 200>"$LOCK_FILE"
if ! flock -n 200; then
echo "[$(date)] 错误：备份脚本已在运行，退出。" | tee -a "$LOG_FILE"
exit 1
fi
\# 重定向所有输出到日志
exec >> "$LOG_FILE" 2>&1
echo "=================================="
echo "[$(date +’%Y-%m-%d %H:%M:%S’)] 开始 binlog 增量备份…"
\# 获取当前活跃 binlog
CURRENT_LOG=$(mysql –defaults-extra-file="$CNF_FILE" -sN -e "SHOW MASTER STATUS;" 2>/dev/null | awk ‘{print $1}’)
if [ -z "$CURRENT_LOG" ]; then
echo "[$(date +’%Y-%m-%d %H:%M:%S’)] 错误：无法获取当前 binlog 名称，请检查 MySQL 连接或权限。"
exit 1
fi
echo "当前活跃 binlog: $CURRENT_LOG"
\# 读取上次备份的 binlog
if [[ -f "$LAST_COPIED_FILE" ]]; then
LAST_LOG=$(cat "$LAST_COPIED_FILE")
echo "上次已备份至: $LAST_LOG"
else
LAST_LOG=""
echo "首次运行，将备份所有历史 binlog（除当前外）。"
fi
COPIED=0
cd "$BINLOG_DIR" || { echo "无法进入 binlog 目录: $BINLOG_DIR"; exit 1; }
\# 获取所有 binlog 文件并按版本排序
mapfile -t LOGS < <(find . -maxdepth 1 -name ‘mysql-bin.*’ -type f -printf ‘%f\n’ | sort -V)
for log in "${LOGS[@]}"; do
[[ ! -f "$log" ]] && continue
\# 跳过当前活跃的 binlog
[[ "$log" == "$CURRENT_LOG" ]] && continue
\# 判断是否需要备份：log > LAST_LOG（版本排序）
if [[ -n "$LAST_LOG" ]]; then
\# 使用 sort -V 判断顺序
greater=$(printf ‘%s\n%s’ "$LAST_LOG" "$log" | sort -V | tail -1)
if [[ "$greater" != "$log" || "$log" == "$LAST_LOG" ]]; then
continue
fi
fi
\# 执行压缩备份
if gzip -c "$log" > "$BACKUP_DIR/${log}.gz"; then
echo "[$(date +’%Y-%m-%d %H:%M:%S’)] 已压缩备份: $log"
((COPIED++))
else
echo "[$(date +’%Y-%m-%d %H:%M:%S’)] 错误：备份失败 $log"
fi
done
\# 原子更新最后备份的 binlog
echo "$CURRENT_LOG" > "${LAST_COPIED_FILE}.tmp" && mv "${LAST_COPIED_FILE}.tmp" "$LAST_COPIED_FILE"
echo "[$(date +’%Y-%m-%d %H:%M:%S’)] binlog 增量备份完成，共复制 $COPIED 个文件。"
echo "=================================="
- MySQL 健康检查脚本（非必需，只需要备份就行）
#!/bin/bash
\# ===================================================================
\# MySQL 备份健康检查脚本（优化生产版 – 已修复）
\# 功能：检查最近备份时效、磁盘使用率，记录日志，发送告警，输出 Prometheus 指标
\# 作者：BridgeLi
\# 版本：1.0
\# ===================================================================
\# ——————————-
\# 配置参数
\# ——————————-
BACKUP_DIR="/project/backup/mysql/dbs"
LOG_DIR="/var/log/mysql"
LOG_FILE="$LOG_DIR/health_check.log"
ALERT_EMAIL="admin@example.com"
HOSTNAME=$(hostname -s)
\# Prometheus 指标输出路径
PROM_FILE="/tmp/backup_health.prom"
PROM_TMP_FILE="/tmp/backup_health.prom.tmp"
\# 告警阈值（小时）
MAX_BACKUP_AGE_HOURS=26
DISK_WARN_THRESHOLD=80
DISK_CRIT_THRESHOLD=90
\# ——————————-
\# 创建日志目录（避免 tee 报错）
\# ——————————-
if [ ! -d "$LOG_DIR" ]; then
mkdir -p "$LOG_DIR" && chmod 755 "$LOG_DIR"
[ $? -ne 0 ] && echo "ERROR: Cannot create log directory $LOG_DIR" && exit 1
fi
\# ——————————-
\# 重定向输出：同时输出到日志和终端
\# ——————————-
exec > >(tee -a "$LOG_FILE") 2>&1
\# ——————————-
\# 检查最近一次备份是否在合理时间内
\# 输出：状态信息
\# 返回值：
\# 0 = OK
\# 1 = CRITICAL（超时）
\# 2 = ERROR（无备份）
\# ——————————-
check_last_backup() {
local latest_entry=$(find "$BACKUP_DIR" -name "*.sql.gz" -type f -printf ‘%T@ %p\n’ 2>/dev/null | sort -n | tail -1)
if [ -z "$latest_entry" ]; then
echo "ERROR no_recent_backup"
return 2
fi
\# 提取时间戳（取整数部分）
local mtime_epoch=$(echo "$latest_entry" | awk ‘{split($1,a,"."); print a[1]}’)
local now_epoch=$(date +%s)
local age_seconds=$((now_epoch – mtime_epoch))
local age_hours=$((age_seconds / 3600))
if [ $age_hours -gt $MAX_BACKUP_AGE_HOURS ]; then
echo "CRITICAL backup_too_old $age_hours hours"
return 1
else
echo "OK last_backup $age_hours hours ago"
return 0
fi
}
\# ——————————-
\# 检查备份目录所在磁盘使用率
\# 返回值：
\# 0 = OK (<80%)
\# 1 = CRITICAL (>90%)
\# 2 = WARN (80%~90%)
\# 输出：状态信息
\# ——————————-
check_disk_usage() {
if [ ! -d "$BACKUP_DIR" ]; then
echo "ERROR backup_dir_not_found: $BACKUP_DIR"
return 1
fi
local df_out=$(df -P "$BACKUP_DIR" 2>/dev/null)
if [ -z "$df_out" ]; then
echo "ERROR disk_check_failed"
return 1
fi
local used_percent=$(echo "$df_out" | tail -1 | awk ‘{print $5}’ | tr -d ‘%’)
if ! [[ "$used_percent" =~ ^[0-9]+$ ]]; then
echo "ERROR disk_usage_invalid: $used_percent"
return 1
fi
if [ $used_percent -gt $DISK_CRIT_THRESHOLD ]; then
echo "CRITICAL disk_usage ${used_percent}%"
return 1
elif [ $used_percent -gt $DISK_WARN_THRESHOLD ]; then
echo "WARN disk_usage ${used_percent}%"
return 2
else
echo "OK disk_usage ${used_percent}%"
return 0
fi
}
\# ——————————-
\# 发送告警邮件
\# ——————————-
send_alert() {
local subject="$1"
local body="$2"
if command -v mail >/dev/null 2>&1; then
echo -e "$body" | mail -s "$subject" "$ALERT_EMAIL"
echo "Alert sent to $ALERT_EMAIL"
else
echo "WARNING: ‘mail’ command not available. Skipping alert."
logger "MySQL Backup Alert: $subject | $body"
fi
}
\# ——————————-
\# 主逻辑开始
\# ——————————-
echo "=== Backup Health Check ($(date)) on $HOSTNAME ==="
\# 执行检查，捕获输出和返回值（只执行一次！）
output1=$(check_last_backup)
res1=$?
echo "$output1"
output2=$(check_disk_usage)
res2=$?
echo "$output2"
\# 判断是否需要告警
alert_needed=false
if [ $res1 -eq 1 ] || [ $res1 -eq 2 ] || [ $res2 -eq 1 ]; then
alert_needed=true
fi
\# 发送告警
if [ "$alert_needed" = true ]; then
subject="⚠️ MySQL 备份异常 – $HOSTNAME"
body="【备份状态】$output1\n【磁盘状态】$output2\n\n请立即检查备份目录：$BACKUP_DIR"
send_alert "$subject" "$body"
else
echo "All checks OK."
fi
\# ——————————-
\# 输出 Prometheus 指标（原子写入）
\# ——————————-
(
echo "# HELP mysql_backup_last_success_age_hours Age of last successful backup in hours, -1 if none"
echo "# TYPE mysql_backup_last_success_age_hours gauge"
local latest_entry=$(find "$BACKUP_DIR" -name "*.sql.gz" -type f -printf ‘%T@ %p\n’ 2>/dev/null | sort -n | tail -1)
if [ -z "$latest_entry" ]; then
echo "mysql_backup_last_success_age_hours -1"
else
local mtime_epoch=$(echo "$latest_entry" | awk ‘{split($1,a,"."); print a[1]}’)
local now=$(date +%s)
local age_hours=$(( (now – mtime_epoch) / 3600 ))
echo "mysql_backup_last_success_age_hours $age_hours"
fi
echo ""
echo "# HELP mysql_backup_disk_usage_percent Disk usage of the backup partition (%)"
echo "# TYPE mysql_backup_disk_usage_percent gauge"
df -P "$BACKUP_DIR" 2>/dev/null | tail -1 | awk ‘{gsub(/%/,"",$5); print "mysql_backup_disk_usage_percent", $5}’
) > "$PROM_TMP_FILE" && mv "$PROM_TMP_FILE" "$PROM_FILE"
if [ $? -eq 0 ]; then
echo "Prometheus metrics written to $PROM_FILE"
else
echo "ERROR: Failed to write Prometheus metrics"
fi
echo "=== Check completed ==="
- MySQL 恢复脚本
#!/bin/bash
\# ===================================================================
\# MySQL Point-in-Time Recovery (PITR) 脚本
\# 功能：基于全量备份 + binlog 恢复到指定时间点
\# 作者：BridgeLi
\# 版本：1.0
#
\# 用法：
\# ./mysql-pitr-restore.sh "2025-09-24 10:00:00" [数据库名]
\# ./mysql-pitr-restore.sh –dry-run "2025-09-24 10:00:00" [db_name]
\# ./mysql-pitr-restore.sh –help
#
\# 依赖：
\# mysql, mysqlbinlog, gzip, find, sort
\# ===================================================================
set -euo pipefail # 严格模式：出错即退出
\# —————————-
\# 默认配置
\# —————————-
BACKUP_DIR="/project/backup/mysql/dbs"
BINLOG_DIR="/project/backup/mysql/binlogs"
RESTORE_DIR="/tmp/mysql_restore_$$" # 使用 PID 避免冲突
CNF_FILE="/project/backup/mysql/my.cnf"
LOG_FILE="$BACKUP_DIR/restore.log"
DRY_RUN=0
DEBUG=0
TARGET_TIME=""
TARGET_DB=""
\# —————————-
\# 函数定义
\# —————————-
usage() {
cat << ‘EOF’
用法: ./mysql-pitr-restore.sh [选项] <目标时间 ‘YYYY-MM-DD HH:MM:SS’> [数据库名]
选项:
–dry-run 模拟执行，不真正恢复数据
–debug 启用调试输出
–help 显示此帮助信息
示例:
./mysql-pitr-restore.sh "2025-09-24 10:00:00"
./mysql-pitr-restore.sh –dry-run "2025-09-24 10:00:00" mydb
./mysql-pitr-restore.sh –debug "2025-09-24 12:30:00"
注意:
– 全量备份文件需包含 CHANGE MASTER TO 语句以提取 binlog 位置
– binlog 文件需为 .gz 压缩格式，命名如 mysql-bin.000001.gz
– 恢复前请确保数据库无写入操作！
EOF
}
log() {
local level="${1}"
shift
echo "\[$(date ‘+%F %T’)\] \[$level\] $*" | tee -a "$LOG_FILE"
}
debug() {
[[ $DEBUG -eq 1 ]] && log "DEBUG" "$@"
}
cleanup() {
if [[ -d "$RESTORE_DIR" ]]; then
debug "正在清理临时目录: $RESTORE_DIR"
rm -rf "$RESTORE_DIR"
fi
}
trap cleanup EXIT
confirm_proceed() {
log "WARN" "即将开始恢复至时间点: $TARGET_TIME"
if [[ -n "$TARGET_DB" ]]; then
log "INFO" "仅恢复数据库: $TARGET_DB"
fi
log "WARN" "请确保 MySQL 当前无写入操作，否则可能导致数据不一致！"
read -p "确定继续？[y/N]: " -n 1 -r
echo
if [[ ! $REPLY =~ ^[Yy]$ ]]; then
log "INFO" "用户取消操作"
exit 1
fi
}
find_latest_full_backup() {
local latest_file=""
local latest_time=""
local file time_str backup_time
for file in "$BACKUP_DIR"/*.sql{,.gz}; do
[[ -f "$file" ]] || continue
\# 提取时间戳：匹配 -YYYY-MM-DD_HHMMSS.sql 或 .sql.gz
if [[ "$file" =~ -([0-9]{4}-[0-9]{2}-[0-9]{2}_[0-9]{6})\.sql(\.gz)?$ ]]; then
time_str="${BASH_REMATCH[1]}"
backup_time="${time_str:0:10} ${time_str:11:2}:${time_str:13:2}:${time_str:15:2}"
debug "发现备份: $file -> 时间: $backup_time"
if [[ "$backup_time" < "$TARGET_TIME" ]]; then
if [[ -z "$latest_time" || "$backup_time" > "$latest_time" ]]; then
latest_file="$file"
latest_time="$backup_time"
fi
fi
else
debug "跳过不匹配的文件: $file"
fi
done
if [[ -z "$latest_file" ]]; then
log "ERROR" "未找到早于 $TARGET_TIME 的全量备份"
return 1
fi
echo "$latest_file|$latest_time"
}
extract_binlog_position() {
local backup_file="$1"
local content_cmd="gzip -dc" # 默认是 .gz
[[ "$backup_file" == *.sql ]] && content_cmd="cat"
local line
line=$(eval "$content_cmd" "$backup_file" | sed -n "s/.\*CHANGE MASTER TO MASTER_LOG_FILE=’\([^’]\*\)’,.\*, MASTER_LOG_POS=\([0-9]\*\).*/\1 \2/p" | head -1)
if [[ -z "$line" ]]; then
log "ERROR" "无法从备份中提取 binlog 位置信息，请检查是否启用 –master-data=2"
return 1
fi
echo "$line"
}
\# —————————-
\# 参数解析
\# —————————-
while [[ $# -gt 0 ]]; do
case $1 in
–dry-run)
DRY_RUN=1
shift
;;
–debug)
DEBUG=1
shift
;;
–help)
usage
exit 0
;;
-*)
log "ERROR" "未知选项: $1"
usage
exit 1
;;
*)
break
;;
esac
done
if [[ $# -lt 1 ]]; then
log "ERROR" "缺少目标时间参数"
usage
exit 1
fi
TARGET_TIME="$1"
if [[ ! "$TARGET_TIME" =~ ^[0-9]{4}-[0-9]{2}-[0-9]{2}\ [0-9]{2}:[0-9]{2}:[0-9]{2}$ ]]; then
log "ERROR" "时间格式无效，应为 ‘YYYY-MM-DD HH:MM:SS’"
exit 1
fi
TARGET_DB="${2:-}"
\# 设置日志输出
exec > >(tee -a "$LOG_FILE") 2>&1
\# —————————-
\# 主流程
\# —————————-
log "INFO" "开始 PITR 恢复流程，目标时间: $TARGET_TIME"
if (( DRY_RUN )); then
log "DRYRUN" "运行在模拟模式 (–dry-run)，不会执行实际恢复"
fi
\# 1. 查找最接近的全量备份
log "INFO" "正在查找最接近且早于 $TARGET_TIME 的全量备份…"
result=$(find_latest_full_backup)
if [[ $? -ne 0 ]]; then
exit 1
fi
FULL_BACKUP=$(echo "$result" | cut -d’|’ -f1)
FULL_TIME=$(echo "$result" | cut -d’|’ -f2)
log "INFO" "使用全量备份: $FULL_BACKUP (时间: $FULL_TIME)"
\# 2. 用户确认
if (( !DRY_RUN )); then
confirm_proceed
fi
\# 3. 恢复全量数据
log "INFO" "开始导入全量数据…"
if (( DRY_RUN )); then
log "DRYRUN" "将导入: $FULL_BACKUP"
else
mkdir -p "$RESTORE_DIR"
if [[ "$FULL_BACKUP" == *.gz ]]; then
if gzip -dc "$FULL_BACKUP" | mysql –defaults-extra-file="$CNF_FILE"; then
log "INFO" "全量恢复成功"
else
log "ERROR" "全量导入失败"
exit 1
fi
else
if mysql –defaults-extra-file="$CNF_FILE" < "$FULL_BACKUP"; then
log "INFO" "全量恢复成功"
else
log "ERROR" "全量导入失败"
exit 1
fi
fi
fi
\# 4. 提取 binlog 起始位置
log "INFO" "提取 binlog 起始位置…"
position_line=$(extract_binlog_position "$FULL_BACKUP")
if [[ $? -ne 0 ]]; then
exit 1
fi
read -r START_FILE START_POS <<< "$position_line"
log "INFO" "从 binlog 开始应用: $START_FILE, 位置: $START_POS"
\# 5. 应用 binlog 到目标时间
log "INFO" "开始应用 binlog 增量日志…"
applied=0
for binlog_gz in $(find "$BINLOG_DIR" -name "*.gz" | sort); do
local binlog_base
binlog_base=$(basename "$binlog_gz" .gz)
\# 跳过早于起始文件的日志
if [[ "$binlog_base" < "$START_FILE" ]]; then
continue
fi
log "INFO" "处理 binlog: $binlog_base"
\# 解压到临时目录
mkdir -p "$RESTORE_DIR/binlogs"
local tmp_binlog="$RESTORE_DIR/binlogs/$binlog_base"
if (( DRY_RUN )); then
log "DRYRUN" "将解压并应用: $binlog_gz -> $tmp_binlog"
applied=1
continue
fi
gzip -dc "$binlog_gz" > "$tmp_binlog"
\# 构建 mysqlbinlog 命令
local mysqlbinlog_cmd=(
mysqlbinlog
–start-position="$START_POS"
–stop-datetime="$TARGET_TIME"
"${TARGET_DB:+–database=$TARGET_DB}"
"$tmp_binlog"
)
local mysql_cmd=(mysql –defaults-extra-file="$CNF_FILE")
debug "执行命令: ${mysqlbinlog_cmd[\*]} | ${mysql_cmd[\*]}"
if "${mysqlbinlog_cmd[@]}" | "${mysql_cmd[@]}"; then
log "INFO" "成功应用 binlog: $binlog_base"
applied=1
else
local ret=$?
log "INFO" "完成 binlog 应用（可能已到达目标时间或中断），返回码: $ret"
applied=1
break # 关键：不再处理后续 binlog
fi
rm -f "$tmp_binlog"
START_POS=4 # 下一个文件从事件头后开始
done
\# 6. 结果汇报
if (( applied == 0 )); then
log "WARN" "未应用任何 binlog，请检查 binlog 是否存在、时间范围是否合理"
fi
if (( DRY_RUN )); then
log "DRYRUN" "模拟执行结束。真实恢复请移除 –dry-run 参数。"
else
log "INFO" "恢复完成：已恢复至 $TARGET_TIME"
log "INFO" "请立即验证数据一致性，并检查关键业务逻辑。"
fi
exit 0
- my.cnf 文件（600 权限）
[client]
user=用户名
password=密码
host=localhost
port=3306
- crontab 表达式
\# 每天 2:00 全量备份
0 2 \* \* * /project/backup/mysql/backup_per_db.sh
\# 每小时 0 分 增量 binlog
0 \* \* \* \* /project/backup/mysql/backup_binlog.sh
\# 每天 3:10 健康检查
10 3 \* \* * /project/backup/mysql/check_backup_health.sh
- 如果报：bash: ./backup_per_db.sh: /bin/bash^M: 坏的解释器: 没有那个文件或目录，解决方案：
使用 vim 编辑器
vim backup_per_db.sh
在命令模式下执行：
:set fileformat=unix
:wq