How to Automatically Back Up a MySQL Database to Cloudflare R2

Most database backup guides stop at the backup. They show you mysqldump, they show you a cron job, and they call it done — and that's exactly how people discover, mid-disaster, that their "backup" was a corrupted file nobody ever tried to restore. A backup you haven't test-restored isn't a backup; it's an assumption. This guide covers both halves: automating the backup to Cloudflare R2, and actually proving it works.

We're using Cloudflare R2 as the destination because it's genuinely the cheapest sane option for this — no egress fees, a 10 GB free tier, and $0.015/GB beyond that, which we broke down in more detail in our cloud cost guide. A typical WordPress or Laravel app's database dump, even backed up daily with 30 days of retention, will often stay inside the free tier entirely.

What you'll need

• A VPS running your MySQL or MariaDB database (if it's the same VPS from our Docker + Caddy multi-app guide, this works the same way — just point at the right container).
• A Cloudflare R2 bucket and an API token, following the same process as our R2 media offload guide — Step 1 and Step 3 there apply here too.
• rclone, an open-source command-line tool for moving files to and from cloud storage.

Step 1: Install rclone and connect it to R2

curl https://rclone.org/install.sh | sudo bash

Then create the config file at ~/.config/rclone/rclone.conf, using the exact format from Cloudflare's own R2 + rclone documentation:

[r2]
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

Confirm it works before writing any backup script:

rclone lsf r2:your-bucket-name

If that lists your bucket's contents (or an empty result with no error) without asking for credentials again, the connection is good.

Step 2: Write the backup script

#!/bin/bash
set -euo pipefail

DB_NAME="your_database"
DB_USER="your_db_user"
DB_PASS="your_db_password"
BUCKET="r2:your-bucket-name/db-backups"
TIMESTAMP=$(date +%Y-%m-%d_%H-%M-%S)
FILENAME="${DB_NAME}_${TIMESTAMP}.sql.gz"
TMP_DIR="/tmp/db-backups"

mkdir -p "$TMP_DIR"

mysqldump --single-transaction --quick --routines --triggers \
  -u "$DB_USER" -p"$DB_PASS" "$DB_NAME" | gzip > "$TMP_DIR/$FILENAME"

rclone copy "$TMP_DIR/$FILENAME" "$BUCKET/" --checksum

rm "$TMP_DIR/$FILENAME"

# Delete remote backups older than 30 days
rclone delete "$BUCKET/" --min-age 30d

A few flags here are doing more than they look like:

• --single-transaction takes a consistent snapshot without locking your tables — critical if the site is live while the backup runs. Without it, you either lock writes during the dump or risk a backup with half-committed data.
• --routines --triggers are easy to forget and don't show up as an error when missing — your restore will just quietly be missing stored procedures and triggers, discovered only when something that depended on them stops working.
• set -euo pipefail makes the script actually fail loudly if mysqldump errors out, instead of silently uploading an empty or partial file — the single most common way "automated backups" turn out to have been failing for weeks unnoticed.

Save this as /usr/local/bin/backup-db.sh and make it executable:

chmod +x /usr/local/bin/backup-db.sh

Step 3: Schedule it

A traditional cron entry works fine:

crontab -e

# Run daily at 2:30 AM
30 2 * * * /usr/local/bin/backup-db.sh >> /var/log/db-backup.log 2>&1

If you'd rather use a systemd timer instead of cron — useful if you want backup runs visible in journalctl alongside your other services — create /etc/systemd/system/db-backup.service:

[Unit]
Description=Database backup to R2

[Service]
Type=oneshot
ExecStart=/usr/local/bin/backup-db.sh

and /etc/systemd/system/db-backup.timer:

[Unit]
Description=Run database backup daily

[Timer]
OnCalendar=*-*-* 02:30:00
Persistent=true

[Install]
WantedBy=timers.target

Then enable it:

sudo systemctl enable --now db-backup.timer

Persistent=true matters here: if your VPS happens to be rebooting at 2:30 AM, the timer runs the backup as soon as the system is back up instead of silently skipping that day entirely — something plain cron won't do for you.

Step 4: Prove you can actually restore it (don't skip this)

This is the step almost every other guide leaves out, and it's the only step that actually matters. A backup file existing in R2 tells you the upload worked — it tells you nothing about whether the dump itself is valid.

Download the most recent backup to a scratch location and restore it into a throwaway database:

rclone copy r2:your-bucket-name/db-backups/latest-backup.sql.gz /tmp/restore-test/
gunzip /tmp/restore-test/latest-backup.sql.gz

mysql -u root -p -e "CREATE DATABASE restore_test;"
mysql -u root -p restore_test < /tmp/restore-test/latest-backup.sql

Then actually look at it — check row counts on a few key tables, confirm a recent record you know should exist is there, and drop the test database once you're satisfied:

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

Do this once when you set the backup up, and again periodically — monthly is reasonable for most small projects. It's the difference between "we have backups" and "we have backups we've verified actually restore," and only one of those sentences means anything during an actual incident.

Where this fits with your other backups

A database dump covers your data, not your application. If you're also running the WordPress media offload setup, your uploaded files are already safe in R2 independently. What this script doesn't cover: your application code (should already be in git) and server configuration (Nginx/Caddy configs, environment files) — worth a second, much smaller backup routine, or at minimum committed somewhere outside the VPS itself.

Commands and configuration in this guide were verified against Cloudflare's official R2 and rclone documentation in August 2026.