How to capture the queries run on MySQL server?

Solution 1:

The most robust way would be to use the "general query log", which will capture all the queries: http://dev.mysql.com/doc/refman/5.1/en/query-log.html

You don't specify the MySQL server version, but if you have 5.1.12 or later you can enable and disable that with a global variable through SQL; see the documentation for details.

Solution 2:

I would use the slow query log. It captures all queries, not just those that are slow, if you set long_query_time = 0.

It also captures ALL queries, which is not true of the TCP-sniffing techniques mentioned here; those won't capture queries executed via a socket. Ditto for watching SHOW PROCESSLIST; you will miss fast-running queries.

If you want to capture queries via the processlist or via TCP traffic, I would suggest using Percona Toolkit's pt-query-digest. It can poll the processlist for you (and make sense out of the results, which is very hard to do if you're capturing a bunch of samples of it yourself), and it can interpret MySQL's TCP protocol, so you can grab some TCP traffic and analyze it. Of course, it's also the best query aggregator / profiler / reporter ever written, but you didn't say what you want to do with the queries after you capture them.

Solution 3:

Try this command as root (or use sudo):

tcpdump -i eth0 -s 0 -l -w - dst port 3306 | strings | perl -e '
while(<>) { chomp; next if /^[^ ]+[ ]*$/;
  if(/^(SELECT|UPDATE|DELETE|INSERT|SET|COMMIT|ROLLBACK|CREATE|DROP|ALTER)/i) {
    if (defined $q) { print "$q\n"; }
    $q=$_;
  } else {
    $_ =~ s/^[ \t]+//; $q.=" $_";
  }
}'

Found http://www.mysqlperformanceblog.com/2008/11/07/poor-mans-query-logging/

Solution 4:

Sure:

mysqladmin -u root -p -i 1 --verbose processlist > /tmp/pl.out

Cheers

Solution 5:

This might be a place for the Mysql Proxy. It basically allows you to capture (and manipulate) the queries being sent. A basic setup to intercept is pretty easy. Then just change your client config to point at the proxy so you can capture all the requests.

Tags:

Mysql

Recent Posts