Trimming MySQL bin logs

During MySQL replication, changes on the Master are written to binary logs for the Slave(s) to update. By default these binary logs will continue to grow indefinitely unless you apply a specific setting.

In this scenario I have chosen to keep 4 days worth of bin logs. The amount of bin logs is dependant on how much lag you have between the master and the slaves. You must ensure that your slaves are synced before the logs are trimmed.

First of all lets see how many binary logs we have:

| Log_name          | File_size  |
| master-bin.000002 |  113051204 |
| master-bin.000003 |  917274240 |
| master-bin.000004 |  966086802 |
| master-bin.000005 | 1073758543 |
| master-bin.000006 |   23100970 |
| master-bin.000007 |  986488928 |
| master-bin.000008 |  288390826 |


We want to set the expire_logs_days variable to ensure that the bin logs are purged regularly. By default this is set to 0 which means the bin logs will never expire.

mysql> SET GLOBAL expire_logs_days=4;


This will set MySQL to trim the bin logs after 4 days. You'll also need to insert the setting in my.cnf, incase MySQL restarts




This will trim the logs. However they will only be trimmed if the MySQL server is restarted or the logs are flushed. You could write a script or manually FLUSH LOGS but that is inconvenient. By default the logs will flushed when the binlogs reach 1GB. You can set this a bit lower if you want by adding the following line to my.cnf


The above attribute sets the maximum binary log file to 500MB. MySQL will need to be restarted for this setting to take affect.


Now check your bin logs tomorrow (or in a few days when they reach maximum size) and they should have trimmed down. Alternatively, if you want an instant trim then the following example commands could be used :

mysql> PURGE BINARY LOGS TO 'master-bin.000005';


mysql> PURGE BINARY LOGS BEFORE '2013-10-01'