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 {
        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.