Cannot prepare partial database backup

[Churchill]

Good day,

I’m fairly new with Percona Xtrabackup, and I’m having a difficulty restoring a backup. First of, I’ve created a script to run a partial backup every x hours. Here’s the backup command I’m using in my script:

innobackupex --include=$database $backupdir --host=$host --user=$username --password=$password --stream=tar | gzip -> $backupdir"database-(date +%Y-%m-%d).tar.gz"

I could say my backup is working since I do see the backup file being created and when I extract the backup, it contains *ibd file. Now, the problem is that when I try to prepare the backup, I get this:

innobackupex --apply-log --export /backups/xtrabackup-data/backup/12122020/
xtrabackup: recognized server arguments:
xtrabackup: recognized client arguments:
210107 02:05:00 innobackupex: Starting the apply-log operation

IMPORTANT: Please check that the apply-log run completes successfully.
At the end of a successful apply-log run innobackupex
prints “completed OK!”.

innobackupex version 2.4.20 based on MySQL server 5.7.26 Linux (x86_64) (revision id: c8b4056)
xtrabackup: auto-enabling --innodb-file-per-table due to the --export option
xtrabackup: cd to /backups/xtrabackup-data/backup/12122020/
xtrabackup: Error: cannot open ./xtrabackup_checkpoints
xtrabackup: Error: failed to read metadata from ‘./xtrabackup_checkpoints’

I look into the files extracted, but I don’t see a file called xtrabackup_checkpoints. All I have are *.ibd files and an ibdata1.

Here’s my percona xtrabackup version:

innobackupex --version
xtrabackup: recognized server arguments: --datadir=/data/mysql --innodb_flush_log_at_trx_commit=0 --innodb_flush_method=O_DIRECT --innodb_log_buffer_size=32M --innodb_read_io_threads=32 --innodb_write_io_threads=16 --innodb_log_file_size=1G --innodb_buffer_pool_size=10G --open_files_limit=50000
innobackupex version 2.4.20 Linux (x86_64) (revision id: c8b4056)

Any help is highly appreciated. Also, Is it possible to restore a partial backup without preparing? Thank you

[CTutte]

Hi Churchill,

If the backup prints “Completed OK!” at the end, it’s very likely the backup is not corrupt.
I have tested on my side with same command as you, and after decompressing the backup, file “xtrabackup_checkpoints” does exist on my backup folder. Can you double check if you had enough disk space to decompress the backup? Otherwise the untar command might have executed partially. Also, can you double check that path “/backups/xtrabackup-data/backup/12122020/” is indeed the correct path of the uncompressed backup ?

What preparing does is applying redo log and rolling back unfinished transactions at the time the backup was taken, which is essentially doing crash recovery. It’s suggested to prepare the backup before restoring , but otherwise crash recovery would be run at startup time after restoring the unprepared backup.

Do note that you are using --export flag, which means that you are trying to import individual tables: The innobackupex Option Reference
If you restore a partial backup, then table dictionary + only included schemas/tables will be restored, and since table dictionary was overwriten, any other table/schema that was not restored will be lost. I.e you cannot merge a partial backup with an existing instance this way. If you wish to restore a partial backup, you should import 1 tablespace at a time as described above and as shown in Restoring Individual Tables

Last, innobackupex program is deprecated. Please switch to xtrabackup to avoid any unexpected behavior.

[Churchill]

Thanks for the very informative response @CTutte. I do have a lot of disk space, so I believe that everything has been extracted. I’ve also checked /backups/xtrabackup-data/backup/12122020/ and it’s the correct path of the uncompressed backup. Unfortunately, due to urgency, I had to find a different approach, and the instructions in Restoring Individual Tables allowed me to restore my backup without the need to prepare. Do you think this is the best method to restore my partial backups?

[CTutte]

Hi again,

if you expect to merge a partial bakcup with more data, you need to either

• restore the partial backup first, then do a logical restore of the rest of the data
• having the rest of the data and restore your physical backup (pxb) and/or some of the tables only, then you need to import tablespace one at a time

Due to data dictionary handled internally by MySQL, it’s not possible to merge two (or more) physical backups and/or existing instance with an existing backup. Keeping this in mind you should plan ahead the restore procedure and script table importing, and/or reconsider going with full backups instead of partial ones.

Regards

[matthewb]

Just wanted to chime in that you should stop using innobackupex and switch to xtrabackup directly. Most commands just map back to xtrabackup anyways. Our documentation is out of date here but we do recommend this.