If you have binary logging enabled on your MySQL server (i.e. the log-bin
parameter is set in your MySQL configuration file), then you may notice
a buildup of rather large files in your MySQL data directory over time
(e.g. mysql-bin.000013). Generally speaking, you only need to enable
this binary logging if your server is acting as a Replication Master or
if you need the ability to do point in time recovery from your latest
backup.
In any case, here are some useful commands for purging your binary log files:
To delete all binary logs older than 7 days:
mysql> PURGE BINARY LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 7 DAY);
To purge all logs before a specific date:
mysql> PURGE MASTER LOGS BEFORE '2008-01-01 00:00:00';
To purge logs automatically (every Monday at 3am) you could use a Unix cron job:
0 3 * * mon mysql -uroot -e "PURGE BINARY LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 7
The purpose of MySQL Binary Log
The binary log has two important purposes:
- Data Recovery : It may be used for data recovery
operations. 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 : The binary log
is 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.
Disable MySQL binlogging
If you are not replicating, you can disable binlogging by changing your my.ini or my.cnf file. Open your my.ini or /etc/my.cnf (/etc/mysql/my.cnf), enter:
# vi /etc/my.cnf
Find a line that reads "log_bin" and remove or comment it as follows:
#log_bin = /var/log/mysql/mysql-bin.log
You also need to remove or comment following lines:
#expire_logs_days = 10
#max_binlog_size = 100M
Close and save the file. Finally, restart mysql server:
# service mysql restart
Purge Master Logs
If you ARE replicating,
then you need to periodically RESET MASTER or PURGE MASTER LOGS to
clear out the old logs as those files are necessary for the proper
operation of replication. Use following command to purge master logs:
$ mysql -u root -p 'MyPassword' -e "PURGE BINARY LOGS TO 'mysql-bin.03';"
OR
$ mysql -u root -p 'MyPassword' -e "PURGE BINARY LOGS BEFORE '2008-12-15 10:06:06';"