Automated MySQL 8.4 backups to S3 with mysqldump, retention and restore tests

Many small SaaS products and business websites run on a single MySQL database. Managed database services include automated snapshots, but plenty of teams run MySQL on a virtual server, or want a second, independent copy outside their main provider. A backup that lives on the same disk, in the same account, protected by the same credentials as the database does not protect you from much.

This tutorial sets up a nightly logical backup of a MySQL 8.4 database to Amazon S3 (or any S3-compatible object store), with a retention policy, credentials kept off the command line, an upload-only IAM policy and, most importantly, a restore test you can run on a schedule. The scripts are plain Bash and use only mysqldump, gzip and the AWS CLI.

It suits databases from a few megabytes to a few tens of gigabytes. Beyond that, read the section on when to switch to physical backups.

Before you start: decide RPO and RTO

Two numbers drive every backup design. The recovery point objective (RPO) is how much data you can afford to lose, measured in time. The recovery time objective (RTO) is how long you can afford to be down while restoring. A nightly dump gives an RPO of up to 24 hours. If that is too much, you need more frequent dumps, binary log archiving or a managed service with point-in-time recovery; this tutorial is still a useful independent safety net alongside those.

Step 1: Create a dedicated backup user

Do not back up with the application's account or with root. The MySQL documentation lists the privileges mysqldump needs: SELECT for tables, SHOW VIEW for views, TRIGGER for triggers (dumped by default) and EVENT when you include scheduled events. LOCK TABLES is only needed when you do not use --single-transaction.

CREATE USER 'backup'@'localhost' IDENTIFIED BY 'use-a-long-random-value';
GRANT SELECT, SHOW VIEW, TRIGGER, EVENT ON appdb.* TO 'backup'@'localhost';

If your server has gtid_mode=ON, the documentation notes that --single-transaction also needs the RELOAD or FLUSH_TABLES privilege in some configurations; add it if the dump fails with a privilege error.

Keep the password out of the command line, where other users can see it in the process list. MySQL's option-file documentation recommends a [client] section in a file that is "accessible only by yourself":

sudo install -d -m 700 /etc/mysql-backup
sudo tee /etc/mysql-backup/client.cnf > /dev/null <<'CNF'
[client]
user=backup
password="use-a-long-random-value"
host=127.0.0.1
CNF
sudo chmod 600 /etc/mysql-backup/client.cnf

Step 2: Prepare the bucket and an upload-only identity

Create a private bucket with versioning enabled and public access blocked. Then give the database server an IAM identity (an instance role on EC2, or an access key stored in ~/.aws/credentials with mode 600 elsewhere) that can only write new objects under the backup prefix:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "UploadBackupsOnly",
      "Effect": "Allow",
      "Action": "s3:PutObject",
      "Resource": "arn:aws:s3:::example-db-backups/mysql/*"
    }
  ]
}

Because the server cannot list, read or delete objects, an attacker who takes over the server cannot quietly wipe your history. The restore test in Step 5 runs elsewhere with a separate read-only identity. For stronger protection, enable S3 Object Lock, which stores objects in a write-once-read-many model. It works only on versioned buckets. In governance mode, users with a special permission can still remove locks; in compliance mode, AWS states that a locked object version "can't be overwritten or deleted by any user, including the root user", so test retention periods in governance mode first.

Step 3: Write the backup script

The script dumps to a local temporary file first, checks it, and only then uploads. Streaming mysqldump straight into aws s3 cp - is tempting, and the AWS CLI does accept - to read from standard input, but if the dump fails halfway the upload still completes and you are left with a truncated backup that looks valid.

#!/usr/bin/env bash
# /usr/local/bin/mysql-backup.sh
set -euo pipefail
DB_NAME="${DB_NAME:?set DB_NAME}"
BUCKET="${BUCKET:?set BUCKET}"
PREFIX="${PREFIX:-mysql/${DB_NAME}}"
CNF="/etc/mysql-backup/client.cnf"
STAMP="$(date -u +%Y-%m-%dT%H%M%SZ)"
WORKDIR="$(mktemp -d)"
trap 'rm -rf "$WORKDIR"' EXIT
FILE="${WORKDIR}/${DB_NAME}-${STAMP}.sql.gz"
mysqldump --defaults-extra-file="$CNF" \
  --single-transaction \
  --routines --events --triggers \
  --no-tablespaces \
  "$DB_NAME" | gzip -6 > "$FILE"
# Integrity checks: valid gzip, and mysqldump reached the end.
gzip -t "$FILE"
if ! gzip -dc "$FILE" | tail -n 1 | grep -q '^-- Dump completed'; then
  echo "Dump did not complete" >&2
  exit 1
fi
aws s3 cp "$FILE" "s3://${BUCKET}/${PREFIX}/$(basename "$FILE")" \
  --sse AES256 --only-show-errors
echo "Uploaded s3://${BUCKET}/${PREFIX}/$(basename "$FILE")"

What the options do

• --defaults-extra-file reads credentials from the protected option file. It must be the first option on the command line.
• --single-transaction starts a transaction so InnoDB tables are dumped from one consistent snapshot without locking writers. It only gives consistency for InnoDB; the MySQL manual warns that no other connection should run ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE or TRUNCATE TABLE during the dump, so schedule it away from deployments that run migrations.
• --routines and --events include stored procedures, functions and scheduled events, which are not dumped by default. --triggers is already on by default and is listed only for clarity.
• --no-tablespaces suppresses CREATE TABLESPACE and CREATE LOGFILE GROUP statements, which a typical application database does not need.
• The -- Dump completed on DATE line is written at the end of a dump when comments are enabled, which is the default. Its presence is a cheap signal that mysqldump finished.
• --sse AES256 requests S3-managed server-side encryption; use aws:kms if you manage keys in AWS KMS.

For other S3-compatible stores such as MinIO, DigitalOcean Spaces or Cloudflare R2, add the AWS CLI's global --endpoint-url option and the credentials that provider issues. Check the provider's documentation for which encryption flags it accepts.

Step 4: Schedule it and set retention

Run the script nightly from cron. Cron normally uses the server's time zone, so convert your quiet hours to that zone. Environment variables can be set at the top of a file in /etc/cron.d:

# /etc/cron.d/mysql-backup
DB_NAME=appdb
BUCKET=example-db-backups
15 2 * * * root /usr/local/bin/mysql-backup.sh >> /var/log/mysql-backup.log 2>&1

Then let S3 handle retention. This lifecycle rule moves backups to S3 Standard-IA after 30 days, deletes them after 120, and cleans up any multipart uploads that never completed:

{
  "Rules": [
    {
      "ID": "mysql-backup-retention",
      "Filter": { "Prefix": "mysql/" },
      "Status": "Enabled",
      "Transitions": [
        { "Days": 30, "StorageClass": "STANDARD_IA" }
      ],
      "Expiration": { "Days": 120 },
      "NoncurrentVersionExpiration": { "NoncurrentDays": 30 },
      "AbortIncompleteMultipartUpload": { "DaysAfterInitiation": 7 }
    }
  ]
}

aws s3api put-bucket-lifecycle-configuration \
  --bucket example-db-backups \
  --lifecycle-configuration file://lifecycle.json

Two details from the S3 documentation are worth knowing. Since September 2024 the default lifecycle behaviour does not transition objects smaller than 128 KB, so a tiny test database will simply stay in Standard. And infrequent-access and archive classes have their own minimum storage duration and retrieval charges, listed on the S3 pricing page; check them before choosing Glacier classes for files you may need to restore quickly.

Step 5: Test the restore automatically

A backup you have never restored is a hope, not a backup. Run a restore test at least weekly on a separate machine or container that has a read-only S3 identity and a throwaway MySQL instance:

#!/usr/bin/env bash
# restore-test.sh: run on a non-production host
set -euo pipefail
BUCKET=example-db-backups
PREFIX=mysql/appdb
CNF=/etc/mysql-restore/client.cnf   # points at a scratch MySQL server
LATEST="$(aws s3 ls "s3://${BUCKET}/${PREFIX}/" | sort | tail -n 1 | awk '{print $4}')"
[ -n "$LATEST" ] || { echo "No backups found" >&2; exit 1; }
aws s3 cp "s3://${BUCKET}/${PREFIX}/${LATEST}" /tmp/restore.sql.gz --only-show-errors
mysql --defaults-extra-file="$CNF" -e "DROP DATABASE IF EXISTS restore_test; CREATE DATABASE restore_test;"
gzip -dc /tmp/restore.sql.gz | mysql --defaults-extra-file="$CNF" restore_test
# Sanity checks: adjust to your schema.
mysql --defaults-extra-file="$CNF" -N restore_test -e "SELECT COUNT(*) FROM users;"
mysql --defaults-extra-file="$CNF" -N restore_test -e "SELECT MAX(created_at) FROM orders;"
rm -f /tmp/restore.sql.gz
echo "Restore test passed for ${LATEST}"

Alert if the script fails, if the newest backup is older than about 26 hours, or if the latest created_at is much older than expected. Record how long the restore took: that number is your real RTO for this method.

When mysqldump is not enough

The MySQL manual is direct about the limits: mysqldump "is not intended as a fast or scalable solution for backing up substantial amounts of data", because restoring means replaying every SQL statement and rebuilding indexes. Signs you have outgrown it:

• The restore test takes longer than your RTO.
• The dump overlaps business hours or slows the server noticeably.
• You need an RPO measured in minutes rather than hours.

The next steps are the MySQL Shell dump utilities, which the manual describes as offering parallel dumping, compression and progress reporting; physical backups that copy InnoDB data files; binary log archiving for point-in-time recovery; or moving to a managed MySQL service with automated backups. Keep the independent nightly copy even then: it protects you from mistakes inside the primary provider account.

Summary

• Use a dedicated MySQL user with only the privileges mysqldump needs, and keep its password in a mode-600 option file.
• Dump with --single-transaction --routines --events, check the file, then upload.
• Give the database server permission to upload only; consider Object Lock for immutability.
• Let an S3 Lifecycle rule handle tiering and deletion.
• Restore automatically, measure how long it takes, and alert on failures and stale backups.