The MySQL slow query log is a very useful feature which all applications in production should have enabled. It logs all queries which complete in more seconds than what the long_query_time server variable specifies.
Sometimes you need to clear this log without restarting mysqld. You could simply erase the file (in Linux) but the file handle would be lost and no further logging would take place.
The best way to purge the log in Linux without having to restart the server is:
cat /dev/null > /path/to/logfile
Another way is to erase or rename the logfile and then run the following statement in mysql:
When you want to do that in Windows though things are different.
I haven’t yet found a way to safely remove the log file while mysqld is running. What I’m left with is to mark the current position in the slow query log so I can look it up and examine the log from that point onwards. The way this can be done is by executing a slow query (slow enough to be logged) and the safest of all looks like:
select "prod-25", sleep(4);
I can now quickly navigate to “prod-25″ in the slow query log; which by the way is a marker of the 25th production release of a system which I’m tunning.
The deployment process could be automatically executing (probably via ant) such a marker query in order to keep all the historic (slow log) data grouped by releases for future analysis.