杀死mysql中too many connection连接进程

杀死mysql中too many connection连接进程

问题:点击navicat中连接的数据库时，出现了too many connection问题

查看mysql默认的连接数,默认是100个连接:

mysql> show variables like "max_connections";
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 100   |
+-----------------+-------+
1 row in set

没有修改mysql配置的权限，只好通过杀死mysql连接客户端的进程了。。。。

查看mysql连接信息:

mysql> show processlist;
+---------+------+--------------------+-------------+---------+------+----------+------------------+
| Id      | User | Host               | db          | Command | Time | State    | Info             |
+---------+------+--------------------+-------------+---------+------+----------+------------------+
| 1380797 | dev  | 192.x.xxx.x50:60041 | NULL        | Sleep   |  628 |          | NULL             |
| 1380799 | dev  | 192.x.xxx.x50:42251 | dev         | Sleep   |  625 |          | NULL             |
| 1380803 | dev  | 192.x.xxx.x50:32889 | NULL        | Sleep   |    5 |          | NULL             |
| 1380952 | dev  | 192.x.xxx.x50:34658 | dev         | Query   |    0 | starting | show processlist |
+---------+------+--------------------+-------------+---------+------+----------+------------------+

可以通过以下命令结束mysql连接:

kill Id
kill 1380799

批量结束mysql连接，网上有方便的操作:

mysql> select concat('KILL ',id,';') from information_schema.processlist where user='dev';
+------------------------+
| concat('KILL ',id,';') |
+------------------------+
| KILL 1380952;          |
| KILL 1380797;          |
| KILL 1380799;          |
| KILL 1380803;          |
+------------------------+

然后通过文本替换成:

KILL 1380952; KILL 1380797; KILL 1380799; KILL 1380803;

之后放在mysql连接客户端上执行:

mysql> KILL 1380952; KILL 1380797; KILL 1380799; KILL 1380803;
1317 - Query execution was interrupted
Query OK, 0 rows affected

Query OK, 0 rows affected

Query OK, 0 rows affected

使用非交互连接

查看客户端进程

mysql -h 19x.xx8.3xx.xx4 -P 3306 -udev -e "show processlist;" -p

拼接sql

select group_concat(concat('KILL ', id) separator ';') from \
				information_schema.processlist where user='dev' group by user

将客户端连接进程pid输出到文件中：

// -N 在控制台不输出结果
// -e 执行sql

mysql -h 19x.xx8.3xx.xx4 -P 3306 -u dev -p -N -e\
		 "select group_concat(concat('KILL ', id) separator ';') from \
		 information_schema.processlist where user='dev' group by user" > text.txt

cat test.txt
结果：KILL 239550;KILL 239551;KILL 238969;KILL 239056;...

kill过多的客户端连接进程:

mysql -h 19x.xx8.3xx.xx4 -P 3306 -u dev -p -N -e "KILL 239056;..."

注意最开头的两个进程属于非交互连接客户端的连接,这里是KILL 239550;KILL 239551;