玩机系列之：Docker+MySQL+Xtrabackup数据库备份和使用一体化

前言

最近搭建了一个开发用的MySQL，用的是Docker搭建的，但是又怕服务器挂掉，所以经过几天的时候使用了现在Docker+MySQL+Xtrabackup 这种方案。

包含文件

• backup.sh 备份脚本
• docker-compose.yml 编排文件
• mysql.cnf MySQL配置文件

docker-compose.yml

version: "3"
services:
  mysql:
    image: mysql:8.0.35
    ports:
      - 23307:3306
    command: --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
    environment:
      MYSQL_ROOT_PASSWORD: 123456  # root 用户密码
      TZ: Asia/Shanghai
    volumes:
      - ./mysql_data:/var/lib/mysql      # 数据存放的目录
      - ./mysql.cnf:/etc/mysql/mysql.cnf # 指定配置文件
    networks:
      - mysql-network

  percona-xtrabackup:
    image: percona/percona-xtrabackup:8.0  # 使用 XtraBackup 官方镜像
    user: "root"  # 临时以 root 身份运行
    volumes:
      - ./backups:/backups  # 备份文件存储位置
      - ./mysql_data:/var/lib/mysql:ro  # 只读访问 MySQL 数据目录
      - ./mysql.cnf:/etc/mysql/mysql.cnf:ro  # 访问 MySQL 配置
    environment:
      MYSQL_HOST: mysql                     # MySQL 服务名称
      MYSQL_USER: root                      # 执行备份的用户，建议用root
      MYSQL_PASSWORD: 123456                # 对应用户的密码
      TZ: Asia/Shanghai
    networks:
      - mysql-network
    depends_on:
      - mysql
    entrypoint: ["tail", "-f", "/dev/null"]  # 保持容器运行，以便随时执行备份命令

networks:
  mysql-network:

mysql.cnf

[mysqld]
# 设置字符集
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

port        = 3306
socket      = /tmp/mysql.sock

datadir = /var/lib/mysql
default_storage_engine = InnoDB
skip-external-locking
key_buffer_size = 128M
max_allowed_packet = 100G
table_open_cache = 512
sort_buffer_size = 2M
net_buffer_length = 4K
read_buffer_size = 2M
read_rnd_buffer_size = 256K
myisam_sort_buffer_size = 32M
thread_cache_size = 64
query_cache_size = 64M
tmp_table_size = 64M
sql-mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

explicit_defaults_for_timestamp = true
#skip-name-resolve
max_connections = 500
max_connect_errors = 100
open_files_limit = 65535

log-bin=mysql-bin
binlog_format=mixed
binlog-do-db = ai_ku_cn
#binlog-do-db = ai_ku_cn
server-id = 101
expire_logs_days = 10
slow_query_log=1
slow-query-log-file=/var/lib/mysql/mysql-slow.log
long_query_time=3
#log_queries_not_using_indexes=on
early-plugin-load = ""

innodb_data_home_dir = /var/lib/mysql
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql
innodb_buffer_pool_size = 512M
innodb_log_file_size = 256M
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
innodb_max_dirty_pages_pct = 90
innodb_read_io_threads = 4
innodb_write_io_threads = 4

!includedir /etc/mysql/conf.d/

[mysqldump]
quick
max_allowed_packet = 500M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 2M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

# 允许远程连接
bind-address = 0.0.0.0

backup.sh

#!/bin/bash

# 设置时区为上海
TZ='Asia/Shanghai'; export TZ

# 配置信息
BACKUP_DIR="./backups"
MYSQL_HOST="mysql"
MYSQL_USER="root"
MYSQL_PASSWORD="123456"
FULL_BACKUP_DAYS=14  # 每14天执行一次全量备份
RETENTION_DAYS=30    # 保留30天的备份
LOG_FILE="$BACKUP_DIR/backup_$(date +%Y%m%d_%H%M%S).log"
# 配置结束

# 日志函数
log() {
    local message="[$(date '+%Y-%m-%d %H:%M:%S')] $1"
    echo "$message"
    echo "$message" >> "$LOG_FILE"
}

log "开始备份脚本执行"

# 创建备份目录（如果不存在）
mkdir -p "$BACKUP_DIR"
chmod 775 "$BACKUP_DIR"  # 确保目录可写
log "确保备份目录存在: $BACKUP_DIR"

# 获取今天的日期和时间（包含时分秒）
TODAY=$(date +%Y%m%d_%H%M%S)
DAY_OF_WEEK=$(date +%u)  # 1-7，1为周一

# 确定备份类型（全量或增量）
if [ $(( (DAY_OF_WEEK - 1) % FULL_BACKUP_DAYS )) -eq 0 ]; then
    log "今天是执行全量备份的日期"
    BACKUP_TYPE="full"
    BACKUP_DIR_PATH="$BACKUP_DIR/full_$TODAY"
    INCREMENTAL_OPTION=""
    COMPRESS_INCREMENTAL=false  # 全量备份不压缩
else
    log "今天是执行增量备份的日期"
    BACKUP_TYPE="incremental"
    # 查找最近的全量备份目录（不包含.tar.gz文件）
    LATEST_FULL=$(ls -d $BACKUP_DIR/full_* 2>/dev/null | grep -v '\.tar\.gz' | sort -r | head -n 1)

    if [ -z "$LATEST_FULL" ]; then
        log "警告: 未找到全量备份目录，将执行全量备份"
        BACKUP_TYPE="full"
        BACKUP_DIR_PATH="$BACKUP_DIR/full_$TODAY"
        INCREMENTAL_OPTION=""
        COMPRESS_INCREMENTAL=false
    else
        log "找到最近的全量备份: $LATEST_FULL"
        BACKUP_DIR_PATH="$BACKUP_DIR/incremental_$TODAY"
        INCREMENTAL_OPTION="--incremental-basedir=$LATEST_FULL"
        COMPRESS_INCREMENTAL=true  # 增量备份压缩
    fi
fi

# 更彻底地清理备份目录
log "清理备份目录: $BACKUP_DIR_PATH"
if [ -e "$BACKUP_DIR_PATH" ]; then
    # 尝试普通删除
    rm -rf "$BACKUP_DIR_PATH" 2>/dev/null

    # 如果目录仍然存在，尝试使用docker执行删除
    if [ -e "$BACKUP_DIR_PATH" ]; then
        log "普通删除失败，尝试通过docker容器删除"
        docker-compose exec -T percona-xtrabackup sh -c "rm -rf $BACKUP_DIR_PATH" 2>/dev/null

        # 再次检查目录是否存在
        if [ -e "$BACKUP_DIR_PATH" ]; then
            log "警告: 备份目录 $BACKUP_DIR_PATH 无法被删除"
            log "尝试更改权限后再次删除"
            chmod -R 777 "$BACKUP_DIR_PATH" 2>/dev/null
            rm -rf "$BACKUP_DIR_PATH" 2>/dev/null

            if [ -e "$BACKUP_DIR_PATH" ]; then
                log "错误: 备份目录 $BACKUP_DIR_PATH 仍然无法被删除，脚本将退出"
                exit 1
            fi
        fi
    fi
fi

# 创建备份目录
mkdir -p "$BACKUP_DIR_PATH"
chmod 775 "$BACKUP_DIR_PATH"  # 确保目录可写
log "创建备份目录: $BACKUP_DIR_PATH"

# 检查docker-compose是否可用
if ! command -v docker-compose &> /dev/null; then
    log "错误: 未找到docker-compose命令"
    exit 1
fi

# 检查percona-xtrabackup容器是否存在
CONTAINER_EXISTS=$(docker-compose ps -q percona-xtrabackup)
if [ -z "$CONTAINER_EXISTS" ]; then
    log "错误: 未找到percona-xtrabackup容器"
    exit 1
fi

# 验证容器内的备份目录是否可写
log "验证容器内的备份目录是否可写"
docker-compose exec -T percona-xtrabackup sh -c "touch $BACKUP_DIR_PATH/test_file && rm -f $BACKUP_DIR_PATH/test_file"
if [ $? -ne 0 ]; then
    log "错误: 容器内无法在备份目录中创建文件"
    log "尝试更改目录权限"
    docker-compose exec -T percona-xtrabackup sh -c "chmod 777 $BACKUP_DIR_PATH"

    # 再次验证
    docker-compose exec -T percona-xtrabackup sh -c "touch $BACKUP_DIR_PATH/test_file && rm -f $BACKUP_DIR_PATH/test_file"
    if [ $? -ne 0 ]; then
        log "错误: 更改权限后仍然无法在容器内写入备份目录"
        exit 1
    else
        log "权限更改成功，现在可以在容器内写入备份目录"
    fi
fi

# 执行备份
log "执行 $BACKUP_TYPE 备份到目录: $BACKUP_DIR_PATH"
docker-compose exec percona-xtrabackup xtrabackup \
    --backup \
    --host=$MYSQL_HOST \
    --user=$MYSQL_USER \
    --password=$MYSQL_PASSWORD \
    --target-dir=$BACKUP_DIR_PATH \
    $INCREMENTAL_OPTION \
    --no-version-check \
    --extra-lsndir=$BACKUP_DIR_PATH \
    --datadir=/var/lib/mysql/ \
    --tmpdir=/tmp/ \
    >> "$LOG_FILE" 2>&1

# 检查备份是否成功
BACKUP_EXIT_CODE=$?
if [ $BACKUP_EXIT_CODE -eq 0 ]; then
    log "备份命令成功执行"

    # 验证备份目录是否包含文件
    FILE_COUNT=$(docker-compose exec percona-xtrabackup sh -c "ls -A $BACKUP_DIR_PATH | wc -l")
    log "备份目录中的文件数量: $FILE_COUNT"

    if [ "$FILE_COUNT" -eq 0 ]; then
        log "警告: 备份目录为空，可能备份未成功生成文件"
    fi

    if [ "$BACKUP_TYPE" = "incremental" ] && [ "$COMPRESS_INCREMENTAL" = true ]; then
        log "开始压缩增量备份文件..."
        # 压缩备份文件
        tar -czvf "$BACKUP_DIR_PATH.tar.gz" -C $(dirname $BACKUP_DIR_PATH) $(basename $BACKUP_DIR_PATH)
        TAR_EXIT_CODE=$?

        if [ $TAR_EXIT_CODE -eq 0 ]; then
            log "压缩成功: $BACKUP_DIR_PATH.tar.gz"
            # 清理未压缩的备份目录（仅针对增量备份）
            rm -rf "$BACKUP_DIR_PATH"
            log "已清理未压缩的备份目录"
        else
            log "压缩失败，退出代码: $TAR_EXIT_CODE"
            # 保留原始备份目录，即使压缩失败
        fi
    else
        log "全量备份目录将保留原始格式，不进行压缩"
    fi
else
    log "备份失败，退出代码: $BACKUP_EXIT_CODE"
    # 保留失败的备份目录用于调试
    log "保留失败的备份目录用于分析: $BACKUP_DIR_PATH"
    # 输出详细错误信息
    log "==== 备份错误详情 ===="
    tail -n 20 "$LOG_FILE"
    exit $BACKUP_EXIT_CODE
fi

# 清理旧备份
log "清理旧备份..."
# 清理旧的增量备份压缩文件
find "$BACKUP_DIR" -name "incremental_*_*.tar.gz" -mtime +$RETENTION_DAYS -exec rm -f {} \;
# 清理旧的全量备份目录（保留最近3个）
find "$BACKUP_DIR" -type d -name "full_*_*" ! -name "full_*tar.gz" | sort -r | tail -n +4 | xargs -r rm -rf
echo "备份脚本执行完毕"

备份脚本策略说明

• 全量备份：每 FULL_BACKUP_DAYS（默认 14 天）执行一次，基于星期几计算触发时间（如周一为第 1 天）。
• 增量备份：非全量备份日执行，依赖最近一次全量备份目录，压缩后保留 .tar.gz 文件。