We use a third-party script called automysqlbackup to take MySQL backups of GOV.UK infrastructure.
This script takes a nightly
mysqldump and stores it on a dedicated mount point on the MySQL backup machines (
The on-site backup machine (
backup-1.management) pulls the latest backup and stores it on disk. Duplicity runs nightly to send encrypted backups to an Amazon S3 bucket.
To restore from this method:
- Using duplicity, fetch a backup from either the dedicated mount point, the on-site machine, or the S3 bucket. To decrypt this you may need a password kept in encrypted hieradata.
- Unzip the file
- Import into MySQL using
mysql < file- see these MySQL docs on using file imports.
xtrabackup to S3
We are required to have frequent data backups so we created a way to stream MySQL backups to S3.
Each night we take a “base” backup, and then every n time after that (default: 15 minutes) we take an “incremental” backup.
To restore the backup we use a script to retrieve the base backup and then apply any number of incremental backups on top of it.
Under the hood the script completes the following steps:
- Retrieve the latest base backup.
- Fetch the latest incremental backups.
- Copy the consolidated backup (base plus incremental) to the MySQL data directory.
Why this approach
The streaming method was inspired by this blog post from MariaDB. We also drew on the incremental backups concept provided by the toolset.
The streaming method is advantageous because it’s a binary backup and restores are faster than having to import SQL text-based backups.
Note: we use Xtrabackup’s encryption functionality to encrypt the backups by providing an encryption key. The S3 bucket is also encrypted.