MySQL slow log deletion while slow-query-log is ON

Question

I'm creating a tool that sets slow-query-log to ON with long_query_time = 0 for 5 minutes to log all queries currently running. The only thing I'm afraid of is that the slow_log will be too big very quickly.

From what I see if I delete the slow-query-log file, MySQL doesn't create it back until I run FLUSH SLOW LOGS.

The question is: Is it safe to delete it while slow-query-log is on? And do I need to FLUSH SLOW LOGS before doing it? I tried to read the documentation but did not understand how FLUSH SLOW LOGS is relevant and what is its exact purpose.

Answer 1

If you unlink the slow log file, but MySQL Server still has an open file descriptor, then the file is not deleted; it's just unlinked from the directory where you previously could see it. The file still exists, invisibly, and MySQL Server is still writing to it.

Once MySQL Server closes its open file descriptor, the file will have nothing tying it to the real world, and the operating system removes it at that time.

This is the way file removal works for any file on a POSIX operating system, not just MySQL slow logs.

Here's what I do to remove slow logs:

mv mysql-slow.log mysql-slow.log.old
mysql -e "flush slow logs"

Renaming the file doesn't affect the open file descriptor. MySQL Server continues to write to the same log file.

But the documentation says:

• FLUSH SLOW LOGS

  Closes and reopens any slow query log file to which the server is writing.

This means MySQL Server closes the file descriptor, which causes it to stop writing to the old file. Then it opens the log file with the original filename, which causes it to start a new, empty file.

You will see:

ls -l *slow*
-rw-r----- 1 mysql mysql 8675309 Aug 15 00:40 mysql-slow.log.old
-rw-r----- 1 mysql mysql 0 Aug 27 16:44 mysql-slow.log

Subsequent logging goes into mysql-slow.log.

The old file remains in the directory, in case you want to analyze it or something. But it does not receive any new logging. Remove the old file whenever you're done looking at it.

• idan ahal: I do not want to move it to a different location. Just to delete it. I check every 10 seconds and if it's bigger than 1GB I delete it. so can I just "SET GLOBAL slow_query_log = 'OFF';" and than delete? I'm just scared that if the DB is too busy it can take time.
• Bill Karwin: Alas, you don't seem to have understood my post.
• idan ahal: This "mv mysql-slow.log mysql-slow.log.old" will make the file still be on the file system right? What am I missing? Why not just setting to "slow_query_log = 'OFF';"

Answer 2

There is a more hands-on way to do this

METHOD #1 : Turn off slow log and then turn it on

mysql -e"FLUSH SLOW LOGS; SET GLOBAL slow_query_log = 'OFF';"
mv mysql-slow.log mysql-slow.log.old
touch mysql-slow.log
chown mysql:mysql mysql-slow.log
mysql -e "SET GLOBAL slow_query_log = 'ON';"

METHOD #2 : Copy and Truncate Slow Log

If you don't want to turn it off, here is another method

mysql -e"FLUSH SLOW LOGS;"
cp mysql-slow.log mysql-slow.log.old
echo -n > mysql-slow.log
mysql -e"FLUSH SLOW LOGS;"

That way, the file descriptor still points at the original slow log.

EPILOGUE

• If the slow log is like 200M, use Method #2
• If the slow log is huge, use Method #1

• idan ahal: In method 1, why do you flush the logs before setting slow_query_log = 'OFF'. Wouldn't it make more sense to stop and then flush? and why do I need to move it and not just delete it? (I do not want to continue logging the to the slow log)
• RolandoMySQLDBA: I have seen Windows OS act funny with open files, so I included in Method #1 an explicit FLUSH SLOW LOGS;. I thought you were rotating the log. So just run mysql -e"FLUSH SLOW LOGS; SET GLOBAL slow_query_log = 'OFF';" followed by rm -f mysql-slow.log
• idan ahal: I'm using Linux, but still, I do not understad why not doing it in this order to make sure: "SET GLOBAL slow_query_log = 'OFF'; FLUSH SLOW LOGS; "
• idan ahal: Can't I use a query to do it? because it counts as a table.