java后台报Too many connections

问题：

服务器打不开了，后台报com.mysql.jdbc.exceptions.MySQLNonTransientConnectionException: Too many connections。很显然数据库连接数过多..解决方向应该是去查看一下数据库的实际连接数以及数据库允许的最大连接数。看看是不是真的超标了。

最快的解决方案：重启mysql，我原来的连接数为下面的25个，重启后变成5个。

mysql> show processlist;
+----+------+-----------------+--------------+---------+------+-------+---------
---------+
| Id | User | Host            | db           | Command | Time | State | Info
         |
+----+------+-----------------+--------------+---------+------+-------+---------
---------+
|  1 | root | localhost:52454 | txman_master | Sleep   |   28 |       | NULL
         |
|  2 | root | localhost:52455 | txman_master | Sleep   |  388 |       | NULL
         |
|  3 | root | localhost:52456 | txman_master | Sleep   |  388 |       | NULL
         |
|  4 | root | localhost:52457 | txman_master | Sleep   |   28 |       | NULL
         |
|  5 | root | localhost:52462 | NULL         | Query   |    0 | NULL  | show pro
cesslist |
+----+------+-----------------+--------------+---------+------+-------+---------
---------+
5 rows in set (0.00 sec)

解决：

1.命令行查询连接状态。

C:\Users\Administrator>mysqladmin -uroot -p123456 status
Uptime: 913  Threads: 9  Questions: 214  Slow queries: 0  Opens: 17  Flush table
s: 1  Open tables: 10  Queries per second avg: 0.234

    使用root登录后，查看详细的连接

mysql> show processlist;
+----+------+-----------------+--------------+---------+------+-------+---------
---------+
| Id | User | Host            | db           | Command | Time | State | Info
         |
+----+------+-----------------+--------------+---------+------+-------+---------
---------+
|  1 | root | localhost:51111 | txman_master | Sleep   |  238 |       | NULL
         |
|  2 | root | localhost:51110 | txman_master | Sleep   |  238 |       | NULL
         |
|  3 | root | localhost:51112 | txman_master | Sleep   |  238 |       | NULL
         |
|  4 | root | localhost:51113 | txman_master | Sleep   |  238 |       | NULL
         |
|  5 | root | localhost:51114 | txman_master | Sleep   |  238 |       | NULL
         |
|  6 | root | localhost:51115 | txman_master | Sleep   |  238 |       | NULL
         |
|  7 | root | localhost:51116 | txman_master | Sleep   |  238 |       | NULL
         |
|  8 | root | localhost:51117 | txman_master | Sleep   |  238 |       | NULL
         |
| 14 | root | localhost:52039 | NULL         | Sleep   |  632 |       | NULL
         |
| 15 | root | localhost:52040 | NULL         | Sleep   |  633 |       | NULL
         |
| 18 | root | localhost:52046 | NULL         | Sleep   |  612 |       | NULL
         |
| 19 | root | localhost:52047 | NULL         | Sleep   |  612 |       | NULL
         |
| 22 | root | localhost:52140 | txman_master | Sleep   |  238 |       | NULL
         |
| 23 | root | localhost:52141 | txman_master | Sleep   |  238 |       | NULL
         |
| 21 | root | localhost:52139 | txman_master | Sleep   |  238 |       | NULL
         |
| 24 | root | localhost:52142 | txman_master | Sleep   |  238 |       | NULL
         |
| 25 | root | localhost:52144 | txman_master | Sleep   |  238 |       | NULL
         |
| 26 | root | localhost:52143 | txman_master | Sleep   |  238 |       | NULL
         |
| 27 | root | localhost:52145 | txman_master | Sleep   |  238 |       | NULL
         |
| 28 | root | localhost:52146 | txman_master | Sleep   |  238 |       | NULL
         |
| 29 | root | localhost:52147 | txman_master | Sleep   |  238 |       | NULL
         |
| 30 | root | localhost:52148 | txman_master | Sleep   |  238 |       | NULL
         |
| 31 | root | localhost:52149 | txman_master | Sleep   |    6 |       | NULL
         |
| 32 | root | localhost:52150 | txman_master | Sleep   |    6 |       | NULL
         |
| 34 | root | localhost:52333 | NULL         | Query   |    0 | NULL  | show pro
cesslist |
+----+------+-----------------+--------------+---------+------+-------+---------
---------+
25 rows in set (0.00 sec)

2.查看允许的最大连接数

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

3.

第一种方案：root登录，修改最大连接数

mysql> set global max_connections=200;
Query OK, 0 rows affected (0.00 sec)

验证修改后的结果

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

第二种方案：修改mysql的配置文件my.ini的max_connections配置项

经测试，my.ini的配置项会高于第一方案。我两种方案都是用了，最终查询变量的结果是：

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