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