Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

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/

share|improve this answer
    
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 H Mar 1 '12 at 6:55
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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