Tuesday 21 March 2017

Safely purging / pruning old mysql binary logs

While binary logging provides an excellent way to roll back a database to a specific point in time, to recover the database in the event of corruption or for use with replication, by default MySQL binary logs are not purged and over time can accumulate using a huge amount of disk space.

Binary logs (as the name suggests) store data in binary format and are consumed by mysqlbinlog.

In order to prune the logs lets firstly locate the appropraite directory -- in my case this is /var/lib/mysql

cd /var/lib/mysql
ls

-rw-rw----  1 mysql mysql   104871967 Jan 01 00:00 BINLOG.000001
-rw-rw----  1 mysql mysql   104885618 Jan 02 00:00 BINLOG.000002
-rw-rw----  1 mysql mysql   104866713 Jan 03 00:00 BINLOG.000003


We can then either purge by file:

mysql -u user -p
PURGE BINARY LOGS TO 'BINLOG.000002';

or date:

PURGE BINARY LOGS BEFORE '2017-01-02 00:00:00';

*Warning* Simply deleting the files is extremely dangerous - since the logs do not get removed from the index!

0 comments:

Post a Comment