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

score 23 · Accepted Answer · edited Mar 13 '13 at 08:48

23

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

> /var/logs/your.log

at your shell prompt.

edited Mar 13 '13 at 08:48

Michael Foukarakis

39,737
6
87
123

answered Feb 23 '09 at 11:39

vartec

131,205
36
218
244

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. – rinogo Feb 13 '14 at 16:12
@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. – vartec Feb 13 '14 at 17:19
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. – rinogo Feb 13 '14 at 18:41
This pushes the filepointer back to the beginning, it doesn't really matter where mysql is writing (or not). – Glenn Plas Sep 11 '15 at 08:26

score 18 · Answer 2 · answered Jul 30 '09 at 17:41

18

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

answered Jul 30 '09 at 17:41

tjanofsky

181
1
2

score 5 · Answer 3 · answered Feb 26 '09 at 01:20

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!

In Linux case, this is the best and easiest way and answer. – shgnInc Apr 26 '17 at 07:03 — shgnInc, Apr 26 '17 at 07:03

score 0 · Answer 4 · answered May 09 '22 at 17:42

0

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

answered May 09 '22 at 17:42

karpa

201
4
17

Truncate Slow Query Log in MySQL

4 Answers4

Linked