#!/bin/bash
set -euo pipefail

############################
# Configuration
############################
MYSQL_HOST="localhost"
MYSQL_USER="YOUR_MYSQL_USER"
MYSQL_PASSWORD="YOUR_MYSQL_PASSWORD"

BACKUP_ROOT="/var/www/vhost/dbbackup"
DAYS_TO_KEEP=2
GZIP=1

MYSQLDUMP_OPTIONS="--routines --triggers --single-transaction --set-gtid-purged=OFF --column-statistics=0"

S3_DB_BUCKET="s3://YOUR_BUCKET/DbBackup"

AWS=$(command -v aws)
TIMESTAMP=$(date +%Y%m%d-%H%M%S)
BACKUP_PATH="$BACKUP_ROOT/$TIMESTAMP"
BACKUP_LOG="$BACKUP_ROOT/backup-$TIMESTAMP.log"
SYNC_LOG="/var/scripts/s3_sync_count.log"

mkdir -p "$BACKUP_PATH"

log() {
    echo "[$(date '+%F %T')] $*" | tee -a "$BACKUP_LOG"
}

[ -x "$AWS" ] || {
    echo "AWS CLI not found"
    exit 1
}

log "========== START =========="

# Test MySQL connection
mysql -h "$MYSQL_HOST" \
      -u "$MYSQL_USER" \
      -p"$MYSQL_PASSWORD" \
      -e "select 1;" >/dev/null

# Get application databases
DBS=$(mysql -N \
    -h "$MYSQL_HOST" \
    -u "$MYSQL_USER" \
    -p"$MYSQL_PASSWORD" \
    -e "show databases" |
    grep -Ev '^(information_schema|performance_schema|mysql|sys|phpmyadmin)$')

DBCOUNT=0

# Backup each database
for db in $DBS; do

    log "Backing up $db"

    if [ "$GZIP" -eq 1 ]; then

        mysqldump \
            -h "$MYSQL_HOST" \
            -u "$MYSQL_USER" \
            -p"$MYSQL_PASSWORD" \
            $MYSQLDUMP_OPTIONS \
            "$db" |
            gzip -c > "$BACKUP_PATH/$db.sql.gz"

    else

        mysqldump \
            -h "$MYSQL_HOST" \
            -u "$MYSQL_USER" \
            -p"$MYSQL_PASSWORD" \
            $MYSQLDUMP_OPTIONS \
            "$db" \
            > "$BACKUP_PATH/$db.sql"
    fi

    DBCOUNT=$((DBCOUNT + 1))
done

log "Uploading DB backups..."

$AWS s3 sync \
    "$BACKUP_PATH" \
    "$S3_DB_BUCKET/$TIMESTAMP" \
    --storage-class STANDARD_IA \
    --only-show-errors

DB_UPLOAD_COUNT=$(
    $AWS s3 ls "$S3_DB_BUCKET/$TIMESTAMP" --recursive |
    wc -l
)

log "DB files uploaded: $DB_UPLOAD_COUNT"

# Delete old backup folders
find "$BACKUP_ROOT" \
    -mindepth 1 \
    -maxdepth 1 \
    -type d \
    -mtime +"$DAYS_TO_KEEP" \
    -exec rm -rf {} \;

log "========== COMPLETED =========="