MySQL Connection--使用tcpkill杀掉MySQL活跃连接

当MySQL连接被打满，连管理员也无法本地登录时，可以考虑使用tcpkill杀掉一些应用服务器创建的连接。

查看数据库连接主要来自那些应用服务器

## 按照IP查看连接数前10的应用服务器
netstat -ano | grep 3306 | \
awk '{print $5}'|awk -F':' '{print $1}'| \
awk '{a[$1]++}END{for (j in a) print j":"a[j]}' | \
sort -n -r -t ":" -k 2 |head -n 10

 

CentOS 6安装tcpkill

rpm安装包：
libnids-1.24-1.el6.x86_64.rpm
libnet-1.1.6-7.el6.x86_64.rpm
dsniff-2.4-0.17.b1.el6.x86_64.rpm
下载地址：
https://cbs.centos.org/koji/packageinfo?packageID=1876
https://cbs.centos.org/koji/packageinfo?packageID=1875
https://cbs.centos.org/koji/packageinfo?packageID=1874

 

tcpkill语法

tcpkill [-i interface] [-1…9] expression
 
-i	interface Specify the interface to listen on.

-1……-9	Specify the degree of brute force to use in killing a connection. Fast connections may require a higher number in order to land a RST in the moving receive window. Default is 3.

{expression}	Specify a tcpdump-like expression to select the connection to kill

 

tcpkill示例：

示例：
## kill与3358端口建立的所有连接
tcpkill -i eth0 port 3358

## kill与服务器10.0.1.111 和10.0.1.112建立的所有连接
tcpkill host 10.0.1.111 and host 10.0.1.112

 

演示：

在服务器192.168.1.20上，想要kill所有来自192.168.1.35的请求

tcpkill -9 host 192.168.1.35

输出结果:
192.168.1.35:14125 > 192.168.1.20:3358: R 1067508583:1067508583(0) win 0
192.168.1.35:14125 > 192.168.1.20:3358: R 1067508612:1067508612(0) win 0
192.168.1.35:14125 > 192.168.1.20:3358: R 1067508670:1067508670(0) win 0
192.168.1.35:14125 > 192.168.1.20:3358: R 1067508757:1067508757(0) win 0
192.168.1.35:14125 > 192.168.1.20:3358: R 1067508873:1067508873(0) win 0
192.168.1.35:14125 > 192.168.1.20:3358: R 1067509018:1067509018(0) win 0
192.168.1.35:14125 > 192.168.1.20:3358: R 1067509192:1067509192(0) win 0
192.168.1.35:14125 > 192.168.1.20:3358: R 1067509395:1067509395(0) win 0
192.168.1.35:14125 > 192.168.1.20:3358: R 1067509627:1067509627(0) win 0
192.168.1.20:3358 > 192.168.1.35:14125: R 1578318895:1578318895(0) win 0
192.168.1.20:3358 > 192.168.1.35:14125: R 1578318926:1578318926(0) win 0

在192.168.1.35向192.168.1.20:3358正在执行SQL请求的连接会报下面错误：

ERROR 2013 (HY000) at line 1: Lost connection to MySQL server during query

在192.168.1.35向192.168.1.20:3358发送新请求会报：

ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 104

 

PS: tcpkill运行后，需要稍等几秒才会收到Lost connection的错误。