136 lines
3.7 KiB
Bash
136 lines
3.7 KiB
Bash
#!/bin/bash
|
|
|
|
# MySQL 连接参数
|
|
MYSQL_USER="cyq"
|
|
MYSQL_PASSWORD="root123123"
|
|
MYSQL_HOST="localhost"
|
|
MYSQL_PORT="3306"
|
|
DATABASE="cdsdb"
|
|
|
|
# 备份目录设置
|
|
BASE_BACKUP_DIR="/home/cyq/backup/mysql"
|
|
DAILY_BACKUP_DIR="$BASE_BACKUP_DIR/daily"
|
|
MONTHLY_BACKUP_DIR="$BASE_BACKUP_DIR/monthly"
|
|
LOG_FILE="$BASE_BACKUP_DIR/backup.log"
|
|
|
|
# 获取当前日期和时间信息
|
|
CURRENT_DATE=$(date +%Y%m%d)
|
|
CURRENT_TIME=$(date +%H%M%S)
|
|
CURRENT_DAY=$(date +%d)
|
|
CURRENT_MONTH=$(date +%m)
|
|
CURRENT_YEAR=$(date +%Y)
|
|
CURRENT_DAY_DIR="$DAILY_BACKUP_DIR/$CURRENT_DATE"
|
|
BACKUP_DIR="$CURRENT_DAY_DIR/$CURRENT_TIME"
|
|
|
|
# 判断是否为月底（每月最后一天）
|
|
TOMORROW_DAY=$(date -d tomorrow +%d)
|
|
if [ "$TOMORROW_DAY" -eq "01" ]; then
|
|
IS_MONTH_END=true
|
|
MONTHLY_DIR="$MONTHLY_BACKUP_DIR/${CURRENT_YEAR}${CURRENT_MONTH}"
|
|
else
|
|
IS_MONTH_END=false
|
|
fi
|
|
|
|
# 创建备份目录
|
|
mkdir -p "$BACKUP_DIR"
|
|
mkdir -p "$MONTHLY_BACKUP_DIR"
|
|
touch "$LOG_FILE"
|
|
|
|
# 日志函数
|
|
log() {
|
|
echo "$(date +'%Y-%m-%d %H:%M:%S') - $1" | tee -a "$LOG_FILE"
|
|
}
|
|
|
|
# 错误处理函数
|
|
error_exit() {
|
|
log "错误: $1"
|
|
exit 1
|
|
}
|
|
|
|
# 开始备份
|
|
log "开始备份数据库 $DATABASE"
|
|
log "备份目录: $BACKUP_DIR"
|
|
|
|
# 1. 整库备份
|
|
FULL_BACKUP_FILE="$BACKUP_DIR/${DATABASE}_full.sql.gz"
|
|
log "创建整库备份: $FULL_BACKUP_FILE"
|
|
|
|
if ! mysqldump -h "$MYSQL_HOST" -P "$MYSQL_PORT" -u "$MYSQL_USER" -p"$MYSQL_PASSWORD" \
|
|
--single-transaction --routines --triggers --events \
|
|
"$DATABASE" | gzip > "$FULL_BACKUP_FILE"; then
|
|
error_exit "整库备份失败"
|
|
fi
|
|
|
|
# 检查备份文件是否有效
|
|
if [ ! -s "$FULL_BACKUP_FILE" ]; then
|
|
error_exit "整库备份文件为空或不存在"
|
|
fi
|
|
|
|
log "整库备份成功，文件大小: $(du -h "$FULL_BACKUP_FILE" | cut -f1)"
|
|
|
|
# 2. 分表备份
|
|
log "开始分表备份"
|
|
TABLES=$(mysql -h "$MYSQL_HOST" -P "$MYSQL_PORT" -u "$MYSQL_USER" -p"$MYSQL_PASSWORD" \
|
|
-e "SHOW TABLES FROM $DATABASE" -s --skip-column-names)
|
|
|
|
if [ $? -ne 0 ]; then
|
|
error_exit "获取表列表失败"
|
|
fi
|
|
|
|
for TABLE in $TABLES; do
|
|
TABLE_BACKUP_FILE="$BACKUP_DIR/${TABLE}.sql.gz"
|
|
log "备份表: $TABLE -> $TABLE_BACKUP_FILE"
|
|
|
|
if ! mysqldump -h "$MYSQL_HOST" -P "$MYSQL_PORT" -u "$MYSQL_USER" -p"$MYSQL_PASSWORD" \
|
|
--single-transaction "$DATABASE" "$TABLE" | gzip > "$TABLE_BACKUP_FILE"; then
|
|
log "警告: 表 $TABLE 备份失败"
|
|
continue
|
|
fi
|
|
|
|
if [ -s "$TABLE_BACKUP_FILE" ]; then
|
|
log "表 $TABLE 备份成功，文件大小: $(du -h "$TABLE_BACKUP_FILE" | cut -f1)"
|
|
else
|
|
log "警告: 表 $TABLE 备份文件为空"
|
|
fi
|
|
done
|
|
|
|
# 3. 如果是月底，移动备份目录到月度目录
|
|
if [ "$IS_MONTH_END" = true ]; then
|
|
log "本月最后一天，备份目录将移动到月度目录: $MONTHLY_DIR"
|
|
mkdir -p "$MONTHLY_DIR"
|
|
|
|
# 移动整个备份目录到月度目录
|
|
mv -f "$BACKUP_DIR" "$MONTHLY_DIR/"
|
|
|
|
# 验证文件是否移动成功
|
|
if [ $? -eq 0 ]; then
|
|
log "月底备份已成功移动到月度目录"
|
|
else
|
|
log "警告: 移动文件到月度目录时出错"
|
|
fi
|
|
fi
|
|
|
|
# 4. 清理旧备份
|
|
log "清理超过3天的日常备份"
|
|
find "$DAILY_BACKUP_DIR" -type d -mtime +3 -exec rm -rf {} \;
|
|
|
|
log "清理超过12个月的月度备份"
|
|
find "$MONTHLY_BACKUP_DIR" -type d -mtime +365 -exec rm -rf {} \;
|
|
|
|
# 5. 检查磁盘空间
|
|
DISK_USAGE=$(df -h "$BASE_BACKUP_DIR" | awk 'NR==2 {print $5}' | tr -d '%')
|
|
if [ "$DISK_USAGE" -gt 90 ]; then
|
|
log "警告: 备份目录磁盘使用率超过90%"
|
|
fi
|
|
|
|
# 备份完成
|
|
log "数据库备份完成"
|
|
log "本次备份文件列表:"
|
|
find "$BACKUP_DIR" -name "*.sql.gz" -exec ls -lh {} \; | tee -a "$LOG_FILE"
|
|
|
|
if [ "$IS_MONTH_END" = true ]; then
|
|
log "月底备份位置: $MONTHLY_DIR/$CURRENT_TIME"
|
|
fi
|
|
|
|
log "备份任务完成"
|