To rotate out logs more than 7 days old, add this to my.cnf
then restart mysql.
To perform this manually, run this command:
mysql> PURGE BINARY LOGS BEFORE DATE(NOW() - INTERVAL 7 DAY) + INTERVAL 0 SECOND;
This will erase binary logs back to Midnight 7 days ago.
Do not erase binary logs from the OS level because it disturbs mysqld's internal functionality for rotating binary logs.
UPDATE 2012-01-24 13:20 EDT
Make sure you do not purge binary logs that replication is using.
Here is an example how you can safely erase binary logs manually...
For a Master-Master setup M1 and M2
Go to M2 and run this:
mysql> SHOW SLAVE STATUS\G
You will see two lines that show binary log names
Master_Log_File represents the current binary log on M1 that was last read on M2.
Relay_Master_Log_File represents the current binary log on M1 that was last executed on M2.
Let's suppose that M2 has this:
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
1 row in set (0.00 sec)
mysql-bin.000131. So on M1, you can run this:
mysql> PURGE BINARY LOGS TO 'mysql-bin.000131';
Since Master_Log_File and Relay_Master_Log_File are the same, why not use Master_Log_File?
REASON #1 : SQL Error in the Slave
When an SQL Error happens in M2 on the SQL Thread, Replication stops processing SQL from the Relay Logs. The IO Thread continues downloading new SQL entries from M1 and appending to M2's Relay Logs.
REASON #2 : Long Running SQL Query in the Slave
If a query comes along, such as
UPDATE that updates every row in a table and it takes 5 minutes. During those 5 minutes, the IO Thread continues downloading new SQL entries from M1 and appending to M2's Relay Logs.
Given both reasons, there are some occassion where
Master_Log_File are different. In that instance, always use
Relay_Master_Log_File. In light of this, there is another reason:
REASON #3 : Log Corruption in Slave or Master
There are rare times when bad network transmission will produce corrupt relay logs. When you do
SHOW SLAVE STATUS\G, an error message will explain the the binary log may be corrupt. You should start firsdt with the relay logs.
To clear them out and start with fresh set, do
SHOW SLAVE STATUS\G, get the
Relay_Master_Log_File (RMLF), the
Exec_Master_Log_Pos (EMLP), and run this on M2
CHANGE MASTER TO master_log_file='RMLF',master_log_pos=EMLP;
CHANGE MASTER TO command erases all relay logs and starts downloading from scratch.
START SLAVE;, if you get the corrupt error log message again, then network transmission was not the issue. The binary log on M1 may just be corrupt after all.
You must go to M1, make copy of the the suspected log, run
mysqlbinlog against that copied binary log and redirect to a text file. Read the text file. If the text files contains gibberish or indiscernable characters, then you must perform a full sync of the slave.