Subscribe
AWS

Nightly MySQL and content backups to S3 with a systemd timer

My old Ghost server ran for years with no backups at all. On the new one, a small bash script dumps MySQL, tars the content folder and uploads both to S3 every night, driven by a systemd timer. Here is the script, the units, the bucket and the gotchas I hit.

Nightly MySQL and content backups to S3 with a systemd timer

My blog ran on an EC2 instance I built by hand in 2020. Ghost, MySQL 5.7 and Nginx, all on one box. When I finally sat down to move it to Docker Compose this month, I checked what backups it had.

None. Not a snapshot, not a dump, nothing.

To make it worse, Node had been uninstalled from that server at some point, so the running Ghost process was the only copy that worked. One reboot and the blog was gone until I rebuilt it. First thing I did was take a manual dump and a snapshot. The second was making sure the new server never ends up like that. In this blog we will look at the nightly backup I set up: a bash script, a systemd timer, and an S3 bucket in Terraform.

The bucket

Let us start with where the backups go. One private S3 bucket, versioned and encrypted, with public access blocked. The account ID goes into the name so it's unique.

data "aws_caller_identity" "current" {}

resource "aws_s3_bucket" "ghost_backups" {
  bucket = "my-ghost-backups-${data.aws_caller_identity.current.account_id}"
}

resource "aws_s3_bucket_versioning" "ghost_backups" {
  bucket = aws_s3_bucket.ghost_backups.id
  versioning_configuration {
    status = "Enabled"
  }
}

resource "aws_s3_bucket_server_side_encryption_configuration" "ghost_backups" {
  bucket = aws_s3_bucket.ghost_backups.id
  rule {
    apply_server_side_encryption_by_default {
      sse_algorithm = "AES256"
    }
  }
}

resource "aws_s3_bucket_public_access_block" "ghost_backups" {
  bucket                  = aws_s3_bucket.ghost_backups.id
  block_public_acls       = true
  block_public_policy     = true
  ignore_public_acls      = true
  restrict_public_buckets = true
}

Then a lifecycle so it doesn't grow forever. Nightly backups live under nightly/ and expire after 30 days. The one-off dumps I took during the migration live under migration/ and stay for 180 days, in case I find something broken months later.

resource "aws_s3_bucket_lifecycle_configuration" "ghost_backups" {
  bucket = aws_s3_bucket.ghost_backups.id

  rule {
    id     = "expire-nightly"
    status = "Enabled"
    filter {
      prefix = "nightly/"
    }
    expiration {
      days = 30
    }
    noncurrent_version_expiration {
      noncurrent_days = 7
    }
  }

  rule {
    id     = "expire-migration"
    status = "Enabled"
    filter {
      prefix = "migration/"
    }
    expiration {
      days = 180
    }
  }
}

The noncurrent_version_expiration is there because of versioning. When a current object expires, S3 only adds a delete marker and the old version stays around. Without that rule you keep paying for every backup ever taken, that adds up slowly.

What the server is allowed to do

The instance has a role, and that role can put and get objects in this one bucket and list it. Nothing else in S3. No delete.

resource "aws_iam_role_policy" "ghost_backups" {
  name = "ghost-backups"
  role = aws_iam_role.ghost.id
  policy = jsonencode({
    Version = "2012-10-17"
    Statement = [
      {
        Effect   = "Allow"
        Action   = ["s3:PutObject", "s3:GetObject"]
        Resource = "${aws_s3_bucket.ghost_backups.arn}/*"
      },
      {
        Effect   = "Allow"
        Action   = ["s3:ListBucket"]
        Resource = aws_s3_bucket.ghost_backups.arn
      }
    ]
  })
}

With versioning on, even an overwrite doesn't lose anything, the old version is still there. So if the box gets compromised, someone can add junk to the bucket but they can't wipe the history. That's the main reason I bothered with versioning on a bucket of dated folders.

The backup script

The stack lives in /opt/ghost, with a compose.yml, a .env and a data/ folder holding data/ghost (Ghost content) and data/mysql. The script sits next to them.

#!/bin/bash
# Nightly backup: MySQL dump + Ghost content -> S3. Run from /opt/ghost.
set -euo pipefail
cd "$(dirname "$0")"
set -a; source .env; set +a
TS=$(date -u +%Y-%m-%dT%H%M%SZ)
TMP=$(mktemp -d); trap 'rm -rf "$TMP"' EXIT

docker compose exec -T db sh -c 'exec mysqldump -uroot -p"$MYSQL_ROOT_PASSWORD" --single-transaction --routines --triggers --hex-blob --no-tablespaces "$MYSQL_DATABASE" 2>/dev/null' | gzip > "$TMP/db.sql.gz"
gunzip -c "$TMP/db.sql.gz" | tail -1 | grep -q "Dump completed" || { echo "dump incomplete" >&2; exit 1; }
tar czf "$TMP/content.tar.gz" --exclude=ghost/logs -C data ghost

aws s3 cp --only-show-errors "$TMP/db.sql.gz"      "s3://$BACKUP_BUCKET/nightly/$TS/db.sql.gz"
aws s3 cp --only-show-errors "$TMP/content.tar.gz" "s3://$BACKUP_BUCKET/nightly/$TS/content.tar.gz"
echo "backup ok: s3://$BACKUP_BUCKET/nightly/$TS/"

A few notes on it.

--single-transaction gives a consistent dump of InnoDB tables without locking them, so the blog stays up while it runs. --hex-blob keeps binary columns safe in the text dump. --no-tablespaces avoids needing the PROCESS privilege on MySQL 8.

The single quotes around the sh -c command matter. $MYSQL_ROOT_PASSWORD gets expanded inside the db container, where compose already set it for the MySQL image, and not by my shell. So the script never has to pass the root password around itself.

Now the check. I send mysqldump's stderr to /dev/null to hide the "using a password on the command line" warning, but that also hides real errors. And with a pipe into gzip, a dump that dies halfway still leaves you a perfectly valid gzip file. So the script reads the last line of the dump. mysqldump writes -- Dump completed on ... only when it finishes, so if that line is missing, the backup fails loudly instead of uploading half a database.

The content tarball skips ghost/logs. On the old server logs were 2.5 GB, against about 45 MB of real content. On the new stack Ghost logs to stdout and Docker caps the log files, so the folder should stay small anyway. I still exclude it.

The tar bug

My first version of that tar line had the options in the wrong order, and it took me a minute to see why the result wasn't what I expected. GNU tar processes its arguments in order: -C changes directory for the paths that come after it, and --exclude should come before the paths it applies to. The rule I follow now is simple. Archive name, then excludes, then -C, then the paths. Always list the tarball after a change like that.

tar tzf content.tar.gz | head
tar tzf content.tar.gz | grep -c '^ghost/logs/' || true

The second command should print 0.

Why a systemd timer and not cron

I could have used cron. I picked a timer because it logs to the journal, Persistent=true catches up a run missed while the machine was off, and I can start the backup by hand in exactly the same way the timer does.

The service is a oneshot.

[Unit]
Description=Ghost backup to S3
After=docker.service

[Service]
Type=oneshot
User=ubuntu
Environment=PATH=/snap/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin
ExecStart=/opt/ghost/backup.sh

That PATH line is the gotcha I hit. On Ubuntu 24.04 I installed the AWS CLI with snap install aws-cli --classic, so aws lives in /snap/bin. My login shell has that in its PATH. systemd services don't. The script worked fine when I ran it by hand and failed under systemd with aws: command not found. Took me a bit to see why. Adding the PATH fixed it.

The timer runs it at 20:00 UTC, which is 01:30 in India, while I'm asleep.

[Unit]
Description=Nightly Ghost backup

[Timer]
OnCalendar=*-*-* 20:00:00 UTC
Persistent=true
RandomizedDelaySec=10m

[Install]
WantedBy=timers.target

Install and turn it on.

sudo cp ghost-backup.service ghost-backup.timer /etc/systemd/system/
sudo systemctl daemon-reload
sudo systemctl enable --now ghost-backup.timer
systemctl list-timers ghost-backup.timer

Then run it once by hand through systemd, not by calling the script, so you test the same environment the timer uses.

sudo systemctl start ghost-backup.service
journalctl -u ghost-backup.service -n 20 --no-pager

You want to see backup ok: and the S3 path.

Stdin and docker compose exec

One more thing that wasted my time. While setting things up I ran commands over SSH with a heredoc, something like ssh host bash <<'EOF' ... EOF. Any docker compose exec inside it read from stdin and stdin was the rest of my heredoc. It ate the remaining commands and they never ran. No error, they just silently didn't happen.

The -T flag stops it allocating a TTY but doesn't stop it reading stdin. Where I don't pipe anything in, I add </dev/null.

docker compose exec -T db mysql -uroot -p"$MYSQL_ROOT_PASSWORD" -e 'SELECT 1' </dev/null

Testing a restore

A backup you never restored is a hope. I restored the latest nightly onto a scratch stack, with its own compose project name and empty data folders, so it couldn't touch the real one. Roughly like this.

TS=$(aws s3 ls "s3://$BACKUP_BUCKET/nightly/" | awk '{print $2}' | sort | tail -1)
mkdir -p /opt/ghost-restore/data && cd /opt/ghost-restore
aws s3 cp "s3://$BACKUP_BUCKET/nightly/${TS}db.sql.gz" .
aws s3 cp "s3://$BACKUP_BUCKET/nightly/${TS}content.tar.gz" .
tar xzf content.tar.gz -C data
docker compose -p ghost-restore up -d db
gunzip -c db.sql.gz | docker compose -p ghost-restore exec -T db \
  sh -c 'exec mysql -uroot -p"$MYSQL_ROOT_PASSWORD" "$MYSQL_DATABASE"'

This need a copy of compose.yml and .env in that folder, and the scratch Ghost shouldn't publish ports 80 and 443 while the real Caddy holds them. Once it's up, compare a few row counts (posts, tags, users) with the live database and open a couple of images from the restored content folder.

When the counts matched, I threw the scratch stack away.

A quick look at journalctl -u ghost-backup.service --since yesterday and the newest folder in the bucket every few days is enough. Takes ten seconds.