EmergencyEMERGENCY? Get 24/7 Help Now!

Encrypted and incremental MySQL backups with Percona XtraBackup

Posted on:



Share Button

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 Button

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.


, , , ,

MySQL, Percona MySQL Consulting, Percona MySQL Support, Percona Software, Percona XtraBackup

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

    Thanks for the ideas Jervin!


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



  • Jervin Real Post author


    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.


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


  • Jervin Real Post author


    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


  • Jervin Real Post author


    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


Leave a Reply

Percona’s widely read Percona Data 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.