Incremental restore fails

MySQL & MariaDB Percona XtraBackup
1

Currently in testing on VMs, my backups are on a remote server

I have made a full backup in /MySQLBackups/
I have made an incremental in /tmp/enc1/
I have made an incremental in /tmp/enc2/

Full backup was done (Launched on the MySQL test server) with:

xtrabackup --backup --user=xtrabk --password-thepass --stream=xbstream | ssh root@remoteip “cat | xbstream -v -x -C /MySQLBackups”

I check the xtrabackup_checkpoints to determine the last LSN on the backup: 4528497

Did the first incremental with:

xtrabackup --backup --user=xtrabk --password-thepass --incremental-lsb=4528498 --stream=xbstream | ssh root@remoteip “cat | xbstream -v -x -C /tmp/enc1”

I check the xtrabackup_checkpoints to determine the last LSN on the backup: 4591652

Did the second incremental with:

xtrabackup --backup --user=xtrabk --password-thepass --incremental-lsb=4591653 --stream=xbstream | ssh root@remoteip “cat | xbstream -v -x -C /tmp/enc2”

My 3 directory have the backups on my remoteip machine

When I do the prepare on the full with:

xtrabackup --prepare --apply-log-only --target-dir=/MySQLBackups

It works, but when I do:

xtrabackup --prepare --apply-log-only --target-dir=/MySQLBackups --incremental-dir=/tmp/enc1

I get:

xtrabackup: error: This incremental backup seems not to be proper for the target.

The checkpoints for Full, enc1, enc2 are:

backup_type = log-applied
from_lsn = 0
to_lsn = 4528488
last_lsn = 4528497
compact = 0
recover_binlog_info = 0
flushed_lsn = 4528497

backup_type = incremental
from_lsn = 4528498
to_lsn = 4591643
last_lsn = 4591652
compact = 0
recover_binlog_info = 0
flushed_lsn = 4591652

backup_type = incremental
from_lsn = 4591653
to_lsn = 4660765
last_lsn = 4660774
compact = 0
recover_binlog_info = 0
flushed_lsn = 4660774

This works under XtraBackup 8.0 and MySQL 8.0 but it is failing (as shown above) on XtraBackup 2.4 with MySQL 5.7

Am I doing something wrong in 2.4 or is there something different to do?

2

Hi @themactech .
Welcome to the Percona Forums.

I was checking in the Docs for xtrabackup 2.4 and there is this comment:

from_lsn is the starting LSN of the backup and for incremental it has to be the same as to_lsn (if it is the last checkpoint) of the previous/base backup.

I can see you’re using the the last_lsn instead of to_lsn. Could you try with to_lsn and se if that works?
Also, I’m not sure if itis a typo, but the flag would be --incremental-lsn.

Give it a try and see if that helps.
Best,
Mauricio.

1 Like
3

It was indeed a typo that is not in my terminal commands. I have just tried with setting the incremental-lsn to the value of “to_lsn” and I still get the same error.

4

I just tried the same process but locally without streaming, and I also get the same error. Is there a way to get more verbose logs from XtraBackup? I also tried traditional incrementals locally and these work. So this seems to be related to increment-lsn. Again, I got this working on version 8 (both XtraBackup and. MySQL) but it fails on 2.4 and 5.7. In both cases I am running Debian 11.

5

Hi @themactech

I can see you have some mismatch in between to_lsn from both base backup and from_lsn on next backup:

#Full
backup_type = log-applied
from_lsn = 0
to_lsn = 4528488

#inc1
backup_type = incremental
from_lsn = 4528498 (full finished at 4528488)
to_lsn = 4591643

#inc2
backup_type = incremental
from_lsn = 4591653 (inc1 finished at 4591643)
to_lsn = 4660765

You should see exact same number from to_lsn and from_lsn when comparing base and incremental backups.

When using streaming, we advise to use --extra-lsndir=/path/full_lsn to save a copy of xtrabackup_checkpoints and then later you just point to this folder as base for incremental.

Example:

#Full
xtrabackup --backup --user=xtrabk --password-thepass --stream=xbstream --extra-lsndir=/tmp/full_lsn | ssh root@remoteip “cat | xbstream -v -x -C /MySQLBackups”

#Inc1
xtrabackup --backup --user=xtrabk --password-thepass --stream=xbstream --incremental-basedir=/tmp/full_lsn  --extra-lsndir=/tmp/inc1_lsn| ssh root@remoteip “cat | xbstream -v -x -C /tmp/enc1”

This helps with typos on the sequence number. If you still have issues, please send us a full copy of all your xtrabackup command (–backup from full, incs and --prepare)

6

The --extra-lsndir option works, thanks!

1 Like