MySQL: ERROR 1040: Too many connections”异常情况处理

经常会遇到”MySQL: ERROR 1040: Too many connections”的异常情况，很多时原因是使用了默认最大连接数或设置过少

 

查看目前最大连接数

mysql> show variables like '%max_connections%';

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

| Variable_name | Value |

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

| max_connections | 300 |

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

1 row in set (0.00 sec)

 

方法1：使用set GLOBAL max_connections

mysql> set GLOBAL max_connections=3000;

Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%max_connections%';

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

| Variable_name | Value |

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

| max_connections | 3000 |

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

1 row in set (0.00 sec)

 

方案2:在mysql配置文件添加或修改max_connections值

vim /etc/mysql/mysql.conf.d/mysqld.cnf

max_connections=3000

此方案需重启mysql服务

 

注意：重启后最大连接数有可能还是214，需修改文件/lib/systemd/system/mysql.service添加

LimitNOFILE=65535

LimitNPROC=65535

保存后重启mysql服务即可生效

systemctl daemon-reload

systemctl restart mysql.service