MySQL备份镜像搭建

​ 容器化备份mysql数据，实现定时全备。

容器的搭建

​ 准备好备份脚本，entrypoint脚本。

mysql_backup.sh

默认是遍历所有数据库并备份。包括MySQL本身自带的数据库。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
#!/bin/bash

# === 载入环境变量 ===
export $(cat /proc/1/environ | tr '\0' '\n' | grep -E 'MYSQL_|BACKUP_DIR|RETENTION_DAYS|ALL_DATABASES|AUTO_DB_BACKUP' | xargs)

# === 默认值 ===
BACKUP_DIR=${BACKUP_DIR:-"/backup"}
MYSQL_HOST=${MYSQL_HOST:-"localhost"}
MYSQL_PORT=${MYSQL_PORT:-"3306"}
MYSQL_USER=${MYSQL_USER:-"root"}
MYSQL_PASSWORD=${MYSQL_PASSWORD:-""}
MYSQL_DATABASE=${MYSQL_DATABASE:-""}
RETENTION_DAYS=${RETENTION_DAYS:-7}
ALL_DATABASES=${ALL_DATABASES:-"false"}
AUTO_DB_BACKUP=${AUTO_DB_BACKUP:-"false"}

LOG_FILE="/var/log/mysql_backup.log"
mkdir -p "$(dirname "$LOG_FILE")"
mkdir -p "$BACKUP_DIR"

TIMESTAMP=$(date +'%Y%m%d_%H%M%S')
STATUS=0
SECONDS=0
FAILED_DATABASES=()

echo "[$(date +'%F %T')] ===== 开始备份任务 =====" | tee -a "$LOG_FILE"

# === 格式化时间函数 ===
format_time() {
    local ELAPSED=$1
    local H=$((ELAPSED / 3600))
    local M=$(((ELAPSED % 3600) / 60))
    local S=$((ELAPSED % 60))
    local OUTPUT=""
    [[ $H -gt 0 ]] && OUTPUT+="${H} 小时 "
    [[ $M -gt 0 ]] && OUTPUT+="${M} 分 "
    OUTPUT+="${S} 秒"
    echo "$OUTPUT"
}

# === 单个数据库备份函数（含--force与错误检查） ===
perform_backup() {
    local DB_NAME="$1"
    local OUTPUT_FILE="$2"
    local RETRY=0
    local MAX_RETRY=3
    local START_TIME=$(date +%s)
    local TMP_LOG="/tmp/mysql_backup_${DB_NAME}_$$.log"
    local TMP_SQL="/tmp/backup_${DB_NAME}_$$.sql"

    local DUMP_CMD=(mysqldump -h "$MYSQL_HOST" -P "$MYSQL_PORT" -u "$MYSQL_USER" -p"$MYSQL_PASSWORD"
                    --single-transaction --quick --max_allowed_packet=512M --net_buffer_length=16384
                    --force --skip-lock-tables)

    [[ "$DB_NAME" == "--all-databases" ]] && DUMP_CMD+=(--all-databases) || DUMP_CMD+=(--databases "$DB_NAME")

    while [[ $RETRY -lt $MAX_RETRY ]]; do
        echo "[$(date +'%F %T')] [INFO] 正在导出数据库: $DB_NAME (尝试第 $((RETRY + 1)) 次)" | tee -a "$LOG_FILE"
        "${DUMP_CMD[@]}" > "$TMP_SQL" 2> "$TMP_LOG"
        if [[ $? -eq 0 && -s "$TMP_SQL" ]]; then
            if grep -q -i "ERROR" "$TMP_LOG"; then
                echo "[$(date +'%F %T')] [WARN] $DB_NAME 导出成功但含错误，请检查如下信息:" | tee -a "$LOG_FILE"
                grep -i "ERROR" "$TMP_LOG" | tee -a "$LOG_FILE"
                FAILED_DATABASES+=("$DB_NAME (含错误)")
            fi
            gzip -c "$TMP_SQL" > "$OUTPUT_FILE"
            END_TIME=$(date +%s)
            DURATION=$((END_TIME - START_TIME))
            FILE_SIZE=$(du -h "$OUTPUT_FILE" | cut -f1)
            echo "[$(date +'%F %T')] [OK] 备份成功: $OUTPUT_FILE" | tee -a "$LOG_FILE"
            echo "[$(date +'%F %T')] [INFO] 耗时: $(format_time "$DURATION")，文件大小: $FILE_SIZE" | tee -a "$LOG_FILE"
            rm -f "$TMP_SQL" "$TMP_LOG"
            return 0
        fi
        echo "[$(date +'%F %T')] [WARN] 第 $((RETRY + 1)) 次备份失败，5 秒后重试..." | tee -a "$LOG_FILE"
        rm -f "$TMP_SQL" "$TMP_LOG"
        sleep 5
        RETRY=$((RETRY + 1))
    done

    echo "[$(date +'%F %T')] [ERROR] 备份失败: $DB_NAME，放弃重试。" | tee -a "$LOG_FILE"
    FAILED_DATABASES+=("$DB_NAME")
    return 1
}

# === 1. 备份所有数据库 ===
if [[ "$ALL_DATABASES" == "true" ]]; then
    BACKUP_FILE="$BACKUP_DIR/all_databases_${TIMESTAMP}.sql.gz"
    echo "[$(date +'%F %T')] [INFO] 备份所有数据库到 $BACKUP_FILE" | tee -a "$LOG_FILE"
    perform_backup "--all-databases" "$BACKUP_FILE" || STATUS=1

# === 2. 自动获取数据库列表并分别备份 ===
elif [[ "$AUTO_DB_BACKUP" == "true" ]]; then
    echo "[$(date +'%F %T')] [INFO] 获取数据库列表..." | tee -a "$LOG_FILE"
    DB_LIST=$(mysql -h "$MYSQL_HOST" -P "$MYSQL_PORT" -u "$MYSQL_USER" -p"$MYSQL_PASSWORD" \
        -e "SHOW DATABASES;" | tail -n +2 | grep -Ev "^(information_schema|performance_schema|mysql|sys)$")

    for DB in $DB_LIST; do
        BACKUP_FILE="$BACKUP_DIR/${DB}_$TIMESTAMP.sql.gz"
        echo "[$(date +'%F %T')] [INFO] 开始备份数据库: $DB" | tee -a "$LOG_FILE"
        perform_backup "$DB" "$BACKUP_FILE" || STATUS=1
    done

# === 3. 备份指定数据库 ===
elif [[ -n "$MYSQL_DATABASE" ]]; then
    BACKUP_FILE="$BACKUP_DIR/${MYSQL_DATABASE}_$TIMESTAMP.sql.gz"
    echo "[$(date +'%F %T')] [INFO] 备份指定数据库: $MYSQL_DATABASE 到 $BACKUP_FILE" | tee -a "$LOG_FILE"
    perform_backup "$MYSQL_DATABASE" "$BACKUP_FILE" || STATUS=1

# === 4. 未指定任何备份方式 ===
else
    echo "[$(date +'%F %T')] [WARN] 未设置数据库备份目标。请设置 MYSQL_DATABASE，或 ALL_DATABASES=true，或 AUTO_DB_BACKUP=true。" | tee -a "$LOG_FILE"
    exit 1
fi

# === 5. 清理过期文件 ===
echo "[$(date +'%F %T')] [INFO] 正在清理 $RETENTION_DAYS 天前的备份文件..." | tee -a "$LOG_FILE"
find "$BACKUP_DIR" -type f -name "*.sql.gz" -mtime +$RETENTION_DAYS -exec rm -f {} \;

# === 6. 输出失败数据库列表（如有） ===
if [[ ${#FAILED_DATABASES[@]} -gt 0 ]]; then
    echo "[$(date +'%F %T')] [ERROR] 以下数据库备份失败或含错误：" | tee -a "$LOG_FILE"
    for DB in "${FAILED_DATABASES[@]}"; do
        echo " - $DB" | tee -a "$LOG_FILE"
    done
fi

# === 7. 输出总耗时 ===
TOTAL_TIME=$(format_time "$SECONDS")
echo "[$(date +'%F %T')] [INFO] 所有备份总耗时: $TOTAL_TIME ($SECONDS 秒)" | tee -a "$LOG_FILE"
echo "[$(date +'%F %T')] ===== 备份任务完成 ===== (状态码: $STATUS)" | tee -a "$LOG_FILE"

exit $STATUS

entrypoint.sh

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
#!/bin/bash

# 设置默认的定时任务时间（如果未提供 CRON_SCHEDULE）
CRON_SCHEDULE=${CRON_SCHEDULE:-"0 2 * * *"}
CRON_FILE="/etc/cron.d/mysql_backup"
BACKUP_SCRIPT="/usr/local/bin/mysql_backup.sh"

echo "[$(date +'%F %T')] ✅ Using cron schedule: $CRON_SCHEDULE"

#检查备份脚本是否存在
if [[ ! -x "$BACKUP_SCRIPT" ]]; then
  echo "[$(date +'%F %T')] ❌ Backup script not found or not executable: $BACKUP_SCRIPT"
  exit 1
fi
# 写入定时任务
#echo "$CRON_SCHEDULE root /usr/local/bin/backup.sh >> /var/log/mysql_backup.log 2>&1" > /etc/cron.d/mysql_backup
#echo "$CRON_SCHEDULE root /usr/local/bin/mysql_backup.sh >> /proc/1/fd/1 2>&1" > "$CRON_FILE"
# 使用 printf 写入 cron 任务，更稳定、无解释错误风险
printf "%s root %s >> /proc/1/fd/1 2>&1\n" "$CRON_SCHEDULE" "$BACKUP_SCRIPT" > "$CRON_FILE"
# 确保 cron 任务文件格式正确
chmod 0644 "$CRON_FILE"

# 显示已写入的任务
echo "[$(date +'%F %T')] === Cron job content:"
cat "$CRON_FILE"

# 应用 cron 任务
#crontab /etc/cron.d/mysql_backup

# 启动 cron 服务（前台）
echo "[$(date +'%F %T')] === Starting cron..."
cron -f

Dockerfile

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
FROM debian:bullseye
#FROM registry.cn-hangzhou.aliyuncs.com/library/debian:bullseye

# 更换阿里云 APT 源（适用于 Debian）
RUN sed -i 's|http://deb.debian.org|http://mirrors.aliyun.com|g' /etc/apt/sources.list && \
    sed -i 's|http://security.debian.org|http://mirrors.aliyun.com|g' /etc/apt/sources.list

RUN apt-get update && \
    DEBIAN_FRONTEND=noninteractive apt-get install -y  \
    default-mysql-client  \
    cron   \
    procps \
    tzdata && \
    #配置时区为Asia/Shanghai
    ln -sf /usr/share/zoneinfo/Asia/Shanghai /etc/localtime && \
    echo "Asia/Shanghai" > /etc/timezone && \
    #清理缓存
    apt-get clean && \
    rm -rf /var/lib/apt/lists/*

# 复制脚本
COPY mysql_backup.sh /usr/local/bin/mysql_backup.sh
RUN chmod +x /usr/local/bin/mysql_backup.sh

# 设置 Cron 定时任务（使用环境变量 CRON_SCHEDULE）
COPY entrypoint.sh /entrypoint.sh
RUN chmod +x /entrypoint.sh

# 可选：设置时区环境变量（某些应用会读取此变量）
ENV TZ=Asia/Shanghai

# 添加健康检查：确保 cron 进程在运行
HEALTHCHECK --interval=60s --timeout=10s --start-period=10s --retries=3 \
  CMD ps aux | grep '[c]ron' > /dev/null || (echo "cron not running" && exit 1)

CMD ["/entrypoint.sh"]

起容器

​ 使用docker-compose起容器，通过.env管理传入环境变量

.env

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
# 镜像名与版本号
IMAGE_NAME=mysql_backup
IMAGE_TAG=1.0

# 容器名
CONTAINER_NAME=mysql_backup

# 定时任务计划：每天 11:18 执行
CRON_SCHEDULE=18 11 * * *

# MySQL 连接配置
MYSQL_HOST=192.168.99.100
MYSQL_PORT=3306
MYSQL_USER=root
MYSQL_PASSWORD=Fdsadasdsad

# 备份策略
AUTO_DB_BACKUP=true
RETENTION_DAYS=30

docker-compose.yml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
version: "3.8"

services:
  mysql_backup:
    image: ${IMAGE_NAME}:${IMAGE_TAG}
    container_name: ${CONTAINER_NAME}
    environment:
      CRON_SCHEDULE: ${CRON_SCHEDULE}
      MYSQL_HOST: ${MYSQL_HOST}
      MYSQL_PORT: ${MYSQL_PORT}
      MYSQL_USER: ${MYSQL_USER}
      MYSQL_PASSWORD: ${MYSQL_PASSWORD}
      AUTO_DB_BACKUP: ${AUTO_DB_BACKUP}
      RETENTION_DAYS: ${RETENTION_DAYS}

    volumes:
      - ./backup_single_db:/backup
      - ./logs:/var/log/
    restart: unless-stopped

    healthcheck:
      test: ["CMD", "pgrep", "cron"]
      interval: 30s
      timeout: 10s
      retries: 3