MySQL Database Backup and Upload to AWS S3

MySQL Database Backup and Upload to AWS S3

This Bash script creates backups of application MySQL databases, optionally compresses them as .sql.gz files, uploads the backup folder to Amazon S3, and removes older local backup folders.

What This Script Does

• Tests the MySQL connection.
• Finds application databases.
• Creates one backup file per database.
• Optionally compresses backups with Gzip.
• Uploads backups to Amazon S3.
• Uses S3 STANDARD_IA storage.
• Logs the backup and upload process.
• Deletes old local backup folders after the retention period.

1. Requirements

• MySQL client tools
• mysqldump
• AWS CLI
• Permission to read the MySQL databases
• Permission to upload to the target S3 bucket

2. Configure the Script

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

S3_DB_BUCKET="s3://YOUR_BUCKET/DbBackup"

DAYS_TO_KEEP controls how long local backup folders are kept. With 2, backup folders older than two days are removed.

GZIP=1 creates compressed .sql.gz files. Set it to 0 to keep plain .sql files.

3. Complete Bash Script

Save the script as mysql_backup_upload_s3.sh:

#!/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 =========="

4. How It Works

Test MySQL

mysql -h "$MYSQL_HOST"       -u "$MYSQL_USER"       -p"$MYSQL_PASSWORD"       -e "select 1;"

The script checks that MySQL is reachable before starting the backup.

Find Application Databases

mysql -N -h "$MYSQL_HOST" -u "$MYSQL_USER" -p"$MYSQL_PASSWORD" -e "show databases"

System databases are excluded, including information_schema, performance_schema, mysql, sys, and phpmyadmin.

Create Database Backups

mysqldump --routines --triggers --single-transaction ...

Each application database is exported separately. With Gzip enabled, a database named exampledb becomes:

exampledb.sql.gz

Upload to S3

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

The backup files are uploaded to a timestamped folder in S3.

Backup Folder Structure

DbBackup/
└── 20260918-173200/
    ├── database1.sql.gz
    ├── database2.sql.gz
    └── database3.sql.gz

Delete Old Local Backups

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

This removes older local backup folders according to the configured retention period.

5. Make the Script Executable

chmod +x mysql_backup_upload_s3.sh

6. Run the Backup

sudo ./mysql_backup_upload_s3.sh

7. Example Output

[2026-09-18 17:32:00] ========== START ==========
[2026-09-18 17:32:01] Backing up app_database
[2026-09-18 17:32:05] Backing up portal_database
[2026-09-18 17:32:12] Uploading DB backups...
[2026-09-18 17:32:18] DB files uploaded: 2
[2026-09-18 17:32:18] ========== COMPLETED ==========

8. Backup Logs

/var/www/vhost/dbbackup/backup-YYYYMMDD-HHMMSS.log

A separate log is created for each backup run, which helps with troubleshooting failed dumps or uploads.

9. Automate with Cron

For example, to run the backup every day at 2:00 AM:

0 2 * * * /path/to/mysql_backup_upload_s3.sh

Test the script manually before adding it to cron.

Quick Reference

# Make executable
chmod +x mysql_backup_upload_s3.sh

# Run backup
sudo ./mysql_backup_upload_s3.sh

Important Notes

• Keep MySQL credentials out of publicly accessible files.
• Use an IAM role or secure AWS credential mechanism where possible.
• Test restoring a backup regularly.
• Make sure the S3 bucket has appropriate access controls.
• Review S3 storage costs when choosing a storage class.
• Keep an appropriate local and S3 retention policy.

Conclusion

This script automates a practical MySQL backup workflow: discover databases, create compressed dumps, upload them to S3, log the operation, and clean up older local backups.