#!/bin/bash
# MySQL backup script — runs daily via cron
# Backs up all databases to /backup/mysql

DATE=$(date +%Y-%m-%d)
BACKUP_DIR="/backup/mysql/daily"
WEEKLY_DIR="/backup/mysql/weekly"
MONTHLY_DIR="/backup/mysql/monthly"
LOG="/backup/logs/mysql-backup.log"
KEEP_DAILY=7
KEEP_WEEKLY=4
KEEP_MONTHLY=3

echo "[$DATE] Starting MySQL backup" >> $LOG

# Dump all databases individually
for DB in $(mysql -e "SHOW DATABASES;" | grep -v "Database\|information_schema\|performance_schema\|sys"); do
    mysqldump \
        --single-transaction \
        --quick \
        --lock-tables=false \
        "$DB" | gzip > "$BACKUP_DIR/${DB}_${DATE}.sql.gz"
    
    if [ $? -eq 0 ]; then
        echo "[$DATE] ✓ Backed up: $DB" >> $LOG
    else
        echo "[$DATE] ✗ FAILED: $DB" >> $LOG
    fi
done

# Weekly backup — every Sunday
if [ $(date +%u) -eq 7 ]; then
    cp -r $BACKUP_DIR/. $WEEKLY_DIR/
    echo "[$DATE] Weekly backup copied" >> $LOG
fi

# Monthly backup — 1st of month
if [ $(date +%d) -eq 01 ]; then
    cp -r $BACKUP_DIR/. $MONTHLY_DIR/
    echo "[$DATE] Monthly backup copied" >> $LOG
fi

# Cleanup old backups
find $BACKUP_DIR -name "*.sql.gz" -mtime +$KEEP_DAILY -delete
find $WEEKLY_DIR -name "*.sql.gz" -mtime +$((KEEP_WEEKLY * 7)) -delete
find $MONTHLY_DIR -name "*.sql.gz" -mtime +$((KEEP_MONTHLY * 30)) -delete

echo "[$DATE] MySQL backup complete" >> $LOG