Backing up MySQL and PostgreSQL to an S3-compatible bucket

To back up MySQL to S3 from your own server you need three pieces: a consistent dump, an upload to a bucket on another provider, and a restore you have actually run. This post builds all three for MySQL and PostgreSQL on Ubuntu 24.04 and 26.04, as one script run from cron, uploading to AWS S3, Cloudflare R2 or Hetzner Object Storage. The script, the restores and the failure cases below were run on both releases against a local S3-compatible server.

mysqldump --single-transaction

sudo mysqldump --single-transaction --routines --triggers --events shop | gzip > shop.sql.gz

--single-transaction dumps every InnoDB table from one consistent snapshot without locking them, so the site keeps taking writes during the backup. It does nothing for MyISAM tables. Check whether you have any with SELECT table_name FROM information_schema.tables WHERE engine = 'MyISAM' AND table_schema = 'shop'.

Stored procedures and events are left out unless you ask for them, which is what --routines and --events do; triggers are included by default and --triggers only says so. The dump names no database, so it can be restored into any database name, which is what makes a test restore next to the live one possible. Users and grants are not in it at all: they live in the mysql system schema.

pg_dump -Fc for PostgreSQL

sudo -u postgres pg_dump -Fc blog > blog.dump

-Fc is PostgreSQL’s custom format: compressed already, and read back with pg_restore, which can restore a single table, list the contents with pg_restore -l, or restore in parallel with -j. A plain SQL dump can do none of those. The dump carries each object’s owner and grants by role name, but not the roles themselves, which belong to the whole server. That comes back to bite in the restore section.

Credentials without a password on the command line

mysqldump -u root -pSecret puts the password in ps output for every user on the server, and in your shell history. On a database on the same server, there is no password to pass. Ubuntu’s MySQL lets the system root user in as MySQL’s root through the Unix socket, and PostgreSQL lets the postgres system user in as the postgres role the same way. A backup job running as root uses both, and the script below has no database password in it.

For a database on another host, or a dedicated backup user, put the credentials in a file only root can read. For MySQL:

[client]
user = backup
password = the-backup-password

sudo chmod 600 /etc/db-backup/my.cnf
sudo mysqldump --defaults-extra-file=/etc/db-backup/my.cnf --single-transaction --no-tablespaces shop

--defaults-extra-file must be the first option, or mysqldump stops with unknown variable 'defaults-extra-file=...'. A backup user needs SELECT, SHOW VIEW, TRIGGER, LOCK TABLES, EVENT. Without the PROCESS privilege as well, mysqldump prints Access denied; you need (at least one of) the PROCESS privilege(s) ... when trying to dump tablespaces; the dump is still complete, and --no-tablespaces silences it. For PostgreSQL the equivalent is a ~/.pgpass line of host:port:database:user:password, mode 600, which pg_dump -h 127.0.0.1 -U blog blog then reads without asking.

Uploading with rclone or the AWS CLI

rclone is in Ubuntu’s archive on both releases, speaks to every S3-compatible service, and can delete by age, which the AWS CLI cannot. Install it and give root a remote named backup:

sudo apt install rclone
sudo install -d -m 700 /root/.config/rclone
sudo nano /root/.config/rclone/rclone.conf
sudo chmod 600 /root/.config/rclone/rclone.conf
sudo rclone lsd backup:

The AWS CLI works too. 26.04 has version 2 in its archive as awscli; 24.04 has no package, so install it with sudo snap install aws-cli --classic or AWS’s own installer. Point it at a non-AWS service with endpoint_url in ~/.aws/config, and upload with aws s3 cp file s3://my-backups/.

Endpoints for S3, Cloudflare R2 and Hetzner Object Storage

Each provider gives you an access key pair scoped to the bucket. Create the key for this one bucket, never an account-wide one: the server holding it can read every backup.

[backup]
type = s3
provider = Cloudflare
access_key_id = your-access-key-id
secret_access_key = your-secret-access-key
endpoint = https://your-account-id.r2.cloudflarestorage.com
acl = private
no_check_bucket = true

The account ID is on the R2 overview page. no_check_bucket is needed for a token with object-level permissions only, which cannot see whether the bucket exists.

[backup]
type = s3
provider = Other
access_key_id = your-access-key-id
secret_access_key = your-secret-access-key
endpoint = fsn1.your-objectstorage.com
region = fsn1
acl = private

The location is part of the hostname and is also the region, such as fsn1 or nbg1, matching where the bucket was created. rclone’s Other provider is what Hetzner’s own documentation uses.

[backup]
type = s3
provider = AWS
access_key_id = your-access-key-id
secret_access_key = your-secret-access-key
region = eu-central-1
acl = private

Put the bucket with a different company from the server, or at least in a different region. A backup next to the database it protects disappears with it.

The backup script

#!/usr/bin/env bash
# Dumps each database, archives uploaded files, and copies both to the bucket.
set -euo pipefail
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin

REMOTE="backup:my-backups/$(hostname)"
KEEP_DAYS=14
MYSQL_DATABASES="shop"
POSTGRES_DATABASES="blog"
FILES_DIR="/var/www/example.com/shared/storage/app"

umask 077
stamp="$(date -u +%Y%m%dT%H%M%SZ)"
work="$(mktemp -d)"
trap 'rm -rf "$work"' EXIT

for db in $MYSQL_DATABASES; do
  mysqldump --single-transaction --routines --triggers --events "$db" | gzip > "$work/$db-$stamp.sql.gz"
done

for db in $POSTGRES_DATABASES; do
  runuser -u postgres -- pg_dump -Fc "$db" > "$work/$db-$stamp.dump"
done

if [ -d "$FILES_DIR" ]; then
  tar -czf "$work/files-$stamp.tar.gz" -C "$FILES_DIR" .
fi

rclone copy "$work" "$REMOTE/"
rclone delete --min-age "${KEEP_DAYS}d" "$REMOTE/"
echo "backup $stamp uploaded: $(ls "$work" | tr '\n' ' ')"

sudo install -m 755 db-backup /usr/local/bin/db-backup
sudo /usr/local/bin/db-backup

15 3 * * * root /usr/local/bin/db-backup >> /var/log/db-backup.log 2>&1

Two lines in it exist because of failures that are easy to miss:

• pipefail. Without it, a pipeline’s status is gzip’s. A mysqldump that fails, here for a misspelt database name, still leaves a valid gzip file of a few hundred bytes, which the script uploads, reports as a success and exits 0 for. With it the script stops before uploading anything and cron’s log shows the error.
• PATH. Cron runs jobs with PATH=/usr/bin:/bin. runuser lives in /usr/sbin, so without the line the script works when you run it by hand and fails nightly with runuser: command not found.

umask 077 keeps the dumps unreadable to other users while they sit in the temporary directory, and the trap removes them however the script exits. Each file is named after the database and the UTC time, so a listing sorts by age.

Retention: the script or a lifecycle rule

rclone delete --min-age 14d removes objects older than fourteen days from the server’s prefix, judged by the modification time rclone stores with each upload. It is portable and you can see it working. The alternative is a lifecycle rule on the bucket, which AWS S3 and Cloudflare R2 both offer. On AWS it has one advantage: with the bucket deleting old objects, the server’s IAM policy can allow uploading and listing but not DeleteObject, and a compromised server can add backups but not erase them. Drop the rclone delete line if you go that way.

Fourteen daily copies is a starting point. If a bad migration or slow data corruption could go unnoticed for longer than two weeks, keep more.

Site files too

The database is half of a Laravel application’s state. Uploaded files are the other half, under storage/app, and the script archives them as files-*.tar.gz. The path above is the shared storage directory of a release-based layout; point it at your own. The code itself does not need backing up when it is in Git. The .env file does, but not in this bucket: objects there are stored as you upload them, and the file holds your database password and APP_KEY. Keep it in a password manager.

Restoring files is one command, run as root so the owners in the archive are kept:

sudo tar -xzf files-20261021T031500Z.tar.gz -C /var/www/example.com/shared/storage/app

Restoring, and testing the restore

Restore into a scratch database next to the live one and look at the data. For MySQL:

sudo rclone copy backup:my-backups/web1/shop-20261021T031500Z.sql.gz .
sudo mysql -e 'CREATE DATABASE shop_restore_test'
gunzip < shop-20261021T031500Z.sql.gz | sudo mysql shop_restore_test
sudo mysql -e 'SELECT COUNT(*) FROM shop_restore_test.orders'
sudo mysql -e 'DROP DATABASE shop_restore_test'

For PostgreSQL, with the scratch database owned by the application’s role:

sudo rclone copy backup:my-backups/web1/blog-20261021T031500Z.dump /tmp
sudo -u postgres createdb -O blog blog_restore_test
sudo -u postgres pg_restore -d blog_restore_test /tmp/blog-20261021T031500Z.dump
psql -h 127.0.0.1 -U blog -d blog_restore_test -c 'SELECT count(*) FROM posts'
sudo -u postgres dropdb blog_restore_test

Connect as the application’s own user for that last check, not as root or postgres. A restore that superusers can read and your application cannot is the common failure, covered next. Put a reminder in your calendar to repeat this every month, or script it the same way as the backup.

To restore over the live MySQL database, drop and recreate it first. The dump replaces the tables it contains, but tables created after the backup was taken would otherwise survive the restore:

sudo mysql -e 'DROP DATABASE shop; CREATE DATABASE shop CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci'
gunzip < shop-20261021T031500Z.sql.gz | sudo mysql shop

Grants after a restore

On MySQL, a user’s grant on shop.* is kept in the mysql schema, not in the database, so it survives the drop and the application logs in afterwards as before. On a new server, recreate the user and its grant before pointing the application at the restored data:

sudo mysql -e "CREATE USER 'shop'@'localhost' IDENTIFIED BY 'the-app-password'; GRANT ALL PRIVILEGES ON shop.* TO 'shop'@'localhost'"

PostgreSQL has three ways to hand back a database the application cannot use:

• The role does not exist yet. On a new server, pg_restore reports role "blog" does not exist for each ALTER ... OWNER, carries on, exits 1 at the end, and leaves every table owned by postgres. Create the role first with CREATE ROLE blog LOGIN PASSWORD '...'.
• --no-owner. Restoring with it, as many guides suggest, gives the tables to postgres on purpose. The application then gets permission denied for table posts.
• The schema was recreated. Emptying a database with DROP SCHEMA public CASCADE; CREATE SCHEMA public; before a restore makes a new schema owned by postgres with no grants. The application cannot even see it, and the error is relation "posts" does not exist, which reads like an empty dump rather than a permission problem.

The cure for the last two is the same set of grants, run as postgres in the restored database, with your role’s name in place of blog:

GRANT ALL ON SCHEMA public TO blog;
GRANT ALL ON ALL TABLES IN SCHEMA public TO blog;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO blog;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO blog;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO blog;

The two ALTER DEFAULT PRIVILEGES lines cover tables that postgres creates later. If restoring into a database owned by the role, as in the test restore above, works for you, that is simpler still: the dump’s own ALTER ... OWNER statements then give everything back to it.

How Provisore handles backups

On the Pro plan and above, Provisore dumps a server’s managed databases and archives its sites’ shared files on a schedule you set, uploads them to a bucket you own on any S3-compatible service, and keeps the last 1 to 30 runs. Restoring a run is a button that asks you to type the database name or domain first, and a PostgreSQL restore puts the application’s grants back as part of the job. A failed backup notifies your channels. See backups and restore and databases.