MY-014054 warnings logged during backup

MySQL & MariaDB Percona XtraBackup
1

Hi all,

I’m running MySQL 8.0.35 on RHEL 8.8 and am using Xtrabackup 8.0.34-29.

When I run a backup using the following command:

xtrabackup --backup --target-dir=${BACKUPDIR} --compress

I get the following warnings dumped to /var/log/mysqld.log and performance_schema.error_log:

2024-05-08T20:01:48.333785+10:00 7 [Warning] [MY-014054] [Repl] Could not purge binary logs since another session is executing LOCK INSTANCE FOR BACKUP. Wait for that session to release the lock.
2024-05-08T20:01:48.333980+10:00 7 [Warning] [MY-014054] [Repl] Could not purge binary logs since another session is executing LOCK INSTANCE FOR BACKUP. Wait for that session to release the lock.

It looks like the backup is triggering a binary log switch which then triggers a purge which is failing. Looking at the date stamps on the binary logs, I see this happens during every backup (daily @ 8pm) and the file date stamp matches the error_log messages:

-rw-r-----. 1 mysql mysql 501M May 7 17:00 /var/lib/mysql/mysql-bin.005354
-rw-r-----. 1 mysql mysql 367M May 7 20:01 /var/lib/mysql/mysql-bin.005355
-rw-r-----. 1 mysql mysql 501M May 7 22:09 /var/lib/mysql/mysql-bin.005356
-rw-r-----. 1 mysql mysql 501M May 8 03:58 /var/lib/mysql/mysql-bin.005357
-rw-r-----. 1 mysql mysql 501M May 8 07:49 /var/lib/mysql/mysql-bin.005358
-rw-r-----. 1 mysql mysql 501M May 8 09:08 /var/lib/mysql/mysql-bin.005359
-rw-r-----. 1 mysql mysql 501M May 8 10:32 /var/lib/mysql/mysql-bin.005360
-rw-r-----. 1 mysql mysql 501M May 8 11:53 /var/lib/mysql/mysql-bin.005361
-rw-r-----. 1 mysql mysql 501M May 8 13:12 /var/lib/mysql/mysql-bin.005362
-rw-r-----. 1 mysql mysql 501M May 8 14:46 /var/lib/mysql/mysql-bin.005363
-rw-r-----. 1 mysql mysql 501M May 8 16:34 /var/lib/mysql/mysql-bin.005364
-rw-r-----. 1 mysql mysql 418M May 8 20:01 /var/lib/mysql/mysql-bin.005365
-rw-r-----. 1 mysql mysql 501M May 8 23:25 /var/lib/mysql/mysql-bin.005366
-rw-r-----. 1 mysql mysql 501M May 9 06:33 /var/lib/mysql/mysql-bin.005367

Checking the backup log file, there doesn’t seem to be any indication of an issue or a log switch.

This occurs on all of our MySQL servers (4 production and 7 non-production).

Is there any way to prevent these warnings?

1 Like
3

Thanks David.

Are you saying that another session, apart from the backup job, holds a LOCK INSTANCE FOR BACKUP?

1 Like
5

Sorry, but I don’t think that’s the case. We are definitely running only one backup process at a time.

Also, I tested this on a test server just now, with no other sessions connected. The only session is my ssh connection which runs the backup.

If I run the same backup command on this server I get the same ‘MY-014054’ warning logged.

1 Like
6

Hey @kevinUU,
What you do you have set for the following parameters?

binlog_expire_logs_seconds
expire_logs_days
max_binlog_size

What I suspect is happening is that MySQL is trying to rotate/purge your binary logs due to a variable configuration, but it cannot do that because PXB is currently doing a backup. I would suggest increasing either the expiration time, or max size and try the backup again.

7

Thanks @matthewb

binlog_expire_logs_seconds=259200
expire_logs_days=0
max_binlog_size=524288000

However, would MySQL rotate the logs exactly at the time the backup runs every night at 8pm?

Also, you can see in my first post that the size of the binlogs that are rotated when the backup starts are smaller than the others. This says to me that even though the max_binlog_size hasn’t been reached, the backup forces a rotate?

8

That’s correct, PXB does FLUSH BINARY LOGS to rotate to the next log.

9

So the backup flushes the binary logs which initiates a purge which fails because the instance has been locked for backup?

10

PXB does not purge any binlogs, so MySQL is attempting to do that. I see your expire time is only 3 days, and your original post shows 3 day old binlogs. Try increasing the expire time, or do a manual ‘PURGE BINARY LOGS TO ‘xxxx’’ before you do the backup and see if that resolves the issue.

1 Like