7

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.

6

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.

3

or set

SET GLOBAL slow_query_log = 'OFF';

then clear the file and then

SET GLOBAL slow_query_log = 'ON';
1

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.

0

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.

0

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;
New contributor
Arunas Bartisius is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.