Rotating General Query & Slow Logs
Sometimes you need to have the general query log on and even though it causes more disk I/O than you may want, it’s good for troubleshooting. This log can and probably will fill up your disks rather quickly. Then there’s the slow query log – setting log_slow_queries and log_queries_not_using_indexes will write out the queries that take longer than long_query_time to execute, as well as any query not using an index.
So, since MySQL does not apply the expire_logs_days value to these logs – only to the binary log (log_bin), we need another solution. There are probably a bunch of custom scripts out there that do this, but big surprise – we have one as well. This was originally written by Jim Wood until I got my hands on it and made some changes. The changes are listed in the head of the script. This little guy will rotate the logs out to another directory and gzip them. Ideally you’ll run this from cron as such:
#mysql rotate logs
01 * * * * root /usr/local/bin/mysql_rotatelogs > /dev/null 2>&1
The script can be downloaded here.
Diamond Notes » Rotating Slow and General Query Logs Said,
May 14, 2008 @ 12:43 pm
[...] http://themattreid.com/wordpress/?p=34 [...]
MySQL Log Rotation « MySQL-HA Said,
May 14, 2008 @ 4:08 pm
[...] Log Rotation Posted in tools by mtaylor on the May 14, 2008 Ok. So I just looked at two blog posts about MySQL log rotation. While not technically incorrect, I’d like to disagree with the [...]
Siturir Said,
August 10, 2009 @ 7:39 am
I’m just dropping by to say that I very much liked seeing this post, it’s very clear and well written. Are you considering posting more about this? It appears like there is more fodder here for more posts.
sandra742 Said,
September 9, 2009 @ 6:50 am
Hi! I was surfing and found your blog post… nice! I love your blog.
Cheers! Sandra. R.
Sandeep Said,
October 4, 2009 @ 10:28 pm
Hi!! This script was really helpful. Thanks
Tony Kammerer Said,
February 26, 2010 @ 10:18 am
Wonderful rotation script Matt.
I have improved it slightly by adding the following:
Added rotation of log-queries-not-using-indexes log file
Added deletion of archived logs after 14 days
Feel free to let me know if you would like a copy of my modified version but I completely understand if you don’t bother since you could add the same easily and most likely in less time than it would take to send the email to me..lol