Docker mysql部署及日常使用以及坑点

前言：nas想要映射一个数据库出来，在使用nas的时候碰到的Docker mysql部署以及坑点进行记录

部署搭建

初始版的compose.yaml

version: '3.8'

services:
  mysql:
    image: mysql:8.0
    container_name: db-mysql
    restart: unless-stopped
    environment:
      MYSQL_ROOT_PASSWORD: your_root_password_here
      MYSQL_DATABASE: example_db
      MYSQL_USER: example_user
      MYSQL_PASSWORD: example_password
    volumes:
      - /volume1/localdb/mysql:/var/lib/mysql
    ports:
      - "3306:3306"
    command:
      - "--character-set-server=utf8mb4"
      - "--collation-server=utf8mb4_unicode_ci"

但是部署的时候发现存在问题，容器一直在进行重启操作

mysqld: Can't create directory '/var/lib/mysql/' (OS errno 17 - File exists)

通过排查发现是volumes路径设置/volume1/localdb/mysql:/var/lib/mysql的问题

在创建容器的时候默认将compose.yaml保存到了/volume1/localdb/mysql文件中，而此时/volume1/localdb/mysql则有文件则会导致报错

最终将/volume1/localdb/mysql:/var/lib/mysql修改为/volume1/localdb/mysql/db:/var/lib/mysql则可以正常，最终的compose.yaml的内容如下所示

version: '3.8'

services:
  mysql:
    image: mysql:8.0
    container_name: db-mysql
    restart: unless-stopped
    environment:
      MYSQL_ROOT_PASSWORD: your_root_password_here
      MYSQL_DATABASE: example_db
      MYSQL_USER: example_user
      MYSQL_PASSWORD: example_password
    volumes:
      - /volume1/localdb/mysql/db:/var/lib/mysql
    ports:
      - "3306:3306"
    command:
      - "--character-set-server=utf8mb4"
      - "--collation-server=utf8mb4_unicode_ci"

备份脚本

#!/bin/bash

ENV_DIR="/data/mysql/env"
SQL_DIR="/data/mysql/sql"
LOG_FILE="/var/log/mysql_backup.log"
mkdir -p "$(dirname "$LOG_FILE")"

NOW=$(date "+%Y-%m-%d_%H-%M-%S")
TODAY=$(date "+%Y-%m-%d")

echo "[$NOW] 🔁 开始执行数据库连接测试和备份..." | tee -a "$LOG_FILE"

for ENV_FILE in "$ENV_DIR"/*.env; do
    NAME=$(basename "$ENV_FILE" .env)

    HOST=$(grep -E '^HOSTNAME *= *' "$ENV_FILE" | head -1 | cut -d '=' -f2- | xargs | cut -d',' -f1)
    PORT=$(grep -E '^HOSTPORT *= *' "$ENV_FILE" | cut -d '=' -f2- | xargs)
    USER=$(grep -E '^USERNAME *= *' "$ENV_FILE" | cut -d '=' -f2- | xargs)
    PASS=$(grep -E '^PASSWORD *= *' "$ENV_FILE" | cut -d '=' -f2- | xargs)
    DB=$(grep -E '^DATABASE *= *' "$ENV_FILE" | cut -d '=' -f2- | xargs)

    if [[ -z "$HOST" || -z "$PORT" || -z "$USER" || -z "$PASS" || -z "$DB" ]]; then
        echo "[$NOW] ⚠️ [$NAME] 缺少连接参数，跳过" | tee -a "$LOG_FILE"
        continue
    fi

    echo "[$NOW] 🧪 测试连接 [$NAME] -> $HOST:$PORT..." | tee -a "$LOG_FILE"
    mysql -h "$HOST" -P "$PORT" -u "$USER" -p"$PASS" -e "SELECT 1;" &>/dev/null

    if [[ $? -ne 0 ]]; then
        echo "[$NOW] ❌ [$NAME] 无法连接，跳过备份" | tee -a "$LOG_FILE"
        continue
    fi

    echo "[$NOW] ✅ [$NAME] 连接成功，开始备份数据库 [$DB]..." | tee -a "$LOG_FILE"

    OUT_DIR="${SQL_DIR}/${NAME}"
    mkdir -p "$OUT_DIR"
    OUT_FILE="${OUT_DIR}/${DB}_${NOW}.sql"

    mysqldump \
        --host="$HOST" --port="$PORT" --user="$USER" --password="$PASS" \
        --single-transaction --skip-lock-tables \
        --set-gtid-purged=OFF "$DB" > "$OUT_FILE"

    if [[ $? -eq 0 ]]; then
        echo "[$NOW] 🎉 [$NAME] 备份成功: $OUT_FILE" | tee -a "$LOG_FILE"
    else
        echo "[$NOW] ❌ [$NAME] 备份失败" | tee -a "$LOG_FILE"
        rm -f "$OUT_FILE"
        continue
    fi

    # 控制保留最多两个备份
    FILE_COUNT=$(ls -1t "$OUT_DIR"/*.sql 2>/dev/null | wc -l)
    if [[ "$FILE_COUNT" -gt 2 ]]; then
        DELETE_FILES=$(ls -1t "$OUT_DIR"/*.sql | tail -n +3)
        echo "[$NOW] 🧹 [$NAME] 删除旧备份: $DELETE_FILES" | tee -a "$LOG_FILE"
        rm -f $DELETE_FILES
    fi

done

echo "[$(date)] ✅ 所有任务完成" | tee -a "$LOG_FILE"

恢复脚本

#!/bin/bash

ENV_DIR="/data/mysql/envnew"
SQL_DIR="/data/mysql/sql"
LOG_FILE="/var/log/mysql_restore.log"
mkdir -p "$(dirname "$LOG_FILE")"

NOW=$(date "+%Y-%m-%d_%H-%M-%S")

# 列出可选项目
echo "🧩 可选项目列表："
select project in $(basename -s .env "$ENV_DIR"/*.env) "全部" "退出"; do
    case "$project" in
        退出)
            echo "👋 退出脚本。"
            exit 0
            ;;
        全部)
            PROJECT_LIST=$(basename -s .env "$ENV_DIR"/*.env)
            break
            ;;
        *)
            if [[ -f "$ENV_DIR/$project.env" ]]; then
                PROJECT_LIST="$project"
                break
            else
                echo "⚠️ 无效选择，请重新输入编号。"
            fi
            ;;
    esac
done

echo "[$NOW] ♻️ 开始批量数据库恢复..." | tee -a "$LOG_FILE"

for NAME in $PROJECT_LIST; do
    ENV_FILE="$ENV_DIR/$NAME.env"
    echo "[$NOW] 📦 处理 [$NAME]..." | tee -a "$LOG_FILE"

    HOST=$(grep -E '^HOSTNAME *= *' "$ENV_FILE" | head -1 | cut -d '=' -f2- | xargs | cut -d',' -f1)
    PORT=$(grep -E '^HOSTPORT *= *' "$ENV_FILE" | cut -d '=' -f2- | xargs)
    USER=$(grep -E '^USERNAME *= *' "$ENV_FILE" | cut -d '=' -f2- | xargs)
    PASS=$(grep -E '^PASSWORD *= *' "$ENV_FILE" | cut -d '=' -f2- | xargs)
    DB=$(grep -E '^DATABASE *= *' "$ENV_FILE" | cut -d '=' -f2- | xargs)

    if [[ -z "$HOST" || -z "$PORT" || -z "$USER" || -z "$PASS" || -z "$DB" ]]; then
        echo "[$NOW] ⚠️ [$NAME] 缺少连接参数，跳过" | tee -a "$LOG_FILE"
        continue
    fi

    SQL_PATH_DIR="${SQL_DIR}/${NAME}"
    LATEST_SQL=$(ls -1t "${SQL_PATH_DIR}"/*.sql 2>/dev/null | head -n 1)

    if [[ -z "$LATEST_SQL" || ! -f "$LATEST_SQL" ]]; then
        echo "[$NOW] ❌ [$NAME] 未找到 SQL 备份文件，跳过" | tee -a "$LOG_FILE"
        continue
    fi

    echo "[$NOW] 🚀 正在将 [$LATEST_SQL] 恢复到 [$HOST:$PORT/$DB]..." | tee -a "$LOG_FILE"
    mysql --host="$HOST" --port="$PORT" --user="$USER" --password="$PASS" "$DB" < "$LATEST_SQL"

    if [[ $? -eq 0 ]]; then
        echo "[$NOW] ✅ [$NAME] 数据恢复成功" | tee -a "$LOG_FILE"
    else
        echo "[$NOW] ❌ [$NAME] 数据恢复失败" | tee -a "$LOG_FILE"
    fi
done

echo "[$(date)] ✅ 所有恢复任务完成" | tee -a "$LOG_FILE"