作者: whooyun发表于: 2025-07-07 12:02
#!/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"