Effectively rotating MySQL slow query logs with log rotate
Slow query logs are a powerfull tool to monitor your sites bottlenecks. However, unless you wanna be working with old data, you need to rotate them to see if you are actually progressing or not.
How to rotate these files is a bit strange, because mysql uses the inode to write to them (ie: if you rename the filename when mysql is running data will continue to be added). Percona has a post on how to effectively rotate these logs, but their suggestion didn't work right out of the box, and I found that I needed compression of older logfiles plus a way to tell what day each file refers to. So here comes an adapted version that actually works, save this as /etc/logrotate.d/mysql-slowquery
# Adapted logrotate for sql-slow-query file.
/var/log/mysql/slow_query.log {
daily
rotate 15
missingok
create 660 mysql mysql
delaycompress
dateext
notifempty
sharedscripts
postrotate
if test -x /usr/bin/mysqladmin &&
/usr/bin/mysqladmin ping &>/dev/null
then
env HOME=/root/ /usr/bin/mysql -e 'select @@global.long_query_time into @lqt_save; set global long_query_time=2000; set global slow_query_log = 0; select sleep(2); FLUSH LOGS; select sleep(2); set global long_query_time=@lqt_save; set global slow_query_log = 1;' > /var/log/mysqladmin.flush-logs 2>&1
fi
endscript
}