How to remove / purge / prune old mysql binary logs (safely)

I generally setup binary logs on a MySQL server where the data isn’t being replicated and I’m using daily mysql-dumps to backup the data. This allows me to recover the database to a specific point in time. However sometimes the binary logs can become very large if there are lots of changes occuring to the data. Then you can suddenly need to delete / purge / prune the mysql binary log files. Here is how I did it:

1 – Examine the binary logs to decide up to where you want to delete to:

ls -la /var/lib/mysql/

2 – Here the traffic to one server had suddenly increased and the server had been logging 101MB every few minutes for several days.

....
-rw-rw----  1 mysql mysql   104871967 Aug 24 00:01 BINLOG.015687
-rw-rw----  1 mysql mysql   104885618 Aug 24 00:04 BINLOG.015688
-rw-rw----  1 mysql mysql   104866713 Aug 24 00:06 BINLOG.015689
...

3 – You can either prune by date or by file. For simplicity, I pruned by file. You need to do this from the MySQL command line.
I connected to mysql and ran the following command:

PURGE BINARY LOGS TO 'BINLOG.015689';

But you could prune by date:

PURGE BINARY LOGS BEFORE '2013-08-24 00:06:00';

You can read the offical mysql documentation here.

Leave a Reply