MYSQL 连接全部打满后的解决方案, 包含5.7 8.0 (外加一个招聘信息)

MYSQL 的连接被打满,然后就无法提供服务了, 那大部分会有几种解决的方案和方法.

1 监控要到位,你的MYSQL到底MAX_CONNECTIONS是多少, 超过多少连接数就需要告警了,这是一个问题. 当然这只能提前预知危险,但不能阻止危险.

2 基于MYSQL 的连接打满后的连接清理的问题

这里分两个部分说

1 MYSQL 5.X

2 MYSQL 8

1 MYSQL 5.X 如何处理这样的问题

首先要确认的是你的MYSQL 的版本, 如果你的MYSQL 5.X 是 percona 或者mariadb的版本,那么OK,下面的方法适合你, 如果你用的是官方的版本,(俗称社区版),那不好意思,自己想办法去吧.

实际上我们只需要在MYSQL 的配置文件中添加另个参数

extra_max_connections = 3

extra_port=3307

为了测试我们将MYSQL的 MAX_CONNECTIONS 变化到 1

我们开了一个SESSION 又开了一个 报错, 这个是自然的, TOO MANY CONNECTIONS

我们将参数打入到MY.CNF 并重启动数据库

我们通过3307 的预设的端口进行访问是可以的, 这个方法可以弥补这个连接打满的问题

后面我们可以通过直接执行下面的存储过程,将应用的连接都KILL 掉,达到治标的解决问题的方案.

DELIMITER $

CREATE PROCEDURE kill_process_proc()

BEGIN

DECLARE kill_done int;

DECLARE EXIT HANDLER FOR NOT FOUND SET kill_done = 1;

BEGIN

DECLARE cursor_ID int;

DECLARE cursor_i CURSOR FOR

SELECT

id

FROM information_schema.PROCESSLIST

WHERE USER not in ('root','sys','repl','proxysql','mha','event_scheduler')

AND ID != CONNECTION_ID();

OPEN cursor_i;

read_loop:

LOOP

FETCH cursor_i INTO cursor_ID;

KILL CONNECTION cursor_ID;

IF kill_done THEN

LEAVE read_loop;

END IF;

END LOOP;

CLOSE cursor_i;

END;

END$

DELIMITER ;

以上的脚本是验证过的.

那么MYSQL 官方版本如何解决这个问题, 实际上在MYSQL 8.014这个版本提供了administrative connection port 这个功能.

要启动这个功能,需要设置admin_address ,如果admin address不设置,则这个功能无法被启用.

默认这个TCP/IP 的端口为33062, 对于通过这个端口连接到数据库的用户的数量我们并没有限制,但连接的用户必须有 service_connection_admin的权限.

另外这个功能create admin listener thread 这个选择项默认是关闭,也就是说,如果按照默认的来说,mysql 的管理连接和普通连接是一个,而不是分开的.

在简单的配置后,就可以通过33062 来访问了

上图是打开 create_admin_listener_thread, 也就是说普通用户的thread 和我们的ADMIN 的 thread 不同 大家可以注意上图 凡是普通thread开通的端口在3开头, 如果是ADMIN thread 是 4开头, 说明的确是通过打开create_admin_listener_thread 将访问的thread 分开了.

另外MYSQL 8 的 MAX_CONNECTION +1 这个就不多说了,和administrator port 相比,那是天上地下.