Friday, September 23, 2011

how kill multiple locked queries in mysql

There are two ways actually. one is if in your mysql information_schema database have processlist table
and second one is simple shell script.

If information_schema.processlist exist.

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)

If information_schema.processlist doesn’t exist on your version of MySQL, this will work.

#!/bin/bash
for each in `mysqladmin -u user -ppassword processlist | awk '{print $2, $4, $8}' | grep mailer | grep shiksha | awk '{print $1}'`;
do mysqladmin -u root -ppassword kill $each;
done

No comments:

Post a Comment