Thursday, April 10, 2014

MySQL: manage (disable, purge/clean) binary log files (mysql-bin.xxx)

MySQL binary log files contain all statements that update data or potentially could have updated it.
These files have 2 important purposes:
  • Data Recovery : after a backup file has been restored, the events in the binary log that were recorded after the backup was made are re-executed. These events bring databases up to date from the point of the backup.
  • High availability / replication : used on master replication servers as a record of the statements to be sent to slave servers. The master server sends the events contained in its binary log to its slaves, which execute those events to make the same data changes that were made on the master.
The problem is these files can cause your hard disk full. So you need to manage them to save your hard disk space and match your replication and backup policy. Please note that you shouldn't delete them manually. Instead of, you should configure MySQL for them.

I suggest 2 ways for handling them.

1. Disable these binary logs if you don't use replication function.
Open my.ini (Windows) or my.cnf (Linux), then find the line (starting with log-bin or log_bin) and comment to disable it:
#log-bin = mysql-bin
You should create a backup (dump data) daily if disable this.

2. Purge / clean them frequently if you use replication function.
Login to mysql then use the following command:
mysql> PURGE BINARY LOGS BEFORE DATE(NOW() - INTERVAL 3 DAY) + INTERVAL 0 SECOND;
It will clean all binary logs before midnight 3 days ago.

You may also need to set expire_logs_days value:
mysql> SET GLOBAL expire_logs_days = 3;

And add or modify this variable in my.ini (Windows) or my.cnf (Linux).
[mysqld]
expire-logs-days=3

To avoid to interrupt your replication, run SHOW SLAVE STATUS\G to check, you will see 2 binary logs from the Master: Master_Log_File and Relay_Master_Log_File. Then run this command:
PURGE BINARY LOGS TO 'Whatever Relay_Master_Log_File Is';

Finally you should restart your MySQL. That's all.

No comments:

Post a Comment

Subscribe to RSS Feed Follow me on Twitter!