Purge Binary Logs

If you have enabled binary logging for the point-in-time recovery (or using replication in your environment) option and forgot to purge it then it may eat up memory in no time, so to “purge” binary logs from production server follow the steps(depending upon your environment)

Simple Environment (No replication etc)

(1) List the current binary logs using “show master logs”/ “show binary logs” SQL command

mysql> SHOW BINARY LOGS;
+---------------+-----------+
Log_name File_size
+---------------+-----------+
xyz-bin.000015 724935
xyz-bin.000016 733481


xyz-bin.000020 733481
+---------------+-----------+

(2) Run PURGE BINARY LOGS statement deletes all the binary log files up to listed in the log index file prior to the specified log file name xyz-bin.000019 (Can also be done for date)

mysql>PURGE BINARY LOGS TO ‘xyz-bin.000019’;

(3) Confirm whether or not files have been deleted

mysql> SHOW BINARY LOGS;
+---------------+-----------+
Log_name File_size
+---------------+-----------+
xyz-bin.000019 724935
xyz-bin.000020 733481
+---------------+-----------+


Complex Environment (Active/dormant Slaves)

If you have an active slave that currently is reading one of the logs you are trying to delete, PURGE BINARY LOGS statement does nothing and fails with an error. However, if a slave is dormant and you happen to purge one of the logs it has yet to read, the slave will be unable to replicate after it comes up.

To safely purge logs, follow this procedure:

1. Suppose we have only one slave, use SHOW SLAVE STATUS to check which log it is reading.

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: root
Master_Port: 3306
Connect_Retry: 3
Master_Log_File: xyz-bin.000020
Read_Master_Log_Pos: 79
Relay_Log_File: abc-relay-bin.009
Relay_Log_Pos: 548
Relay_Master_Log_File: xyz-bin.000020

2. Obtain a listing of the binary logs on the master server with SHOW BINARY LOGS

mysql> SHOW BINARY LOGS;
+---------------+-----------+
Log_name File_size
+---------------+-----------+
xyz-bin.000015 724935
xyz-bin.000016 733481

xyz-bin.000020 733481
+---------------+-----------+

3. Determine the earliest log among all the slaves. This is the target log xyz-bin.000019. If all the slaves are up to date, this is the last log on the list.
4. Make a backup of all the logs you are about to delete. (This step is optional, but always advisable.)
5. Purge all logs up to but not including the target log.
Run PURGE BINARY LOGS statement deletes all the binary log files up to listed in the log index file prior to the specified log file name xyz-bin.000019

mysql>PURGE BINARY LOGS TO ‘xyz-bin.000019’;

Automatically purge binary logs:

You can also set the expire_logs_days system variable to expire binary log files automatically after a given number of days. If you are using replication, you should set the variable no lower than the maximum number of days your slaves might lag behind the master.