MySQL backup to cloud — Backing up MySQL databases to AWS S3 and Google Cloud

Storing MySQL backups on the same server as your database is asking for trouble. If the server fails, you lose both your data and your backups. Cloud storage solves this by keeping backups offsite, automatically replicated across multiple data centers. This guide covers how to back up MySQL databases to AWS S3 and Google Cloud Storage, from manual uploads to fully automated pipelines.

Why cloud storage for MySQL backups

Local backups work until they don’t. A disk failure, ransomware attack, or datacenter issue can wipe out everything on one machine. Cloud storage provides geographic redundancy and durability that local storage can’t match.

Durability and availability

AWS S3 offers 99.999999999% (11 nines) durability. Google Cloud Storage provides similar guarantees. That means if you store 10 million objects, you’d statistically lose one every 10,000 years. Compare that to a single hard drive with 1-3% annual failure rate.

Cost efficiency

Cloud storage costs less than maintaining redundant local infrastructure. A terabyte on S3 Standard costs about $23/month. Infrequent access tiers drop to $12.50/month. Glacier deep archive goes as low as $0.99/month. For backups you rarely access, cold storage is remarkably cheap.

Operational simplicity

No hardware to manage, no capacity planning, no failed disks to replace. Upload your backups and the cloud provider handles replication, durability and availability.

Creating MySQL backups with mysqldump

Before uploading to cloud storage, you need a backup file. mysqldump is the standard tool for MySQL logical backups.

Basic mysqldump usage

Create a full database backup:

mysqldump -u root -p --single-transaction --databases mydb > mydb_backup.sql

The --single-transaction flag ensures a consistent snapshot without locking tables for InnoDB databases.

Compressed backups

Compress the backup to reduce upload time and storage costs:

mysqldump -u root -p --single-transaction --databases mydb | gzip > mydb_backup.sql.gz

Compression typically reduces MySQL dump files by 70-90%, depending on data content. A 10GB dump might compress to 1-2GB.

All databases backup

Back up all databases on the server:

mysqldump -u root -p --single-transaction --all-databases | gzip > all_databases.sql.gz

Backup with timestamp

Include timestamps in filenames for easier management:

TIMESTAMP=$(date +%Y%m%d_%H%M%S)
mysqldump -u root -p --single-transaction --databases mydb | gzip > mydb_${TIMESTAMP}.sql.gz

Uploading to AWS S3

AWS S3 is the most widely used object storage service. Getting backups there requires the AWS CLI and proper credentials.

Setting up AWS CLI

Install the AWS CLI:

# Linux/macOS
curl "https://awscli.amazonaws.com/awscli-exe-linux-x86_64.zip" -o "awscliv2.zip"
unzip awscliv2.zip
sudo ./aws/install

# Verify installation
aws --version

Configure credentials:

aws configure
# Enter your AWS Access Key ID
# Enter your AWS Secret Access Key
# Enter default region (e.g., us-east-1)
# Enter output format (json)

Creating an S3 bucket

Create a bucket for your backups:

aws s3 mb s3://my-mysql-backups --region us-east-1

Enable versioning to protect against accidental deletions:

aws s3api put-bucket-versioning 
    --bucket my-mysql-backups 
    --versioning-configuration Status=Enabled

Uploading backup files

Upload a single backup:

aws s3 cp mydb_backup.sql.gz s3://my-mysql-backups/daily/

Upload with metadata:

aws s3 cp mydb_backup.sql.gz s3://my-mysql-backups/daily/ 
    --metadata "database=mydb,created=$(date -Iseconds)"

Multipart uploads for large files

For backups over 5GB, use multipart upload for reliability:

aws s3 cp large_backup.sql.gz s3://my-mysql-backups/ 
    --expected-size $(stat -f%z large_backup.sql.gz)

The AWS CLI handles multipart uploads automatically for large files.

Combined backup and upload script

Backup and upload in one operation:

#!/bin/bash
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
BUCKET="s3://my-mysql-backups"
DB_NAME="mydb"

# Backup and compress
mysqldump -u root -p"$MYSQL_PASSWORD" --single-transaction --databases $DB_NAME | 
    gzip | 
    aws s3 cp - "$BUCKET/daily/${DB_NAME}_${TIMESTAMP}.sql.gz"

echo "Backup uploaded to $BUCKET/daily/${DB_NAME}_${TIMESTAMP}.sql.gz"

The - after aws s3 cp reads from stdin, allowing direct pipe from mysqldump without creating a local file first. This saves disk space and time.

Uploading to Google Cloud Storage

Google Cloud Storage (GCS) offers similar capabilities with different tooling.

Setting up gsutil

Install the Google Cloud SDK:

# Linux/macOS
curl https://sdk.cloud.google.com | bash
exec -l $SHELL
gcloud init

Authenticate:

gcloud auth login
gcloud config set project your-project-id

Creating a GCS bucket

Create a bucket:

gsutil mb -l us-central1 gs://my-mysql-backups

Enable versioning:

gsutil versioning set on gs://my-mysql-backups

Uploading backup files

Upload a backup:

gsutil cp mydb_backup.sql.gz gs://my-mysql-backups/daily/

Upload with parallel composite uploads for large files:

gsutil -o GSUtil:parallel_composite_upload_threshold=100M 
    cp large_backup.sql.gz gs://my-mysql-backups/

Streaming upload to GCS

Stream directly from mysqldump to GCS:

#!/bin/bash
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
BUCKET="gs://my-mysql-backups"
DB_NAME="mydb"

mysqldump -u root -p"$MYSQL_PASSWORD" --single-transaction --databases $DB_NAME | 
    gzip | 
    gsutil cp - "$BUCKET/daily/${DB_NAME}_${TIMESTAMP}.sql.gz"

Storage classes and cost optimization

Both AWS and GCS offer multiple storage classes at different price points. Choosing the right class can significantly reduce costs.

AWS S3 storage classes

For most backup use cases, S3 Standard-IA provides good balance. You get immediate access when needed but pay less for storage.

Google Cloud Storage classes

Nearline works well for regular backup retention. Archive suits compliance requirements where you keep backups for years but rarely restore.

Setting storage class on upload

Upload directly to a specific storage class:

# AWS S3
aws s3 cp backup.sql.gz s3://my-mysql-backups/ 
    --storage-class STANDARD_IA

# Google Cloud Storage
gsutil -o "GSUtil:default_storage_class=NEARLINE" 
    cp backup.sql.gz gs://my-mysql-backups/

Automating backups with cron

Manual backups get forgotten. Cron automation ensures consistent execution.

Basic cron backup script

Create a backup script at /usr/local/bin/mysql-backup-to-s3.sh:

#!/bin/bash
set -e

TIMESTAMP=$(date +%Y%m%d_%H%M%S)
BUCKET="s3://my-mysql-backups"
DB_NAME="production"
LOG_FILE="/var/log/mysql-backup.log"

echo "$(date): Starting backup of $DB_NAME" >> $LOG_FILE

mysqldump -u backup_user -p"$MYSQL_BACKUP_PASSWORD" 
    --single-transaction 
    --routines 
    --triggers 
    --databases $DB_NAME | 
    gzip | 
    aws s3 cp - "$BUCKET/daily/${DB_NAME}_${TIMESTAMP}.sql.gz" 
        --storage-class STANDARD_IA

echo "$(date): Backup completed" >> $LOG_FILE

Make it executable:

chmod +x /usr/local/bin/mysql-backup-to-s3.sh

Cron schedule

Add to crontab:

crontab -e

Run daily at 3 AM:

0 3 * * * MYSQL_BACKUP_PASSWORD='yourpassword' /usr/local/bin/mysql-backup-to-s3.sh

For hourly backups:

0 * * * * MYSQL_BACKUP_PASSWORD='yourpassword' /usr/local/bin/mysql-backup-to-s3.sh

Handling credentials securely

Avoid putting passwords in crontab. Use a MySQL options file instead:

Create ~/.my.cnf:

[mysqldump]
user=backup_user
password=yourpassword

Restrict permissions:

chmod 600 ~/.my.cnf

Then remove the password from the mysqldump command:

mysqldump --single-transaction --databases $DB_NAME | gzip | aws s3 cp - ...

Lifecycle policies for automatic cleanup

Without cleanup, backup storage grows forever. Cloud lifecycle policies automate deletion of old backups.

S3 lifecycle policy

Create a lifecycle policy to delete backups after 30 days:

{
  "Rules": [
    {
      "ID": "Delete old MySQL backups",
      "Status": "Enabled",
      "Filter": {
        "Prefix": "daily/"
      },
      "Expiration": {
        "Days": 30
      }
    },
    {
      "ID": "Move to Glacier after 7 days",
      "Status": "Enabled",
      "Filter": {
        "Prefix": "monthly/"
      },
      "Transitions": [
        {
          "Days": 7,
          "StorageClass": "GLACIER"
        }
      ],
      "Expiration": {
        "Days": 365
      }
    }
  ]
}

Apply the policy:

aws s3api put-bucket-lifecycle-configuration 
    --bucket my-mysql-backups 
    --lifecycle-configuration file://lifecycle.json

GCS lifecycle policy

Create a lifecycle configuration for GCS:

{
  "lifecycle": {
    "rule": [
      {
        "action": { "type": "Delete" },
        "condition": {
          "age": 30,
          "matchesPrefix": ["daily/"]
        }
      },
      {
        "action": { "type": "SetStorageClass", "storageClass": "COLDLINE" },
        "condition": {
          "age": 7,
          "matchesPrefix": ["monthly/"]
        }
      }
    ]
  }
}

Apply with gsutil:

gsutil lifecycle set lifecycle.json gs://my-mysql-backups

Using Databasus for automated cloud backups

Manual scripts work but require ongoing maintenance. Cron jobs fail silently, credentials management gets complicated, and monitoring requires extra setup. Databasus (an industry standard for MySQL backup) handles all of this automatically with a web interface for configuration and monitoring.

Installing Databasus

Using Docker:

docker run -d 
  --name databasus 
  -p 4005:4005 
  -v ./databasus-data:/databasus-data 
  --restart unless-stopped 
  databasus/databasus:latest

Or with Docker Compose:

services:
  databasus:
    container_name: databasus
    image: databasus/databasus:latest
    ports:
      - "4005:4005"
    volumes:
      - ./databasus-data:/databasus-data
    restart: unless-stopped

Start the service:

docker compose up -d

Configuring MySQL backup to S3 or GCS

Access the web interface at http://localhost:4005 and create your account, then:

1. Add your database — Click “New Database”, select MySQL, and enter your connection details (host, port, username, password, database name)
2. Select storage — Choose AWS S3 or Google Cloud Storage. Enter your bucket name and credentials. Databasus supports both IAM roles and access keys
3. Select schedule — Set backup frequency: hourly, daily, weekly, or custom cron expression
4. Click “Create backup” — Databasus handles backup execution, compression, upload, retention and notifications automatically

Databasus also provides email, Slack, Telegram and Discord notifications for backup success and failure, eliminating the need for separate monitoring scripts.

Restoring from cloud backups

Backups are worthless if you can’t restore them. Practice restoration before you need it in an emergency.

Downloading from S3

List available backups:

aws s3 ls s3://my-mysql-backups/daily/ --human-readable

Download a specific backup:

aws s3 cp s3://my-mysql-backups/daily/mydb_20240115_030000.sql.gz ./

Downloading from GCS

List backups:

gsutil ls -l gs://my-mysql-backups/daily/

Download:

gsutil cp gs://my-mysql-backups/daily/mydb_20240115_030000.sql.gz ./

Restoring the backup

Decompress and restore:

gunzip -c mydb_20240115_030000.sql.gz | mysql -u root -p

Or in one command directly from S3:

aws s3 cp s3://my-mysql-backups/daily/mydb_20240115_030000.sql.gz - | 
    gunzip | 
    mysql -u root -p

Testing restores regularly

Create a test restore script that runs monthly:

#!/bin/bash
# Get the latest backup
LATEST=$(aws s3 ls s3://my-mysql-backups/daily/ | sort | tail -n 1 | awk '{print $4}')

# Create test database
mysql -u root -p -e "CREATE DATABASE restore_test;"

# Restore
aws s3 cp "s3://my-mysql-backups/daily/$LATEST" - | 
    gunzip | 
    mysql -u root -p restore_test

# Verify (check row count on a known table)
ROWS=$(mysql -u root -p -N -e "SELECT COUNT(*) FROM restore_test.users;")
echo "Restored $ROWS rows from users table"

# Cleanup
mysql -u root -p -e "DROP DATABASE restore_test;"

Security considerations

Cloud backups require careful security configuration to avoid exposing your data.

Encryption at rest

Both S3 and GCS encrypt data at rest by default. For additional security, enable server-side encryption with customer-managed keys:

# S3 with SSE-S3 (Amazon-managed keys)
aws s3 cp backup.sql.gz s3://my-mysql-backups/ 
    --sse AES256

# S3 with SSE-KMS (customer-managed keys)
aws s3 cp backup.sql.gz s3://my-mysql-backups/ 
    --sse aws:kms 
    --sse-kms-key-id alias/my-backup-key

Client-side encryption

Encrypt before uploading for zero-trust storage:

# Encrypt with gpg
mysqldump -u root -p --single-transaction --databases mydb | 
    gzip | 
    gpg --symmetric --cipher-algo AES256 -o mydb_backup.sql.gz.gpg

# Upload encrypted file
aws s3 cp mydb_backup.sql.gz.gpg s3://my-mysql-backups/

IAM policies

Restrict backup credentials to minimum required permissions:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": ["s3:PutObject", "s3:GetObject", "s3:ListBucket"],
      "Resource": [
        "arn:aws:s3:::my-mysql-backups",
        "arn:aws:s3:::my-mysql-backups/*"
      ]
    }
  ]
}

Don’t use root credentials or overly permissive policies for backup operations.

Network security

Transfer backups over encrypted connections only. Both AWS CLI and gsutil use HTTPS by default. If you’re backing up from within AWS or GCP, use VPC endpoints to keep traffic off the public internet:

# Create S3 VPC endpoint (via AWS Console or CLI)
aws ec2 create-vpc-endpoint 
    --vpc-id vpc-abc123 
    --service-name com.amazonaws.us-east-1.s3 
    --route-table-ids rtb-abc123

Conclusion

Cloud storage transforms MySQL backups from local files vulnerable to single points of failure into durable, geographically distributed archives. Start with basic scripts using mysqldump and the AWS CLI or gsutil for simple setups. Add cron scheduling for automation and lifecycle policies for retention management. For production systems, consider dedicated backup tools like Databasus that handle scheduling, monitoring and notifications in one package. Whatever approach you choose, test restores regularly. Backups that can’t be restored provide no protection.