Common methods of killing conversations in MySQL

This article mainly introduces four methods of killing MySQL connections in batches. This paper gives code examples respectively. Friends who need them can refer to them.

Method one

The statement temporary file of the MySQL connection that needs to be processed is generated from the connection information in the information_ schema. process list table, and then the instructions generated in the temporary file are executed.

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 $2}'| xargs-n 1 mysqladmin-uroot-p kill

Kill the connection that the specified user is running, here is Mike

Mysqladmin-uroot-p processlist | awk-F "|"'{if ($3 = = "Mike") print $2}'| xargs-n 1 mysqladmin-uroot-p kill

Method three

Implemented through SHEL script

# kill locked MySQL connections

For id in `mysqladmin processlist | grep-I locked | awk'{print $1}'`

Do

Mysqladmin kill ${id}

Done

Method 4

Through the mk-kill command available in the Maatkit toolset

# kill sql for more than 60 seconds

Mk-kill-busy-time 60-kill

# if you want not to kill first, see which sql runs for more than 60 seconds

Mk-kill-busy-time 60-print

# if you want to kill, output which processes are killed at the same time

Mk-kill-busy-time 60-print-kill