Can not connect to MySQL server. Too many connections

前两天早上上班客户的应用不能访问,连接数据库报1040 too many connections.

一,处理故障

编辑mysql配置文件
vi /etc/my.cnf
# 添加一行,如果之前有这个参数直接修改大一点即可
max_connections = 1000  
重启数据库即可  

二、引申检查

  1. max_connections参数检查
mysql> show variables like '%max_connections%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
  1. 当前使用情况
mysql> show status like  'Threads%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 32    |
| Threads_connected | 10    |
| Threads_created   | 50    |
| Threads_rejected  | 0     |
| Threads_running   | 1     |
+-------------------+-------+
5 rows in set (0.00 sec)
Threads_connected :打开的连接数.
Threads_running :激活的连接数,这个数值一般远低于connected数值.
  1. 查看历史最大使用量
show status like 'max_used_connections%';
  1. 查看当前连接详情
show full PROCESSLIST

三、参数学习

  • MySQL的max_connections参数用来设置最大连接(用户)数。每个连接MySQL的用户均算作一个连接,
  • MySQL无论如何都会保留一个用于管理员(SUPER)登陆的连接,用于管理员连接数据库进行维护操作,即使当前连接数已经达到了max_connections。因此MySQL的实际最大可连接数为max_connections+1;
  • 这个参数实际起作用的最大值(实际最大可连接数)为16384,即该参数最大值不能超过16384,即使超过也以16384为准;
  • 增加max_connections参数的值,不会占用太多系统资源。但每个连接都会被分配资源,但每个打开的连接都需要消耗资源,所以需合理配置次参数;

四、引申优化学习

大量空闲链接请求会占用内存资源,导致MySQL超过最大连接数从来无法新建连接导致“Too many connections”的错误。
用show processlist 如果经常发现MYSQL中有大量的Sleep进程,则需要 修改wait-timeout值了。

  1. interactive_timeout、wait_timeout
interactive_timeout
#mysql在关闭一个交互的连接之前所要等待的秒数 默认值 28800秒(8小时)   
wait_timeout
#mysql在关闭一个非交互的连接之前所要等待的秒数,默认值为8个小时:show variables like 'wait_timeout'

服务器关闭非交互连接之前等待活动的秒数。在线程启动时,根据全局wait_timeout值或全局 interactive_timeout值初始化会话wait_timeout值,
这两个参数必须配合使用。否则单独设置wait_timeout无效

另外需要注意,show processlist出现的结果有多个状态为sleep的线程,应该优先断开事务外空闲的连接.

  1. 如何判断哪些是事务外空闲的呢?
    要看具体的事务状态,可以查看information_schema库的innodb_trx表。
select * from information_schema.innodb_trx G

查询结果里,trx_mysql_thread_id=32,表示id=32的线程还处在事务中。

因此,如果是连接数过多,你可以优先断开事务外空闲太久的连接;如果这样还不够,再考虑断开事务内空闲太久的连接。

从服务端断开连接使用的命令是kill connection + id的命令,一个客户端处于sleep状态时,它的连接被服务端主动断开后,这个客户端并不会马上知道。直到客户端再发起下一个请求的时候,才会收到这样的报错“ERROR 2013(HY000):Lost connection to MySQL server during query”。

从数据库端主动断开连接可能是有损的,尤其是有的应用端收到这个错误后,不重新连接,而是直接用这个已经不能用的句柄重试连接。这会导致从应用端看上去,”MySQL一直没恢复“。

  1. 第二种方法:让数据库跳过权限验证阶段。

如果现在数据库确认是被连接行为打挂了,那么一种可能的做法是:让数据库跳过权限验证阶段,减少连接过程的消耗。

跳过权限验证的方法:

重启数据库,并使用–skip-grant-tables参数启动。
这样,整个MySQL会跳过所有的权限验证阶段,包括连接过程和语句执行过程在内。


在MySQL8.0版本里,如果你启用–skip-grant-tables参数,MySQL默认会把–skip-networking参数打开,表示这时候数据库只能被本地的客户端连接。可见MySQL官方对skip-grant-tables这个参数的安全问题也很重视。

文章来源于互联网:Mysql ERROR 1040: Too many connections连接数满

发表评论