MySQL Too many connections报错

MySQL 时不时出现Too many connections报错，重启MySQL就好了 但是过段时间又出现

一、解决方案：

1.修改mysql最大连接数

set global max_connections=500;

 以上是修改立即生效的，重启MySQL就会还原回去

在MySQL配置文件修改

max_connections = 400

修改完之后，重启MySQL就会永久生效 

2.设置wait_timeout和interactive_timeout

set global wait_timeout=500;

set global interactive_timeout =500;

 上面这个是修改立即生效的，重启MySQL就会还原回去

wait_timeout = 300
interactive_timeout = 300

 修改完之后，重启MySQL就会永久生效 

二、wait_timeout和interactive_timeout的区别

官网解释:https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html

interactive_timeout = 28800 wait_timeout = 28800 #这两个参数默认都是28800s，即8个小时; interactive_timeout指的是mysql在关闭一个交互的连接之前所要等待的秒数 wait_timeout指的是mysql在关闭一个非交互的连接之前所要等待的秒数 通过mysql客户端连接数据库是交互式连接，通过jdbc连接数据库是非交互式连接 https://www.cndba.cn/hbhe0316/article/22633https://www.cndba.cn/hbhe0316/article/22633

如果设置过大，则可能会产生大量的sleep进程，如果设置过小，可能会产生如下错误：

mysql> show variables like 'interactive_timeout';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    2
Current database: *** NONE ***

1.查看默认值

mysql> show variables like 'interactive_timeout';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| interactive_timeout | 28800 |
+---------------------+-------+
1 row in set (0.01 sec)

mysql> show variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+
1 row in set (0.00 sec)

2.根据生产经验需要将该参数设置为300，也就是5分钟

[root@mysql57 ~]# cat /etc/my.cnf | grep timeout
wait-timeout = 300
interactive_timeout = 300
[root@mysql57 ~]# service mysqld restart
Shutting down MySQL.... SUCCESS! 
Starting MySQL... SUCCESS! 
mysql> show variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 300   |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'interactive_timeout';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| interactive_timeout | 300   |
+---------------------+-------+
1 row in set (0.00 sec)