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.