Four Methods for killing MySQL connections in batches and four methods for mysql

Four Methods for killing MySQL connections in batches and four methods for mysql

Method 1
Use the connection information in the information_schema.processlist table to generate a temporary MySQL connection statement file to be processed, and then execute the commands generated in the temporary file.
Copy codeThe Code is 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 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 2
Kill all current MySQL connections
Copy codeThe Code is as follows:
Mysqladmin-uroot-p processlist | awk-F "|" '{print $2}' | xargs-n 1 mysqladmin-uroot-p kill

Kill the connection run by the specified user. Here it is Mike.
Copy codeThe Code is as follows:
Mysqladmin-uroot-p processlist | awk-F "|" '{if ($3 = "Mike ") print $2} '| xargs-n 1 mysqladmin-uroot-p kill

Method 3
Implemented through SHEL script
Copy codeThe Code is as follows:
# Kill the locked MySQL connection
For id in 'mysqladmin processlist | grep-I locked | awk '{print $1 }''
Do
Mysqladmin kill $ {id}
Done

Method 4
Run the mk-kill command provided in the Maatkit tool.
Copy codeThe Code is as follows:
# Killing SQL statements that exceed 60 seconds
Mk-kill-busy-time 60-kill
# If you want to stop running SQL statements for more than 60 seconds
Mk-kill-busy-time 60-print
# If you want to kill and output the processes killed at the same time
Mk-kill-busy-time 60-print-kill

For more information about mk-kill, see:
Http://www.maatkit.org/doc/mk-kill.html