Buy Percona ServicesBuy Now!

Enabling/Disabling query log without server restart in MySQL 5.0

 | September 13, 2007 |  Posted In: Insight for DBAs


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.

Peter Zaitsev

Peter managed the High Performance Group within MySQL until 2006, when he founded Percona. Peter has a Master's Degree in Computer Science and is an expert in database kernels, computer hardware, and application scaling.


  • 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.

  • Hi,
    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.
    To reroute the traffic without need of disconnecting the clients, you can use a IPTABLES command


  • 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.

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

    apt-get install libpcap0.8 (if needed)
    curl | tar xvzf –

    I like this unobtrusive way of debugging!


  • 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

  • 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)

  • Hi,

    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.

  • 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.

Comments are closed