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

时间: 2021-03-27

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 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 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 "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 open connections问题解决方法

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

PHP+MySQL高并发加锁事务处理问题解决方法

本文实例讲述了PHP+MySQL高并发加锁事务处理问题解决方法.分享给大家供大家参考,具体如下: 1.背景: 现在有这样的需求,插入数据时,判断test表有无username为'mraz'的数据,无则插入,有则提示"已插入",目的就是想只插入一条username为'mraz'的记录. 2.一般程序逻辑如下: $conn = mysqli_connect('127.0.0.1', 'root', '111111') or die(mysqli_error()); mysqli_selec

mysql登录遇到ERROR 1045问题解决方法

在Windows系统中,mysql登录的时候出现了这样的错误:ERROR 1045: Access denied for user: 'root@localhost' (Using password: YES) 从网上找了一些办法,终于得到了解决,现在记录一下具体的解决方法: 复制代码 代码如下: c:\>sc stop mysql c:\>mysqld --defaults-file="C:\MySQL\MySQLServer6.0\my.ini" --console -

Mysql 下中文乱码的问题解决方法总结

解决Mysql下中文乱码全是问号的问题 又是头疼的中文乱码问题,刚开始我一直用的本地数据库测,这没问题啊,一点问题都没,但我忘记了安卓和web用的是服务器上我搭的数据库,测了一下,果然乱码了. 试了网上不少方法,虽然不知道具体哪个方法给弄好了,就都说一下吧 第一种方法 刚开始先检查MySQL数据库,发现正如网上所说,部分编码是latin1, 是如下的样子: 果断改啊 set character_set_client=utf8; set character_set_connection=utf8;

碰到MySQL无法启动1067错误问题解决方法

今早在对一张table 创建primay key过程中发生了断电,当电脑再次启动时候,发现mysql 服务无法启动,使用 net start 提示 1067错误,折腾了2个小时无法解决,后来只能通过手工删除数据文件,日志文件,再启动服务,然后导入数据来完成. 启动服务发生1067错误: 1.删除datadir 下的用户数据库文件 和 日志文件 (ib_logfile0,ib_logfile1). 2.删除innodb表空间ibdata1 3.启动mysql服务: 4.导入数据: 5.檢查數據:

Mysql CPU占用高的问题解决方法小结

通过以前对mysql的操作经验,先将mysql的配置问题排除了,查看msyql是否运行正常,通过查看mysql data目录里面的*.err文件(将扩展名改为.txt)记事本查看即可.如果过大不建议用记事本了,容易死掉,可以用editplus等工具 简单的分为下面几个步骤来解决这个问题: 1.mysql运行正常,也有可能是同步设置问题导致 2.如果mysql运行正常,那就是php的一些sql语句导致问题发现,用root用户进入mysql管理mysql -u root -p输入密码mysql:sh

MySQL:reading initial communication packet问题解决方法

解决方法之一: 网站访问出现如题错误,经过检查my.cnf,发现innodb_buffer_pool_size = 2048M 设置过大,调整为innodb_buffer_pool_size = 1024M即可,网上也有该问题的其他解决方法,但都不能解决我的问题. Lost connection to MySQL server at 'reading initial communication packet' 错误解决 上次解决了这个问题,今天又碰到,突然失忆,又做了一番无用功后终于搞定,这次一

Can't connect to MySQL server on 'localhost' (10048)问题解决方法

解决Can't connect to MySQL server on 'localhost' (10048), 一般见于使用mysql的windows 2003服务器. 错误的出现的原因: 应用程序需要快速释放和创建新连接, 但是由于 TIME_WAIT 中存在的连接超过默认值,导致较低吞吐量. 解决方案: 和本错误密切相关的两个windows的注册表项:TcpTimedWaitDelay和MaxUserPort的值. TcpTimedWaitDelay 确定 TCP/IP 可释放已关闭连接并重

mysql使用source 命令乱码问题解决方法

今天从windows上导出一个sql执行文件,再倒入到unbutn中,结果出现乱码,折腾7-8分钟, 解决方式 在导出mysql sql执行文件的时候,指定一下编码格式: 复制代码 代码如下: mysqldump -uroot -p --default-character-set=utf8 mo(dbname) > E://xxxx.sql 导入的时候OK了 执行如下 复制代码 代码如下: mysql -u root -p --default-character-set=utf8 use dbn

mysql主从库不同步问题解决方法

遇到这样的错误如:"Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'"等或由于清数据导致主从库不同步了,解决办法如下: 先进入slave中执行:"slave stop;"来停止从库同步: 再去master中执行:"flu