此篇介绍一下MySQL连接数相关的内容。

查看Mysql最大连接数设置

show variables like '%max_connections%';

+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.00 sec)

查看MySQL服务这次启动到现在，同一时刻并行连接数的最大值

show status like 'Max_used_connections';

+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 1     |
+----------------------+-------+
1 row in set (0.00 sec)

修改最大连接数设置

  1. sql命令修改，立即生效，服务器重启后失效

set global max_connections = 1000;

  1. 配置文件修改，永久生效

在 /etc/my.cnf 中添加 max_connections = 1000;

减少Sleep进程

查看当前的所有连接状态

show processlist;

+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host      | db   | Command | Time | State | Info             |
+----+------+-----------+------+---------+------+-------+------------------+
|  3 | root | localhost | NULL | Query   |    0 | init  | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)

查看timeout设置

MySQL数据库有一个属性wait_timeout就是Sleep连接的最大存活时间，默认是28800s。

show global variables like '%wait_timeout';

+--------------------------+----------+
| Variable_name            | Value    |
+--------------------------+----------+
| innodb_lock_wait_timeout | 50       |
| lock_wait_timeout        | 31536000 |
| wait_timeout             | 28800    |
+--------------------------+----------+
3 rows in set (0.00 sec)

修改timeout设置

set global wait_timeout=180;

这样可以解决报 “Too Many Connections” 的问题。