2

I would like to configure a general query log for a busy MySQL 5.0.95 production server without effecting the availability.

Obviously I can set the "log" option in the my.cnf file and restart the server, but this is going to kick off a bunch of monitoring emails and cause some tomcat errors which I would like to avoid.

According to the docs, the server "log" option is actually read-only until it becomes the general_log in version 5.1.x; http://dev.mysql.com/doc/refman/5.0/en/query-log.html

[mysqld] 
log
log-output=/var/log/mysqld.log

the /etc/init.d/mysqld script that I have doesn't support a "refresh" and its looks like a reload is going do a fairly brutal stop and start, which is definitely going to show up in monitoring.

on a test server I tried the various mysqladmin refresh and reload options, but couldn't get it to reread the config.

Do I have any other options?

Actually, while researching for this question I found a couple of possibilities for logging the ongoing SQL in real time without restarting the database.

If you are not using SSL for the network connection to port 3306, then its possible to tcpdump the traffic, and then use mk-query-digest or wireshark to re-assemble the TCP stream and decode to the mysql SQL text.

This obviously is only practical when you can collect the data you need in a short time, because you are going to fill up the tcp dump file quickly for anything non-trivial.

Improve this question

1 Answer 1

Reset to default
2

I do not think for v5.0.95 is it possible to enable general log without server restart, but as of MySQL 5.1.12, you can by executing this query, You can try this on v5.0.95 as well and see whether it works or not,

SET GLOBAL general_log = 'ON';

This method works on any platform and does not require a server restart.

Baron has written a script to enable and disable log without restart of server for v5.0 as mentioned below:

http://www.mysqlperformanceblog.com/2007/09/13/enablingdisabling-query-log-without-server-restart-in-mysql-50/

Improve this answer
1
  • I should have known that they have covered this topic at percona. There should probably be a captcha on dba stackexchange that prevents you asking mysql questions until you have read every single mysql performance blog post evar.
    – Tom
    Mar 1, 2012 at 6:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.