September 13, 2007

Enabling/Disabling query log without server restart in MySQL 5.0

Posted by peter |

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.

Posted by peter @ 3:19 am :: tips  
Print This Post Print This Post book mark Enabling/Disabling query log without server restart in MySQL 5.0 in del.icio.usdel.icio.us :: submit Enabling/Disabling query log without server restart in MySQL 5.0 to digg.comdigg  
Comment RSS :: Trackback URI  
Related posts: :Getting real life query speeds with MySQL::Poor man’s query logging::Query Profiling with MySQL: Bypassing caches:
 

9 Comments »

  1. 1. Giuseppe Maxia

    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. 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
    http://forge.mysql.com/snippets/view.php?id=82
    Cheers

    Giuseppe

    Comment :: September 13, 2007 @ 9:26 am

  2. 2. peter

    Thanks Giuseppe,

    This is indeed other good way.

    Comment :: September 13, 2007 @ 11:57 am

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

    Comment :: September 13, 2007 @ 2:02 pm

  4. 4. peter

    Thanks Keith,

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

    Comment :: September 14, 2007 @ 3:52 am

  5. 5. 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 -
    querysniffer-0.10/mysqlsniff.x64

    I like this unobtrusive way of debugging!

    Cheers,
    Eike

    Comment :: September 16, 2007 @ 6:50 am

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

    Comment :: July 11, 2008 @ 5:14 pm

  7. 7. peter

    Bill,

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

    Comment :: July 11, 2008 @ 5:56 pm

  8. 8. jk

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

    Comment :: July 29, 2008 @ 11:15 am

  9. 9. peter

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

    Comment :: July 30, 2008 @ 9:13 am

 

Subscribe without commenting

Trackbacks/Pingbacks