Enabling/Disabling query log without server restart in MySQL 5.0

PREVIOUS POST
NEXT POST

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.

PREVIOUS POST
NEXT POST

Comments

  1. WebTenet says

    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.

  2. says

    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.

  3. bill says

    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

  4. says

    Bill,

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

  5. jk says

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

  6. vesko says

    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)

  7. says

    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.

  8. vesko says

    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:

    log=/var/log/mysql-full1.log

    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.

Leave a Reply

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