Table of contents

MySQL backups

automysqlbackup

This is how MySQL backups have traditionally been taken on the GOV.UK Infrastructure.

A third-party script called automysqlbackup takes a mysqldump every night and stores them on disk on a dedicated mount point on the MySQL backup machines (mysql-backup-1.backend and whitehall-mysql-backup-1.backend).

The onsite backup machine (backup-1.management) pulls the latest backup and stores it on disk. Duplicity runs each night to send encrypted backups to an Amazon S3 bucket.

To restore from this method:

  • Fetch a backup from either the dedicated mount point, the onsite machine or the S3 bucket using duplicity (to decrypt you may need a password kept in encrypted hieradata).
  • Unzip the file
  • Import into MySQL using mysql < <file>

xtrabackup to S3

There is a requirement to have data backups which are taken more frequently. Streaming MySQL backups to S3 was created to satisfy this requirement.

To take the backup, we use a tool written by Percona called Innobackupex which is a wrapper for Xtrabackup. This takes binary “hot” backups and uses the xbstream tool to stream data to STDOUT. We redirect this output into a file stored in an Amazon S3 bucket using a tool written in Go called gof3r. Xtrabackup has an encryption function that we can use to encrypt the backups by providing an encryption key, and we also ensure we have serverside encryption in the S3 bucket. The way that the backups are piped straight to S3 means that they never touch the disk so we do not have to worry about stuff like disk usage.

This method was inspired by this blog post from MariaDB.

We use the concept of incremental backups which are built in the toolset. 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 would be able to get the “base” backup, and then apply any number of “incremental” backups on top of it.

The drawback of this method is that restores are more complicated.

To make this easier, a script has been written which will automatically get the very latest base backup, prepare it, and then fetch the latest incremental backup, prepare there and then copy them both back to the MySQL data directory.

An advantage of this method is that because it is a binary type of backup, restores are much quicker than having to import SQL text based backups.

Related documentation:

This page is owned by #2ndline and needs to be reviewed