mysql5.7出现大量too many connections及too many open files错误，且配置最大连接数未生效

too many connections是由于mysql配置中连接数过少，不足以支撑当前的并发数，too many open files是由于mysql open_files_limit的值大小不够。

最开始mysql日志出现的错误为too many connections，mysql配置文件已经配置了最大连接数max_connections=2000，登录到mysql中，查看mysql连接收:show processlist;  (查看详细执行信息使用：show full processlist;)，发现线程数只有500多，并没有达到配置文件中的最大配置，登录mysql查看其生效的最大连接数及mysql open_files_limit

mysql> show variables like '%max_connections%';

+-----------------+-------+

| Variable_name   | Value |

+-----------------+-------+

| max_connections |    |

+-----------------+-------+

 row in set (0.00 sec)

mysql> show variables like '%open_files_limit%';

+------------------+-------+

| Variable_name    | Value |

+------------------+-------+

| open_files_limit |   |

+------------------+-------+

 row in set (0.00 sec)

配置文件中配置的连接数2000未生效

linux一切皆文件，查看linux open files

[root@localhost work]# ulimit -a

core file size          (blocks, -c)

data seg size           (kbytes, -d) unlimited

scheduling priority             (-e)

file size               (blocks, -f) unlimited

pending signals                 (-i)

max locked memory       (kbytes, -l)

max memory size         (kbytes, -m) unlimited

open files                      (-n)

pipe size            ( bytes, -p)

POSIX message queues     (bytes, -q)

real-time priority              (-r)

stack size              (kbytes, -s)

cpu time               (seconds, -t) unlimited

max user processes              (-u)

virtual memory          (kbytes, -v) unlimited

file locks                      (-x) unlimited

open files的大小只有1024，需要调大一些，设置为65535

vim /etc/security/limits.conf

添加

* soft  nofile

* hard  nofile 

重新打开ssh窗口，登录,查看设置结果

[root@localhost ~]# ulimit -n

使mysql配置生效有如下两种方式

1、登录mysql，查看连接数，没有变化，还是214，在mysql开机启动服务文件中添加如下

vim /usr/lib/systemd/system/mysql.service

LimitNOFILE=infinity

LimitMEMLOCK=infinity

重启mysql

systemctl daemon-reload

systemctl restart mysql

查看最大连接数连接数及mysql open_files_limit,可以发现open_files_limit的大小被设置为与unlimit -n大小一致

mysql> show variables like '%open_files_limit%';

+------------------+-------+

| Variable_name    | Value |

+------------------+-------+

| open_files_limit |  |

+------------------+-------+

 row in set (0.00 sec)

mysql>  show variables like '%max_connections%';

+-----------------+-------+

| Variable_name   | Value |

+-----------------+-------+

| max_connections |   |

+-----------------+-------+

 row in set (0.00 sec)

2、如果未使用开机启动，使用mysql_safe也可以使配置生效，但open_files_limit的大小被设置为了10000

mysql> show variables like '%open_files_limit%';

+------------------+-------+

| Variable_name    | Value |

+------------------+-------+

| open_files_limit |  |

+------------------+-------+

 row in set (0.00 sec)

mysql> show variables like '%max_connections%';

+-----------------+-------+

| Variable_name   | Value |

+-----------------+-------+

| max_connections |   |

+-----------------+-------+

 row in set (0.00 sec)

总结：

调大linux系统open files

设置mysql max_connections

1、如果使用开机启动，则在service文件中添加

LimitNOFILE=infinity

LimitMEMLOCK=infinity

使配置生效，并重新启动

systemctl daemon-reload

systemctl restart mysql

2、未使用开机启动，使用mysql_safe启动(open_files_limit被设置为10000，如果不够大，可以在配置文件my.cnf中修改)

mysqld_safe --defaults-file=/etc/my.cnf &

mysql5.7出现大量too many connections及too many open files错误，且配置最大连接数未生效的相关教程结束。