Ramblings of Linux openstack & ceph

Mysql > Compress > Encrypt > Steam > S3

| Comments

Having a point in time backup of your critical Mysql Data can be a life saver when ‘something bad happens’. This post explains how we can use the power of innobackupx, quicklz, xbstream and s3cmd, to take a none locking hot dump of your Mysql Data, Compress it using quicklz, encrypt it using AES256 and stream it into Ecloud Vault with the help of XBSteam and s3cmd.

The below outlines the Software Requirments you will need to walk with this guide.

1
2
3
innobackupx
s3cmd (>1.5 to support streams)
quicklz

You can Find innobackupx and qpress iin the Percona repo here You can find s3cmd in the epel repo here

You will need to ensure you can login to mysql without a password at the command line, this can be done by configuring a ~/.my.cnf file - an example is below

1
2
3
[client]
user=root
pass=myreallysecurepassword

You will them need a copy of the latests backup scrip you can find that on my github here

I place this file under /usr/local/bin and make it executable - the below should do this for you

1
2
wget -O /usr/local/bin/backup.sh https://raw.githubusercontent.com/laggeduout/vault-mysql-backup/master/backup.sh
chmod +x /usr/local/bin/backup.sh

Now you can edit the backup script to modify the below variables at the top of the file:

1
2
3
4
5
6
CRYPT_KEY=/root/.vault.key
S3_BUCKET=
VAULT_ACCESS_KEY_ID=
VAULT_SECRET_ACCESS_KEY=
VAULT_HOST=vault.ecloud.co.uk
S3_EXTRA_ARGS="--limit-rate 50m"

You can find your Vault access details here, You should create a new bucket for each server you backup - You can create a bucket here or using the below s3cmd options

1
s3cmd mb NEWBUCKETNAME

The first time you run the script you will need to setup an encryption key to ensure the data is stored securely. We use AES256 for this as it is currently very difficult for even a state sponsored attack to break - You Must keep a copy of your encryption key otherwise you won’t be able to restore your data later. The below is used to setup the key for the first time.

1
2
3
4
# /usr/local/bin/backup.sh -s
enter aes-256-cbc encryption password:
Verifying - enter aes-256-cbc encryption password:
Your encryption key is 555E81E690B202332983186064FEE1CB. Saving in ~/.vault.key

I can stress enough how important this key is for when you come to restore your data, The password you entered will not be enough to regenerate the key.

Next you can run the backup for the first time using the below:

1
# /usr/local/bin/backup.sh -b

You should watch the output to ensure you see “completed OK!” at the end of the output.

You may then wish to run the backup daily by adding the below to cron.daily

1
echo "/usr/local/bin/backup.sh -b" > /etc/cron.daily/mysqlbackup

If you wish to restore a backup you will need to download the backup file from vault using s3cmd and follow the below, I assume your backup is called today.full.xbstream, that ./restore/ as enough free disk space to extract the backup and your AES-256 encryption key is located in /root/.vault.key - you may need to edit these details for your case.

We need to extract the xbstream file

1
cat today.full.xbstream | xbstream -x -C ./restore/

We then need to remove the AES encryption using xbcrypt

1
2
cd ./restore
for i in `find . -iname "*\.xbcrypt"`; do xbcrypt -d --encrypt-key-file=/root/.vault.key --encrypt-algo=AES256 < $i > $(dirname $i)/$(basename $i .xbcrypt) && rm -f $i; done

We then need to remove the quicklq compression using qpress

1
for i in `find . -iname "*\.qp"`; do qpress -do $i > $(dirname $i)/$(basename $i .qp) && rm -f $i; done

We then need to apply the log file

1
innobackupex --apply-log ./restore

You are then ready to copy the mysql data dir back into your mysql server

1
innobackupex --copy-back ./restore

Comments