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 |
|
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 |
|
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 |
|
Now you can edit the backup script to modify the below variables at the top of the file:
1 2 3 4 5 6 |
|
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
|
|
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 |
|
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
|
|
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
|
|
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
|
|
We then need to remove the AES encryption using xbcrypt
1 2 |
|
We then need to remove the quicklq compression using qpress
1
|
|
We then need to apply the log file
1
|
|
You are then ready to copy the mysql data dir back into your mysql server
1
|
|