Home / server / Questions / 358978
Accepted
hafichuk
Asked: 2012-02-11 14:26:06 +0800 CST

How to capture the queries run on MySQL server?

  • 772

We're trying to do some server performance debugging and I would like to capture a snapshot of the queries being run on our MySQL server over a period of a couple minutes.

I'm familiar with MySQL's SHOW FULL PROCESSLIST, however I'd like to be able to run this via the command line so I can dump it to a file and post process it.

Is there a way to output this query to a file and have it run every second or so?

Is there a better way to capture all of the queries being run?

Note that I'm not interested in just the slow queries (I'm familiar with the slow query log).

mysql

9 Answers

  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.

    • 13
  2. Best Answer

    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.

    • 10

  3. Sure:

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

    Cheers

    • 5

  4. 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/

    • 5

  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.

    • 4

  6. The terminal-based Wireshark program tshark may help:

    tshark -T fields -R mysql.query -e mysql.query

    sudo yum install wireshark will give you tshark on Amazon Linux and sudo apt-get install tshark will give you tshark on Ubuntu 14+

    • 4

  7. I used 'Rui Pedro Bernardino's solution. Works great except I changed a couple things in the first line as detailed below...

    tcpdump -i any -s 0 -l -vvv -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.=" $_";
  }
}'
    • 3

  8. Capture mysql tcp sessions using tcpdump, both queries and replies. You can analyze a dump for example using maatkit tools:

    http://www.maatkit.org/doc/mk-tcp-model.html

    http://www.maatkit.org/doc/mk-query-digest.html

    • 0

  9. I was searching and searching and finally I landed at MONyog for monitoring all the queries at real-time that are executed in the mysql server.The only thing to be noted is "Performance_schema" and "statements_digest" table is to be enabled and Performance_schema is available with MySQL 5.6.14 and above.

    • 0