数据库备份(通义千问写的)

#!/bin/bash

# 配置参数
DB_NAME="my_db"
DB_USER="user01"
DB_PASSWORD="1234556"
DB_HOST="192.168.2.3"
BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
WEEKDAY=$(date +%u)  # 1=周一, 7=周日
LOG_FILE="/var/log/mysql_backup.log"

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

# 记录备份开始时间
echo "=== Backup started at $(date) ===" >> $LOG_FILE

# 判断是否为周日（全量备份）
if [ "$WEEKDAY" -eq 7 ]; then
    echo "Full backup triggered..." >> $LOG_FILE
    BACKUP_TYPE="full"
    BACKUP_FILE="$BACKUP_DIR/my_db_${BACKUP_TYPE}_${DATE}.sql"

    # 全量备份
    mysqldump -h$DB_HOST -u$DB_USER -p$DB_PASSWORD --single-transaction --flush-logs --master-data=2 $DB_NAME > $BACKUP_FILE

    if [ $? -eq 0 ]; then
        echo "Full backup completed: $BACKUP_FILE" >> $LOG_FILE
        # 保存当前binlog位置（用于增量备份）
        mysql -h$DB_HOST -u$DB_USER -p$DB_PASSWORD -e "SHOW MASTER STATUS;" | awk 'NR==2 {print $1" "$2}' > $BACKUP_DIR/last_backup.info
    else
        echo "Full backup failed!" >> $LOG_FILE
    fi

else
    echo "Incremental backup triggered..." >> $LOG_FILE
    BACKUP_TYPE="incr"

    # 获取上一次备份的binlog位置
    if [ ! -f "$BACKUP_DIR/last_backup.info" ]; then
        echo "Error: last_backup.info not found! Please run a full backup first." >> $LOG_FILE
        exit 1
    fi

    LAST_LOG_FILE=$(cat $BACKUP_DIR/last_backup.info | awk '{print $1}')
    LAST_LOG_POS=$(cat $BACKUP_DIR/last_backup.info | awk '{print $2}')

    # 获取当前binlog状态
    CURRENT_LOG_FILE=$(mysql -h$DB_HOST -u$DB_USER -p$DB_PASSWORD -e "SHOW MASTER STATUS;" | awk 'NR==2 {print $1}')
    CURRENT_LOG_POS=$(mysql -h$DB_HOST -u$DB_USER -p$DB_PASSWORD -e "SHOW MASTER STATUS;" | awk 'NR==2 {print $2}')

    # 生成增量备份文件
    BACKUP_FILE="$BACKUP_DIR/my_db_${BACKUP_TYPE}_${DATE}.sql"
    mysqlbinlog --host=$DB_HOST --user=$DB_USER --password=$DB_PASSWORD \
        --start-position=$LAST_LOG_POS \
        --stop-position=$CURRENT_LOG_POS \
        $LAST_LOG_FILE > $BACKUP_FILE

    if [ $? -eq 0 ]; then
        echo "Incremental backup completed: $BACKUP_FILE" >> $LOG_FILE
        # 更新last_backup.info为当前binlog状态
        echo "$CURRENT_LOG_FILE $CURRENT_LOG_POS" > $BACKUP_DIR/last_backup.info
    else
        echo "Incremental backup failed!" >> $LOG_FILE
    fi
fi

# 记录备份结束时间
echo "=== Backup finished at $(date) ===" >> $LOG_FILE