[ Back to log ]

Automated MySQL/MariaDB backups to Cloudflare R2

Database

You need database backups, and keeping them on the same server as the database is useless if that server dies, you can use Cloudflare R2 for this. This guide dumps your databases, zips them up, ships them to R2, and cleans out the old ones.

Step 0: Create a dedicated backup user

A backup job only needs to read data, so don’t run it as root. An account that can also drop tables and rewrite grants is a needless risk, and if it leaks the blast radius is your whole server. Create a user that can do one thing: read enough to take a consistent dump. Log in as the system root:

sudo mysql

Then create the backup account with the narrow set of privileges mysqldump actually needs:

CREATE USER 'backup'@'localhost' IDENTIFIED BY 'a-long-random-password';
GRANT SELECT, SHOW VIEW, EVENT, TRIGGER, LOCK TABLES, RELOAD, PROCESS, REPLICATION CLIENT ON *.* TO 'backup'@'localhost';
FLUSH PRIVILEGES;
EXIT;

That covers --single-transaction, routines, triggers, and events without granting any write access at all. Generate the password with something like openssl rand -base64 24 and avoid quotes in it so it doesn’t break the SQL string.

Store the password in an option file, not the script

Put the password on the command line (mysqldump -ubackup -p"...") and it’s visible in ps aux to every other user on the box for as long as the dump runs, plus it lands in your shell history. Use a MySQL option file that the client reads automatically instead.

Create it, then lock it down so only root can read it:

sudo nano /etc/mysql/backup.cnf
[client]
user = backup
password = a-long-random-password
host = localhost
sudo chmod 600 /etc/mysql/backup.cnf
sudo chown root:root /etc/mysql/backup.cnf

Now the script can point mysql and mysqldump at this file with --defaults-extra-file and the password never touches the command line.

Step 1: Install the AWS CLI

Since R2 uses the S3 API, we can just use the standard AWS CLI to upload files.

Install the basics:

Ubuntu/Debian:

sudo apt update
sudo apt install curl unzip default-mysql-client -y

CentOS/RHEL:

sudo yum install curl unzip mysql -y

Then download and install the AWS CLI:

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

Step 2: Configure credentials

Our backup credentials file is readable only by root, so the script has to run as root, which means cron runs as root too. Configure the AWS CLI for root so it can find the keys at backup time:

sudo aws configure

It will ask for four things:

  • AWS Access Key ID: your R2 Access Key ID
  • AWS Secret Access Key: your R2 Secret Access Key
  • Default region name: auto
  • Default output format: json

This writes the keys to /root/.aws/credentials, which the CLI already locks to 600. Scope the R2 token to the one bucket you’re backing up to, with object read and write only. There’s no reason a backup key should be able to touch the rest of your account.

Step 3: Create the backup script

Create the bash script (this opens the nano editor):

sudo nano /usr/local/bin/db-backup.sh

Paste this code. Swap out the variables in the Configuration section with your actual details.

#!/bin/bash
set -euo pipefail
PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin

# ==========================================
# Configuration
# ==========================================

DB_CONF="/etc/mysql/backup.cnf"
BACKUP_DIR="/var/backups/mysql"

R2_ACCOUNT_ID="your_cloudflare_account_id"
R2_BUCKET="your-bucket-name"
R2_ENDPOINT="https://${R2_ACCOUNT_ID}.r2.cloudflarestorage.com"
MAX_BACKUPS=5

DATE=$(date +"%Y-%m-%d_%H-%M-%S")
PREFIX="alldbs_archive_"
FILE_NAME="${PREFIX}${DATE}.tar.gz"
FILE_PATH="${BACKUP_DIR}/${FILE_NAME}"
STAGE_DIR="${BACKUP_DIR}/staging_${DATE}"

# Always wipe the unencrypted staging dumps, even if the script dies early.
trap 'rm -rf "$STAGE_DIR"' EXIT

# ==========================================
# Execution
# ==========================================

# Keep dumps in a root-only directory, never world-readable /tmp.
mkdir -p "$BACKUP_DIR"
chmod 700 "$BACKUP_DIR"
mkdir -p "$STAGE_DIR"
echo "Fetching databases..."

DATABASES=$(mysql --defaults-extra-file="$DB_CONF" -N -e "SHOW DATABASES;" | grep -Ev "^(information_schema|performance_schema|sys)$")

for DB in $DATABASES; do
    echo " -> Dumping $DB..."
    if ! mysqldump --defaults-extra-file="$DB_CONF" --single-transaction --quick --routines --triggers --events "$DB" > "${STAGE_DIR}/${DB}.sql"; then
        echo "Error: Failed to dump $DB." >&2
        exit 1
    fi
done

echo "Archiving..."
if ! tar -czf "$FILE_PATH" -C "$STAGE_DIR" .; then
    echo "Error: Archiving failed." >&2
    exit 1
fi
echo "Archive created."

echo "Uploading to R2..."
if aws s3 cp "$FILE_PATH" "s3://${R2_BUCKET}/" --endpoint-url "$R2_ENDPOINT"; then
    echo "Upload successful."
    rm -f "$FILE_PATH"
else
    echo "Error: Upload failed." >&2
    rm -f "$FILE_PATH"
    exit 2
fi

# ==========================================
# Retention Policy
# ==========================================

BACKUP_LIST=$(aws s3 ls "s3://${R2_BUCKET}/" --endpoint-url "$R2_ENDPOINT" | awk '{print $4}' | grep "^${PREFIX}" | sort || true)

if [ -z "$BACKUP_LIST" ]; then
    BACKUP_COUNT=0
else
    BACKUP_COUNT=$(echo "$BACKUP_LIST" | wc -l)
fi

if [ "$BACKUP_COUNT" -gt "$MAX_BACKUPS" ]; then
    DELETE_COUNT=$((BACKUP_COUNT - MAX_BACKUPS))
    FILES_TO_DELETE=$(echo "$BACKUP_LIST" | head -n "$DELETE_COUNT")

    for FILE in $FILES_TO_DELETE; do
        aws s3 rm "s3://${R2_BUCKET}/${FILE}" --endpoint-url "$R2_ENDPOINT"
    done
fi

Save and exit (Ctrl+O, Enter, Ctrl+X).

Step 4: Make it executable

The script reads root-only files, so keep it owned by root and make it executable:

sudo chown root:root /usr/local/bin/db-backup.sh
sudo chmod 755 /usr/local/bin/db-backup.sh

Run it once by hand before you trust it to a schedule:

sudo /usr/local/bin/db-backup.sh

If it dumps, archives, uploads, and the file shows up in your R2 bucket, you’re done. If it fails, the error output tells you which step broke.

Step 5: Schedule with cron

Use root’s crontab, since that’s the user with access to the credentials file and the R2 keys:

sudo crontab -e

Add this to the bottom to run hourly:

0 * * * * /usr/local/bin/db-backup.sh >> /var/log/db-backup.log 2>&1

Save and exit. Backups now run on their own.

One more thing: R2 encrypts objects at rest, but the dump travels there as a plain .tar.gz. If the data is sensitive enough that you don’t want Cloudflare able to read it, pipe the archive through gpg before uploading and keep the key off the server. For most small projects the server-side encryption is enough.