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
Published at DZone with permission ofPeter Zaitsev, author and DZone MVB. (source)
(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)
Tags:
- MySQL
- Percona XtraBackup
- Tips and Tricks
