Encrypted and Incremental MySQL Backups with Percona XtraBac_MySQL

Related MicroZone Resources

Proven in Production: Clustrix Case Studies

Clustrix Whitepapers

What We Offer: Clustrix Features

INFOGRAPHIC: The Future of the Database

Like this piece? Share it with your friends:

This post was originally written byJervin Real

We’ve recently received a number of questions on how to implement incremental MySQL backups alongside encryption withPercona XtraBackup. Some users thought it was not initially possible because with the default --encrypt options with XtraBackup, all files will be encrypted, but alas, that is not the case. This is where the option --extra-lsn-dir becomes useful, because it allows you to save LSN (Log Sequence Number) information to another directory and exclude it from encryption, allowing you to use the same information needed by incremental backups. Enough talk, let me show you.

Because you would want to usually script your backup and restore procedure, I’d use variables here as well to make you more familiar. First, I’d create 3 folders, where my backups will be stored, ‘full’ for full backups, ‘incr’ for incremental backups, and ‘lsns’ to store an extra copy of my xtrabackup_checkpoints file with --extra-lsn-dir .

mkdir -p /ssd/msb/msb_5_5_360/bkp/fullmkdir -p /ssd/msb/msb_5_5_360/bkp/incrmkdir -p /ssd/msb/msb_5_5_360/bkp/lsns

Second, to have better control of where my backups would go, I prefer assigning timestamped folders instead and use the –no-timestamp option to innobackupex.

CURDATE=$(date +%Y-%m-%d_%H_%M_%S)

Then manually create the specific directory where the backup’s xtrabackup_checkpoints file would be saved:

mkdir -p /ssd/msb/msb_5_5_360/bkp/lsns/$CURDATE

Of course, I need an encryption key for my encrypted backups, in this case, taking the example from themanual, I used openssl to generate a random key. You can use your own key string as long as its size conforms to the size required by the --encrypt algorithm you chose.

echo -n $( openssl enc -aes-256-cbc -pass pass:Password -P -md sha1 / | grep iv | cut -d'=' -f2) > /ssd/msb/msb_5_5_360/bkp/backups.key

Next, I would run my full backup:

innobackupex --defaults-file=/ssd/msb/msb_5_5_360/my.sandbox.cnf / --extra-lsndir=/ssd/msb/msb_5_5_360/bkp/lsns/$CURDATE / --encrypt=AES256 --encrypt-key-file=/ssd/msb/msb_5_5_360/bkp/backups.key / --no-timestamp /ssd/msb/msb_5_5_360/bkp/full/$CURDATE

The output says my full backup is saved to:

innobackupex: Backup created in directory '/ssd/msb/msb_5_5_360/bkp/full/2014-04-23_01_20_46'140423 01:20:55innobackupex: Connection to database server closed140423 01:20:55innobackupex: completed OK!

Now here’s the trick, because the full backup is encrypted, we will use the xtrabackup_checkpoints file separately saved by xtrabackup to the --extra-lsn-dir path we specified above to get the LSN and use that for our next incremental backup.

LAST_LSN=$( cat /ssd/msb/msb_5_5_360/bkp/lsns/$CURDATE/xtrabackup_checkpoints / | grep to_lsn | cut -d'=' -f2)CURDATE=$(date +%Y-%m-%d_%H_%M_%S)mkdir /ssd/msb/msb_5_5_360/bkp/lsns/$CURDATE

Above, we get the LSN value and assign it to a variable. Similarly, we created a new CURDATE string for our incremental backup to use and created a new directory for the xtrabackup_checkpoints file. If you plan to create another incremental backup based off of what we are about to take now, you will use this next xtrabackup_checkpoints file to get LAST_LSN.

With the up and coming Percona XtraBackup 2.2.1, you will not need --extra-lsn-dir anymore nor parse the xtrabackup_checkpoints file anymore for this purpose. Anew featurethat will allow the user to save backup metadata to an InnoDB table will be available.

So, now that we got our $LAST_LSN value, we execute our incremental backup with the command:

innobackupex --defaults-file=/ssd/msb/msb_5_5_360/my.sandbox.cnf / --extra-lsndir=/ssd/msb/msb_5_5_360/bkp/lsns/$CURDATE / --encrypt=AES256 --encrypt-key-file=/ssd/msb/msb_5_5_360/bkp/backups.key / --no-timestamp --incremental --incremental-lsn $LAST_LSN / /ssd/msb/msb_5_5_360/bkp/incr/$CURDATE

Again, based on the output, my backup was created at:

innobackupex: Backup created in directory '/ssd/msb/msb_5_5_360/bkp/incr/2014-04-23_01_21_00'140423 01:21:47innobackupex: Connection to database server closed140423 01:21:47innobackupex: completed OK!

No we have a full backup and an incremental backup, of course to make sure our backups are usable, we’d like to validate them. To do that, our first step is to decrypt both full and incremental backups. innobackupex has another handy --decrypt option for that, you can even use --parallel to make it faster.

innobackupex --decrypt=AES256 / --encrypt-key-file=/ssd/msb/msb_5_5_360/bkp/backups.key / /ssd/msb/msb_5_5_360/bkp/full/2014-04-23_01_20_46innobackupex --decrypt=AES256 / --encrypt-key-file=/ssd/msb/msb_5_5_360/bkp/backups.key / /ssd/msb/msb_5_5_360/bkp/incr/2014-04-23_01_21_00

Once the backups are decrypted, we can go through the usual process of preparing a full and incremental backups as described on themanual.

innobackupex --defaults-file=/ssd/msb/msb_5_5_360/my.sandbox.cnf / --apply-log --redo-only /ssd/msb/msb_5_5_360/bkp/full/2014-04-23_01_20_46innobackupex --defaults-file=/ssd/msb/msb_5_5_360/my.sandbox.cnf / --apply-log --redo-only /ssd/msb/msb_5_5_360/bkp/full/2014-04-23_01_20_46 / --incremental-dir=/ssd/msb/msb_5_5_360/bkp/incr/2014-04-23_01_21_00innobackupex --defaults-file=/ssd/msb/msb_5_5_360/my.sandbox.cnf / --apply-log /ssd/msb/msb_5_5_360/bkp/full/2014-04-23_01_20_46

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)

• MySQL
• Percona XtraBackup
• Tips and Tricks