MySQL 连接数过多的处理方法 Too many connections

目录

一、出现背景

二、 错误原因

三、 如何查看当前 MySQL 连接池是否已满？

四、合理设置超时时间

1、在配置文件中修改超时时间（需重启 MySQL 生效）：

2、临时变更连接超时时间（无需重启）：

五、查看及修改最大连接数

1、查看当前 MySQL 连接数

2、临时调整当前 MySQL 连接数

3、通过修改 MySQL 配置文件调整最大连接数

六、 无法登录 MySQL 时，如何修改最大连接数

1、修改 pid 增大连接数

七、 提前布局，防患于未然

八、 总结

---

一、出现背景

多租户场景下数据库连接太多，且连接之后没有释放（todo:为什么会出现连接没有释放），导致数据库连接数过多

sudo mysql -uroot -p
ERROR 1040 (00000): Too many connections

二、 错误原因

出现 MySQL 连接数过多有多种情况，多数是因为mysql_connect ，没有 mysql_close; 当sleep连接占满最大连接数max_connections时，会导致 Too many connections 错误。

MySQL 默认最大连接数max_connections为 151，其实 MySQL 还给 root 留了多一个通道，真正的最大连接数为max_connections + 1 。但实际工作中因为各种原因，这个 1 也有可能被占用。这时，我们无法通过登录 MySQL 调整参数的方法来处理这个错误。

三、 如何查看当前 MySQL 连接池是否已满？

使用 mysqladmin -u root -p status 查看当前连接数情况

将 root 替换为你的 MySQL 账号名称，在返回的结果中，Threads 的值为当前连接数，如果当前连接数接近或等于最大连接数，那么就说明 MySQL 连接数已经满了或接近满了。

四、合理设置超时时间

之所以会出现大量 sleep 占满连接，除了业务量的原因外，也有可以从超时时间着手调整，可根据实际情况适当缩短超时时间，让 MySQL 可在短时间自动清理超时连接，以达到保证连接通常的目的。

mysqld 连接超时参数有以下两个：

• interactive_timeout
• wait_timeout

默认情况下，两者都是 28800 秒（8 小时），我们可以在 MySQL 配置文件中修改这两个参数。

如果你使用的是mysql_pconnect 这种持久连接的话，可以将超时时间降到更合适的值，比如 600 （10 分钟）甚至 60（1 分钟）。这个超时时间并没有一个明确的时间，主要还是要看你的应用场景中的实际需求。

1、在配置文件中修改超时时间（需重启 MySQL 生效）：

首先打开 mysqld.cnf 配置文件。

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

然后在配置文件中找到这两行，并修改对应的参数：

[mysqld]
interactive_timeout=60
wait_timeout=60

2、临时变更连接超时时间（无需重启）：

SET GLOBAL interactive_timeout = 60;
SET GLOBAL wait_timeout = 60;

注意：

• 这是临时变更配置的方法，在重启 MySQL 后会恢复配置文件中的设置值。
• 对于已经打开的连接，是不会被关闭的。只有新建立的连接才会在 60 秒后关闭。

五、查看及修改最大连接数

在 MySQL 中，默认连接数为 151，我们可以通过修改 MySQL 配置文件永久调整连接数参数，也可以通过 SQL 命令临时调整。

1、查看当前 MySQL 连接数

2、临时调整当前 MySQL 连接数

set GLOBAL max_connections = 300;

3、通过修改 MySQL 配置文件调整最大连接数

首先打开 MySQL 配置文件：

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

在 [mysqld] 下面找到 max_connections ，如果没有可直接添加。

[mysqld]
...

max_connections = 300

...

修改后重启 MySQL，使配置文件生效：

sudo systemctl restart mysql

重启后，进入 MySQL ，我们可以看到最大连接数配置已经生效。

mysql> show variables like '%max_connections%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| max_connections        | 300   |
| mysqlx_max_connections | 100   |
+------------------------+-------+
2 rows in set (0.02 sec)

mysql>

六、 无法登录 MySQL 时，如何修改最大连接数

在无法登录又无法重启 MySQL 时，我们可以使用以下方法进行操作，以增大连接数。

1、修改 pid 增大连接数

我们可以使用 gdb 工具，在不进入数据库的情况下，修改最大连接数。

gdb -p $(cat data/kalacloud.pid)
-ex "set max_connections=5000" -batch

• data/kalacloud.pid：将这里修改为你服务器中 pid 的文件路径及文件名。

此方法仅适用于特殊、紧急情况，在生产环境使用，有一定风险，慎用。

七、 提前布局，防患于未然

在 MySQL 配置文件中，有两个有关连接数的参数

• max_connections：控制最大连接数。
• max_user_connections：控制单个用户的最大连接数。当此参数为 100 时，那么任意用户（含 root 用户）最多可创建 100 个连接。

制定连接策略：

max_connections = 2000
max_user_connections= 300

当 MySQL 有 6 个用户时(不含 root )，单个用户最大连接数为 300，那么 6 个用户最多有 1800 连接。那么系统总会剩下 200 个连接留给 root 使用。

八、 总结

有关 MySQL 连接数过多的错误，我们要在平时的工作中多实践，这里的很多关键参数都需要我们对手中的工作有更宏观的认识，才能更好的设定这些参数。