17

Sometimes during a SNAFU I have to run kill query xxxxxxx twenty or thirty times. Any sort of kill all command I am missing?

On account of how I don't like typing.

  • restart the server? – Derek Downey May 10 '11 at 18:21
  • @DTest - can that be done with a query? I don't have direct server access. – JIStone May 10 '11 at 18:23
  • This is a question that can come in handy for other developers and DBAs (+1) !!! – RolandoMySQLDBA Jun 20 '11 at 21:25
15

From the Linux command line

for PROC_TO_KILL in `mysql -h... -u... -p... -A --skip-column-names -e"SHOW PROCESSLIST" | grep -v "system user" | awk '{print $1}'` ; do mysql -h... -u... -p... -A --skip-column-names -e"KILL QUERY ${PROC_TO_KILL}" ; done

You can change the grep option in the header of the for loop to locate a specific user or specific string in the query.

If you have MySQL 5.1 where the processlist is in the INFORMATION_SCHEMA, you can do this to generate the KILL QUERY commands in bulk from within the mysql client:

SELECT GROUP_CONCAT(CONCAT('KILL QUERY ',id,';') SEPARATOR ' ') KillQuery
FROM information_schema.processlist WHERE user<>'system user'\G

You can do WHERE clauses against the INFO field to look for a specific query, the TIME field against long running queries, or the DB field against a specific database.

5
mysql> select concat('KILL ',id,';') from information_schema.processlist where user='root' into outfile '/tmp/a.txt';
Query OK, 2 rows affected (0.00 sec)

mysql> source /tmp/a.txt;
Query OK, 0 rows affected (0.00 sec)

http://www.mysqlperformanceblog.com/2009/05/21/mass-killing-of-mysql-connections/

  • I actually like this approach because it can be done within the confines of the mysql client regardless of Linux or Windows. +1 !!! – RolandoMySQLDBA May 2 '12 at 20:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.