Method One
Generate the statement temporary files that need to be processed by the connection information in the Information_schema.processlist table, and then execute the instructions generated in the temporary file.
Copy Code code as follows:
Mysql> Select Concat (' KILL ', id, '; ') from information_schema.processlist where user= ' root ';
+------------------------+
| Concat (' KILL ', id, '; ') |
+------------------------+
| KILL 3101; |
| KILL 2946; |
+------------------------+
2 rows in Set (0.00 sec)
Mysql>select concat (' KILL ', id, '; ') from the 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)
Method Two
Kill all of the current MySQL connections
Copy Code code as follows:
Mysqladmin-uroot-p processlist|awk-f "|" ' {print $} ' |xargs-n 1 mysqladmin-uroot-p Kill
Kill the connection that the specified user is running, this is Mike.
Copy Code code as follows:
Mysqladmin-uroot-p processlist|awk-f "|" ' {if ($ = = ' Mike ') print $} ' |xargs-n 1 mysqladmin-uroot-p Kill
Method Three
Implemented by Shel script
Copy Code code as follows:
#杀掉锁定的MySQL连接
For ID in ' mysqladmin processlist|grep-i Locked|awk ' {print $} '
Todo
Mysqladmin Kill ${id}
Done
method Four
Through the Mk-kill command provided by the Maatkit tool set
Copy Code code as follows:
#杀掉超过60秒的sql
Mk-kill-busy-time 60-kill
#如果你想先不杀, let's see what SQL runs for more than 60 seconds.
Mk-kill-busy-time 60-print
#如果你想杀掉, at the same time the output killed what process
Mk-kill-busy-time 60-print–kill
Mk-kill more usage can refer to:
Http://www.maatkit.org/doc/mk-kill.html