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? May 10, 2011 at 18:21
  • @DTest - can that be done with a query? I don't have direct server access.
    – JIStone
    May 10, 2011 at 18:23
  • This is a question that can come in handy for other developers and DBAs (+1) !!! 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.

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 !!! 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.