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.
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.
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.
AND id <> connection_id()
. Right?
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/