#!/bin/bash
#----------------------------------------
# OPTIONS
#----------------------------------------

DB_USER='root'
#DB_PASS='JustSMS@2023$#$DB'
DB_PASS='1q3e8i9o@#'
BACKUP_DIR=/var/www/html/sqlbackup/sqlfiles/$(date +\%F)
Delete_DIR=/var/www/html//sqlbackup/sqlfiles/
DAYS_TO_KEEP=10    # 0 to keep forever

# Create a backup directory with today's date
mkdir -p "$BACKUP_DIR"

# Get a list of all databases (excluding system databases)
databases=$(mysql -u "$DB_USER" -p"$DB_PASS" -e "SHOW DATABASES;" | awk '{print $1}' | tail -n +2 | grep -v -E "^(information_schema|performance_schema|mysql|sys)$")

# Loop through each database
for DB in $databases; do
    DB_DIR="$BACKUP_DIR/$DB"
    mkdir -p "$DB_DIR"
    
    # Get a list of all tables in the database
    tables=$(mysql -u "$DB_USER" -p"$DB_PASS" -D "$DB" -e "SHOW TABLES;" | awk '{print $1}' | tail -n +2)

    # Backup each table separately
    for table in $tables; do
        echo "Backing up $DB.$table..."
        mysqldump -u "$DB_USER" -p"$DB_PASS" "$DB" "$table" > "$DB_DIR/$table.sql"
    done
done

echo "Backup completed! Files are stored in: $BACKUP_DIR"

# Delete old backups
if [ "$DAYS_TO_KEEP" -gt 0 ]; then
  LOGFILE="$Delete_DIR/backup_cleanup.log"
  echo "Deleting backups older than $DAYS_TO_KEEP days in $Delete_DIR"
  # Delete old files first
  find "$Delete_DIR" -type f -mtime +"$DAYS_TO_KEEP" -exec rm -f {} + >> "$LOGFILE" 2>&1
    
    # Delete old directories
  find "$Delete_DIR" -type d -mtime +"$DAYS_TO_KEEP" -exec rm -rf {} + >> "$LOGFILE" 2>&1
 echo "$(date) - Cleanup completed for $Delete_DIR" | tee -a "$LOGFILE"
  else
    echo "Directory $Delete_DIR does not exist. Skipping..." | tee -a "$LOGFILE"
fi