Purging Binary LogsIn this blog post, we’ll discuss some of the options available when purging binary logs. We’ll look at how to safely purge them when you have slaves in your topology and want to avoid deleting any binary log that still needs to be applied.

Safely Purging Binary Logs From Master

We generally want to ensure that, before purging the binary logs from the master, all logs were applied to the slaves to avoid halting them. The example error below is a classic case of a binary log purged before being applied on the slave:

Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: ‘Could not open log file’

MySQL offers some options to purge of binary logs. One of them is executing the PURGE BINARY LOGS command. The documentation describes this command and its options. Here is an example:

This will remove the binary logs and update the index file. Another option to purge binary logs is to use the expire_log_days variable. This variable defines the number of days for automatic binary log file removal. You can edit your my.cnf to make this persistent, and also change it dynamically (it is not necessary a restart to take effect):

And on my.cnf:

One alternative to control the number of binary log files introduced in Percona Server for MySQL 5.6.11-60.3 is the max_binlog_files parameter. When you set the variable max_binlog_files  to a non-zero value, the server removes the oldest binlog file(s) whenever their number exceeds the value of the variable. This is useful to limit the disk usage of the binlog files. Using this parameter limits the maximum disk usage to this theoretical value:

The size limit can be smaller because a server restart or FLUSH LOGS will make the server start a new log file and thus resulting in log files that are not fully written. The max_binlog_files  has a dynamic scope and you can change online using this command:

And on my.cnf under [mysqld] section:

However, using these options does not ensure that the slave is already applied the binary log transactions and can be safely removed. For this scenario, it comes in handy to use the mysqlbinlogpurge tool. The mysqlbinlogpurge tool is part of MySQL utilities, and you can download them here. This tool ensures that any files that are in use or required by any of the slaves in a replication topology are not deleted.

But how does mysqlbinlogpurge determine when it is safe to purge the binlogs? A slave in MySQL has two parts that make replication happen: the Slave_IO thread is responsible for gathering events from the master, while the Slave_SQL thread(s) is responsible for executing the events locally. You can see if the slave IO or slave SQL is running and where they are at in their processes by looking at a slave’s status:

And to briefly summarize other important parameters and its meanings:

  • Master_Log_File/Read_Master_Log_Pos – This is what the Slave_IO is currently fetching from the master
  • Relay_Master_Log_File/Exec_Master_Log_Pos – This is what the Slave_SQL thread is actively executing in terms of the Master’s coordinates (master’s log file)
  • Relay_Log_File/Relay_Log_Pos – This is what the SQL thread is actively executing in terms of the Slave’s coordinates (relay log file)

The Master_Log_File is the latest binlog file on the master server that the Slave_IO knows about and reads from. This is where it gathers the information from. Therefore it is this file, and any files after this on the Master server, that we must preserve for replication to continue. The Relay_Master_Log_File is the point of execution in the Master’s binlog that the Slave_SQL thread has executed.

Below is a few examples of how to use the tool and how it behaves in different scenarios. If the slave is stopped/halted, the tool throws an error and will not purge the binary logs:

If you want to check what the tool is going to perform before executing, you can use the --dry-run option:

If you don’t want to purge all binary logs, let’s say you want to keep the binlogs until the SQL thread is executing (Relay_Master_Log_File):

To effectively remove the binary logs, it is just necessary to remove the --dry-run option:

The tool has proven safe to run under the most general scenarios, like:

  • Master x Slave (1:1, 1:N) and with GTID on/off
  • Master x Master and with GTID on/off

Caveats

There are a few caveats using the mysqlbinlogpurge tool:

Multi-source replication. The tool does not work properly when the topology has Multi-Source replication enabled. The tool will run, but it will not get the binlog properly. Here is an example:

Relay log corrupted. If for some reason a slave corrupts its local relay log, you need to restart replication from the Relay_Master_Log_File, and the tool might already have executed the purge of the required binlog.

Summary

If you have space constraints and are reasonably certain that relay logs won’t be corrupted, the mysqlbinlogpurge tool provides a good way for purging binary logs. You might consider it as an option to keep the bin logs under control. It attends the most general topologies (except multi-source topologies).