How to clear or purge MySQL Bin log Files

Binary log

MySQL Official website defines the binary log as following:

The binary log contains “events” that describe database changes such as table creation operations or changes to table data. It also contains events for statements that potentially could have made changes (for example, a DELETE which matched no rows). The binary log also contains information about how long each statement took that updated data. The binary log has two important purposes:

a) Data replication

b) Also certain data recovery options require this binary log

Considering the replication purpose, it is necessary to clear the old logs for proper functioning of replication. Also clearing the old logs will free up a large about of disk space.

Here are some example commands to clear/purge MySQL binary logs.

Access the MySQL prompt.

Following command will delete all logs prior to the log file mysql-bin.0003

mysql> PURGE BINARY LOGS TO 'mysql-bin.0003';

Following command will delete all logs before 2015-09-15

mysql> PURGE BINARY LOGS BEFORE '2015-09-15 10:00:00';

Following command will delete all logs older than 7 days.

mysql> PURGE BINARY LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 7 DAY);

Following command will delete all logs till now.

mysql> PURGE BINARY LOGS BEFORE now();

You will not need to do purge logs manually, logs older than 10 days will be purged automatically by MySQL server if you add the following lines in the MySQL configuration file.

expire_logs_days = 10
max_binlog_size = 100M

Restart mysql server

/etc/init.d/mysql restart

Done!