Nightly MySQL backups to S3 with lifecycle tiering

Trellis provisions a lot for you, but nightly database backups aren’t part of it. We wanted dumps shipped off the server every night, kept cheaply, and reproducible from code — so we added a small Ansible role and an S3 bucket.

A tiny role

The whole backup is one streamed pipeline — no temp files, no stored credentials:

wp db export - | gzip | aws s3 cp - 
  s3://…-backups/mysql/rootstest.de/…sql.gz

A cron entry runs it nightly as the web user, and the AWS CLI authenticates using the EC2 instance’s IAM role — so there are no access keys anywhere on the box.

Cheaper over time

The bucket has a lifecycle rule: gzipped dumps live in standard storage for 30 days (instant retrieval if we need last week’s data), then transition to Infrequent Access, and expire after a year. Old backups quietly get cheaper without anyone touching them.

Restoring

Recovery is the pipeline in reverse — stream straight from S3 back into the database:

aws s3 cp s3://…/dump.sql.gz - | gunzip | wp db import -