#!/bin/bash

# MySQL credentials
MYSQL_USER="root"
MYSQL_PASSWORD="CQ7tW^LVFa:&r8QfqV7%2PD"

# Backup directory
BACKUP_DIR="/02-db-backup"

# Date format
DATE_FORMAT=$(date +"%Y%m%d_%H%M%S")

# Create backup directory if it does not exist
mkdir -p "$BACKUP_DIR"

# Get the list of databases
DATABASES=$(mysql -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema|mysql|sys)")

# Backup each database
for DB in $DATABASES; do
    BACKUP_FILE="${BACKUP_DIR}/${DB}_${DATE_FORMAT}.sql"
    echo "Backing up database $DB to $BACKUP_FILE"
    mysqldump -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" "$DB" > "$BACKUP_FILE"
done

# Remove backups older than 1 day
find "$BACKUP_DIR" -type f -name "*.sql" -mtime +1 -exec rm {} \;

echo "Backup and cleanup completed."



# Transfer backups to the remote server
REMOTE_SERVER="38.242.229.45"
REMOTE_USER="root"
REMOTE_PASSWORD="4=&L7qw3@PxFH"
REMOTE_DIR="/02-db-backup/207-244-255-251"

echo "Transferring backups to the remote server $REMOTE_SERVER..."
sshpass -p "$REMOTE_PASSWORD" scp "$BACKUP_DIR"/*.sql "$REMOTE_USER@$REMOTE_SERVER:$REMOTE_DIR"

echo "Transfer completed."