远程备份服务器数据库shell脚本

#!/bin/bash
# 配置参数
ONLINE_DB_USER="xxx"
ONLINE_DB_PASS="xxx"
ONLINE_DB_HOST="192.168.86.21"
ONLINE_DB_PORT="3306"
BACKUP_DIR="./data/mysql_backups"
LOG_FILE="./data/log/mysql_backup.log"
DATE=$(date +%Y%m%d)
RETENTION_DAYS=7

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

# 记录开始时间
echo "[$(date +'%Y-%m-%d %H:%M:%S')] 开始备份" >> $LOG_FILE

# 执行备份 (使用gzip压缩)
mysqldump -h $ONLINE_DB_HOST -P $ONLINE_DB_PORT -u $ONLINE_DB_USER -p"$ONLINE_DB_PASS" \
--single-transaction \
--routines \
--triggers \
--events \
--all-databases | gzip > $BACKUP_DIR/full_backup_$DATE.sql.gz

# 检查备份是否成功
if [ ${PIPESTATUS[0]} -ne 0 ]; then
echo "[$(date +'%Y-%m-%d %H:%M:%S')] 错误: mysqldump执行失败" >> $LOG_FILE
exit 1
fi

# 验证备份文件
if [ ! -s $BACKUP_DIR/full_backup_$DATE.sql.gz ]; then
echo "[$(date +'%Y-%m-%d %H:%M:%S')] 错误: 备份文件为空" >> $LOG_FILE
exit 1
fi

# 清理旧备份
find $BACKUP_DIR -name "full_backup_*.sql.gz" -type f -mtime +$RETENTION_DAYS -delete

# 记录完成时间
echo "[$(date +'%Y-%m-%d %H:%M:%S')] 备份成功完成" >> $LOG_FILE