# Why Does Xtrabackup Keep Corrupting My Database? **URL:** \ **Category:** Percona XtraBackup\ **Created:** [February 15, 2022, 4:47am UTC](https://forums.percona.com/t/why-does-xtrabackup-keep-corrupting-my-database/14396 "2022-02-15T04:47:15Z")\ **Posts on this page:** 11\ **Page:** 1
**Author:** ![geek\_prophet](https://avatars.discourse-cdn.com/v4/letter/g/85f322/32.png) [@geek\_prophet](https://forums.percona.com/u/geek_prophet)\ **Post date:** [February 15, 2022, 4:47am UTC](https://forums.percona.com/t/why-does-xtrabackup-keep-corrupting-my-database/14396/1 "2022-02-15T04:47:15Z")
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 1. 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 1. Prep xtrabackup --defaults-file=/fs/site000/mysqld/my.cnf --login-path=admin --rsync --prepare --target-dir=/xb.nfs/store50b/site000/base --apply-log-only 1. 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 1. Delete unused inc1 folder rm -rf inc1 1. 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 1. 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 1. 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](https://www.dropbox.com/s/vjx9mpyp5w61iti/xtrabackup_db_corruption_log.txt?dl=0)** > > Shared with Dropbox What am I doing wrong? ---
**Author:** ![Rahul\_Malik](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/rahul_malik/32/3580_2.png) [@Rahul\_Malik](https://forums.percona.com/u/Rahul_Malik)\ **Post date:** [February 15, 2022, 6:45am UTC](https://forums.percona.com/t/why-does-xtrabackup-keep-corrupting-my-database/14396/2 "2022-02-15T06:45:57Z")
> [@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 ---
**Author:** ![geek\_prophet](https://avatars.discourse-cdn.com/v4/letter/g/85f322/32.png) [@geek\_prophet](https://forums.percona.com/u/geek_prophet)\ **Post date:** [February 15, 2022, 4:36pm UTC](https://forums.percona.com/t/why-does-xtrabackup-keep-corrupting-my-database/14396/3 "2022-02-15T16:36:25Z")
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. ---
**Author:** ![Marcelo\_Altmann](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/marcelo_altmann/32/11299_2.png) [@Marcelo\_Altmann](https://forums.percona.com/u/Marcelo_Altmann)\ **Post date:** [February 15, 2022, 5:54pm UTC](https://forums.percona.com/t/why-does-xtrabackup-keep-corrupting-my-database/14396/4 "2022-02-15T17:54:01Z")
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` ---
**Author:** ![geek\_prophet](https://avatars.discourse-cdn.com/v4/letter/g/85f322/32.png) [@geek\_prophet](https://forums.percona.com/u/geek_prophet)\ **Post date:** [February 15, 2022, 6:19pm UTC](https://forums.percona.com/t/why-does-xtrabackup-keep-corrupting-my-database/14396/5 "2022-02-15T18:19:04Z")
> [@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 1. 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 1. 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 1. 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 1. 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 1. 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 1. 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 1. 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. ---
**Author:** ![Marcelo\_Altmann](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/marcelo_altmann/32/11299_2.png) [@Marcelo\_Altmann](https://forums.percona.com/u/Marcelo_Altmann)\ **Post date:** [February 15, 2022, 6:23pm UTC](https://forums.percona.com/t/why-does-xtrabackup-keep-corrupting-my-database/14396/6 "2022-02-15T18:23:23Z")
> [@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` ---
**Author:** ![geek\_prophet](https://avatars.discourse-cdn.com/v4/letter/g/85f322/32.png) [@geek\_prophet](https://forums.percona.com/u/geek_prophet)\ **Post date:** [February 15, 2022, 6:33pm UTC](https://forums.percona.com/t/why-does-xtrabackup-keep-corrupting-my-database/14396/7 "2022-02-15T18:33:20Z")
> [@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 1. 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 1. 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 1. 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 1. 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 1. 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 1. 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 1. 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. ---
**Author:** ![Marcelo\_Altmann](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/marcelo_altmann/32/11299_2.png) [@Marcelo\_Altmann](https://forums.percona.com/u/Marcelo_Altmann)\ **Post date:** [February 15, 2022, 7:41pm UTC](https://forums.percona.com/t/why-does-xtrabackup-keep-corrupting-my-database/14396/8 "2022-02-15T19:41:23Z")
> [@geek\_prophet](#): > > 1. 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 > > 1. 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](#): > > 1. 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 > > 1. 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. ---
**Author:** ![geek\_prophet](https://avatars.discourse-cdn.com/v4/letter/g/85f322/32.png) [@geek\_prophet](https://forums.percona.com/u/geek_prophet)\ **Post date:** [February 15, 2022, 7:49pm UTC](https://forums.percona.com/t/why-does-xtrabackup-keep-corrupting-my-database/14396/9 "2022-02-15T19:49:45Z")
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? ---
**Author:** ![Marcelo\_Altmann](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/marcelo_altmann/32/11299_2.png) [@Marcelo\_Altmann](https://forums.percona.com/u/Marcelo_Altmann)\ **Post date:** [February 15, 2022, 8:03pm UTC](https://forums.percona.com/t/why-does-xtrabackup-keep-corrupting-my-database/14396/10 "2022-02-15T20:03:36Z")
> [@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. ---
**Author:** ![geek\_prophet](https://avatars.discourse-cdn.com/v4/letter/g/85f322/32.png) [@geek\_prophet](https://forums.percona.com/u/geek_prophet)\ **Post date:** [February 15, 2022, 9:00pm UTC](https://forums.percona.com/t/why-does-xtrabackup-keep-corrupting-my-database/14396/11 "2022-02-15T21:00:55Z")
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.