#!/bin/bash
# MySQL备份脚本（修复版）
DB_USER="why"
DB_NAME="demogo"
DB_PASSWORD="123456"
BACKUP_DIR="/home/bakdb"  # 请确保此目录存在且有写入权限
DATE=$(date +%Y%m%d_%H%M%S)
LOG_FILE="$BACKUP_DIR/backup.log"

# 创建备份目录（如果不存在）
mkdir -p "$BACKUP_DIR"

# 开始备份
echo "[$(date +'%Y-%m-%d %H:%M:%S')] === 开始备份数据库: $DB_NAME ===" | tee -a "$LOG_FILE"

# 测试MySQL连接
if ! mysql -u"$DB_USER" -p"$DB_PASSWORD" -e "USE $DB_NAME;" 2>> "$LOG_FILE"; then
    echo "[$(date +'%Y-%m-%d %H:%M:%S')] 错误: 无法连接MySQL! 请检查:" | tee -a "$LOG_FILE"
    echo "  1. MySQL服务状态 (sudo systemctl status mysql)" | tee -a "$LOG_FILE"
    echo "  2. 用户名/密码是否正确" | tee -a "$LOG_FILE"
    echo "  3. 用户 '$DB_USER' 是否有访问 '$DB_NAME' 的权限" | tee -a "$LOG_FILE"
    exit 1
fi

# 使用mysqldump备份（修复版）
BACKUP_FILE="$BACKUP_DIR/${DB_NAME}_backup_$DATE.sql.gz"
mysqldump --single-transaction --routines --triggers --events \
  -u"$DB_USER" -p"$DB_PASSWORD" "$DB_NAME" 2>> "$LOG_FILE" | gzip > "$BACKUP_FILE"

# 检查备份结果
if [ ! -s "$BACKUP_FILE" ]; then
    echo "[$(date +'%Y-%m-%d %H:%M:%S')] 严重错误: 备份文件为空! 可能原因:" | tee -a "$LOG_FILE"
    echo "  1. 数据库 '$DB_NAME' 不存在" | tee -a "$LOG_FILE"
    echo "  2. 用户 '$DB_USER' 没有备份权限" | tee -a "$LOG_FILE"
    echo "  3. mysqldump命令执行失败" | tee -a "$LOG_FILE"
    echo "  请检查日志: $LOG_FILE" | tee -a "$LOG_FILE"
    
    # 获取最后5行错误日志
    echo "=== 最近错误日志 ===" | tee -a "$LOG_FILE"
    tail -n 5 "$LOG_FILE" | tee -a "$LOG_FILE"
    
    # 删除空备份文件
    rm -f "$BACKUP_FILE"
    exit 1
fi

# 验证备份文件
BACKUP_SIZE=$(du -h "$BACKUP_FILE" | awk '{print $1}')
echo "[$(date +'%Y-%m-%d %H:%M:%S')] 备份成功! 文件: $(basename "$BACKUP_FILE") (大小: $BACKUP_SIZE)" | tee -a "$LOG_FILE"

# 检查备份内容是否有效
if ! gzip -t "$BACKUP_FILE" 2>> "$LOG_FILE"; then
    echo "[$(date +'%Y-%m-%d %H:%M:%S')] 警告: 备份文件损坏!" | tee -a "$LOG_FILE"
    exit 1
fi

# 删除30天前的备份
echo "[$(date +'%Y-%m-%d %H:%M:%S')] 清理旧备份..." | tee -a "$LOG_FILE"
find "$BACKUP_DIR" -name "*.sql.gz" -mtime +30 -exec rm -v {} \; 2>&1 | tee -a "$LOG_FILE"

echo "[$(date +'%Y-%m-%d %H:%M:%S')] 备份任务完成 √" | tee -a "$LOG_FILE"