Four ways to kill MySQL connections in bulk

Method One
Generate the statement temporary file for the MySQL connection that needs to be processed through the connection information in the Information_schema.processlist table, and then execute the instructions generated in the temporary file.

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 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 current MySQL connections

Mysqladmin-uroot-p processlist|awk-f "|" ' {print $} ' |xargs-n 1 mysqladmin-uroot-p Kill

Kill the connection run by the specified user, this is Mike

Mysqladmin-uroot-p processlist|awk-f "|" ' {if ($ = = ' Mike ') print $ ' |xargs-n 1 mysqladmin-uroot-p Kill

Method Three
Implemented with Shel scripts

#杀掉锁定的MySQL连接
For ID in ' mysqladmin processlist|grep-i Locked|awk ' {print '} '
Do
Mysqladmin Kill ${id}
Done

Method Four
Through the Mk-kill command provided in the Maatkit toolset

#杀掉超过60秒的sql
Mk-kill-busy-time 60-kill
#如果你想先不杀, first look at what SQL is running for more than 60 seconds
Mk-kill-busy-time 60-print
#如果你想杀掉, and the output kills which processes
Mk-kill-busy-time 60-print–kill

Four ways to kill MySQL connections in bulk