With a little side of applesauce...

Wednesday, April 16, 2008

MySQL - binary logs fill up /var/log/mysql

An interesting problem happened when MySQL filled up the /var partition with binary logs. Every connection was returning a "Too many connections" error, including mysqladmin -u admin -p shutdown.

Here were the steps I had to take:
1. /etc/init.d/apache2 stop
2. cd /var/log/mysql
3. for f in $(ls -ltr | head | awk '{ print $9 }'| grep -v "^$") ; do rm $f ; done
4. mysqladmin --force -u admin -p shutdown
5. /etc/init.d/mysql start
6. /etc/init.d/apache2 start

Everything was happy :)

I found this juicy tidbit to change the default number of days that binary logs are allowed to remain:
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#option_mysqld_expire_logs_days

expire_logs_days is a global variable which is set in my.cnf with the following syntax:

expire_logs_days = 40

No comments: