We’ve recently received a number of questions on how to implement incremental MySQL backups alongside encryption with Percona 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 MySQL 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 .

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.

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

Of course, I need an encryption key for my encrypted backups, in this case, taking the example from the manual, 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.

Next, I would run my full backup:

The output says my full backup is saved to:

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.

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. A new feature that 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:

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

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.

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

12 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Jo Valerio

very timely post. I’ve been working on my incremental script for our large DB backups.

Thanks for the ideas Jervin!

0
Tomáš Jacík

Hello,

I have a little offtopic question. Why someone didn’t yet (maybe from percona) implemented snapshotting directly to mysql? I mean ZFS-like snapshotting using COW. Af far as I know, InnoDB already have transaction log. With this feature, backups and rollbacks would be perfect.

What would be the most difficult areas if I wanted to implement this feature or why it is not possible?

Thanks

0
Jervin Real

Tomáš,

XtraBackup is in a way taking snapshots, except that unlike most filesystem based snapshots, it tries to guarantee consistency at the end of the backup not at the beginning. If you are talking about internally maintained snapshots which you can easily roll back or forward to – this will definitely be a great feature but likely to be a big undertaking.

0
Tomáš Jacík

I would like to have opportunity to query (read-only of course) already made snapshots and database/table level rollback. The should be perfectly possible to have clones too (deduplication).

I understand it will be a lot of work, but I think that essential parts are already implemented. Can you give me some advices where can I broke my teeth? What would be the most difficult part?

0
Jervin Real

Tomáš,

I can’t tell where to start either with the feature you wish to implement, InnoDB has a lot of moving parts :-). If you are a developer, it would help to get familiar with its internals first and hang about the developers mailings lists. I would think the most difficult part is avoiding performance regressions for this feature since if you wish to keep this MySQL native, you’d probably be adding a lot more change tracking involved and this is already an known problem with InnoDB if you search for other posts on this blog i.e. history list length.

0
Aaron Bishop

You can get around keeping track of where you last backed up to by making a symbolic link to the last lsns directory. I found this helpful when running this all from a script in a cron job.

0
Suyash Jain

Hi,

encrypt: unable to set libgcrypt cipher key – User defined source 1 : Invalid key length
encrypt: failed to create worker threads.

I am trying to create enc file like this.

openssl enc -aes-256-cbc -k secret123sec_8dv4j89t4jit4u89t7 -P -md sha1 | grep iv | cut -d’=’ -f2 > DB_ENC_STR

0
Jervin Real

Suyash,

I tested your command and it resulted in a 33 byte file – meaning it may be exceed the 256bits key size you are trying to use. Try this:

key=$(openssl enc -aes-256-cbc -k secret123sec_8dv4j89t4jit4u89t7 -P -md sha1 | grep iv | cut -d'=' -f2)
echo -n "$key" > DB_ENC_STR

0
Julien Deloubes

Interesting post.
In a case you want to use it for a backup script how could you make the variables LAST_LSN persistent?
In the case of an incremental backup you have to retrieve the lsn of the full or older incs backups, but the variable is based on the date of the actual day.
Thanks

0
mayank1dbayank

Hi Jervin,

I am planning to implement your pyxbackup script for backup. while testing i am facing below error:
Please check.
====
root@LSG-:/home/xtra# ./pyxbackup full
Traceback (most recent call last):
File “./pyxbackup”, line 3292, in
_error(“An uncaught exception error has occurred!”)
File “./pyxbackup”, line 233, in _error
_out(‘ERROR’, *msgs)
File “./pyxbackup”, line 219, in _out
out = “[%s] %s: %s” % (date(time.time()), tag, s)
NameError: global name ‘date’ is not defined

====

0
Tien Huy

Please help me guide perform restore incremental backup base your example.
I’ve perform restore incremental backup base your example but not successfully.
Thanks!

0
Surachit Pattanodom

I tested on encryption full backup. It was OK. But I could not restore full back up. Do I need to decrypt file before restore or not?. Also –defaults-file=/ssd/msb/msb_5_5_360/my.sandbox.cnf was not success. ERROR..

0