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错误的资料请关注我们其它相关文章!

(0)

相关推荐

  • Mysql错误:Too many connections的解决方法

    MySQL数据库 Too many connections 出现这种错误明显就是 mysql_connect 之后忘记 mysql_close: 当大量的connect之后,就会出现Too many connections的错误,mysql默认的连接为100个,而什么情况下会出现这种错误呢? 正常的mysql_connect 之后调用 mysql_close()关闭连接 但在连接错误时,会者mysql_real_query()出现错误退出时,可能忘记mysql_close(); 所以在程序ret

  • Mysql 错误too many connections解决方案

    Mysql 错误提示too many connections,最近遇到这个错误,经过上网查资料解决了,这里记录下,帮助有需要的朋友, 解决方法是修改/etc/mysql/my.cnf,添加以下一行: set-variable = max_connections=500 或在启动命令中加上参数 max_connections=500 就是修改最大连接数,然后重启mysql.默认的连接数是100,太少了,所以容易出现如题错误. 以下是mysql.com网站的相关说明: If you get a To

  • 解决mysql 1040错误Too many connections的方法

    从官方文档知道linux上面编译安装的mysql默认的连接为100个,这样对于网站的需求来说是远远不够的. mysql官方告诉我们需要修改max_connections的值,那么我们怎么去修改呢?有两种方法 1.修改配置文件文件 修改/etc/my.cnf这个文件,在[mysqld]中新增max_connections=N,如果你没有这个文件请从编译源码中的support-files文件夹中复制你所需要的*.cnf文件为到/etc/my.cnf.我使用的是my-medium.cnf,中型服务器配

  • mysql连接数设置操作方法(Too many connections)

    mysql在使用过程中,发现连接数超了~~~~ [root@linux-node1 ~]# mysql -u glance -h 192.168.1.17 -p Enter password: ERROR 1040 (08004): Too many connections 解决办法,这也是centos7下修改mysql连接数的做法: 1)临时修改 MariaDB [(none)]> show variables like "max_connections"; +--------

  • mysql too many open connections问题解决方法

    曾经以为在my.cnf写入max_connections = 2000就可以改变mysql的最大并发量,今天查到一个命令,发现服务器的mysql最大连接数为151. 控制台,连接上mysql 复制代码 代码如下: show variables; 这条命令可以看到所有基础配置 如果单独看max_connections可以这样 复制代码 代码如下: show variables like 'max%';+----------+----+| Variable_name | Value |+------

  • mysql "too many connections" 错误 之 mysql解决方法

    解决方法是修改/etc/mysql/my.cnf,添加以下一行: set-variable = max_connections=500 或在启动命令中加上参数 max_connections=500 就是修改最大连接数,然后重启mysql.默认的连接数是100,太少了,所以容易出现如题错误. 以下是mysql.com网站的相关说明: If you get a Too many connections error when you try to connect to the mysqld serv

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

    今天中午,开发测试环境的MySQL服务报了一个too many connections的错误,从问题上看,可能是连接池被打满了,导致所有的连接都不可用了. 在这种情况下,最为直接的办法就是重新设置最大连接数,查看my.cnf文件,里面关于连接数的参数有两个,分别是: max_connections:最大连接数 max_user_connections:用户最大连接数 其中,第一个参数确定的是该实例的最大连接数,第二个参数确定的是单个用户的最大连接数. 一般的线上环境,为了保险起见,一般这两个参数

  • MySQL无法创建外键的原因及解决方法

    关联2张表时出现了无法创建外键的情况,从这个博客看到,问题出在第六点的Charset和Collate选项在表级和字段级上的一致性上.我的2张表的编码charset和collate不一致,2张表都执行执行SQL语句: alter table 表名 convert to character set utf8; 完美解决问题: ps:下面看下MySQL无法创建外键.查询外键的属性 MyISAM 和InnoDB 讲解 InnoDB和MyISAM是许多人在使用MySQL时最常用的两个表类型,这两个表类型各

  • mysql数据库中字符集乱码问题原因及解决

    前言 有的时候我们在查看数据库数据时,会看到乱码.实际上,无论何种数据库只要出现乱码问题,这大多是由于数据库字符集设定的问题. 下面我们就介绍一下,数据库的字符集的设定及乱码问题的解决. mysql数据库的字符集 直白的说,字符就像是单个的文字,编码就像是给每个文字的编号,字符集就像是字符与编码的集合,校验规则就是字符集的对应的排序规则,字符集加上对应的校验规则就是语言.(每种字符集可以有多种校对规则,但都有一个默认的校对规则) mysql数据库可以通过设定字符集,来使用对应的字符集和检验规则来

  • MySQL忘记root密码错误号码1045的解决办法

    停止MySQL服务 Windows可以右键我的电脑--管理--服务和应用程序--服务--找到对应的服务停止掉 免密登录 切换到MySQL安装路径下:D:\mysql-5.7.24-winx64\bin:如果已经配了环境变量,则不用再安装目录 打开CMD命令窗口,在命令行输入:mysqld -nt --skip-grant-table 8.0以上数据库输入:mysqld --console --skip-grant-tables --shared-memory 进入数据库 以管理员身份重新启动一个

  • MySQL sql_mode修改不生效的原因及解决

    前言 近期多次聊到sql_mode的话题,也是多次遇到相关问题,今天就趁热打铁,再给大家带来一个sql_mode的案例分享. 场景模拟 基于业务敏感性的考虑,下面涉及的表.存储过程等均非真实数据,但并不影响排查过程. (1)客户侧开发童鞋创建了一个存储过程,该存储过程没有严格遵守group by标准语法 session 1: mysql> delimiter // mysql> create procedure test_for_group_by() -> begin -> sel

  • Nginx 403 forbidden错误的原因以及解决方法

    目录 一.由于启动用户和nginx工作用户不一致所致 1.1查看nginx的启动用户,发现是nginx,而为是用root启动的 1.2将nginx.config的user改为和启动用户一致, 1.3 也可以使用如下命令直接查看 二.缺少index.html,就是配置文件中index index.html index.htm这行中的指定的文件. 三.权限问题,如果nginx没有web目录的操作权限,也会出现403错误. 总结 一.由于启动用户和nginx工作用户不一致所致 1.1查看nginx的启

  • Python编写的com组件发生R6034错误的原因与解决办法

    解决该问题的方法可以为调用本程序的exe文件建立一个合适的manifest文件,指定正确的msvcr90.dll版本即可,具体可参照http://www.jb51.net/article/35219.htm ps:可以使用mt.exe进行导出或合并manifest资源到exe或者dll文件. 查看manifest的方法:mt -inputresource:pythoncom27.dll;#2 -out:sss.txt 合并manifest到dll的方法: mt.exe –manifest MyA

  • 出现“不能执行已释放的Script代码”错误的原因及解决办法

    很多web开发者或许都遇到过这样的问题,程序莫名奇怪出现"不能执行已释放Script的代码",错误行1,列1.对于这种消息描述不着边,行列描述更是乱写的js错误,相信是所有调试js程序的朋友们最郁闷也最憎恨的事情!遇到这种问题,最简单的办法直接把错误贴到baidu或google那个小输入框中让他们先给点指点,再去解决,可这次却没有这么容易,在baidu搜索似乎很多人都遇到过这种问题,但是都是些什么qq空间错误,狂晕,或者就是只有问没有答的,闷! 这种错误从何调起?后面没办法,又是猜测又

  • “不能执行已释放的Script代码”错误的原因及解决办法

    很多web开发者或许都遇到过这样的问题,程序莫名奇怪出现"不能执行已释放Script的代码",错误行1,列1.对于这种消息描述不着边,行列描述更是让人迷茫的js错误,相信是所有调试js程序的朋友们最郁闷也最憎恨的事情!遇到这种问题,最简单的办法直接把错误贴到baidu或google那个小输入框中让他们先给点指点,再去解决,可这次却没有这么容易,在baidu搜索似乎很多人都遇到过这种问题,但是都是些什么qq空间错误,狂晕,或者就是只有问没有答的,闷! 这种错误从何调起?后面没办法,又是猜

随机推荐