Delete Slow Query Log File

Question

I was logging slow queries of my system. Now I have few queries to be optimized and reset the global slow_query_log variable to 0. Now I want to delete slow query log file mysqld-slow.log. can anyone tell me what is the standard way to do this ?

I am using Cent OS and do not want to delete or affect other log files (i.e. general log or binary log) Thanks.

Answer 1

As of 5.5.3, FLUSH LOGS will close and reopen the slowlog. (In old versions, FLUSH had no effect on the slowlog.)

So, on *nix OS, this should work without restarting the server:

1. rm (to delete) or mv the slowlog to another name. (Note: mysqld will continue to write to the file, even though you changed the name.)
2. FLUSH LOGS;. As of 5.5.3, you can limit the effect via FLUSH SLOW LOGS;

See http://bugs.mysql.com/bug.php?id=14104 and http://bugs.mysql.com/bug.php?id=60878

I would not turn off the slowlog -- next month someone will add a naughty query and you will want to know about it.

Answer 2

or set

SET GLOBAL slow_query_log = 'OFF';

then clear the file and then

SET GLOBAL slow_query_log = 'ON';

Answer 3

It's very simple. If you want the slow query log file just take the backup and do below.

tail -n slow-query.log > slow-query.log

The above file will recreate with 'n' number of line with same name which clears the space.

Answer 4

Let me tell you, how I did that.

1. Stop the mysql service.
2. Backup / Copy the existing slow query log to another directory or folder.
3. Remove / Move the log file to another directory.
4. Create a new log file with the same name.
5. Start the mysql service.

Note:

1. Make sure, you have rights to move/remove the log file.
2. You cannot delete the file, when mysql service is accessing the log file.

This worked for me, I hope it will help you too. Thanks.

Answer 5

In case you are using AWS RDS service, trying to

>truncate mysql.slow_log;

Error Code: 1044. Access denied for user 'rds_admin'@'%' to database 'mysql'

The RDS way to do this, is to call built-in function which will move slow_log table to slow

CALL mysql.rds_rotate_slow_log;