MySQL too many connections错误的原因及解决

MySQL too many connections错误的原因及解决 目录

• 简单总结一下

   今天中午，开发测试环境的MySQL服务报了一个too many connections的错误，从问题上看，可能是连接池被打满了，导致所有的连接都不可用了。

   在这种情况下，最为直接的办法就是重新设置最大连接数，查看my.cnf文件，里面关于连接数的参数有两个，分别是：

max_connections:最大连接数

max_user_connections:用户最大连接数

其中，第一个参数确定的是该实例的最大连接数，第二个参数确定的是单个用户的最大连接数。

   一般的线上环境，为了保险起见，一般这两个参数不能设置为相等，可以将max_user_connections参数设置的稍微小一点，留有一定的余量，这样可以防止单个用户占用完所有的连接池。

    看到上面这个问题，我的第一反应是，登陆上去，kill掉一些连接，然而，当我想办法登陆的时候，发现已经没法登陆了，连DBA的管理账号dba_admin都已经无法建立连接了。查看错误日志：

2019-08-12T06:02:42.928412Z 0 [Warning] Too many connections
2019-08-12T06:02:42.930181Z 0 [Warning] Too many connections
2019-08-12T06:02:44.595199Z 0 [Warning] Too many connections
2019-08-12T06:02:44.597160Z 0 [Warning] Too many connections
2019-08-12T06:02:44.782042Z 0 [Warning] Too many connections

   全部都是too many connections，幸亏是开发环境，这样的话，我们可以使用停止数据库服务的方法来进行重启，然后重新增加最大连接数，当然，这个增加的量需要考量，一般情况下，需要跟wait_timeout的值结合起来设置，也就是等待超时时间，一般情况下，如果wait_timeout的值比较大，往往会造成连接数比较多的情况，而一个连接会消耗大约2M左右的内存，如果你设置的连接比较多，很可能出现内存耗尽的情况，而wait_timeout的值如果设置的比较小，连接会不停的创建和销毁，这样会浪费一定的IO资源。

  所以，如何得到一个平衡的值成为关键，在MySQL官方文档中有如下解释：

   从官方文档的这个说明来看，几个GB的内存完全可以支持你把连接数设置在500-1000之间，事实上，这个区间的值能够满足大部分的需求。

   除此之外，官方文档上还说了一个特点，挺好的，就是

mysqld actually permits max_connections + 1 client connections. The extra connection is reserved for use by accounts that have the SUPER privilege. By granting the privilege to administrators and not to normal users (who should not need it), an administrator who also has the PROCESS privilege can connect to the server and use SHOW PROCESSLIST to diagnose problems even if the maximum number of unprivileged clients are connected. 

   翻译过来就是事实上，允许的最大连接数是max_connections的值+1个连接，最后多的这个链接，是给super权限的用户用的，这样在连接被耗尽的时候，可以使用super权限登陆上去，进行show processlist的方法来查看连接，并kill掉一些不需要的连接，让服务重新可用。

     然而，实际应用中，我们经常为了 *** 作方便，而给一些不必要的账号分配super权限，导致拥有super权限的普通用户占用了多余的这一个连接，在今天这个问题中，这样的设置却无济于事，还是无法登陆，因为，毕竟一个连接太少了，如果有其他的账号已经占用了个这个super的连接，那么这个实例还是无法访问。

   这样的情况下，除了重启mysql实例，修改my.cnf文件中的最大连接数，有没有不停止服务而进行处理方法呢，答案是有的。但是有一个前提，你的服务要是Percona-server的，如果你使用的官方版本的MySQL，那这个办法是行不通的，据说MariaDB也可以使用这个办法，没有测试过，不太懂，有兴趣的可以测一测。

   从Percona Server 5.5.29开始，您可以简单地添加extra_port到您的my.cnf，并且下次重新启动时，端口将变为可用，并将侦听与常规连接相同的bind_address。如果未设置extra_port变量，则默认情况下不会有其他端口可用。您还可以定义extra_max_connections此端口将处理的连接数。此默认值为1。

    以上所述，是Percona-Server官网上的描述，可以通过另外2个参数来实现连接池占用完之后的登陆问题：

mysql:(none) 22:12:51>>show variables like '%extra%';
+----------------------------------+-------+
| Variable_name                    | Value |
+----------------------------------+-------+
| extra_max_connections            | 10    || extra_port                       | 43130 |
+----------------------------------+-------+
2 rows in set (0.00 sec)

   就是这两个参数，一个是extra_max_connections，另外一个是extra_port参数，这两个参数使我们拥有额外的连接和额外的端口去连接服务器，从而使用show processlist观察连接情况，杀掉一些不必要的连接，使得服务器重新可用。

 但是需要注意的是，在Percona Server 8.0.14及更新版本中已删除extra_port，  因为MySQL社区已实现了admin_port，它复制了此功能。因此，请确保在升级到Percona Server 8.0.14时编辑my.cnf，如果已经在那里定义了extra_port，还请及时更新！

   最后我们看看连接的情况吧，在这个例子中，我们将extra_port设置成为了43130：

[root@ log]# /usr/local/mysql/bin/mysql -udba_admin -p -h127.0.0.1 -P4313
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 71920
Server version: 5.7.16-10-log Percona Server (GPL), Release 10, Revision a0c7d0d

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql--dba_admin@127.0.0.1:(none) 22:40:59>>exit
Bye

---------------------------------------------------------------------------

[root@ log]# /usr/local/mysql/bin/mysql -udba_admin -p -h127.0.0.1 -P43130
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 71941
Server version: 5.7.16-10-log Percona Server (GPL), Release 10, Revision a0c7d0d

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql--dba_admin@127.0.0.1:(none) 22:41:05>>

简单总结一下

• 如果使用MySQL官方版本：

   发生连接数超过最大值的情况的时候，首先使用super全新的账户进行登陆，查看是否可以直接登陆，如果不行的话，那就只能停服务，然后重新设置连接数，在重启服务。

• 如果使用Percona-Server或者MariaDB：

   可以尝试提前配置extra_port来防止出现连接数被占满的情况。如果一旦发生了这种情况而没有提前进行配置，那么在停止服务之后最好进行补充，以防止此类情况再次发生。

以上就是MySQL too many connections错误的原因及解决的详细内容，更多关于MySQL too many connections错误的资料请关注脚本之家其它相关文章！