mysql 5.7 设置连接数_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 | 214 |

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

1 row in set (0.00sec)

mysql> show variables like '%open_files_limit%';+------------------+-------+

| Variable_name | Value |

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

| open_files_limit | 1024 |

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

1 row in set (0.00 sec)

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

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

[root@localhost work]# ulimit -a

corefile size (blocks, -c) 0data seg size (kbytes,-d) unlimited

scheduling priority (-e) 0

file size (blocks, -f) unlimited

pending signals (-i) 7739max locked memory (kbytes,-l) 64max memory size (kbytes,-m) unlimited

open files (-n) 1024pipe size (512 bytes, -p) 8POSIX message queues (bytes,-q) 819200real-time priority (-r) 0stack size (kbytes,-s) 8192cputime (seconds, -t) unlimited

max user processes (-u) 7739virtual memory (kbytes,-v) unlimitedfile locks (-x) unlimited

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

vim /etc/security/limits.conf

添加

* soft nofile 65535

* hard nofile 65535

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

[root@localhost ~]# ulimit -n65535

使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 | 65536 |

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

1 row in set (0.00sec)

mysql> show variables like '%max_connections%';+-----------------+-------+

| Variable_name | Value |

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

| max_connections | 2000 |

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

1 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 | 10000 |

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

1 row in set (0.00sec)

mysql> show variables like '%max_connections%';+-----------------+-------+

| Variable_name | Value |

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

| max_connections | 2000 |

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

1 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 &