Автоматическое Резервное Копирование MySQL

#!/bin/bash

# MySQL Automated Backup Script

BACKUP_DIR="/backup/mysql"
MYSQL_USER="root"
MYSQL_PASSWORD="your_password"
RETENTION_DAYS=7
DATE=$(date +%Y%m%d_%H%M%S)

mkdir -p "$BACKUP_DIR"

echo "======================================"
echo "   MYSQL BACKUP PROCESS"
echo "======================================"
echo "Backup directory: $BACKUP_DIR"
echo "Date: $DATE"
echo "Retention: $RETENTION_DAYS days"
echo ""

DATABASES=$(mysql -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema|mysql|sys)")

for DB in $DATABASES; do
    echo "📦 Backing up database: $DB"
    BACKUP_FILE="$BACKUP_DIR/${DB}_${DATE}.sql"
    
    mysqldump -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" \
        --single-transaction \
        --routines \
        --triggers \
        --events \
        "$DB" > "$BACKUP_FILE"
    
    if [ $? -eq 0 ]; then
        gzip "$BACKUP_FILE"
        echo "  ✓ Backup completed: ${BACKUP_FILE}.gz"
        SIZE=$(du -h "${BACKUP_FILE}.gz" | cut -f1)
        echo "  📊 Size: $SIZE"
    else
        echo "  ✗ Backup failed for database: $DB"
        echo "  ⚠️  Check MySQL credentials and permissions"
    fi
    echo ""
done

echo "🧹 Cleaning backups older than $RETENTION_DAYS days..."
find "$BACKUP_DIR" -name "*.sql.gz" -mtime +$RETENTION_DAYS -delete
echo "✓ Cleanup completed"
echo ""

echo "======================================"
echo "   BACKUP SUMMARY"
echo "======================================"
echo "Total backup files:"
ls -lh "$BACKUP_DIR"/*.gz 2>/dev/null | wc -l
echo ""
echo "Total disk usage:"
du -sh "$BACKUP_DIR"
echo ""
echo "Latest 5 backups:"
ls -lth "$BACKUP_DIR"/*.gz 2>/dev/null | head -n 5
echo ""
echo "======================================"
echo "Backup process completed at $(date)"
echo "======================================"

# Edit script and enter your credentials
nano mysql_backup.sh

# Make executable
chmod +x mysql_backup.sh

# Run manually
sudo ./mysql_backup.sh

# Automate with crontab (daily at 2:00 AM)
sudo crontab -e
# Add: 0 2 * * * /path/to/mysql_backup.sh