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.
About the Author
Peter ZaitsevPeter 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.
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
Thanks Giuseppe,
This is indeed other good way.
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.
Thanks Keith,
Indeed query sniffer is another good way to approach the same problem.
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
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
Bill,
It was my impression this allows to disable logging statements for this session only, while I’m speaking about global log.
allow me to follow up on this post:
So does mysql 5.0.X not allow dynamically switching general query at all?
You can’t turn on/off slow query log globally in MySQL 5.0 (though you can use trick described)
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:
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.
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.