How to do incemental xtrabackups with compressed & encrypted xbstreams?

MySQL & MariaDB Percona XtraBackup
1

Hi,

I’m getting started with xtrabackup, backing up my 1st setup of Percona Server for MySQL.

I can create full backups OK, either as normal directory backups or using streams.

With the normal directory approach, where I haven’t compressed or encrypted the backup, it’s easy to just do incremental backups relative to the full backup directory.
It works OK; I can prepare across multiple incremental backups & restore to the server.

I’m not clear on how you do incremental backups when you’re using streams & are encrypting and compressing

If I do a full stream backup, and pipe it through gzip to, and then pipe through openssl to encrypt, I end up with a single file. for example, I create the 1st full stream backup with the command like

xtrabackup --backup --stream=xbstream --extra-lsndir=/pathto/dir0 ... \
| gzip \
| openssl enc -e ... -out /pathto/dir0/streambackup.0.enc.xz

I can decrypt & decompress that output file, and verify that it’s the same as the source database dir.

So now, I want to do a 2nd backup, this time incremental relateive to THAT 1st full backup.

This approach, just pointing at the original dir0 as the relative source,

xtrabackup --backup --stream=xbstream --extra-lsndir=/pathto/dir1 --incremental-basedir=/pathto/dir0 ... \
| gzip \
| openssl enc -e ... -out /pathto/dir1/streambackup.1.enc.xz

doesn’t seem like it’ll work becuase the prior backup is compressed & encrypted still.

When I do an incremental backup relative to a prior backup, do I need to 1st completely decrypt & decompress the prior, streamed backup, so it’s available locally as a fullly populated data directory?

Or is there some inline xtrabackup commands &/or fancy piping that I can do to keep the original & prior backups in their ‘single file’ form?

I guess I’m looking for an all-streams procedure for doing stream-based backups.
So I’m not tied to any particular approach – just one that will reliably work and be done right.

1 Like
2

When taking the incremental backup, use –incremental-lsn to pass the LSN from the previous full backup. You are using --extra-lsndir so you have the info in there. Just parse xtrabackup_info to get the last LSN and pass that to your incremental process instead of incremental-basedir

1 Like
3

@matthewb

I RTFM’d a few more times.

IIUC using “–incremental-lsn” looks in the prior stream backup’s lsn data, and finds where it “left off”, and the new incremental backup will be done “against” the live server data starting at the point-in-time ID’d by that prior, incremental-lsn ID?

and, therefore, decrypting/drecompressing the prior stream backup is NOT necessary to the process?

( of backing up, that is.

I understand that I will have to to restore, eventually. although I’m not sure how to ‘prepare’. I think I’ll have to decrypt, decompress & prepare each of the incr mbstream-backups’ dirs in the chain.)

And just to be sure I’m not doing something wrong here, you said to “parse xtrabackup_info” to get the last LSN.

When I check

grep _lsn xtrabackup_*
	xtrabackup_checkpoints:from_lsn = 0
	xtrabackup_checkpoints:to_lsn = 18956716
	xtrabackup_checkpoints:last_lsn = 18956728
	xtrabackup_info:innodb_from_lsn = 0
	xtrabackup_info:innodb_to_lsn = 18956716

do you mean THIS value

last_lsn = 18956728

from xtrabackup_checkpoints, not xtrabackup_info?

2 Likes
4

Correct. As long as you have the prior ending LSN, that’s where you start the next incremental from.

2 Likes