Why Does Xtrabackup Keep Corrupting My Database?

[geek_prophet]

Why does xtrabackup keep corrupting my database?

Here is the sequence:

1. Do Initial full backup.

xtrabackup --defaults-file=/fs/site000/mysqld/my.cnf --login-path=admin --rsync --backup --target-dir=/xb.nfs/store50b/site000/base

2. Do incremental to folder inc1

xtrabackup --defaults-file=/fs/site000/mysqld/my.cnf --login-path=admin --rsync --backup --target-dir=/xb.nfs/store50b/site000/inc1 --incremental-basedir=/xb.nfs/store50b/site000/base

3. Prep

xtrabackup --defaults-file=/fs/site000/mysqld/my.cnf --login-path=admin --rsync --prepare --target-dir=/xb.nfs/store50b/site000/base --apply-log-only

4. Merge

xtrabackup --defaults-file=/fs/site000/mysqld/my.cnf --login-path=admin --rsync --prepare --target-dir=/xb.nfs/store50b/site000/base --incremental-dir=/xb.nfs/store50b/site000/inc1

5. Delete unused inc1 folder

rm -rf inc1

6. Do new incremental to folder inc1

xtrabackup --defaults-file=/fs/site000/mysqld/my.cnf --login-path=admin --rsync --backup --target-dir=/xb.nfs/store50b/site000/inc1 --incremental-basedir=/xb.nfs/store50b/site000/base

7. Prep again

xtrabackup --defaults-file=/fs/site000/mysqld/my.cnf --login-path=admin --rsync --prepare --target-dir=/xb.nfs/store50b/site000/base --apply-log-only

8. Merge again… BOOM. Corruption warnings.

xtrabackup --defaults-file=/fs/site000/mysqld/my.cnf --login-path=admin --rsync --prepare --target-dir=/xb.nfs/store50b/site000/base --incremental-dir=/xb.nfs/store50b/site000/inc1

Here is a link to the full logs…

xtrabackup_db_corruption_log.txt

What am I doing wrong?

[Rahul_Malik]

@geek_prophet

xtrabackup --defaults-file=/fs/site000/mysqld/my.cnf --login-path=admin --rsync --prepare --target-dir=/xb.nfs/store50b/site000/base --incremental-dir=/xb.nfs/store50b/site000/inc1

Please do merge with --apply-log-only . Only for the final incremental should run --prepare without --apply-log-only

[geek_prophet]

When I try the merge with --apply-log-only, I get an error.

I do…

xtrabackup --defaults-file=/fs/site000/mysqld/my.cnf --login-path=admin --rsync --backup --target-dir=/xb.nfs/store50b/site000/base

then…

xtrabackup --defaults-file=/fs/site000/mysqld/my.cnf --login-path=admin --rsync --backup --target-dir=/xb.nfs/store50b/site000/inc1 --incremental-basedir=/xb.nfs/store50b/site000/base

then…

xtrabackup --defaults-file=/fs/site000/mysqld/my.cnf --login-path=admin --rsync --prepare --target-dir=/xb.nfs/store50b/site000/base

then…

xtrabackup --defaults-file=/fs/site000/mysqld/my.cnf --login-path=admin --rsync --prepare --target-dir=/xb.nfs/store50b/site000/base --apply-log-only --incremental-dir=/xb.nfs/store50b/site000/inc1

xtrabackup --defaults-file=/fs/site000/mysqld/my.cnf --login-path=admin --rsync --prepare --target-dir=/xb.nfs/store50b/site000/base --apply-log-only --incremental-dir=/xb.nfs/store50b/site000/inc1
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=1073741824 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=1 --redo-log-version=1
xtrabackup: recognized client arguments: --user=root --password=* --rsync=1 --prepare=1 --target-dir=/xb.nfs/store50b/site000/base --apply-log-only=1 --incremental-dir=/xb.nfs/store50b/site000/inc1
xtrabackup version 2.4.24 based on MySQL server 5.7.35 Linux (x86_64) (revision id: b4ee263)
incremental backup from 3058148 is enabled.
xtrabackup: cd to /xb.nfs/store50b/site000/base/
xtrabackup: This target seems to be already prepared.
xtrabackup: error: applying incremental backup needs target prepared with --apply-log-only.

[Marcelo_Altmann]

Your base is the one that needs to be prepared with --apply-log-only.

Basically, apply log only means you don’t want to rollback transactions that are not completed by the end of backup, because the changes are that they might get a commit on the next incremental.

So you do for all backups expect the last: --prepare --apply-log-only
Last backup: --prepare

[geek_prophet]

@Marcelo_Altmann

Your base is the one that needs to be prepared with --apply-log-only .

That’s what I’m doing, isn’t it?

My sequence is…

1. Take initial backup:

xtrabackup --defaults-file=/fs/site000/mysqld/my.cnf --login-path=admin --rsync --backup --target-dir=/xb.nfs/store50b/site000/base

2. Take incremental backup.

xtrabackup --defaults-file=/fs/site000/mysqld/my.cnf --login-path=admin --rsync --backup --target-dir=/xb.nfs/store50b/site000/inc1 --incremental-basedir=/xb.nfs/store50b/site000/base

3. Prep the base:

xtrabackup --defaults-file=/fs/site000/mysqld/my.cnf --login-path=admin --rsync --prepare --apply-log-only --target-dir=/xb.nfs/store50b/site000/base

4. Merge the incremental.

xtrabackup --defaults-file=/fs/site000/mysqld/my.cnf --login-path=admin --rsync --prepare --target-dir=/xb.nfs/store50b/site000/base --incremental-dir=/xb.nfs/store50b/site000/inc1

5. At this point, the base folder is consistent. We remove the old inc1 folder and re-use it for the next incremental.

rm -rf inc1

6. Perform a new incremental.

xtrabackup --defaults-file=/fs/site000/mysqld/my.cnf --login-path=admin --rsync --backup --target-dir=/xb.nfs/store50b/site000/inc1 --incremental-basedir=/xb.nfs/store50b/site000/base

7. Prep the base folder again.

xtrabackup --defaults-file=/fs/site000/mysqld/my.cnf --login-path=admin --rsync --prepare --apply-log-only --target-dir=/xb.nfs/store50b/site000/base

8. Merge the new incremental.

xtrabackup --defaults-file=/fs/site000/mysqld/my.cnf --login-path=admin --rsync --prepare --target-dir=/xb.nfs/store50b/site000/base --incremental-dir=/xb.nfs/store50b/site000/inc1

At this point, we get errors…

InnoDB: Are you sure you are using the right ib_logfiles to start up the database? Log sequence number in the ib_logfiles is 3058241, less than the log sequence number in the first system tablespace file header, 3058737.
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: Page [page id: space=0, page number=7] log sequence number 3058728 is in the future! Current system log sequence number 3058267.
InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files.

[Marcelo_Altmann]

@geek_prophet

then…

xtrabackup --defaults-file=/fs/site000/mysqld/my.cnf --login-path=admin --rsync --prepare --target-dir=/xb.nfs/store50b/site000/base

then…

xtrabackup --defaults-file=/fs/site000/mysqld/my.cnf --login-path=admin --rsync --prepare --target-dir=/xb.nfs/store50b/site000/base --apply-log-only --incremental-dir=/xb.nfs/store50b/site000/inc1

From your previous reply, you did on /xb.nfs/store50b/site000/base a --prepare then on /xb.nfs/store50b/site000/inc1 you did a --prepare --apply-log-only

[geek_prophet]

@Marcelo_Altmann

From your previous reply, you did on /xb.nfs/store50b/site000/base a --prepare then on /xb.nfs/store50b/site000/inc1 you did a --prepare --apply-log-only

Yes, please ignore that post. The following sequence is correct, no? And yet I am still getting corruption warnings.

1. Take initial backup:

xtrabackup --defaults-file=/fs/site000/mysqld/my.cnf --login-path=admin --rsync --backup --target-dir=/xb.nfs/store50b/site000/base

2. Take incremental backup.

xtrabackup --defaults-file=/fs/site000/mysqld/my.cnf --login-path=admin --rsync --backup --target-dir=/xb.nfs/store50b/site000/inc1 --incremental-basedir=/xb.nfs/store50b/site000/base

3. Prep the base:

xtrabackup --defaults-file=/fs/site000/mysqld/my.cnf --login-path=admin --rsync --prepare --apply-log-only --target-dir=/xb.nfs/store50b/site000/base

4. Merge the incremental.

xtrabackup --defaults-file=/fs/site000/mysqld/my.cnf --login-path=admin --rsync --prepare --target-dir=/xb.nfs/store50b/site000/base --incremental-dir=/xb.nfs/store50b/site000/inc1

5. At this point, the base folder is consistent. We remove the old inc1 folder and re-use it for the next incremental.

rm -rf inc1

6. Perform a new incremental.

xtrabackup --defaults-file=/fs/site000/mysqld/my.cnf --login-path=admin --rsync --backup --target-dir=/xb.nfs/store50b/site000/inc1 --incremental-basedir=/xb.nfs/store50b/site000/base

7. Prep the base folder again.

xtrabackup --defaults-file=/fs/site000/mysqld/my.cnf --login-path=admin --rsync --prepare --apply-log-only --target-dir=/xb.nfs/store50b/site000/base

8. Merge the new incremental.

xtrabackup --defaults-file=/fs/site000/mysqld/my.cnf --login-path=admin --rsync --prepare --target-dir=/xb.nfs/store50b/site000/base --incremental-dir=/xb.nfs/store50b/site000/inc1

At this point, we get errors…

InnoDB: Are you sure you are using the right ib_logfiles to start up the database? Log sequence number in the ib_logfiles is 3058241, less than the log sequence number in the first system tablespace file header, 3058737.
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: Page [page id: space=0, page number=7] log sequence number 3058728 is in the future! Current system log sequence number 3058267.
InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files.

[Marcelo_Altmann]

@geek_prophet

4. Merge the incremental.

xtrabackup --defaults-file=/fs/site000/mysqld/my.cnf --login-path=admin --rsync --prepare --target-dir=/xb.nfs/store50b/site000/base --incremental-dir=/xb.nfs/store50b/site000/inc1

5. At this point, the base folder is consistent. We remove the old inc1 folder and re-use it for the next incremental.

Once you have prepared a backup with only --prepare you cannot use it for a new incremental.

@geek_prophet

7. Prep the base folder again.

xtrabackup --defaults-file=/fs/site000/mysqld/my.cnf --login-path=admin --rsync --prepare --apply-log-only --target-dir=/xb.nfs/store50b/site000/base

8. Merge the new incremental.

xtrabackup --defaults-file=/fs/site000/mysqld/my.cnf --login-path=admin --rsync --prepare --target-dir=/xb.nfs/store50b/site000/base --incremental-dir=/xb.nfs/store50b/site000/inc1

Here at point 7, you are running --prepare --apply-log-only on /xb.nfs/store50b/site000/base however, your already did a --prepare without --apply-log-only on this folder at step 4.

[geek_prophet]

I was assuming that a base folder that has been prepared and merged is the same as a base folder immediately after a full backup. Is that not the case?

We want to take a daily incremental and merge it with the base, and just keep doing that over and over. After the first full backup, we don’t ever want to take another full backup.

We want to have a backup procedure like this:

full backup
incremental
merge
incremental
merge
incremental
merge
…etc.

Is that strategy not possible?

[Marcelo_Altmann]

@geek_prophet

I was assuming that a base folder that has been prepared and merged is the same as a base folder immediately after a full backup. Is that not the case?

Once you have ran the --prepare without --apply-log-only that folder is like a mysql datadir ready to be used.

You cannot from this point forward try to apply another incremental backup to it.

What you can do on your merge is to always --prepare it with --apply-log-only. In case something happens and you have to use the merge, you just need to run --prepare once again to go through the rollback phases and make the backup dir ready to be used.

[geek_prophet]

That suggestion seems to work! I am still getting the following warning, but at least it not talking about a corrupt database any more.

InnoDB: Are you sure you are using the right ib_logfiles to start up the database? Log sequence number in the ib_logfiles is 3058411, less than the log sequence number in the first system tablespace file header, 3058718.