xtrabackup 24 、mysql 5.7可以实现不锁库进行备份。在备份多个库的时候，往db_name中塞，中间用空格分开

#!/bin/bash
set -eo pipefail

# =============================================
# 配置部分
# =============================================

# 数据库配置
readonly USER="数据库用户"
readonly PASSWORD="数据库密码"
readonly DB_NAME="数据库"
readonly PLATFORM="标志"
readonly DEFAULTS_FILE="/etc/my.cnf"
readonly SOCKET_FILE="/tmp/mysql.sock"

# 备份配置
readonly BASE_BACKUP_DIR="/www/embk/nbk"
readonly BACKUP_RETENTION_DAYS=7
readonly PARALLEL_THREADS=8
readonly COMPRESS_THREADS=2
readonly ZSTD_THREADS=4
readonly ZSTD_LEVEL=3

# 排除的表（使用数组更清晰）
readonly EXCLUDED_TABLES=(
    "failed_jobs" "data_aaaaa" 
)

# =============================================
# 初始化部分
# =============================================

# 创建日志目录和文件
readonly LOG_DIR="${BASE_BACKUP_DIR}/logs"
readonly BACKUP_LOG="${LOG_DIR}/backup_$(date +%Y%m%d).log"
readonly CLEANUP_LOG="${LOG_DIR}/cleanup_$(date +%Y%m%d).log"

mkdir -p "${LOG_DIR}"
# exec > > (tee -a "${BACKUP_LOG}") 2>&1


# 时间变量
readonly BACKUP_TIMESTAMP=$(date +%Y%m%d%H%M)
readonly CURRENT_DATE=$(date +%Y-%m-%d-%H)
readonly OBS_UPLOAD_DATE=$(date +%Y/%m/%d)

# 备份目录和文件
readonly FULL_BACKUP_DIR="${BASE_BACKUP_DIR}/${DB_NAME}/full_${CURRENT_DATE}"
readonly BACKUP_FILE="${FULL_BACKUP_DIR}/${BACKUP_TIMESTAMP}.xbstream.zst"

# =============================================
# 函数定义
# =============================================

# 检查必要命令是否存在
check_requirements() {
    local commands=("xtrabackup" "zstd")
    
    for cmd in "${commands[@]}"; do
        if ! command -v "${cmd}" &> /dev/null; then
            echo "错误: ${cmd} 命令未找到，请安装后重试"
            exit 1
        fi
    done
}

# 验证MySQL连接
verify_mysql_connection() {
    if ! mysql -u"${USER}" -p"${PASSWORD}" -e "SELECT 1" &> /dev/null; then
        echo "错误: 无法连接到MySQL服务器"
        exit 1
    fi
}

# 执行备份
perform_backup() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] 开始备份数据库 ${DB_NAME}"
    
    mkdir -p "${FULL_BACKUP_DIR}"
    
    # 将排除的表列表转换为管道分隔的字符串
    local excluded_tables_str=$(IFS="|"; echo "${EXCLUDED_TABLES[*]}")
    
    xtrabackup \
        --user="${USER}" \
        --password="${PASSWORD}" \
        --databases="${DB_NAME}" \
        --tables-exclude="${excluded_tables_str}" \
        --socket="${SOCKET_FILE}" \
        --backup \
        --target-dir="${FULL_BACKUP_DIR}" \
        --parallel="${PARALLEL_THREADS}" \
        --compress \
        --compress-threads="${COMPRESS_THREADS}" \
        --stream=xbstream  \
        | zstd -T"${ZSTD_THREADS}" -"${ZSTD_LEVEL}" -q -o "${BACKUP_FILE}"
    
    # 验证备份文件
    if [[ ! -s "${BACKUP_FILE}" ]]; then
        echo "错误: 备份文件创建失败"
        exit 1
    fi
    
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] 备份完成，文件大小: $(du -h "${BACKUP_FILE}" | cut -f1)"
}

# 上传到OBS
upload_to_obs() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] 开始上传备份到OBS"
    
    local obs_path="obs://ibbak/db/${OBS_UPLOAD_DATE}/${PLATFORM}/$(basename "${BACKUP_FILE}")"
    
    if ! /usr/local/obsutil_linux_amd64_5.5.12/obsutil cp "${BACKUP_FILE}" "${obs_path}" >> "${BACKUP_LOG}" 2>&1; then
        echo "错误: 上传到OBS失败"
        exit 1
    fi
    
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] 上传完成"
}

# 清理旧备份
cleanup_old_backups() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] 开始清理超过${BACKUP_RETENTION_DAYS}天的备份" | tee -a "${CLEANUP_LOG}"
    
    # 清理旧目录
    find "${BASE_BACKUP_DIR}/${DB_NAME}" -type d -name "full_*" -mtime +${BACKUP_RETENTION_DAYS} \
        -exec echo "删除目录: {}" \; -exec rm -rf {} \; >> "${CLEANUP_LOG}" 2>&1
    
    # 清理旧文件
    find "${BASE_BACKUP_DIR}/${DB_NAME}" -type f -name "*.xbstream.zst" -mtime +${BACKUP_RETENTION_DAYS} \
        -exec echo "删除文件: {}" \; -exec rm -f {} \; >> "${CLEANUP_LOG}" 2>&1
    
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] 清理完成" | tee -a "${CLEANUP_LOG}"
}

# =============================================
# 主程序
# =============================================

main() {
    echo "============================================="
    echo "开始数据库备份流程 - $(date '+%Y-%m-%d %H:%M:%S')"
    echo "============================================="
    
    check_requirements
    verify_mysql_connection
    perform_backup
    upload_to_obs
    cleanup_old_backups
    
    echo "============================================="
    echo "备份流程完成 - $(date '+%Y-%m-%d %H:%M:%S')"
    echo "============================================="
}

main

直接解压并恢复压缩的流式备份 zstd -d -c backup.xbstream.zst | xbstream -x -C /path/to/restore