Linux 4 All

Best Linux tricks source

Posts in the Percona category

I admit that is pretty lame, but until today I had no idea how to kill a specific MySQL query. It is quite easy.

First we need to get the queries:

mysql> show processlist;
+------+------+-----------+------+---------+------+-------+------------------+
| Id   | User | Host      | db   | Command | Time | State | Info             |
+------+------+-----------+------+---------+------+-------+------------------+
| 5585 | root | localhost | NULL | Query   |    0 | NULL  | show processlist |
+------+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)

Then kill the id:

mysql> kill 5585;
ERROR 1317 (70100): Query execution was interrupted
mysql>

Here is a quick trick for using tcpdump to get all MySQL queries running on a server:

tcpdump -i $INTERFACE -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.=" $_";
  }
}'

I think I got this from Percona website