Truncate Slow Query Log in MySQL

Question

What's the safest way to truncate the MySQL slow query log (under Linux primarily, but Windows would be handy to know) while MySQL is running?

By safe I mean:

• Mustn't cause any permissions problems
• Mustn't jump back to its original size next time its appended to

Adaline

Answer 1

To truncate a file regardless if it is open by a running application do:

> /var/logs/your.log

at your shell prompt.

Considering

I haven't investigated thoroughly, but doesn't this approach have the chance of truncating in the middle of a log entry, thus introducing errors into the log file? I like this solution (especially to add to my general Linux knowledge), but from what I can see, @tjanofsky's solution is more robust. – Anatomical

@rinogo: Good question. I'd have to check if single write() syscall can be interleaved with another one. OTOH, I wouldn't expect logger to use more than one write() for writing a line to log. – Considering

That's true! However, something to keep in mind is that in the case of MySQL's slow query log, each "entry" is actually multiple lines. So, worst-case scenario, each "entry" could actually consist of multiple write() calls, which would present a scenario that is much more vulnerable to this issue. – Anatomical

This pushes the filepointer back to the beginning, it doesn't really matter where mysql is writing (or not). – Usm

Answer 2

From here:

You can move the open file, then tell mysqld to flush logs, which will open a new log.

shell> cd mysql-data-directory
shell> mv mysql.log mysql.old
shell> mv mysql-slow.log mysql-slow.old
shell> mysqladmin flush-logs

Respect

Answer 3

If this is going to be a ongoing concern, you should take a look at logrotate as it can do this for you.

I did not know about echo > file.log, though it makes sense. I've always used cat /dev/null > file.log.

Also, it should be noted that it's very important not to delete a log file that is being written to because the program that has it open will continue to write to the file. Very difficult to figure out why all your hard drive space is gone!

Aurlie

In Linux case, this is the best and easiest way and answer. – Thackeray

Answer 4

Keep in mind that just deleting (rm) or moving (mv) the file will not free up space until mysql restart.

On ubuntu and MariaDB i used this to empty the disk space used from mysql-slow.log without restarting.

echo > /var/lib/mysql/mysql-slow.log

Fir