mysql备份方案常用策略：全量备份+增量备份，全量备份周为单位，增量备份天为单位，外加log备份确保数据备份低风险。在此我因数据量较小采用每天一次全量备份，如下为该方案：

备份脚本

#!/bin/bash
# /xworks/bak/mysql846/script/mysql_backup_dingtalk_final.sh

# MySQL配置
MYSQL_HOST="172.22.xx.xxx"
MYSQL_PORT="端口"
MYSQL_USER="账号"
MYSQL_PASS="密码"

# 备份配置
BACKUP_DIR="/exec/bak/mysql846/data/daily"
TIMESTAMP=$(date "+%Y%m%d_%H%M%S")
BACKUP_FILE="ybw_pro_${TIMESTAMP}.sql.gz"
BACKUP_PATH="${BACKUP_DIR}/${BACKUP_FILE}"

# 钉钉机器人配置
#DINGTALK_WEBHOOK="https://oapi.dingtalk.com/robot/send?access_token=637e7195c4210be170701fb158f0d钉钉token8262c95993b9a4107"
DINGTALK_WEBHOOK="https://oapi.dingtalk.com/robot/send?access_token=b269fba4钉钉tokenb10037e4b18b50168bde3d4cec"

# 备份文件阈值配置
MIN_BACKUP_SIZE_MB=5
MIN_BACKUP_SIZE=$((MIN_BACKUP_SIZE_MB * 1024 * 1024))

# 创建备份目录
mkdir -p "${BACKUP_DIR}"

# 发送钉钉通知函数（修复版）
send_dingtalk() {
    local status="$1"
    local title="$2"
    local message="$3"
    
    if [ -z "${DINGTALK_WEBHOOK}" ] || [[ ! "${DINGTALK_WEBHOOK}" =~ ^https:// ]]; then
        echo "警告: 钉钉Webhook未配置或格式不正确，跳过通知"
        return 0
    fi
    
    # 测试钉钉连接
    #echo "测试钉钉连接..."
    #local test_response=$(curl -s -X POST "${DINGTALK_WEBHOOK}" \
     #   -H "Content-Type: application/json" \
      #  -d '{"msgtype": "text", "text": {"content": "备份测试消息"}}')
    
    #if echo "$test_response" | grep -q '"errcode":0'; then
    #    echo "✓ 钉钉连接正常"
    #else
    #    echo "✗ 钉钉连接失败: $test_response"
    #    return 1
    #fi
    
    # 构建正确的JSON格式
    local json_data=$(cat <<EOF
{
    "msgtype": "markdown",
    "markdown": {
        "title": "${title}",
        "text": "${message}"
    }
}
EOF
    )
 

    echo ${json_data}   
   
    echo "发送钉钉通知..."
    echo "标题(数据库备份): ${title}"
    echo "消息预览:"
    echo "${message}" | sed 's/\\n/\n/g'
    
    # 发送请求
    local response=$(curl -s -X POST "${DINGTALK_WEBHOOK}" \
        -H "Content-Type: application/json" \
        -d "${json_data}")
    
    echo "钉钉响应: ${response}"
    
    if echo "$response" | grep -q '"errcode":0'; then
        echo "✓ 钉钉通知发送成功"
        return 0
    else
        echo "✗ 钉钉通知发送失败"
        return 1
    fi
}

# 验证数据库是否存在
check_database_exists() {
    echo "验证数据库 ybw_pro 是否存在..."
    
    local db_exists=$(docker run --rm mysql:8.0 \
        mysql -h"${MYSQL_HOST}" -P"${MYSQL_PORT}" -u"${MYSQL_USER}" -p"${MYSQL_PASS}" \
        -N -e "SELECT COUNT(*) FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = 'ybw_pro';" 2>/dev/null)
    
    if [ $? -ne 0 ]; then
        echo "数据库连接失败"
        return 1
    fi
    
    if [ "${db_exists}" -eq 1 ]; then
        echo "✓ 数据库 ybw_pro 存在"
        return 0
    else
        echo "✗ 数据库 ybw_pro 不存在"
        return 1
    fi
}

# 检查备份文件是否有效
check_backup_valid() {
    local backup_file="$1"
    
    if [ ! -f "${backup_file}" ]; then
        echo "备份文件不存在: ${backup_file}"
        return 1
    fi
    
    # 检查文件大小
    local file_size=$(stat -c%s "${backup_file}" 2>/dev/null || stat -f%z "${backup_file}" 2>/dev/null)
    
    if [ "${file_size}" -lt ${MIN_BACKUP_SIZE} ]; then
        local size_mb=$(echo "scale=2; ${file_size} / 1024 / 1024" | bc 2>/dev/null || echo "0")
        echo "警告: 备份文件过小 (${size_mb} MB)，可能有问题"
        return 1
    fi
    
    # 检查是否是有效的gzip文件
    if ! gzip -t "${backup_file}" 2>/dev/null; then
        echo "备份文件不是有效的gzip格式"
        return 1
    fi
    
    local size_mb=$(echo "scale=2; ${file_size} / 1024 / 1024" | bc 2>/dev/null || echo "0")
    echo "✓ 备份文件有效，大小: ${size_mb} MB"
    return 0
}

# 主程序
echo "=== MySQL备份开始 ==="
echo "时间: $(date '+%Y-%m-%d %H:%M:%S')"
echo "数据库: ybw_pro"
echo "最小备份大小阈值: ${MIN_BACKUP_SIZE_MB} MB"

# 注意：为了调试，暂时注释掉开始通知
# send_dingtalk "start" "🔔 MySQL备份开始" "**📊数据库:** ybw_pro\\n**⏰备份时间:** $(date '+%Y-%m-%d %H:%M:%S')\\n**状态:** 开始验证数据库..."

# 1. 验证数据库是否存在
if ! check_database_exists; then
    ERROR_MSG="数据库 ybw_pro 不存在或无法连接"
    echo "✗ ${ERROR_MSG}"
    
    # 修正消息格式：使用 \n 而不是 \\n
    send_dingtalk "failure" "❌ 数据库验证失败" "**错误:** ${ERROR_MSG}\\n**⏰备份时间:** $(date '+%Y-%m-%d %H:%M:%S')\\n**建议:** 请检查数据库名称和连接配置"
    exit 1
fi

# 2. 执行备份
echo "开始备份数据库 ybw_pro..."
START_TIME=$(date +%s)

# 注意：这里我改回了 ybw_pro，如果你想测试错误，可以改为 ybw_pro11
docker run --rm \
    -v "${BACKUP_DIR}:/backup" \
    mysql:8.0 \
    sh -c "mysqldump -h'${MYSQL_HOST}' -P'${MYSQL_PORT}' -u'${MYSQL_USER}' -p'${MYSQL_PASS}' \
    --single-transaction \
    --routines \
    --triggers \
    --events \
    --no-tablespaces \
    --column-statistics=0 \
    ybw_pro 2>&1 | tee /tmp/mysqldump_output.$$ | gzip > /backup/${BACKUP_FILE}"

BACKUP_EXIT_CODE=$?
MYSQLDUMP_OUTPUT=$(cat /tmp/mysqldump_output.$$ 2>/dev/null)
rm -f /tmp/mysqldump_output.$$

END_TIME=$(date +%s)
DURATION=$((END_TIME - START_TIME))

echo "备份命令退出码: ${BACKUP_EXIT_CODE}"
echo "备份耗时: ${DURATION}秒"

# 3. 检查备份结果
if [ ${BACKUP_EXIT_CODE} -eq 0 ]; then
    echo "备份命令执行成功，检查备份文件..."
    
    if check_backup_valid "${BACKUP_PATH}"; then
        # 获取文件大小
        SIZE_HR=$(ls -lh "${BACKUP_PATH}" | awk '{print $5}')
        SIZE_BYTES=$(stat -c%s "${BACKUP_PATH}" 2>/dev/null || stat -f%z "${BACKUP_PATH}" 2>/dev/null)
        SIZE_MB=$(echo "scale=2; ${SIZE_BYTES} / 1024 / 1024" | bc 2>/dev/null || echo "0")
        
        echo "✓ 备份成功"
        echo "  文件: ${BACKUP_FILE}"
        echo "  大小: ${SIZE_HR} (${SIZE_MB} MB)"
        echo "  耗时: ${DURATION}秒"
        
        # 发送成功通知 - 正确格式化消息
        success_message="📊数据库:** ybw_pro ** ⏰ 备份时间:** $(date '+%Y-%m-%d %H:%M:%S') \\n
**📦文件:** ${BACKUP_FILE} \\n
**📏大小:** ${SIZE_HR} (${SIZE_MB} MB) \\n
**⏱耗时:** ${DURATION}秒 \\n
**💾位置:** ${BACKUP_DIR} \\n
**状态:** ✅ 备份文件大小正常大于(${MIN_BACKUP_SIZE_MB}MB) \\n"

        
        send_dingtalk "success" "✅ 备份成功" ${success_message}
        
        # 清理旧备份
        echo "清理15天前的旧备份..."
        OLD_FILES_COUNT=$(find "${BACKUP_DIR}" -name "ybw_pro_*.sql.gz" -mtime +15 | wc -l)
        
        if [ ${OLD_FILES_COUNT} -gt 0 ]; then
            find "${BACKUP_DIR}" -name "ybw_pro_*.sql.gz" -mtime +15 -delete 2>/dev/null
            echo "已清理 ${OLD_FILES_COUNT} 个旧备份文件"
        fi
        
    else
        # 获取文件大小信息
        if [ -f "${BACKUP_PATH}" ]; then
            SIZE_BYTES=$(stat -c%s "${BACKUP_PATH}" 2>/dev/null || stat -f%z "${BACKUP_PATH}" 2>/dev/null)
            SIZE_MB=$(echo "scale=2; ${SIZE_BYTES} / 1024 / 1024" | bc 2>/dev/null || echo "0")
            SIZE_HR=$(ls -lh "${BACKUP_PATH}" | awk '{print $5}' 2>/dev/null || echo "未知")
        else
            SIZE_MB=0
            SIZE_HR="文件不存在"
        fi
        
        ERROR_MSG="备份文件异常"
        
        if [ $(echo "${SIZE_MB} < ${MIN_BACKUP_SIZE_MB}" | bc 2>/dev/null || echo "1") -eq 1 ]; then
            ERROR_MSG="${ERROR_MSG} - 文件过小(${SIZE_MB} MB < ${MIN_BACKUP_SIZE_MB} MB)"
        fi
        
        echo "✗ ${ERROR_MSG}"
        
        # 删除无效备份文件
        rm -f "${BACKUP_PATH}"
        
        # 构建错误消息
        error_message=$(cat <<EOF
**🔔错误:** ${ERROR_MSG}
**⏰备份时间:** $(date '+%Y-%m-%d %H:%M:%S')
**📦文件:** ${BACKUP_FILE}
**📏大小:** ${SIZE_HR}
**⏱耗时:** ${DURATION}秒
**⚠️阈值:** 最小${MIN_BACKUP_SIZE_MB}MB
**建议:** 请检查数据库内容和备份参数
EOF
)
        
        send_dingtalk "failure" "⚠️ 备份文件异常" "${error_message}"
        exit 1
    fi
    
else
    # 备份命令失败
    ERROR_MSG="mysqldump命令执行失败 (退出码: ${BACKUP_EXIT_CODE})"
    echo "✗ ${ERROR_MSG}"
    
    if [ -n "${MYSQLDUMP_OUTPUT}" ]; then
        echo "错误输出:"
        echo "${MYSQLDUMP_OUTPUT}"
    fi
    
    # 删除可能生成的空文件
    rm -f "${BACKUP_PATH}"
    
    # 构建失败消息
    failure_message=$(cat <<EOF
**🔔错误:** ${ERROR_MSG}
**⏰备份时间:** $(date '+%Y-%m-%d %H:%M:%S')
**📊数据库:** ybw_pro
**⏱耗时:** ${DURATION}秒
EOF
)
    
    send_dingtalk "failure" "❌ 备份失败" "${failure_message}"
    exit 1
fi

echo "=== 备份完成 ==="

给文件执行权限

chmod +x /exec/bak/mysql846/script/exec.sh

编辑定时任务

#编辑定时任务
crontab -e
#设置每日凌晨执行,并输出日志到固定目录
0 5 * * * /exec/bak/mysql846/script/exec2.sh >> /exec/bak/mysql846/logs/mysql_bak_corn.log 2>&1

钉钉发送效果