EmergencyEMERGENCY? Get 24/7 Help Now!

Encrypted and incremental MySQL backups with Percona XtraBackup

 | April 24, 2014 |  Posted In: MySQL, Percona MySQL Consulting, Percona MySQL Support, Percona Software, 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.

Jervin Real

As Senior Consultant, Jervin partners with Percona's customers on building reliable and highly performant MySQL infrastructures while also doing other fun stuff like watching cat videos on the internet. Jervin joined Percona in Apr 2010.


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

    Thanks for the ideas Jervin!

  • 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?


  • 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.

  • 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?

  • 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.

  • 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.

  • 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

  • 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

  • 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.

  • 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


Leave a Reply


Percona’s widely read Percona Database Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.

Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below and we’ll send you an update every Friday at 1pm ET.

No, thank you. Please do not ask me again.