18

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.

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

2 Answers 2

17

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.

2
  • I suppose WHERE should be extended with AND id <> connection_id(). Right?
    – Alex Yu
    Commented Sep 24 at 13:20
  • 1
    @AlexYu Good catch. The WHERE clause can be extended any way you wish. Commented Sep 24 at 13:35
6
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/

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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