Occasionally there is a need to see what queries reach MySQL. The database provides several ways to share that information with you. One is called general log activated with
|
1 |
--log |
(or
|
1 |
--general-log |
in MySQL 5.1+) start-up parameter. The log writes any query being executed by MySQL to a file with limited amount of additional information. The other is slow log enabled by
|
1 |
--log-slow-queries |
parameter (MySQL 5.1 requires also
|
1 |
--slow-query-log |
), which was designed to store poorly performing queries that run at least 2 seconds. Percona actually extended the slow log to, among others, include any query regardless of the execution time.
The problem is that for both you need to prepare earlier either by enabling the logging before starting the database instance or, even more work, by applying the patch and rebuilding the entire database from sources.
I know that many databases out there run with none of these and it would require a restart to get the logging in place and possibly another restart to disable it when no longer necessary (though actually slow log can be disabled by simply setting
|
1 |
long_query_time |
MySQL variable vale high enough).
So what can be done when you really need to see the queries, but can’t afford any downtime?
If you are a privileged user (i.e. root), you can use tcpdump on a database server to take a peek into a network stream and filter for packets that go to MySQL. Those packets contain queries. Here’s my quick one-liner which I will write in multiple lines:
|
1 |
<br>garfield ~ # tcpdump -i eth0 -s 0 -l -w - dst port 3306 | strings | perl -e '<br>while(<>) { chomp; next if /^[^ ]+[ ]*$/;<br> if(/^(SELECT|UPDATE|DELETE|INSERT|SET|COMMIT|ROLLBACK|CREATE|DROP|ALTER)/i) { <br> if (defined $q) { print "$qn"; } <br> $q=$_;<br> } else { <br> $_ =~ s/^[ t]+//; $q.=" $_"; <br> } <br>}'<br> |
The output may contain little garbage, but it can be easily filtered out.
Obviously this method works only when applications communicate with MySQL through TCP sockets. When localhost (not to be confused with 127.0.0.1) is used as a MySQL host, this will not work since all traffic goes through a unix socket file.
It’s most definitely not a MySQL log replacement, but can be very useful if you need just a few minute dump.
Maciek
