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.