BridgeLi's Blog

作为开发，我们都知道数据备份的重要性，而数据备份最重要的就是数据库备份，前一段时间由于操作失误，误删过一次数据库，所以特把备份和恢复脚本分享出来，作为笔记。

1. 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

2. 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 "=================================="

3. 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 ==="

4. 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

5. my.cnf 文件（600 权限）

[client]
user=用户名
password=密码
host=localhost
port=3306

6. 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

7. 如果报：bash: ./backup_per_db.sh: /bin/bash^M: 坏的解释器: 没有那个文件或目录，解决方案：

使用 vim 编辑器

vim backup_per_db.sh

在命令模式下执行：

:set fileformat=unix
:wq