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.

Improve this question
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. 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

Reset to default
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.

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

Improve this answer
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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.