Enabling/Disabling query log without server restart in MySQL 5.0

General query logging can be very handy in MySQL on profuction server for various debugging needs. Unfortunately you can’t switch it on and off without restarting server until MySQL 5.0.

What can you do in MySQL 5.0 and below ?

Use Our Patch – With this patch you can get all queries logged as slow queries (with times) and as you can change long-query-time online you can effectively enable and disable debug logging live. Note however this is not 100% equivalent for general query log – for example connects or queries with syntax errors will not be logged.

Enable logging to /dev/null You can enable queries to say “all_queries” log and symlink that to /dev/null. So when you will need to enable queries you can symlink it to something else and run “flush logs” so logs are reopened and written to the file in question. When you have debug info you can just switch it back. Using /dev/null as a target allows to eliminate a lot of log writing overhead and save disk space which can be consumed very fast otherwise. Of course it does not remove all logging overhead – but this should not be major for most applications.

Share this post

Comments (13)

  • WebTenet

    One thing everyone has possibly missed to mention that the log=/var/log/filename.log needs to go in the [mysqld] section of the my.cnf file. I already had slow-queries-log turned on and it was in the [mysqld_safe] section; so I thought the log option should also go there, but it did not work out. After trying to figure out for about an hour, I just tried blindly by putting it in the [mysqld] section and hey it started working.

    Hope this may be useful to someone.

    September 13, 2007 at 12:00 am
  • Giuseppe Maxia

    As an alternative, you can enable a log on-the fly, in any version from 4.1 to 6.0, using MySQL Proxy with an appropriate script. http://forge.mysql.com/snippets/view.php?id=81
    To reroute the traffic without need of disconnecting the clients, you can use a IPTABLES command


    September 13, 2007 at 9:26 am
  • peter

    Thanks Giuseppe,

    This is indeed other good way.

    September 13, 2007 at 11:57 am
  • Keith Murphy

    It seems that great minds think alike. I wasn’t aware of your patch. And the proxy hadn’t been released when we started working on our query sniffer program. The sniffer is a bit different in that is a perl program that uses the libpcap library. It can be started and stopped at any time and you can direct output to a file without any problems.

    September 13, 2007 at 2:02 pm
  • peter

    Thanks Keith,

    Indeed query sniffer is another good way to approach the same problem.

    September 14, 2007 at 3:52 am
  • Eike Herzbach

    Just tried out the query sniffer, it works great and is dead simple to use:

    apt-get install libpcap0.8 (if needed)
    curl http://iank.org/querysniffer/querysniffer-0.10.tar.gz | tar xvzf –

    I like this unobtrusive way of debugging!


    September 16, 2007 at 6:50 am
  • bill

    set sql_log_off = ‘ON’;
    turns of the log without restarting

    set sql_log_off = ‘OFF’;
    turns it back on

    this works on 5.0, anyway

    July 11, 2008 at 5:14 pm
  • peter


    It was my impression this allows to disable logging statements for this session only, while I’m speaking about global log.

    July 11, 2008 at 5:56 pm
  • jk

    allow me to follow up on this post:
    So does mysql 5.0.X not allow dynamically switching general query at all?

    July 29, 2008 at 11:15 am
  • peter

    You can’t turn on/off slow query log globally in MySQL 5.0 (though you can use trick described)

    July 30, 2008 at 9:13 am
  • vesko

    I’m trying to use a symbolic link for general log file as described in your book. It seems like a nice and clean solution for enabling/disabling query log dynamically on MySQL 5.0

    However I’m having the following error:

    Error 13. Turning logging off for the whole duration of the MySQL server process. To turn it on again: fix the cause, shutdown the MySQL server and restart it

    I made sure the symlink and the file that it points to are owned by the mysql user. It still gives me that error 13 (permission denied)

    August 31, 2010 at 5:49 pm
  • peter


    Check out permission for directories as well. It could be there is no access to the directory where this file is located or some higher level directory.

    September 3, 2010 at 5:04 pm
  • vesko

    Here it is. The target file is in the same dir as the link in /var/log/

    lrwxrwxrwx 1 mysql mysql 14 Aug 26 05:31 mysql-full1.log -> mysql-full.log
    -rwxrwxrwx 1 mysql mysql 152694272 Aug 26 05:04 mysql-full.log

    MySQL is instructed to use it:


    And here’s the error

    07:07:19 mysqld started
    ^G/usr/libexec/mysqld: File ‘/var/log/mysql-full1.log’ not found (Errcode: 13)
    7:07:19 [ERROR] Could not use /var/log/mysql-full1.log for logging (error 13). Turning logging off for the whole duration of the MySQL server process. To turn it on again: fix the cause, shutdown the MySQL server and restart it.

    September 29, 2010 at 5:13 am

Comments are closed.

Use Percona's Technical Forum to ask any follow-up questions on this blog topic.