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.
