Log Rotation for MySQL using logrotate

The logrotate utility is a powerful and underrated utility used to rotate logs. It is one of Red Hat’s lesser known utilities; even so, it is available for a number of platforms, including Ubuntu.

However, its set up for MySQL is missing on Red Hat and incomplete on Ubuntu.

For Ubuntu, there is no rotation of the slow query logs. To rotate these logs, just add them to the standard Ubuntu logrotate file for MySQL – that is, /etc/logrotate.d/mysql-server. Add the logs to rotate to the beginning of the file, adding to the list of files already present there.

For Red Hat, a complete MySQL log rotation file is needed as there is none at all. The MySQL logrotation script was removed as part of a security update to Fedora Core 4 back on 17 May 2006, and later removed from Red Hat Enterprise Linux 4 Update 4. The reasoning was detailed in Bug #180639 (not available?) and Bug #182025. Since then, this missing logrotate file has been the subject of several bugs (such as Bug #547007) and also of message threads like this one from Red Hat’s rhelv5-list in July of 2007.

The response to all these queries is that the MySQL logrotate script is broken and it’s up to MySQL to fix it. However, this does not seem to take into account the new FLUSH LOGS command, and admins everywhere are creating their own scripts.

Over at Question Defense, Alex has a fabulous description of the entire process – from enabling logging through implementing log rotation. However, this process uses ~/.my.cnf to automatically log in; better is to use a file like /etc/mysql/maint.cnf the way that Debian (and Ubuntu) does it. In that case, Debian creates a special user and a password to go with it, and puts these into a file /detc/mysql/debian.cnf; here is a sample debian.cnf:

# Automatically generated for Debian scripts. DO NOT TOUCH!
[client]
host     = localhost
user     = debian-sys-maint
password = i5Px6N4SZ9UhfSWa
socket   = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
host     = localhost
user     = debian-sys-maint
password = i5Px6N4SZ9UhfSWa
socket   = /var/run/mysqld/mysqld.sock
basedir  = /usr

For most purposes, only the [client] section is needed, along with the first three entries (host, user, and password). You could also specify the section as [mysqladmin] instead, which would limit the username and password to being used for mysqladmin only – which is the tool used during log rotation.

The critical command is this one:

/usr/bin/mysqladmin --defaults-file=/etc/mysql/logrotate.cnf

…where logrotate.cnf contains username and password details as described above. All the rest of the logrotate file is settings and script-bulletproofing:

# Modified Ubuntu logrotate script for MySQL server
#
# Untested under Red Hat, but should work: filenames will have to be changed
/var/log/mysql.log /var/log/mysql/mysql.log /var/log/mysql/mysql-slow.log {
        daily
        rotate 7
        missingok
        create 640 mysql adm
        compress
        sharedscripts
        postrotate
                test -x /usr/bin/mysqladmin || exit 0
                MYADMIN="/usr/bin/mysqladmin --defaults-file=/etc/mysql/logrotate.cnf"
                if [ -z "`$MYADMIN ping 2>/dev/null`" ]; then
                  if killall -q -s0 -umysql mysqld; then
                    exit 1
                  fi 
                else
                  $MYADMIN flush-logs
                fi
        endscript
}