About the new warnings generated during 'PURGE BINARY LOGS' execution

In this blog, I will discuss about warnings that users might face if they are trying to purge active/in_use binary log which are introduced in MySQL-5.6.12/MySQL-5.7.2 releases.  Before jumping directly to that, I will be covering few basic things about PURGE BINARY LOGS command, about safe way to purge the binary logs on a replication forum. If you are good at basics on "PURGE BINARY LOGS" , you can choose to directly move to the section (at the end of the blog) where I was talking about these new warnings.

Introduction:
==========
A binary log file contains “events” that describe database changes occurred on a MySQL Server (typically called Master) that enabled binary logging using --log-bin. On a Master server, the files that are related to binary logging are binary log files (files with binlog_base_name.6_digit serial_number) and a binary log index file (file with binlog_index_base_name.index name) which contains the names of all available binary log files.

A binary log file is rotated when its size reaches the binary log limit (that can be specified using max_binlog_size) or when a user (with RELOAD privileges) issues  FLUSH LOGS command.  If you want to see the list of all the available binary log files on the server, you could use 

"SHOW [BINARY | MASTER]  LOGS"

These files can get accumulated over the period of time and will occupy good amount of disk space.
You can delete all binary log files with the RESET MASTER statement, or a subset of them with PURGE BINARY LOGS manually or automatically by setting expire_logs_days system variable.  

PURGE BINARY LOGS syntax:
=========================

PURGE { BINARY | MASTER } LOGS

{ TO 'log_name' | BEFORE datetime_expr }

This statement deletes all the binary log files listed in the log index file prior to the specified log file name or date. Deleted log files are also removed from the list recorded in the index file, so that the given log file becomes the first in the list.

Screen shot for PURGE BINARY LOGS TO 'log_name' example

=================================================

When you are purging binary logs, you have to make sure that no slaves will require them. To safely purge binary log files that are not required any more, follow this procedure:

1. On each slave server, check Master_Log_File value in SHOW SLAVE STATUS output to check which master log file it is reading 
2. Obtain a listing of the binary log files on the master server with SHOW BINARY LOGS.
3. Determine 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 the log files you are about to delete. (This step is optional, but always advisable.)
5. Purge all log files up to but not including the target file. Lets say the target file from the step.3 is binlog.000005. You can execute PURGE BINARY LOGS binlog.000005 which will purge all binary logs prior to binlog.000005 but not binlog.000005.

PURGE BINARY LOGS BEFORE datatime_expr:
======================================
If you want to purge all the binary log files before a given datatime, you can use

PURGE BINARY LOGS BEFORE datatime_expr

The BEFORE variant's datetime_expr argument should evaluate to a DATETIME value (a value in 'YYYY-MM-DD hh:mm:ss' format). Lets say we have 4 binary log files. Two of them last modified on July 3rd and two of them are last modified on July 4th

and you want to delete all the files that are modified before July 4th 00.00AM, then the following command can be used

PURGE BINARY LOGS BEFORE '2015-07-04 22:46:26'

Screen shot for PURGE BINARY LOGS BEFORE datetime_expr
=================================================

Purging binary logs using expire_logs_days system variable:
=============================================
You can also set the expire_logs_days system variable to expire binary log files automatically after a given number of days. This variable can be specified at the time of start up using command line parameters or by mentioning in conf file (my.cnf) or using 'SET GLOBAL expire_logs_days=#' command. You should set the variable no lower than the maximum number of days your slaves might lag behind the master. The permitted value range for this variable is between 0 to 99 , 0 being the default for it. The value '0' indicates that there is no automatic purging enabled. Expired binary log files purged either at the time of server start up or when the binary log is rotated.

ER_WARN_PURGE_LOG_IN_USE/ER_WARN_PURGE_LOG_IS_ACTIVE Warnings:
==================================================================
Purging is safe to do while slaves are replicating. You need not stop them.
If you have an active slave's dump thread or active user session that is currently reading one of the log files you are trying to delete, this statement does not delete that file. Instead it will generate a warning. This could be one of the reasons for users complaining about still seeing binary log files even after executing 'PURGE BINARY LOGS' command. Before 5.6.12 (or 5.7.2), the command was silently stopped when it finds an active/in use binary log.  But in the later versions, user will see a warning (ER_WARN_PURGE_LOG_IN_USE) if you are trying to purge a binary log that is in use (either a show binary log events on it or a dump thread reading it) and user will see a warning (ER_WARN_PURGE_LOG_IS_ACTIVE) if you are trying to purge the active binary log
where the current ongoing transactions are going into.

Screenshot to show ER_WARN_PURGE_LOG_IN_USE(1867) Warning:
=======================================================

Client1 is reading events from 'master-bin.000004'

Client2 is trying to execute purge binary logs to 'master-bin.000007'

As you can see above, PURGE BINARY LOGS command found that a file (master-bin.000004) is being read by a thread. Hence it did not purge that file and any file that follows in the list of files to be purged and throws a warning ER_WARN_PURGE_LOG_IN_USE(1867) with all possible information

 Screenshot to show ER_WARN_PURGE_LOG_IS_ACTIVE  Warning:
==========================================================

As you can see above, PURGE BINARY LOGS command found that one of the binary log files you are trying to delete is active binary log and it did not purge that file and throws a warning ER_WARN_PURGE_LOG_IS_ACTIVE (1868) with all possible information


Summary:
This blog post provided few basics things about PURGE BINARY LOGS command , safe way to purge the binary logs on a replication forum and at the end I also spoke about warnings (ER_WARN_PURGE_LOG_IN_USE/ER_WARN_PURGE_LOG_IS_ACTIVE) that users might face if they are trying to purge active/in_use binary log which are introduced in MySQL-5.6.12/MySQL-5.7.2 releases. As always, Thank you for using MySQL. If you find any issues, please feel free to create bugs @ bugs.mysql.com.