Bender: Ahhh, what an awful dream. Ones and zeroes everywhere... and I thought I saw a two.
Fry: It was just a dream, Bender. There's no such thing as two.

Fresh from my blog...

Effectively rotating MySQL slow query logs with log rotate

Posted by Bill Seremetis on Thu, 11/05/2015 - 10:12

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 {
    rotate 15
    create 660 mysql mysql
      if test -x /usr/bin/mysqladmin &&  
           /usr/bin/mysqladmin ping &>/dev/null        
           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