你好呀，我的老朋友！我是老寇，欢迎来到老寇IoT云平台！

安装Mysql

services:
  mysql:
    image: mysql:9.6.0
    container_name: mysql
    # 保持容器在没有守护程序的情况下运行
    tty: true
    restart: always
    privileged: true
    ports:
      - "3306:3306"
    command:
      --character-set-server=utf8mb4
      --collation-server=utf8mb4_general_ci
      --lower-case-table-names=1
    volumes:
      - ./mysql9/data:/var/lib/mysql
    environment:
      - MYSQL_ROOT_PASSWORD=laokou123
      - TZ=Asia/Shanghai
    networks:
      - iot_network
networks:
 iot_network:
    driver: bridge

数据备份

创建备份账号

CREATE USER 'backup'@'%'
    IDENTIFIED BY 'backup@2026';

GRANT SELECT,
    SHOW VIEW,
    TRIGGER,
    EVENT,
    PROCESS,
    RELOAD
    ON *.*
    TO 'backup'@'%';

SHOW GRANTS FOR 'backup'@'%';

mysql-backup.sh

#!/usr/bin/env bash

set -Eeuo pipefail

# ============================================================
# Docker MySQL 生产备份脚本
#
# 功能：
# 1. Docker容器内执行mysqldump
# 2. 宿主机保存备份
# 3. 全库备份
# 4. gzip压缩
# 5. 备份完整性校验
# 6. 防止重复执行
# 7. 磁盘空间检查
# 8. 自动保留最近7天
# 9. 失败自动删除临时文件
# ============================================================


# ==========================
# 配置
# ==========================

# MySQL容器名称
MYSQL_CONTAINER="mysql"

# MySQL用户名
MYSQL_USER="backup"

# MySQL密码
MYSQL_PASSWORD="backup@2026"

# 备份根目录
BACKUP_DIR="/data/backup/mysql"

# 日志目录
LOG_DIR="/var/log/mysql-backup"

# 保留天数
RETENTION_DAYS=7

# 最低磁盘剩余空间 MB
MIN_FREE_SPACE_MB=5120

# 防止多个任务同时运行
LOCK_FILE="/var/run/mysql-docker-backup.lock"

# 当前日期
DATE="$(date '+%Y%m%d_%H%M%S')"

# 当天目录
TODAY="$(date '+%Y%m%d')"

TODAY_DIR="${BACKUP_DIR}/${TODAY}"

# 最终文件
BACKUP_FILE="${TODAY_DIR}/mysql_all_${DATE}.sql.gz"

# 临时文件
TMP_FILE="${BACKUP_FILE}.tmp"

# 日志文件
LOG_FILE="${LOG_DIR}/mysql-backup-${TODAY}.log"


# ==========================
# 初始化
# ==========================

mkdir -p "${TODAY_DIR}"
mkdir -p "${LOG_DIR}"

chmod 750 "${BACKUP_DIR}"
chmod 750 "${LOG_DIR}"


# ==========================
# 日志
# ==========================

log() {

    local level="$1"
    shift

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


# ==========================
# 清理临时文件
# ==========================

cleanup() {

    if [[ -f "${TMP_FILE}" ]]; then
        rm -f "${TMP_FILE}"
    fi
}


# ==========================
# 错误处理
# ==========================

error_handler() {

    local exit_code=$?

    log "ERROR" "MySQL备份失败，exit_code=${exit_code}"

    cleanup

    exit "${exit_code}"
}

trap error_handler ERR
trap cleanup EXIT


# ==========================
# 防重复执行
# ==========================

exec 200>"${LOCK_FILE}"

if ! flock -n 200; then

    log "WARN" "已有MySQL备份任务正在执行，本次退出"

    exit 1
fi


# ==========================
# 环境检查
# ==========================

if ! command -v docker >/dev/null 2>&1; then

    log "ERROR" "docker命令不存在"

    exit 1
fi

if ! command -v gzip >/dev/null 2>&1; then

    log "ERROR" "gzip命令不存在"

    exit 1
fi


# ==========================
# 检查容器是否存在
# ==========================

if ! docker inspect "${MYSQL_CONTAINER}" >/dev/null 2>&1; then

    log "ERROR" "MySQL容器不存在：${MYSQL_CONTAINER}"

    exit 1
fi


# ==========================
# 检查容器是否运行
# ==========================

CONTAINER_STATUS=$(
    docker inspect \
        -f '{{.State.Running}}' \
        "${MYSQL_CONTAINER}"
)

if [[ "${CONTAINER_STATUS}" != "true" ]]; then

    log "ERROR" "MySQL容器没有运行：${MYSQL_CONTAINER}"

    exit 1
fi

log "INFO" "MySQL容器运行正常"


# ==========================
# 检查mysqldump
# ==========================

if ! docker exec "${MYSQL_CONTAINER}" \
    sh -c 'command -v mysqldump' \
    >/dev/null 2>&1; then

    log "ERROR" "MySQL容器内没有mysqldump命令"

    exit 1
fi


# ==========================
# 检查MySQL连接
# ==========================

log "INFO" "检查MySQL连接"

docker exec \
    -e MYSQL_PWD="${MYSQL_PASSWORD}" \
    "${MYSQL_CONTAINER}" \
    mysql \
    -u"${MYSQL_USER}" \
    -Nse "SELECT 1;" \
    >/dev/null

log "INFO" "MySQL连接正常"


# ==========================
# 磁盘空间检查
# ==========================

AVAILABLE_MB=$(
    df -Pm "${BACKUP_DIR}" \
        | awk 'NR==2 {print $4}'
)

log "INFO" "备份磁盘剩余空间：${AVAILABLE_MB} MB"

if (( AVAILABLE_MB < MIN_FREE_SPACE_MB )); then

    log "ERROR" \
        "磁盘空间不足，当前=${AVAILABLE_MB}MB，最低要求=${MIN_FREE_SPACE_MB}MB"

    exit 1
fi


# ==========================
# 开始备份
# ==========================

START_TIME=$(date +%s)

log "INFO" "开始备份MySQL"

log "INFO" "容器：${MYSQL_CONTAINER}"

log "INFO" "备份文件：${BACKUP_FILE}"


docker exec \
    -e MYSQL_PWD="${MYSQL_PASSWORD}" \
    "${MYSQL_CONTAINER}" \
    mysqldump \
    -u"${MYSQL_USER}" \
    --all-databases \
    --single-transaction \
    --quick \
    --routines \
    --events \
    --triggers \
    --hex-blob \
    --no-tablespaces \
    --default-character-set=utf8mb4 \
    --set-gtid-purged=OFF \
    | gzip -6 > "${TMP_FILE}"


# ==========================
# 判断文件是否为空
# ==========================

if [[ ! -s "${TMP_FILE}" ]]; then

    log "ERROR" "备份文件为空"

    exit 1
fi


# ==========================
# 校验gzip
# ==========================

log "INFO" "开始检查gzip完整性"

gzip -t "${TMP_FILE}"

log "INFO" "gzip完整性检查成功"


# ==========================
# 临时文件 -> 正式文件
# ==========================

mv "${TMP_FILE}" "${BACKUP_FILE}"

chmod 640 "${BACKUP_FILE}"


# ==========================
# 统计
# ==========================

END_TIME=$(date +%s)

COST=$((END_TIME - START_TIME))

BACKUP_SIZE=$(
    du -h "${BACKUP_FILE}" \
        | awk '{print $1}'
)

log "INFO" "MySQL备份成功"

log "INFO" "文件：${BACKUP_FILE}"

log "INFO" "大小：${BACKUP_SIZE}"

log "INFO" "耗时：${COST} 秒"


# ==========================
# 删除过期备份
# ==========================

log "INFO" "清理 ${RETENTION_DAYS} 天以前的备份"

find "${BACKUP_DIR}" \
    -type f \
    -name 'mysql_all_*.sql.gz' \
    -mtime "+${RETENTION_DAYS}" \
    -print \
    -delete \
    >> "${LOG_FILE}" 2>&1


# 删除空目录
find "${BACKUP_DIR}" \
    -mindepth 1 \
    -type d \
    -empty \
    -delete


log "INFO" "历史备份清理完成"

log "INFO" "MySQL备份任务执行完成"

mysql-backup.service

[Unit]
Description=MySQL Docker Backup
Requires=docker.service
After=docker.service

[Service]
Type=oneshot
User=root
ExecStart=/home/a/app/backup/mysql-backup.sh

# 降低备份任务对业务的影响
Nice=10
IOSchedulingClass=best-effort
IOSchedulingPriority=7

# 失败时视为失败
SuccessExitStatus=0

mysql-backup.timer

[Unit]
Description=Run MySQL Backup Daily

[Timer]

# 每天凌晨 2 点执行
OnCalendar=*-*-* 02:00:00

# 如果 2 点服务器没开机，开机后补执行一次
Persistent=true

# 定时器精度
AccuracySec=1min

# service执行完成后timer保持激活
RemainAfterElapse=yes

[Install]
WantedBy=timers.target

chmod -R 7777 /etc/systemd/system/mysql-backup.service
systemctl daemon-reload
systemctl enable --now mysql-backup.timer
systemctl status mysql-backup.timer
# 下一次运行时间
systemctl list-timers mysql-backup.timer

我是老寇，我们下次再见啦！