środa, 9 września 2009

clean mysql binary logs with expire_logs_days

Overall binary logs in mysql are good thing, you really need them, same like regular backups of your databases, but - I asked why they are so huge. Illusion mysql server with 10 databases has binary logs (default settings), and while databases has about 50MB, binary logs have 2GB.
15068 Dec 28  2008 mysqld-bin.000001
625410 Dec 28  2008 mysqld-bin.000002
676 Dec 28  2008 mysqld-bin.000003
661045342 Feb 20  2009 mysqld-bin.000007

If you want to clean them up, just do:
mysql> set global expire_logs_days=14;
Query OK, 0 rows affected (0.00 sec)

because this variable is not persistent, after restart you will get it back (to 0 default). So better add it to your config file like:
expire_logs_days = 14

it should look like this:
mysql> show variables like "expire%";
| Variable_name    | Value |
| expire_logs_days | 14    |
1 row in set (0.00 sec)

Next time you restart mysql should rotate the current log file (create a new one with the index number incremented) and delete the files not newer than 14 days. To do it promptly execute:
mysql> flush logs;
Query OK, 0 rows affected (1.62 sec)

0 komentarze: