How to Back Up Your MySQL Database to an Amazon S3 Bucket

Category: Backup & Restore • Est. reading time: 2 minutes

Keeping a backup somewhere other than your own server is one of the smartest things you can do. If the server fails, an off-site copy is what saves you. This guide sets up an automated job that dumps your MySQL databases and uploads them to an Amazon S3 bucket. It is aimed at people running their own dedicated or self-managed server with root access.

Before You Start

You need an AWS account with a private S3 bucket (never make a database bucket public). Install and configure the AWS CLI on your server with an IAM user that can write only to that one bucket, nothing more. You also need MySQL running locally with a user that can read the databases you want to back up.

Step 1: Store Your Database Login Safely

Rather than putting your password in the script, where it can show up in the server’s process list, save it in a protected file. Create a file at /root/.my.cnf with this inside:

[mysqldump]
user=your_db_user
password=your_db_password
host=localhost

Then lock it down so only root can read it: chmod 600 /root/.my.cnf. That permission is what actually protects the password, so it matters more than where you store it. mysqldump reads these credentials automatically, so the script never has to mention them.

Step 2: The Backup Script

Save this as /root/db-backup.sh. It stops if the dump fails, so a broken backup never gets uploaded.

#!/bin/bash
set -euo pipefail

BUCKET="your-bucket-name"
BACKUP_DIR="/root/db-backups"
FILE="$BACKUP_DIR/$(date +%Y-%m-%d)-all-databases.sql.gz"

mkdir -p "$BACKUP_DIR"
echo "Started $(date)"

mysqldump --single-transaction --routines --triggers --all-databases | gzip > "$FILE"

aws s3 cp "$FILE" "s3://$BUCKET/database/$(basename "$FILE")"

rm -f "$FILE"
echo "Finished $(date)"

Make it runnable with chmod +x /root/db-backup.sh, then test it once by running it by hand before you rely on it.

Step 3: Run It Automatically

A backup only helps if it happens on its own. Add it to root’s crontab to run nightly. This example runs it every day at 2 a.m.:

0 2 * * * /root/db-backup.sh

See our guide on creating cron jobs if you want a walk-through of that screen.

A Few Security Notes

Keep the bucket private and turn on default encryption for it. Set a lifecycle rule so old backups expire on their own, which keeps your storage bill down and your history tidy. And keep both the script and the .my.cnf file out of any web-accessible folder.