26 March 2018 ~ 0 Comments

Solution: Out of Control MySQL Slow Log Files

I’ve been having an ongoing problem on one of my servers storage space, long story short the culprit ended up being that the mysql-slow.log file was 80GB.

It had gotten turned on for testing at one point and the threshold turned very low and I never thought to check it again over the years.

Anyway, I had a bit of a hassle figuring out how to resolve it so thought I would post it here for future reference.

How to Safely Clear Your Mysql-Slow.log File

This was actually pretty simple once I figured out the quirks.

The first thing to do is to disable slow query logging in mysql:

set global slow_query_logging=OFF

After it is disabled then it will no longer want to write to the file, if it is showing that the file is still in use then simply run:

flush slow logs

After it is disabled you can safely move, compress, or delete the old log file.  If you plan on deleting it then I would recommend at least renaming it first until everything is completed, just as a precaution if the file needs to be restored.

Once you have removed or deleted it, you can create a new one with the same name

>mysql-slow.log

Once it is created, you may need to adjust it’s rights.  This is where I got hung up on, the mysql service was unable to write to the file.

chmod 775 mysql-slow.log

After this is all done, you can turn slow queries back on

set global slow_query_logging=ON

And you will begin slow query logging again in your new file, you can now safely delete the old file if desired.

Hope this helps someone, it’s pretty straight forward but a lot of the other stuff I found when trying to research the problem did not mention having to change the access for the new file and it stumped me for a bit.

Related Posts Plugin for WordPress, Blogger...

Continue Reading