Unable prepare mysql incremental backups

MySQL & MariaDB Percona XtraBackup
1

My issue is a bit different than reported in [Jul 24] (Unable to restore incremental data).

# rpm -q mysql-community-server
mysql-community-server-8.0.26-1.el8.x86_64

# xtrabackup -v
xtrabackup: recognized server arguments: --datadir=/mysqldata/mysqldata --log_bin=/mysqlbinlogs/mysqlbinlogs/binlog
xtrabackup version 8.0.26-18 based on MySQL server 8.0.26 Linux (x86_64) (revision id: 4aecf82)

Steps i took:

  1. Backup (full + incrementals)
xtrabackup  --backup --target-dir=/mysqlbackup/mysqlbackup/base
xtrabackup  --backup --target-dir=/mysqlbackup/mysqlbackup/inc1 --incremental-basedir=/mysqlbackup/mysqlbackup/base
xtrabackup  --backup --target-dir=/mysqlbackup/mysqlbackup/inc2 --incremental-basedir=/mysqlbackup/mysqlbackup/base
xtrabackup  --backup --target-dir=/mysqlbackup/mysqlbackup/inc3 --incremental-basedir=/mysqlbackup/mysqlbackup/base
xtrabackup  --backup --target-dir=/mysqlbackup/mysqlbackup/inc4 --incremental-basedir=/mysqlbackup/mysqlbackup/base
  1. Now trying to restore. Preparing “base”:
# xtrabackup --prepare --apply-log-only --target-dir=/mysqlbackup/mysqlbackup/base/
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksums=1 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=50331648 --innodb_page_size=16384 --innodb_undo_directory=./ --innodb_undo_tablespaces=2 --server-id=0 --innodb_log_checksums=ON --innodb_redo_log_encrypt=0 --innodb_undo_log_encrypt=0
xtrabackup: recognized client arguments: --prepare=1 --apply-log-only=1 --target-dir=/mysqlbackup/mysqlbackup/base/
xtrabackup version 8.0.26-18 based on MySQL server 8.0.26 Linux (x86_64) (revision id: 4aecf82)
xtrabackup: cd to /mysqlbackup/mysqlbackup/base/
xtrabackup: This target seems to be not prepared yet.
/* skip * /
Shutdown completed; log sequence number 18457473
Number of pools: 1
211001 11:09:58 completed OK!

First incremental:

# xtrabackup --prepare --apply-log-only --target-dir=/mysqlbackup/mysqlbackup/base/ --incremental-dir=/mysqlbackup/mysqlbackup/inc1
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksums=1 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=50331648 --innodb_page_size=16384 --innodb_undo_directory=./ --innodb_undo_tablespaces=2 --server-id=0 --innodb_log_checksums=ON --innodb_redo_log_encrypt=0 --innodb_undo_log_encrypt=0
xtrabackup: recognized client arguments: --prepare=1 --apply-log-only=1 --target-dir=/mysqlbackup/mysqlbackup/base/ --incremental-dir=/mysqlbackup/mysqlbackup/inc1
xtrabackup version 8.0.26-18 based on MySQL server 8.0.26 Linux (x86_64) (revision id: 4aecf82)
incremental backup from 18457463 is enabled.
xtrabackup: cd to /mysqlbackup/mysqlbackup/base/
xtrabackup: This target seems to be already prepared with --apply-log-only.
Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(18461688)
/* skip */
211001 11:10:33 [00] Copying /mysqlbackup/mysqlbackup/inc1/binlog.index to ./binlog.index
211001 11:10:33 [00]        ...done
211001 11:10:33 completed OK!

Second incremental and failure:

# xtrabackup --prepare --apply-log-only --target-dir=/mysqlbackup/mysqlbackup/base/ --incremental-dir=/mysqlbackup/mysqlbackup/inc2
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksums=1 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=50331648 --innodb_page_size=16384 --innodb_undo_directory=./ --innodb_undo_tablespaces=2 --server-id=0 --innodb_log_checksums=ON --innodb_redo_log_encrypt=0 --innodb_undo_log_encrypt=0
xtrabackup: recognized client arguments: --prepare=1 --apply-log-only=1 --target-dir=/mysqlbackup/mysqlbackup/base/ --incremental-dir=/mysqlbackup/mysqlbackup/inc2
xtrabackup version 8.0.26-18 based on MySQL server 8.0.26 Linux (x86_64) (revision id: 4aecf82)
incremental backup from 18457463 is enabled.
xtrabackup: cd to /mysqlbackup/mysqlbackup/base/
xtrabackup: This target seems to be already prepared with --apply-log-only.
xtrabackup: error: This incremental backup seems not to be proper for the target.
xtrabackup:  Check 'to_lsn' of the target and 'from_lsn' of the incremental.
#

There are more incrementals i need to apply.
I have been reading Xtrabackup documentations for a while, but just cant figure out what i’m doing wrong.

Regards

1 Like
2

Hi @psipelgas

Seems like you have an issue on your --backup phase

Here the basedir for incremental must be the last backup, not always base:

xtrabackup  --backup --target-dir=/mysqlbackup/mysqlbackup/base
xtrabackup  --backup --target-dir=/mysqlbackup/mysqlbackup/inc1 --incremental-basedir=/mysqlbackup/mysqlbackup/base
xtrabackup  --backup --target-dir=/mysqlbackup/mysqlbackup/inc2 --incremental-basedir=/mysqlbackup/mysqlbackup/inc1
xtrabackup  --backup --target-dir=/mysqlbackup/mysqlbackup/inc3 --incremental-basedir=/mysqlbackup/mysqlbackup/inc2
xtrabackup  --backup --target-dir=/mysqlbackup/mysqlbackup/inc4 --incremental-basedir=/mysqlbackup/mysqlbackup/inc3

--incremental-basedir works by telling the backup to copy all changes from the last LSN recorded on the base dir.
What happened in your case is that on incremental 2 to 4, you are recopying all changes from base. When you try to apply the second backup, it sees that you already applied those changes (that came from inc1).

Please give it a try.

1 Like
3

Hi,
Thank You for pointing that out! I cant be more ashamed of myself.
I too another look at the documentation and now immediately saw its there… how can one be so blind…
Thank You!

2 Likes