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.

Advertisements

One Response

  1. To make it easy for all people.

    /etc/init.d/mysqld stop
    cd /var/lib/mysql
    ls /var/log/mysql/mysql-bin.00* > mysql-bin.index
    /etc/init.d/mysqld start

    This is how I got it fixed and you don’t have to find out which files don’t exist and which files do

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: