暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / MySQL数据库逻辑备份方案

MySQL数据库逻辑备份方案

原创 雄霸天下 2025-08-29
320

企业级MySQL数据库逻辑备份方案，使用mydumper实现。

支持：

  • ✅ 支持使用代理进行远程备份，例如k8s中的mysql docker容器
  • ✅ 支持多实例备份
  • ✅ 每个实例独立的备份策略（保留天数、线程数、压缩、后处理等）
  • ✅ 支持指定要备份的库
  • ✅ 支持 后处理脚本（如上传 S3、发送通知）
  • ✅ 支持多线程
  • ✅ 支持at+systemd调度

 备份策略：（配套的 instance_policies.txt 示例）

root@mysql-shell-backup:/data/mydumper_backup# cat instance_policies.txt
# =============================================================================
# MySQL 实例备份策略配置列表
# ----------------------- 支持的高级策略 -----------------------
#策略           说明
#----------     ------------------------------------------------
#差异化保留     核心业务保留 90 天，测试库保留 3 天
#动态线程数     大库用 12 线程，小库用 4 线程
#条件压缩       大表开启 --compress，小表不压缩
#分块大小       --chunk-filesize=500 控制单个文件大小
#长查询保护     --long-query-guard=2000 毫秒
#后处理脚本     例如：备份后自动上传 S3、发送钉钉通知、校验完整性
# =============================================================================

# 格式：别名|保留天数|线程数|压缩(yes/no)|额外参数|后处理脚本|执行时间(HH:MM)
# 使用 "|" 分隔，空值留空

# 生产核心业务：保留90天，高线程，压缩，上传S3
#prod_order|90|8|yes|"--compress --ssl"|/scripts/upload_to_s3.sh|00:00

# 用户库：保留30天，中等线程，压缩
#prod_user|30|6|yes|""|/scripts/send_backup_notify.sh|01:00

# 大库：保留60天，高线程，压缩，分块
#large_db|60|12|yes|"--compress --chunk-filesize=1000"|/scripts/archive_and_upload.sh|02:00

# 预发布：保留7天，低线程，压缩
#staging|7|4|yes|""|/scripts/cleanup_staging.sh|03:00

# 开发库：保留3天，不压缩（节省CPU）
#dev_master|3|2|no|""|""|04:00  # 无后处理

# 测试库：保留1天
#test_db|1|4|yes|""|""|00:00

# ============================以上是配置示例===================================

# ----------------------- 测试环境 -----------------------
# CC测试库:保留7天，6线程，不压缩，无额外参数|无后处理脚本|凌晨1点执行
test_cc|7|6|no|""|""|01:00


# ----------------------- 注意事项 -----------------------
# 1. 本文件应设置为 600 权限：chmod 600 instance_policies.txt
# 2. 压缩为yes的时候，额外参数不用加 --compress
# --------------------------------------------------------------

✅ 示例：后处理脚本 /scripts/upload_to_s3.sh （后处理这块没做测试了，大家有兴趣可以动手看看）

#!/bin/bash
# upload_to_s3.sh

BACKUP_DIR="$1"
ALIAS="$2"
HOST="$3"
PORT="$4"

# 上传到 S3
aws s3 sync "$BACKUP_DIR" "s3://your-backup-bucket/$ALIAS/" \
    --region ap-northeast-1 \
    --quiet

if [ $? -eq 0 ]; then
    echo "Backup $BACKUP_DIR uploaded to S3 successfully." >> "$BACKUP_DIR/upload.log"
    exit 0
else
    echo "Failed to upload $BACKUP_DIR to S3." >> "$BACKUP_DIR/upload.log"
    exit 1
fi



 备份配置：（配套的 instance_.txt 示例）

root@mysql-shell-backup:/usr/local/bin# cat /data/mydumper_backup/instance_list.txt
# =============================================================================
# MySQL 实例备份配置列表
# 格式：别名|主机IP|端口|用户名|密码|备份库列表（可选）
#
# 密码中的特殊字符（如 #, $, !, @）在 .cnf 文件中会自动用引号包裹
# 备份库列表为空表示备份该实例上的所有数据库
# 使用 "|" 作为分隔符，避免库名中使用 |
# =============================================================================

# ----------------------- 生产环境 -----------------------
# 订单系统数据库
#prod_order_db|172.16.100.10|3306|bak_user|P@ssw0rd#2025!|orders,payments,inventory

# 用户中心数据库
#prod_user_db|172.16.100.11|3306|backup|Secure$Pass2024|users,profiles,sessions

# 支付系统数据库（全备）
#prod_payment|172.16.100.12|3307|mydumper|MyDumper!2024||

# ----------------------- 预发布环境 -----------------------
# Staging 数据库
#staging_db|172.16.100.20|3306|stg_bak|Staging#Pass123|app_db,log_db

# ----------------------- 开发环境 -----------------------
# 开发主库
#dev_master|172.16.100.30|3306|dev_bak|Dev@2024#Test|dev_orders,dev_users

# 开发从库（只读账号）
dev_slave|172.16.100.31|3306|ro_bak|ReadOnly$2024||


# ----------------------- 特殊情况示例 -----------------------
# 密码包含引号（虽然不推荐，但演示处理能力）
#special_quote|172.16.100.50|3306|user|Pass\"With\"Quote|special_db

# 密码包含 & 和空格（需在脚本中正确处理）
#special_and|172.16.100.51|3306|user|Pass&Word WithSpace|special_and_db

# 端口不是默认的 3306
#custom_port|172.16.100.60|4306|bak_user|Custom#Pass4306|custom_db

# 别名包含下划线和数字
#db_007|172.16.100.70|3306|agent|Bond#007|mission_db

# ----------------------- 注意事项 -----------------------
# 1. 确保 IP 地址和端口可访问
# 2. 确保备份账号有足够权限 (SELECT, RELOAD, LOCK TABLES, REPLICATION CLIENT 等)
# 3. 备份库列表用英文逗号分隔，不要有空格
# 4. 备份库列表留空表示备份所有数据库（除了系统库）
# 5. 本文件应设置为 600 权限：chmod 600 instance_list.txt
# --------------------------------------------------------------

at调度脚本：

#!/bin/bash

#确保 PATH 包含 mydumper 所在目录
export PATH="/usr/local/bin:/usr/bin:/bin:/usr/local/sbin:/usr/sbin"

#锁定工作目录
cd /usr/local/bin || {
    echo "无法进入备份脚本的目录" >&2
    exit 1
}


ROOT_DIR="/data/mydumper_backup"
POLICY_FILE="$ROOT_DIR/instance_policies.txt"
SCHEDULER_LOG="$ROOT_DIR/scheduler.log"
BACKUP_SCRIPT="/usr/local/bin/backup_mydumper.sh"

log() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a "$SCHEDULER_LOG"
}

# 检查 atd 服务是否运行
if ! systemctl is-active --quiet atd; then
    log "ERROR: atd 服务未运行！请执行: sudo systemctl start atd"
    exit 1
fi

if [ ! -f "$POLICY_FILE" ]; then
    log "ERROR: 策略文件不存在: $POLICY_FILE"
    exit 1
fi

if [ ! -x "$BACKUP_SCRIPT" ]; then
    log "ERROR: 备份脚本不可执行: $BACKUP_SCRIPT"
    exit 1
fi

log "开始调度备份任务..."

# 清除今天已有的相关 at 任务（避免重复）
# atq | awk '$4 ~ /today|'$(date +%m\/%d)'/ && $6 ~ /backup_mydumper\.sh/ {print $1}' | xargs -r atrm

while IFS='|' read -r alias retain threads compress extra post_script backup_time || [ -n "$alias" ]; do
    [[ "$alias" =~ ^#.*$ ]] && continue
    [[ -z "$alias" ]] && continue
    [[ -z "$backup_time" ]] && continue

    # 解析时间
    hour=$(echo "$backup_time" | cut -d: -f1)
    minute=$(echo "$backup_time" | cut -d: -f2)

    if ! [[ "$hour" =~ ^[0-2][0-9]$ ]] || ! [[ "$minute" =~ ^[0-5][0-9]$ ]]; then
        log "跳过无效时间: $backup_time (实例: $alias)"
        continue
    fi

    # 生成唯一的任务标识
    job_tag="backup_$alias"

    # 检查是否已有相同任务（可选）
    # existing_job=$(atq | grep "$job_tag" | awk '{print $1}')
    # [ -n "$existing_job" ] && atrm "$existing_job" && log "已清除旧任务: $job_tag"

    # 使用 at 调度
    {
        echo "#!/bin/bash"
        echo "export PATH=/usr/local/bin:/usr/bin:/bin"
        echo "cd \"$ROOT_DIR\""
        echo "echo '--- 开始执行 $alias 备份 ---' >> \"$SCHEDULER_LOG\""
        echo "/bin/bash \"$BACKUP_SCRIPT\" --instance=\"$alias\" >> \"$SCHEDULER_LOG\" 2>&1"
        echo "echo '--- 完成 $alias 备份 ---' >> \"$SCHEDULER_LOG\""
    } | at "$hour:$minute" today > /dev/null

    if [ $? -eq 0 ]; then
        log " 已调度: $alias -> $backup_time"
    else
        log " 调度失败: $alias -> $backup_time"
    fi
done < "$POLICY_FILE"

log "调度完成！使用 'atq' 查看任务列表。"

备份脚本：

root@mysql-shell-backup:/usr/local/bin# cat backup_mydumper.sh
#!/bin/bash

# =============================================================================
# 文件：backup_mydumper.sh
# 名称：MyDumper 完整备份脚本
# 功能：备份指定数据库，压缩，保留N天，记录日志，支持独立策略
# 作者：Bear
# 日期：2025-08-29
# =============================================================================

# ----------------------- 配置区 -----------------------
# 1. 正常运行（安全模式）
# ./backup_mydumper.sh
# → 只创建缺失的文件，不碰已存在的
# 2. 强制覆盖所有配置文件
# ./backup_mydumper.sh --force-update
# → 即使文件存在，也会用新的内容覆盖

FORCE_UPDATE=false  # 默认不强制更新配置
TARGET_INSTANCE=""  # 只备份指定实例

# 解析参数
while [[ $# -gt 0 ]]; do
    case $1 in
        --force-update)
            FORCE_UPDATE=true
            shift
            ;;
        --instance=*)
            TARGET_INSTANCE="${1#*=}"
            shift
            ;;
        *)
            echo "用法: $0 [--force-update] [--instance=别名]"
            exit 1
            ;;
    esac
done



# 项目根目录
ROOT_DIR="/data/mydumper_backup"

# 实例列表文件（连接信息）
INSTANCE_LIST="$ROOT_DIR/instance_list.txt"

# 实例策略文件（备份策略）
POLICY_FILE="$ROOT_DIR/instance_policies.txt"  # 👈 新增：策略文件

# 实例配置文件存放目录
CNF_DIR="$ROOT_DIR/instances"
mkdir -p "$CNF_DIR"

# 备份文件根目录
BACKUP_ROOT="$ROOT_DIR/backups"
mkdir -p "$BACKUP_ROOT"

# 默认保留天数（策略文件中未指定时使用）
DEFAULT_RETAIN_DAYS=7

# 默认并行线程数
DEFAULT_THREADS=4

# 默认长查询超时（毫秒）
DEFAULT_LONG_QUERY_TIMEOUT=1000

# 日志文件
LOG_FILE="$ROOT_DIR/batch_backup.log"

# 是否使用 proxychains4
USE_PROXY=true

# 代理数组
if [ "$USE_PROXY" = true ]; then
    PROXY_ARRAY=("proxychains4" "-q")
else
    PROXY_ARRAY=()
fi

# ----------------------- 函数区 -----------------------

log() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a "$LOG_FILE"
}

error_log() {
    log "ERROR: $1"
}

# ========== 新增：加载备份策略 ==========
# 使用关联数组存储策略
declare -A POLICY_RETAIN
declare -A POLICY_THREADS
declare -A POLICY_COMPRESS
declare -A POLICY_EXTRA_OPTS
declare -A POLICY_POST_SCRIPT

load_backup_policies() {
    if [ ! -f "$POLICY_FILE" ]; then
        error_log "警告：策略文件不存在: $POLICY_FILE，将使用默认策略"
        return 0
    fi

    while IFS='|' read -r alias retain_days threads compress extra_opts post_script || [ -n "$alias" ]; do
        [[ "$alias" =~ ^#.*$ ]] && continue
        [[ -z "$alias" ]] && continue

        # 存储策略（允许为空，后续使用默认值）
        POLICY_RETAIN["$alias"]="${retain_days:-$DEFAULT_RETAIN_DAYS}"
        POLICY_THREADS["$alias"]="${threads:-$DEFAULT_THREADS}"
        POLICY_COMPRESS["$alias"]="${compress:-yes}"  # yes/no
        POLICY_EXTRA_OPTS["$alias"]="$extra_opts"     # 如 "--ssl"
        POLICY_POST_SCRIPT["$alias"]="$post_script"   # 后处理脚本路径
    done < "$POLICY_FILE"

    log "已加载备份策略文件: $POLICY_FILE"
}

# 创建或更新实例的配置文件
create_or_update_cnf() {
    local alias=$1
    local host=$2
    local port=$3
    local user=$4
    local password=$5
    local cnf_path="$CNF_DIR/${alias}.cnf"

    # 构建要写入的内容（不包含 EOF）
    local new_content="[client]
user = $user
password = \"$password\"
host = $host
port = $port
protocol = tcp"

    # 情况 1: 强制更新模式
    if [ "$FORCE_UPDATE" = true ]; then
        log "强制更新模式，覆盖配置文件: $cnf_path"
        echo "$new_content" > "$cnf_path"
        chmod 600 "$cnf_path"
        return 0
    fi

    # 情况 2: 文件不存在 → 创建
    if [ ! -f "$cnf_path" ]; then
        log "配置文件不存在，新建: $cnf_path"
        echo "$new_content" > "$cnf_path"
        chmod 600 "$cnf_path"
        return 0
    fi

    # 情况 3: 文件存在 → 检查内容是否相同
    if cmp -s <(echo "$new_content") "$cnf_path"; then
        log "配置文件已存在且内容一致，跳过: $cnf_path"
        return 0
    else
        log "⚠️  配置文件已存在但内容不同！"
        log "    旧配置: host=$(grep '^host' "$cnf_path" | cut -d'=' -f2), user=$(grep '^user' "$cnf_path" | cut -d'=' -f2)"
        log "    新配置: host=$host, user=$user"
        log "    由于未启用 --force-update，跳过更新。"
        return 0
    fi
}

# 执行单个实例的备份
backup_single_instance() {
    local alias=$1
    local host=$2
    local port=$3
    local user=$4
    local password=$5
    local db_list=$6  # 备份哪些库，空则全备

    #  从策略中获取参数（或使用默认值）
    local retain_days=${POLICY_RETAIN["$alias"]:-$DEFAULT_RETAIN_DAYS}
    local threads=${POLICY_THREADS["$alias"]:-$DEFAULT_THREADS}
    local compress=${POLICY_COMPRESS["$alias"]:-yes}
    local extra_opts=${POLICY_EXTRA_OPTS["$alias"]}
    local post_script=${POLICY_POST_SCRIPT["$alias"]}

    local cnf_file="$CNF_DIR/${alias}.cnf"
    local backup_dir="$BACKUP_ROOT/${host}_${port}_$(date +%Y%m%d_%H%M%S)"
    mkdir -p "$backup_dir"

    log "开始备份实例 [$alias] $host:$port -> $backup_dir (保留 $retain_days 天)"

    # 构建 mydumper 命令
    MYDUMPER_CMD=("${PROXY_ARRAY[@]}" "mydumper"
        "--defaults-file=$cnf_file"
        "--host=$host"
        "--port=$port"
        "--outputdir=$backup_dir"
        "--threads=$threads"           # 👈 使用策略中的线程数
        "--verbose=2"
    )

    #  根据策略添加选项
    if [ "$compress" = "yes" ]; then
        MYDUMPER_CMD+=( "--compress" )
    fi

    # 添加长查询保护（使用策略中的值，如果定义了的话）
    # 这里可以扩展为从策略文件读取 LONG_QUERY_TIMEOUT
    MYDUMPER_CMD+=( "--long-query-guard=$DEFAULT_LONG_QUERY_TIMEOUT" )

    # 添加额外选项
    if [ -n "$extra_opts" ] && [ "$extra_opts" != '""' ]; then
        # 安全地处理额外选项（避免代码注入）
        eval "extra_array=($extra_opts)"
        MYDUMPER_CMD+=( "${extra_array[@]}" )
    fi

    # 根据 db_list 决定备份范围
    if [ -n "$db_list" ] && [ "$db_list" != "|" ]; then
        # 备份指定库列表
        for db in $(echo "$db_list" | tr ',' ' '); do
            db_dir="$backup_dir/$db"
            mkdir -p "$db_dir"
            "${MYDUMPER_CMD[@]}" "--database=$db" "--outputdir=$db_dir"
            if [ $? -ne 0 ]; then
                error_log "备份库 $db 失败"
                return 1
            fi
        done
    else
        # 备份所有库
        "${MYDUMPER_CMD[@]}"
    fi

    if [ $? -eq 0 ]; then
        log " 实例 [$alias] 备份成功"

        #  保存备份信息
        echo "Backup for $alias at $(date)" > "$backup_dir/backup_info.txt"
        echo "Retain Days: $retain_days" >> "$backup_dir/backup_info.txt"
        echo "Threads: $threads" >> "$backup_dir/backup_info.txt"
        echo "Compress: $compress" >> "$backup_dir/backup_info.txt"

        #  执行后处理脚本
        if [ -n "$post_script" ] && [ -f "$post_script" ] && [ -x "$post_script" ]; then
            log "执行后处理脚本: $post_script"
            if "$post_script" "$backup_dir" "$alias" "$host" "$port"; then
                log "后处理成功: $post_script"
            else
                error_log "后处理失败: $post_script (退出码: $?)"
                # 注意：后处理失败是否影响整体？这里不返回失败
            fi
        fi

        #  清理旧备份（使用策略中的保留天数）
        find "$BACKUP_ROOT" -maxdepth 1 -type d -name "${host}_${port}_*" -mtime +$retain_days -exec rm -rf {} \; 2>/dev/null || true
        return 0
    else
        error_log " 实例 [$alias] 备份失败"
        return 1
    fi
}

# ========== 主程序 ==========
log "开始批量备份 100 个实例..."

#  1. 先加载备份策略
load_backup_policies

if [ ! -f "$INSTANCE_LIST" ]; then
    error_log "实例列表文件不存在: $INSTANCE_LIST"
    exit 1
fi

SUCCESS_COUNT=0
FAIL_COUNT=0

# 读取实例列表
while IFS='|' read -r alias host port user password db_list || [ -n "$alias" ]; do

        # 如果指定了 TARGET_INSTANCE，只处理匹配的实例
    if [ -n "$TARGET_INSTANCE" ] && [ "$alias" != "$TARGET_INSTANCE" ]; then
        continue
    fi

    # 跳过注释行和空行
    [[ "$alias" =~ ^#.*$ ]] && continue
    [[ -z "$alias" ]] && continue

    log "处理实例: $alias ($host:$port)"

    # 生成配置文件
    create_or_update_cnf "$alias" "$host" "$port" "$user" "$password"

    # 执行备份
    if backup_single_instance "$alias" "$host" "$port" "$user" "$password" "$db_list"; then
        ((SUCCESS_COUNT++))
    else
        ((FAIL_COUNT++))
    fi

done < "$INSTANCE_LIST"

log "批量备份完成！成功: $SUCCESS_COUNT, 失败: $FAIL_COUNT"

if [ $FAIL_COUNT -eq 0 ]; then
    log " 全部实例备份成功！"
    exit 0
else
    log " 有 $FAIL_COUNT 个实例备份失败，请检查日志: $LOG_FILE"
    exit 1
fi

每天凌晨调度一次：

crontab -e

0 0 * * * /data/mydumper_backup/schedule_backups.sh

检查调度执行情况：

root@mysql-shell-backup:/usr/local/bin# cat check_at_job.sh
#!/bin/bash
# check_at_job.sh

echo "=== 1. at 队列状态 ==="
atq

echo -e "\n=== 2. 最近 atd 系统日志 ==="
sudo grep "atd" /var/log/syslog | tail -10

echo -e "\n=== 3. 备份日志最后 10 行 ==="
tail -10 /data/mydumper_backup/batch_backup.log

echo -e "\n=== 4. 最近备份目录 ==="
ls -h /data/mydumper_backup/backups | tail -5
墨力计划mysql备份
最后修改时间：2025-09-12 14:44:11
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

文章被以下合辑收录

备份与恢复（共3篇）
数据备份&恢复是企业数据安全的最重要的一环。

评论