Dynamic General and Slow Query Log Before MySQL 5.1

Posted in: Technical Track

This is a hack, but it is a good one. I was looking at some machines on a new client, and they had the general log turned on. I was surprised, because it was a fairly busy server, and they had had many problems with the server a few months ago. I thought perhaps they had turned on the general log to diagnose a problem and forgotten to turn it off, or something similar.

When I looked at the log on disk, I saw it was a symlink to /dev/null. They were running MySQL 5.0, so I immediately realized that it was a hack to have a general log they could “turn on” without having to restart mysqld.

On a different server, I saw the same link to /dev/null done with a slow query log.

The drawbacks to doing it this way is that MySQL still has the overhead of writing to the log file. The I/O overhead is greatly reduced because the writes are to /dev/null, but there’s still overhead from other resources such as RAM, CPU, etc.

But if you are in a similar position — where you frequently need to turn the general log or slow query log on temporarily for debugging purposes — this tip just might help.

Interested in working with Sheeri? Schedule a tech call.

5 Comments. Leave new

Hi…I am begineer of Mysql,

According to the hack which you mention, it General and slow query log is turn on ‘all the while’ but spool to /dev/null.

So when they needed the actual output , will it mean just change the location will do ?

Take a look at mk-query-digest | MySQL Expert | MySQL Performance | MySQL Scalability
October 8, 2009 9:49 am

[…] just wrote about Dynamic General and Slow Query Log Before MySQL 5.1 which apart from the File I/O overhead is an idea I’d not considered before. What may be a […]


Raymond — yes, so if the general query log is in


then an ls -l would show

/var/lib/mysql/mysql-general.log -> /dev/null

And to “turn the query log on” all they need to do is:

rm /var/lib/mysql/mysql-general.log

and at the mysql command prompt:

mysql> FLUSH LOGS;

Log Buffer #165: a Carnival of the Vanities for DBAs | Pythian Group Blog
October 9, 2009 12:55 pm

[…] Sheeri Cabral relates a little hack she found to simulate a dynamic general and slow query log before MySQL 5.1. She writes, “If you are in a similar position — where you frequently need to turn the […]

Four Short Links, Oct 14, 2009 | ghostar
December 23, 2010 8:13 pm

[…] Dynamic general and slow query log before MySQL 5.1 – This is an interesting way of handling the slow and general query logs on pre-5.1 MySQL instances. We don’t need this on slow, but there’s been occasions that we’ve needed the general query log, but enabling it and disabling it requires a full restart of the service on 5.0 and earlier. You still take a performance hit because you’re always logging, but I would think it to be fairly minimized on modern fast hardware. […]


Leave a Reply

Your email address will not be published. Required fields are marked *