1. How To Encrypt MySQL Backups on S3

How To Encrypt MySQL Backups on S3


TwinDB Backup supports encrypted backup copies since version 2.11.0. As usual the tool supports natively backup and restore operations, if backup copies are encrypted the tool takes care of decryption.

Installing TwinDB Packages Repository

I will work with CentOS 7 system to show the example, but there are also packages for Ubuntu trusty and Debian jessie.

We host our packages in PackageCloud which provides a great installation guide if you need to install the repo via puppet, chef etc. The manual way is pretty straightforward as well. A PackageCloud script installs and configures the repository.

Installing Twindb-Backup

Once the repository is ready it’s time to install the tool.

Let’s review what files the tool actually installs.

The RPM installs the files in opt because we use OmniBus to package twindb-backup. We package with the tool itself its own python, dependencies. That way we make sure there are no conflicts, no surprises due to different modules versions etc.

The post installation script also creates a cron config and a sample tool configuration file.

Preparing Encryption Key

We use GPG to encrypt the backups. The tool doesn’t manage the keys so it’s all user responsibility to create and save a backup copy of the key.

Let’s generate the key first.

We don’t use passphrase for the key.

Preparing Twindb-Backup Configuration

We need to change default config. Let’s review the changes.

It’s always nice to save backup copies of /etc. If you don’t want to backup directories, comment out backup_dirs.

We store backups in s3 and we will also keep a local copy (for faster restore time).

We will store backups in S3, so change these options to your key and bucket values.

The tool uses a defaults file to connect to MySQL, so specify it here.

Don’t forget to chmod 600 /etc/twindb/my.cnf.

The config also tells how often to take daily full copies. The hourly copies will be the difference between the last full copy and the current state. It’s so-called differential backups.

To encrypt the backup copies add a [gpg] section

It specifies where GnuPG can find private and public keys of the recipient.

Optionally you may want to change local and remote retention policies, but the defaults should be good enough.

Test Backup Run

Now let’s run the tool manually to see how it works.

The tool should produce no output unless there is an error.

Listing Available Backup Copies

The tool can tell you what backup copies are available now.

The encrypted copies have .gpg suffix. Note the local copies are not encrypted.

Restore MySQL From Backup

Now we have a backup copy s3://twindb-backup-test-0/d312b5e3a877/daily/mysql/mysql-2017-03-28_05_32_30.xbstream.gz.gpg. Let’s restore MySQL database from it.

Now we have a restored database in restored directory that we can copy to /var/lib/mysql

Previous Post Next Post