Purging binary logs from MySQL Master safely

In this post we will discus about the different ways we can purge binary logs safely in MySQL, We recommend you to confirm before purging the binary logs from the master, all logs were applied to the slaves to avoid halting them. The following error is usual when binary log is purged before being applied on 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’

How can we safely purge MySQL binary log files ? 

1. On each slave server, use SHOW SLAVE STATUS to check which log file it is reading.
2. Get the binary log files details on the master with SHOW BINARY LOGS.
3. Check for the earliest log file among all the slaves, This is the target file. If all the slaves are up to date, this is the last log file on the list.
4. Make a backup of all log files you are about to delete (We insist this step to our customers to avoid human errors)
5. Purge all log file up to but please do not include the target file.

The default binary log expiration is 30 days, You can get this details from MySQL system variable binlog_expire_logs_seconds . When using MySQL replication, You should specify an expiration period that no lower than maximum time your slaves might lag behind the master.

mysql> select @@binlog_expire_logs_seconds;
+------------------------------+
| @@binlog_expire_logs_seconds |
+------------------------------+
|                      2592000 |
+------------------------------+
1 row in set (0.00 sec)

List binary log files  in MySQL

mysql> SHOW BINARY LOGS;
+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| binlog.000001 |       657 |
| binlog.000002 |       178 |
| binlog.000003 |       178 |
| binlog.000004 |       178 |
| binlog.000005 |       178 |
| binlog.000006 |      3354 |
| binlog.000007 |       199 |
| binlog.000008 |       155 |
+---------------+-----------+
8 rows in set (0.00 sec)

mysql> 

** Both “SHOW BINARY LOGS” and “SHOW MASTER LOGS” are synonyms. **

Purging MySQL binary log files  using PURGE BINARY LOGS command 

The PURGE BINARY LOGS command deletes all the binary log files listed in the log index file prior to the specified log file name or date .

mysql> PURGE BINARY LOGS TO 'binlog.000001';
Query OK, 0 rows affected (0.01 sec)

mysql> PURGE BINARY LOGS BEFORE '2018-05-11 20:20:20';
Query OK, 0 rows affected, 1 warning (0.01 sec)

** ABOVE STATEMENTS HAS NO EFFECT IF THE MYSQL SERVER WAS NOT STARTED WITH –log-bin OPTION ENABLED

What you should be careful about before purging MySQL binary log files ? 

There are several ways to purge MySQL binary log files but none guarantee slave has already applied the binary log transactions and can be safely removed (Now, that’s the reason we have the title for this topic in red color ) , This is when “mysqlbinlogpurge” tool comes really helpful. The “mysqlbinlogpurge” tool is a part of MySQL utilities, You can download it from here . This tools ensures that any binary log files that in use or required by any of the slave in replication ecosystem will not be deleted.

How “mysqlbinlogpurge” finds when it is safe to purge binary log files ?

A MySQL slave has two entities to complete the replication successfully: the Slave_IO thread is accountable for collecting the events successfully from the master, While Slave_SQL threads(s) is responsible for executing the events locally. To monitor Slave IO / Slave SQL is running successfully and where they are at in their process, you have to run the command ” SHOW SLAVE STATUS ”

mysql > show slave statusG
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.56.3
Master_User: India
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000047
Read_Master_Log_Pos: 374
Relay_Log_File: mysql-relay.000713
Relay_Log_Pos: 45793012
Relay_Master_Log_File: mysql-bin.000038
<strong>Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...
</strong>Exec_Master_Log_Pos: 45792781

The following are important matrices / parameters and their descriptions:

Master_Log_File / Read_Master_Log_Pos – This is what Slave_IO is currently fetching from the master.

Relay_Master_Log_File / Exec_Master_Log_Pos – This is what Slave_SQL thread is currently executing in terms of the Masters coordinates (master’s log file).

Relay_Log_File / Relay_Log_Pos – This is what the SQL thread is currently executing in terms of the Slave’s coordinates (relay log file)

The Master_Log_File is the latest binlog file on MySQL master that Slave_IO reads from and it is this file, and any files after this on the Master server, that we must preserve for successful completion of replication. The Relay_Log_File is the point of execution in MySQL Master’s binlog that Slave_SQL thread has successfully executed.

How to use “mysqlbinlogpurge” for purging MySQL binary log files ? 

We recommend our customers to use –dry-run option with “mysqlbinlogpurge” to check what tool is going to deliver :

** if ever slave is stopped, “mysqlbinlogpurge” tool will report error and stop purging binary log files:

$ mysqlbinlogpurge --master=root:india@192.168.56.3:3306 
>           --slaves=root:singapore@192.168.56.4:3306,root:srilanka@192.168.56.5:3306 
>           --dry-run
ERROR: Can not verify the status for slave singapore@192.168.56.4:3306. Make sure the slave are active and accessible.

You can check what the “mysqlbinlogpurge” is going to perform before executing, you can use the –dry-run option:

$ mysqlbinlogpurge --master=root:root@192.168.56.3:3306 --slaves=root:root@192.168.56.4:3306,root:root@192.168.56.5:45009 --dry-run
# Latest binlog file replicated by all slaves: mysql-bin.000481
# To manually purge purge the binary logs Execute the following query:
PURGE BINARY LOGS TO 'mysql-bin.000482'

** To remove binary log file, you just need to remove the –dry-run option.

mysqlbinlogpurge can occasionally go wrong under following circumstances :

• Not reliable when used in Multi-Source replication ecosystem.
• If slave corrupts the local relay log, We need to restart replication from Relay_Master_log_file, and mysqlbinlogpurge might already have executed the purge of required binlog .

Summary 

We caution our customers who regularly purge their binary log files on being reasonably certain that relay logs won’t be corrupted . We don’t recommend mysqlbinlogpurge multi-source replication ecosystem.