Does xtrabackup flush the binlog files after successful backup

MySQL & MariaDB Percona XtraBackup
1

As the title states, I’m trying to find out what will cause the binlog files to clear after a backup. Does xtrabackup do this?

2

This is was actually xtrabackup does with binlogs

xtrabackup will use LOCK BINLOG FOR BACKUP to block all operations that might change either the binary log position or Exec_Master_Log_Pos or Exec_Gtid_Set (i.e. source binary log coordinates corresponding to the current SQL thread state on a replication replica) as reported by SHOW MASTER/SLAVE STATUS. xtrabackup will then finish copying the REDO log files and fetch the binary log coordinates. After this is completed xtrabackup will unlock the binary log and tables.

Finally, the binary log position will be printed to STDERR and xtrabackup exits, returning 0 if all went OK.

you can read full details about how percona xtrabackup works here How Percona XtraBackup Works - Percona XtraBackup

3

So is that a no? If not, how does the binlog files get cleaned up after a backup?

4

Hello John,

The answer is No. Xtrabackup is not going to clear binlogs.

(I’m going to assume by “cleanup” you meant “purge”.)

Binlog file rotation/ cleanup have no direct relation Xtrabackup; it does not produce them nor does it back-them-up. To rotate binary logs, you may use " expire_logs_days" (deprecated) or in new versions " binlog_expire_logs_seconds".

If you need to purge binary logs after (xtra)backup, you will have to do that manually.

Thanks,
K

5

So, is it ok to purge binlogs as long as all replicas are caught up to same position as source server? It won’t cause corrupt backups?

I come from a MsSql background, where the logs are transaction logs and you can’t delete/flush them until a successful backup. Does MySql not work like this?

6

Hi John,

So, is it ok to purge binlogs as long as all replicas are caught up to same position as source server? It won’t cause corrupt backups?

  • As long as replicas are caught-up, you may purge the binlogs on master.
  • It should not corrupt the backups.
  • BUT remember, you don’t want to purge the binlogs for a possible [the point in time recovery] (MySQL Point in Time Recovery the Right Way) requirement.

I come from a MsSql background, where the logs are transaction logs and you can’t delete/flush them until a successful backup. Does MySql not work like this?

The prime objective of binary logs in MySQL is Replication. Binary log records the DML/DDL changes /w timestamps.
Some example usage:

Thus they can also be used in point in time recovery, for eg: You have full backup of yesterday and binlogs until now you can restore it as:

  • restore a fullbackup
  • covert binlog to “SQL” and run those SQL changes against restored database to bring it back to last available state.

If you want to setup a replication then:

  • restore a full backup on “replica node”
  • issue “change source to” command, and MySQL will then read binlogs from master. Ofcourse you will need to mention the binlog co-ordinates to start the replication from noted in Xtrabackup.

Binlogs are not required for server functionality and MySQL can run with them disabled.
What you’re probably referring to are “redo logs” and “undo logs” and I’d not really touch them :slight_smile:

Again, we don’t really touch binary logs along with backup. It is better be managed by the configuration options (expire_logs_days / binlog_expire_logs_seconds); mysql will automatically purge the binary logs after that duration. Most common days I have seen is like a week, but again, that depends on disk size and RPO/RTO objectives.

Thanks,
K

7

Hi @johnwcarew ,

Let me clarify one thing. As part of the backup we do rotate the binlog by executing FLUSH NO_WRITE_TO_BINLOG BINARY LOGS this will essentially create a new binlog and every new DML after the backup will belong to this new log.

I hope it clarifies your question.