The process of killing all lock tables in MySQL

The process of killing all lock tables in--mysql

--------------------------------2014/05/20

In the management of the database, we often encounter the problem of lock table, look at the process list.

mysql>show processlist;

Out of several screens, no 1000 also have hundreds of, query statements to lock the table, hurriedly find the first locked thread_id, in the shell of MySQL execution.

mysql>kill thread_id;

The process of killing the first lock table still does not improve. Since it doesn't improve, let's try to kill all the lock-list processes, the simple script is as follows.

#!/bin/bashmysql -uroot-e "show processlist" | Grep-i "Locked" >> locked_log.txt for line  in ' Cat Locked_log.txt | awk '{print '}'do      
echo"kill $line; " >>kill_thread_id.sqldone 

Now Kill_thread_id.sql's content looks like this.

kill66402982; kill66402983; kill66402986; kill66402991;

Well, we execute it in the shell of MySQL and we can kill all the lock-list processes.

mysql>source kill_thread_id. SQL Of course, can also be done in one line For ID in'mysqladmin processlist | grep-i locked | awk ' {print $';   do mysqladmin kill ${id}; Done