• 2019-05-04 06:34:02

MySQL how to delete old binary logs

 

You should not just delete your MySQL old binary logs.

You need to let mysqld do that for you.

The file mysql-bin.[index] keeps a list of all binary logs mysqld has generated and auto-rotated. The mechanisms for cleaning out the binlogs in conjunction with mysql-bin.[index] are:

PURGE BINARY LOGS TO 'binlogname';

Or:

PURGE BINARY LOGS BEFORE 'datetimestamp';

These will clear all binary logs before the binlog or timestamp you just specified.

For example, if you run

PURGE BINARY LOGS TO `mysql-bin.000223`;

this will erase all binary logs before mysql-bin.000223.

If you run:

PURGE BINARY LOGS BEFORE DATE(NOW() - INTERVAL 3 DAY) + INTERVAL 0 SECOND;

this will erase all binary logs before midnight 3 days ago.

If you want to have binlog rotated away automatically and keep 3 days woth, simply set this:

mysql> SET GLOBAL expire_logs_days = 3;

then add this to /etc/my.cnf

[mysqld]
expire_logs_days=3

and mysqld will delete them logs for you.

This is pretty much it!

Source