1. How To Encrypt MySQL Backups on S3

How To Encrypt MySQL Backups on S3

lock

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.

curl -s https://packagecloud.io/install/repositories/twindb/main/script.rpm.sh | sudo bash

Installing Twindb-Backup

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

yum install twindb-backup

Let’s review what files the tool actually installs.

# rpm -ql twindb-backup
/opt
/opt/twindb-backup
...
/opt/twindb-backup/bin
...
/opt/twindb-backup/bin/twindb-backup
...

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.

# cat /etc/cron.d/twindb-backup
@hourly  root twindb-backup backup hourly
@daily   root twindb-backup backup daily
@weekly  root twindb-backup backup weekly
@monthly root twindb-backup backup monthly
@yearly  root twindb-backup backup yearly
# cat /etc/twindb/twindb-backup.cfg
# NOTE: don't quote option values
# What to backup
[source]
backup_dirs=/etc /root /home
backup_mysql=no

# Destination
[destination]
# backup destination can be ssh or s3
backup_destination=ssh
keep_local_path=/var/backup/local


[s3]

# S3 destination settings

AWS_ACCESS_KEY_ID=XXXXX
AWS_SECRET_ACCESS_KEY=YYYYY
AWS_DEFAULT_REGION=us-east-1
BUCKET=twindb-backups

[ssh]

# SSH destination settings

backup_host=127.0.0.1
backup_dir=/tmp/backup
ssh_user=root
ssh_key=/root/.ssh/id_rsa

[mysql]

# MySQL

mysql_defaults_file=/etc/twindb/my.cnf

full_backup=daily

[retention]

# Remote retention policy

hourly_copies=24
daily_copies=7
weekly_copies=4
monthly_copies=12
yearly_copies=3

[retention_local]

# Local retention policy

hourly_copies=1
daily_copies=1
weekly_copies=0
monthly_copies=0
yearly_copies=0

[intervals]

# Run intervals

run_hourly=yes
run_daily=yes
run_weekly=yes
run_monthly=yes
run_yearly=yes

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.

# gpg --gen-key
gpg (GnuPG) 2.0.22; Copyright (C) 2013 Free Software Foundation, Inc.
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.

gpg: directory `/root/.gnupg' created
gpg: new configuration file `/root/.gnupg/gpg.conf' created
gpg: WARNING: options in `/root/.gnupg/gpg.conf' are not yet active during this run
gpg: keyring `/root/.gnupg/secring.gpg' created
gpg: keyring `/root/.gnupg/pubring.gpg' created
Please select what kind of key you want:
(1) RSA and RSA (default)
(2) DSA and Elgamal
(3) DSA (sign only)
(4) RSA (sign only)
Your selection? 1
RSA keys may be between 1024 and 4096 bits long.
What keysize do you want? (2048)
Requested keysize is 2048 bits
Please specify how long the key should be valid.
0 = key does not expire
<n> = key expires in n days
<n>w = key expires in n weeks
<n>m = key expires in n months
<n>y = key expires in n years
Key is valid for? (0) 0
Key does not expire at all
Is this correct? (y/N) y

GnuPG needs to construct a user ID to identify your key.

Real name: Aleksandr Kuzminsky
Email address: backups@twindb.com
Comment: Key for encrypting MySQL backups
You selected this USER-ID:
"Aleksandr Kuzminsky (Key for encrypting MySQL backups) <backups@twindb.com>"

Change (N)ame, (C)omment, (E)mail or (O)kay/(Q)uit? O

You don't want a passphrase - this is probably a *bad* idea!
I will do it anyway. You can change your passphrase at any time,
using this program with the option "--edit-key".

We need to generate a lot of random bytes. It is a good idea to perform
some other action (type on the keyboard, move the mouse, utilize the
disks) during the prime generation; this gives the random number
generator a better chance to gain enough entropy.
We need to generate a lot of random bytes. It is a good idea to perform
some other action (type on the keyboard, move the mouse, utilize the
disks) during the prime generation; this gives the random number
generator a better chance to gain enough entropy.
gpg: /root/.gnupg/trustdb.gpg: trustdb created
gpg: key 8564B88A marked as ultimately trusted
public and secret key created and signed.

gpg: checking the trustdb
gpg: 3 marginal(s) needed, 1 complete(s) needed, PGP trust model
gpg: depth: 0 valid: 1 signed: 0 trust: 0-, 0q, 0n, 0m, 0f, 1u
pub 2048R/8564B88A 2017-03-28
Key fingerprint = 441E 4B7A FD92 C0D5 4C6B 0C89 4AE0 849C 8564 B88A
uid Aleksandr Kuzminsky (Key for encrypting MySQL backups) <backups@twindb.com>
sub 2048R/0CE02576 2017-03-28

We don’t use passphrase for the key.

Preparing Twindb-Backup Configuration

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

[source]
backup_dirs=/etc
backup_mysql=yes

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

# Destination
[destination]
# backup destination can be ssh or s3
backup_destination=s3
keep_local_path=/var/backup/local

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

[s3]

# S3 destination settings

AWS_ACCESS_KEY_ID=XXXXX
AWS_SECRET_ACCESS_KEY=YYYYY
AWS_DEFAULT_REGION=us-east-1
BUCKET=twindb-backups

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

[mysql]

# MySQL
mysql_defaults_file=/etc/twindb/my.cnf
full_backup=daily

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

# cat /etc/twindb/my.cnf
[client]
user=root

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

[gpg]
keyring = /root/.gnupg/pubring.gpg
secret_keyring = /root/.gnupg/secring.gpg
recipient = backups@twindb.com

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.

# twindb-backup backup daily

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.

# twindb-backup ls
2017-03-28 05:32:40,412: INFO: ls.list_available_backups():22: Local copies:
/var/backup/local/d312b5e3a877/status
/var/backup/local/d312b5e3a877/daily/files/_etc-2017-03-28_05_32_26.tar.gz
/var/backup/local/d312b5e3a877/daily/mysql/mysql-2017-03-28_05_32_30.xbstream.gz
2017-03-28 05:32:40,417: INFO: ls.list_available_backups():33: hourly copies:
2017-03-28 05:32:41,087: INFO: ls.list_available_backups():33: daily copies:
s3://twindb-backup-test-0/d312b5e3a877/daily/files/_etc-2017-03-28_05_32_26.tar.gz.gpg
s3://twindb-backup-test-0/d312b5e3a877/daily/mysql/mysql-2017-03-28_05_32_30.xbstream.gz.gpg
2017-03-28 05:32:41,687: INFO: ls.list_available_backups():33: weekly copies:
2017-03-28 05:32:42,269: INFO: ls.list_available_backups():33: monthly copies:
2017-03-28 05:32:42,831: INFO: ls.list_available_backups():33: yearly copies:

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.

# twindb-backup restore mysql s3://twindb-backup-test-0/d312b5e3a877/daily/mysql/mysql-2017-03-28_05_32_30.xbstream.gz.gpg --dst restored
...
170328 05:39:49  innobackupex: completed OK!
2017-03-28 05:39:49,566: INFO: restore.restore_from_mysql():354: Successfully restored s3://twindb-backup-test-0/d312b5e3a877/daily/mysql/mysql-2017-03-28_05_32_30.xbstream.gz.gpg in restored.
2017-03-28 05:39:49,566: INFO: restore.restore_from_mysql():356: Now copy content of restored to MySQL datadir: cp -R restored/* /var/lib/mysql/
2017-03-28 05:39:49,566: INFO: restore.restore_from_mysql():357: Fix permissions: chown -R mysql:mysql /var/lib/mysql/
2017-03-28 05:39:49,566: INFO: restore.restore_from_mysql():359: Make sure innodb_log_file_size and innodb_log_files_in_group in restored/backup-my.cnf and in /etc/my.cnf are same.
2017-03-28 05:39:49,566: INFO: restore.restore_from_mysql():362: Original my.cnf is restored in restored/_config.
2017-03-28 05:39:49,566: INFO: restore.restore_from_mysql():364: Then you can start MySQL normally.

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

# ls -la restored/
total 30756
drwxr-xr-x 6 root root     4096 Mar 28 05:39 .
dr-xr-x--- 5 root root     4096 Mar 28 05:39 ..
drwxr-xr-x 3 root root     4096 Mar 28 05:39 _config
-rw-r----- 1 root root      262 Mar 28 05:39 backup-my.cnf
-rw-r--r-- 1 root root  5242880 Mar 28 05:39 ib_logfile0
-rw-r--r-- 1 root root  5242880 Mar 28 05:39 ib_logfile1
-rw-r----- 1 root root 18874368 Mar 28 05:39 ibdata1
drwx------ 2 root root     4096 Mar 28 05:39 mysql
drwx------ 2 root root     4096 Mar 28 05:39 performance_schema
drwx------ 2 root root     4096 Mar 28 05:39 test
-rw-r----- 1 root root       89 Mar 28 05:39 xtrabackup_checkpoints
-rw-r----- 1 root root      562 Mar 28 05:39 xtrabackup_info
-rw-r----- 1 root root  2097152 Mar 28 05:39 xtrabackup_logfile
Previous Post Next Post