Encrypted and incremental MySQL backups with Percona XtraBackup

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.

Share this post

Comments (12)

  • Jo Valerio

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

    Thanks for the ideas Jervin!

    April 28, 2014 at 1:17 am
  • Tomáš Jacík


    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?


    April 29, 2014 at 8:33 pm
  • Jervin Real


    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.

    April 29, 2014 at 8:41 pm
  • 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?

    April 29, 2014 at 8:54 pm
  • Jervin Real


    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.

    April 29, 2014 at 11:59 pm
  • 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.

    October 1, 2014 at 1:02 pm
  • Suyash Jain


    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

    February 12, 2016 at 2:25 am
  • Jervin Real


    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

    February 13, 2016 at 1:54 am