Purge MySQL Binary Logs periodically

MySQL

In MySQL database If you have binary logging enabled on your MySQL server (i.e. the log-bin parameter is set in the config file), then you may notice a bulk of large files in the MySQL data directory over time. Generally you only need to enable this binary logging if your server is acting as a relication master or if you need the ability to do point in time recovery from your latest backup.
NOTE: On a MySQL replication environment, users must be careful in deleting binary logs. If slaves are lagging behind the server SOME days, the binary log purge might cause replication to fail. Make sure the logs file are not in use before removing them!
In any case here are some useful examples for purging binary log files easiliy. Weíll show you two quick ways on deleting the expired bin-log files.
As the purge work here is issued by mysql itself, please make sure you have MySQL-4.1 and later version installed. For version 4.0 and 3.xx, it does not support the following feathers.
1, Delete MySQL bin-log files by PURGE statement
For example, if you need to delete all binary logs older than 7 days, you just need to run this sql:
mysql> PURGE BINARY LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 7 DAY);
To purge all logs before a specific date:
mysql> PURGE MASTER LOGS BEFORE ‘2010-03-01 00:00:00’;
To purge logs automatically (every Monday at 3am) you could use a cron job like this:
0 3 * * mon mysql -uroot -e ‘PURGE BINARY LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 7 DAY)’
2, Remove bin-log by the variable expire_log_days
An alternative option is to set ìexpire_log_daysî as MySQL server parameter. The default values for expire_log_days is not to purge logs. You can either set it in MySQLís configuration file or in a command terminal like below:
mysql> SET GLOBAL expire_logs_days = 7;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like ‘expire_%’;
+——————+——-+
| Variable_name | Value |
+——————+——-+
| expire_logs_days | 7 |
+——————+——-+
1 row in set (0.00 sec)
When this vavirable takes effect, the log files will be removed automatically on a weekly basis.
For any issues, donít forget to raise a thread at our database support forum here.

———————-OR——————–

Please do not just delete them in the OS.

You need to let mysqld do that for you. Here is how mysqld manages it:

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’;
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

SHOW SLAVE STATUS\G
This is critical. When you run SHOW SLAVE STATUS\G, you will see two binary logs from the Master:

Master_Log_File
Relay_Master_Log_File
When replication has little or no lag these are usually the same value. When there is a lot of replication lag, these values are different. Just to make it simple, choose whatever Relay_Master_Log_File is, and go back to the Master and run

PURGE BINARY LOGS TO ‘Whatever Relay_Master_Log_File Is’;
That way, replication is not interrupted.

Give it a Try !!!