When expire_logs_days has no effect

I’ve spent all morning setting up log rotation in MySQL, except some servers were not erasing old log files.

The idea is to set expire_logs_days to say 7. This means only the logs covering the last seven days will be kept on disk. If you need them, or a slave needs to read from them, they will be kept. But get rid of really old binary files.

You can set this in my.cnf and also at the MySQL console with:

set global expire_logs_days=7

Either the next time you restart mysql, or the next time you issue purge logs or flush logs, MySQL should rotate the current log file (create a new one with the index number incremented) and delete the files not newer than seven days.

Except while the rotation worked, the old files remained on some servers.

It turns out that the binary files had been rm deleted from the filesystem, but the index file not updated (it’s a text file). Issuing show binary logs listed all the old files no longer on the filesystem. As a result, the deletion was failing, as detailed by Baron in this bug (which has turned in to a feature request).

The fix? Stop mysqld, edit the .index file to remove entried no longer present, and restart mysqld. At this point if you have the expire_logs_days entry in your my.cnf file, MySQL will delete the old files as it starts, otherwise you’ll need to issue the flush logs command yourself.

Admittedly the title of this post is misleading – it does have an effect, just not the entire effect as documented.

New arrival

I have recently been looking into architecture of web applications. It follows some interest in Amazon’s EC2 and S3 products where you rent data center resources.

And as if Amazon read my mind, my pre-order of Baron Schwartz’s (et. al) High Performance MySQL (2nd Ed) arrived yesterday (Saturday). It’s certainly a thick book covering all sorts of topics. Hopefully I’ll get a chance to actually read it over the coming days…