Logrotate for MySQL slow query logs

Enabling the MySQL slow query log is extremely useful for finding out which queries are taking too long to execute, and either needs a table index created, or perhaps the query itself may need to be rewritten a bit more efficiently.

But oftentimes, it is forgotten that this log file can grow very large, and it needs to be rotated out. Below is a very quick and easy way to setup log rotation for the MySQL slow query logs:

vim /etc/logrotate.d/mysqllogs
/var/lib/mysql/slow-log {
        missingok
        rotate 2
        size 125M
        create 640 mysql mysql
}

That last line is critical. The file must be recreated with the owner and group set to ‘mysql’, otherwise, MySQL will not have permissions to write to that file.