Mysql binary logging revisited

After last time, I'd poked at the LB servers databases, so we were getting effectively lock free backups, on one of the servers.

In the intervening period, after it was seen to be stable, I did the same for the other server.

However, Mike noted that the disk space used for the logs was growing rapidly. (I blame those pesky LHC physicists. Running jobs on our systems - anyone would think there was data to analyse or something ...). Because we're running with LB servers on the same machines as the WMS, this means that the /var partition contains both the database files, and the users sandbox - hence the old binary logs take space away from users stuff. (That's something to think about for the reinstall - might be worth separating them).

Time to automate log triming. Firstly, the manual side: the statement

PURGE BINARY LOGS BEFORE '2009-11-01 00:00:00';

to the server trims out some of the older logs. You can also trim up to a given file.

Better than that, however, is to put

expire_logs_days=8

in the my.cnf. This tells mysql to retire logs older than 8 days at server start up, or when the logs are flushed.

So as long as we ensure that when we take a full backup we flush the logs, then logs are automatically trimmed to just over a weeks worth. Adding that parameter to the mysqldump script, and we're done.

The binary logs have value, independant of the backups - there's a tool to read them, and look at what was happening. Whether 8 days is the best level for us is something that we'll have to monitor - arguments for shorter time periods seem stronger than for longer.