Xtrabackup flushing binary logs while still locked

MySQL & MariaDB Percona XtraBackup
1

I’m just trying to ascertain if I have a Bug or not with xtrabackup, or if it is intended to work this way.

I am using:
MySQL (Oracle): 8.0.39
xtrabackup version: 8.0.35-31

When xtrabackup runs it runs using the --lock-ddl option which runs lock instance for backup.

At the end of the process it runs FLUSH NO_WRITE_TO_BINLOG BINARY LOGS

Then afterwards it runs UNLOCK INSTANCE

From the logs:
2025-04-04T05:00:01.560946+01:00 0 [Note] [MY-011825] [Xtrabackup] Executing LOCK INSTANCE FOR BACKUP ... 2025-04-04T05:21:10.247239+01:00 0 [Note] [MY-011825] [Xtrabackup] Executing FLUSH NO_WRITE_TO_BINLOG BINARY LOGS 2025-04-04T05:21:10.911779+01:00 0 [Note] [MY-011825] [Xtrabackup] Executing UNLOCK INSTANCE 2025-04-04T05:21:10.912107+01:00 0 [Note] [MY-011825] [Xtrabackup] Executing UNLOCK TABLES 2025-04-04T05:21:10.912295+01:00 0 [Note] [MY-011825] [Xtrabackup] All tables unlocked

The problem is that this generates an error:
2025-04-04T05:21:10.459945+01:00 testdb1 mysqld[708]: 2025-04-04T04:21:10.458949Z 185315 [Warning] [MY-014054] [Server] Could not purge binary logs since another session is executing LOCK INSTANCE FOR BACKUP. Wait for that session to release the lock.

On some of my smaller databases I have found that this causes a problem as the binary logs never become large enough to be rotated (and purged) due to size, so each day we get a new binary log created, but the old ones don’t get removed, until eventually we will run out of space.

Obviously I can fix this by reducing the max size of the binary logs or simply calling the flush command manually after xtrabackup has run. But it seems a bit strange, and was wondering if this was intended to happen or not?

2

Can you please provide the configuration to purge binary logs? Also the size of binary logs currently in the server?

Do you use https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html#sysvar_binlog_expire_logs_seconds ?

3

I have the following set up:

binlog_expire_logs_seconds : 259200
expire_logs_days : 0
max_binlog_size : 104857600

As mentioned, in these smaller databases, the binary logs never reach the max_binlog_size

It just seemed strange that xtrabackup would try and flush the binary logs, even though it held a lock on the database. You would assume it would unlock the database, then try and flush the binary logs, unless there is a good reason for doing it that way?

4

By doing this, you create a consistent point in time related to committed transactions. If you unlock, then flush, the binlog position is no longer consistent to the backup.

5

OK cheers, so its a feature, not a Bug, and the error is just an unfortunate side affect.

I added a command to run “flush binary logs” to the end of the script now, so that it should run without error after xtrabackup has finished.