Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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

2 Answers 2

up vote 7 down vote accepted

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.

share|improve this answer
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/

share|improve this answer
    
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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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