How to make an incremental backup from compressed(and encrypted) full backup?

MySQL & MariaDB Percona XtraBackup
1

Hi all,

I was going through documentation and there is a nice description how to make an incremental backup from full backup. So the command is:

   Now that you have a full backup, you can make an incremental backup based on it. Use a command such as the following:

      $ xtrabackup --backup --target-dir=/data/backups/inc1 \
      --incremental-basedir=/data/backups/base --datadir=/var/lib/mysql/

But in my case it’s a bit different, because my full backup is compressed AND encrypted and I would like to make an incremental backup from this file.
My full backup command(script) looks like below:

#!/bin/bash

DAY=$(date +%Y-%m-%d)
mkdir -p /var/backups/mysql/${DAY} 2>/dev/null || true
cd /var/backups/mysql/${DAY}
DIRECTORY=/var/backups/mysql/${DAY}

Execute the compressed and encrypted full backup.

xtrabackup --defaults-file=/etc/mysql/backup-my.cnf
–no-timestamp
–use-memory=1G
–stream=xbstream
–parallel=4
–encrypt=AES256
–encrypt-key-file=/etc/mysql/.mykeyfile
–encrypt-threads=4
–compress
–compress-threads=4
–backup
–target-dir=$DIRECTORY
–history=$(hostname)-$(date +%d-%m-%Y) >
$(hostname)-$(date +%d-%m-%Y_%T).qp.xbc.xbs 2>
backup-progress-1-$(date +%d-%m-%Y_%T).log

So when I’m trying to do incremental backup as defined in manual, I got an error:

xtrabackup version 8.0.22-15 based on MySQL server 8.0.22 Linux (x86_64) (revision id: fea8a0e)
xtrabackup: Error: cannot open /var/backups/mysql/2021-11-10//xtrabackup_checkpoints
xtrabackup: error: failed to read metadata from /var/backups/mysql/2021-11-10//xtrabackup_checkpoints

Which is true, because I cannot get checkpoints file, as it is not generated in compressed and encrypted backup. But I’m wondering if someone solved this issue?

Thanks in advance for sharing solution.

BR,
Jan

2

Hi @JohnnyW

Since you are using stream, you will not have the checkpoints file. What you will have to do is to grep for last checkpoint on your backup-progress file:

cat /tmp/backup.progress | grep 'xtrabackup: The latest check point (for incremental):'
xtrabackup: The latest check point (for incremental): '18198029'

This means that next incremental has to start from LSN 18198029 .
Now you need to start xtrabackup but instead of --incremental-basedir you should use --incremental-lsn= passing this number.

If you want you can automate it by:

xtrabackup --backup ... --incremental-lsn=$(grep 'xtrabackup: The latest check point (for incremental):' /tmp/backup.progress | awk -F':' '{print $3}' | awk -F"'" '{print $2}')

Just replace /tmp/backup.progress with the log file produced by 2> backup-progress-1-$(date +%d-%m-%Y_%T).log