XtraBackup getting failed while apply-log run with error: Cannot open ./xtrabackup_checkpoints

MySQL & MariaDB Percona XtraBackup
1

I am running a shell script for taking backup of the database using Percona’s XtraBackup tool, the backup part is getting completed successfully but the apply-log part is getting failed with an error.

Below is the part of the script for the backup and apply-log:

# Set the current date
CURRENT_DATE=$(date +%Y-%m-%d)

# Set the backup file name
BACKUP_FILE_NAME=$MYSQL_DATABASE-$CURRENT_DATE.tar.gz

# Set the full path for the backup file
BACKUP_FILE=$BACKUP_DIRECTORY/$BACKUP_FILE_NAME

# Take the database backup using Percona XtraBackup
innobackupex --user=$MYSQL_USERNAME --password=$MYSQL_PASSWORD --port=$MYSQL_PORT --host=$MYSQL_HOSTNAME $BACKUP_DIRECTORY

# Apply the log to the backup to make it ready for restore
innobackupex --apply-log $BACKUP_DIRECTORY

# Compress the backup file
tar -czf $BACKUP_FILE $BACKUP_DIRECTORY

Below is the error after the backup was completed and apply-log started:

xtrabackup: Transaction log of lsn (40690253702547) to (40690371465719) was copied.
230111 15:43:14 completed OK!
230111 15:43:14 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.3.5 based on MySQL server 5.6.24 Linux (x86_64) (revision id: 45cda89)
xtrabackup: cd to /data/backup
xtrabackup: Error: cannot open ./xtrabackup_checkpoints
xtrabackup: error: xtrabackup_read_metadata()
xtrabackup: This target seems not to have correct metadata...
2023-01-11 15:43:14 7f50338aa740  InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
xtrabackup: Warning: cannot open ./xtrabackup_logfile. will try to find.
2023-01-11 15:43:14 7f50338aa740  InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
  xtrabackup: Fatal error: cannot find ./xtrabackup_logfile.
xtrabackup: Error: xtrabackup_init_temp_log() failed.
tar: Removing leading `/' from member names
tar: /data/backup: file changed as we read it.

From the above error, I understand that the error is because it cannot find the file specified because that is present inside the compressed file which is $MYSQL_DATABASE-$CURRENT_DATE.tar.gz but we need to take the backup using compressed format to save on disk space.

Can someone please assist me here in resolving this issue?

2

Please confirm that you are running MySQL 5.6. If so, that is dead software, as is innobackupex. You need to work on upgrading to MySQL 8.0 and xtrabackup 8.0.

1 Like
3

Thank you for your comment Matthew, yes I am using Percona MySQL 5.6. We have planned for upgrading it to 5.7 soon. However, there is an another instance of Percona MySQL which has the backup running and it’s fine with the same innobackupex and MySQL versions.

It would be great if you could just guide me to fix the above error keeping the version aside because I think the error has to do something with the file structure(since it’s compressed) and that’s why it cannot open/find the ./xtrabackup_checkpoints file. But if I extract the .tar.gz file, I can see the same file inside the backup directory.

1 Like
4

Your backup script needs to do a better job of checking if the previous command failed or not.

innobackupex --user=$MYSQL_USERNAME --password=$MYSQL_PASSWORD --port=$MYSQL_PORT --host=$MYSQL_HOSTNAME $BACKUP_DIRECTORY

if [ $? -ne 0 ]; then
 echo "Backup failed"
 exit 1
fi

# Apply the log to the backup to make it ready for restore
innobackupex --apply-log $BACKUP_DIRECTORY
if [ $? -ne 0 ]; then
 echo "Apply log failed"
 exit 1
fi

The tar should not run if the apply fails.

As a test, remove the tar component from your script. Does the backup work when you do this?

For another test, remove the --apply-log stage from your script and run. Do you see the xtrabackup_checkpoints file inside $BACKUP_DIRECTORY ?

1 Like
5

Hi @MahendraB

xtrabackup requires the file uncompressed in order to run a --preprare --apply-log-only. So you have two options here:

  1. Do not take the backup compressed. Run --apply-log-only and then manually compress it
  2. Take a compressed backup but do not run --apply-log-only . Leave this part to restore.
1 Like
6

Here is what I did with the script, I commented out the following parameters:
BACKUP_FILE_NAME
BACKUP_FILE
tar -czf $BACKUP_FILE $BACKUP_DIRECTORY

Since the innobackupex command creates a new directory with the current date and timestamp in the format YYYY-MM-DD_HH-MM-SS within the specified backup directory which is what is happening now. The backup was getting generated in the /data/backup directory with the current timestamp however, the apply-log was still referring to the $BACKUP_DIRECTORY which is incorrect because the files : xtrabackup_logfile and xtrabackup_checkpoints would be inside the newly created directory(For eg. : /data/backup/2023-01-20_13-55-45) and the apply-log would look for them under /data/backup. So I added the following in the script:

LATEST_BACKUP_DIR=$(ls -t $BACKUP_DIRECTORY | head -1)

innobackupex --apply-log $BACKUP_DIRECTORY/$LATEST_BACKUP_DIR

This command uses the “ls -t” command to list all the directories in the backup directory sorted by modification time, and the “head -1” command to get the latest directory.

Now, with this changes my backup is getting completed as well as the apply-log is working properly.

Thank you for all your help @matthewb.

7

Hi @Marcelo_Altmann,

With few changes in the script that I have mentioned in the comment to @matthewb , I used the 1st approach but have not checked the compression part. The changes that I did to my script worked with apply-log properly. That was the logic which was missing earlier in my script.

Thank you for all your help @Marcelo_Altmann

1 Like