In 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:
1 2 | mysql> PURGE BINARY LOGS TO 'mysql-bin.000010'; mysql> PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26'; |
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):
1 2 | mysql> set global expire_logs_days=3; Query OK, 0 rows affected (0.00 sec) |
And on my.cnf
:
1 | expire-logs-days = 3 |
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:
1 | Binlogs disk usage = max_binlog_size * max_binlog_files |
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:
1 2 | mysql> set global max_binlog_files = 10; Query OK, 0 rows affected (0.00 sec) |
And on my.cnf under [mysqld]
section:
1 2 | [mysqld] max_binlog_files = 20 |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | mysql > show slave statusG *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 127.0.0.1 Master_User: rsandbox Master_Port: 45007 Connect_Retry: 60 Master_Log_File: mysql-bin.000024 Read_Master_Log_Pos: 194 Relay_Log_File: mysql-relay.000013 Relay_Log_Pos: 24996028 Relay_Master_Log_File: mysql-bin.000006 <strong>Slave_IO_Running: Yes Slave_SQL_Running: Yes ... </strong>Exec_Master_Log_Pos: 24995815 |
And to briefly summarize other important parameters and its meanings:
- Master_Log_File/Read_Master_Lo
g_Pos – This is what the Slave_IO is currently fetching from the master - Relay_Master_Log_File/Exec_Mas
ter_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:
1 2 3 4 | $ mysqlbinlogpurge --master=root:msandbox@localhost:45007 > --slaves=root:msandbox@localhost:45008,root:msandbox@localhost:45009 > --dry-run ERROR: Can not verify the status for slave localhost:45008. Make sure the slave are active and accessible. |
If you want to check what the tool is going to perform before executing, you can use the --dry-run
option:
1 2 3 4 | $ mysqlbinlogpurge --master=root:msandbox@localhost:45007 --slaves=root:msandbox@localhost:45008,root:msandbox@localhost:45009 --dry-run # Latest binlog file replicated by all slaves: mysql-bin.000011 # To manually purge purge the binary logs Execute the following query: PURGE BINARY LOGS TO 'mysql-bin.000012' |
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):
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | $ mysqlbinlogpurge --master=root:msandbox@localhost:45007 > --slaves=root:msandbox@localhost:45008,root:msandbox@localhost:45009 > --dry-run > --binlog=mysql-bin.000002 -v # Checking user permission to purge binary logs... # # Master active binlog file: mysql-bin.000012 # Checking slave: localhost@45008 # I/O thread is currently reading: mysql-bin.000012 # Checking slave: localhost@45009 # I/O thread is currently reading: mysql-bin.000012 # Range of binlog files available: from mysql-bin.000001 to mysql-bin.000012 # Latest binlog file replicated by all slaves: mysql-bin.000011 # To manually purge purge the binary logs Execute the following query: PURGE BINARY LOGS TO 'mysql-bin.000002' # Range of binlog files available: from mysql-bin.000001 to mysql-bin.000012 |
To effectively remove the binary logs, it is just necessary to remove the --dry-run
option:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | $ ls -larth data/ | grep -i mysql-bin -rw-r----- 1 vinicius.grippa vinicius.grippa 99M Feb 2 10:20 mysql-bin.000001 -rw-r----- 1 vinicius.grippa vinicius.grippa 201 Feb 2 10:20 mysql-bin.000002 -rw-r----- 1 vinicius.grippa vinicius.grippa 201 Feb 2 10:20 mysql-bin.000003 -rw-r----- 1 vinicius.grippa vinicius.grippa 50M Feb 2 10:28 mysql-bin.000004 -rw-r----- 1 vinicius.grippa vinicius.grippa 201 Feb 2 10:28 mysql-bin.000005 -rw-r----- 1 vinicius.grippa vinicius.grippa 201 Feb 2 10:28 mysql-bin.000006 -rw-r----- 1 vinicius.grippa vinicius.grippa 201 Feb 2 10:28 mysql-bin.000007 -rw-r----- 1 vinicius.grippa vinicius.grippa 201 Feb 2 10:28 mysql-bin.000008 -rw-r----- 1 vinicius.grippa vinicius.grippa 201 Feb 2 10:28 mysql-bin.000009 -rw-r----- 1 vinicius.grippa vinicius.grippa 201 Feb 2 10:28 mysql-bin.000010 -rw-r----- 1 vinicius.grippa vinicius.grippa 228 Feb 2 10:28 mysql-bin.index -rw-r----- 1 vinicius.grippa vinicius.grippa 201 Feb 2 10:28 mysql-bin.000011 -rw-r----- 1 vinicius.grippa vinicius.grippa 739M Feb 2 10:32 mysql-bin.000012 $ mysqlbinlogpurge --master=root:msandbox@localhost:45007 > --slaves=root:msandbox@localhost:45008,root:msandbox@localhost:45009 > -v # Checking user permission to purge binary logs... # # Master active binlog file: mysql-bin.000012 # Checking slave: localhost@45008 # I/O thread is currently reading: mysql-bin.000012 # Checking slave: localhost@45009 # I/O thread is currently reading: mysql-bin.000012 # Range of binlog files available: from mysql-bin.000001 to mysql-bin.000012 # Latest binlog file replicated by all slaves: mysql-bin.000011 # Latest not active binlog file: mysql-bin.000011 # Purging binary logs prior to 'mysql-bin.000012' # Binlog file available: mysql-bin.000012 # Range of binlog files purged: from mysql-bin.000001 to mysql-bin.000011 $ ls -larth data/ | grep -i mysql-bin -rw-r----- 1 vinicius.grippa vinicius.grippa 739M Feb 2 10:32 mysql-bin.000012 -rw-r----- 1 vinicius.grippa vinicius.grippa 19 Feb 2 10:44 mysql-bin.index |
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:
1 2 3 | $ mysqlbinlogpurge --master=root:msandbox@localhost:45008 --slaves=root:msandbox@localhost:45009 --dry-run # Latest binlog file replicated by all slaves: mysql-bin.000000 # No binlog files can be purged. |
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).