MySQL-bin.XXX taking up extreme amounts of space in logs

Question

We have recently deployed a Drupal 6 install and the directory storing the mysqld-bin.XXX files /var/run/mysqld has caused the mysql server to crash. The size of these files (log) are over 1g in size each and there are tons of them. I would like to know a way we can either enable log rotation or something similar to insure this does not happen again.

Any insight is greatly appreciated.

Thanks in advance,

JN

Answer 1

Short answer, yes you can: http://dev.mysql.com/doc/refman/5.0/en/binary-log.html

http://dev.mysql.com/doc/refman/5.0/en/replication-options-binary-log.html

But you need to make sure you have a good backup system in place so you can take snapshots of your DB, then recover with your binlog.

Specifically, check the max_binlog_size option

Set this to something like 128 or 256Mb then you can then just add on a cron job to copy old binlogs off with each database dump, then delete old binlogs.