MySQL tip "Too many connections" solution

Today the production server MySQL appeared a not too unfamiliar error "Too many connections". I usually encounter this problem, I basically modify the/etc/my.cnf max_connections parameter, and then restart the database. But
The database on the production server is not restarted at random.

No way, have to find ways to manually release some useless connection.
Login to the MySQL prompt, the data show processlist This command, can get so connected to the MySQL connection on this server:

Mysql> show Processlist;
+---------+------+---------------------+---------+---------+------+-------+-------------------+
| Id | User | Host | db | Command | Time | State | Info |
+---------+------+---------------------+---------+---------+------+-------+-------------------+
| 1180421 | ur | 202.103.96.68:49754 | Test1 |    Sleep |       1 | | NULL |
| 1180427 | ur | 202.103.96.68:55079 | Test2 |    Sleep |       1 | | NULL |
| 1180429 | ur | 202.103.96.68:55187 | TESTDBA |    Sleep |       0 | | NULL |
| 1180431 | ur | 202.103.96.68:55704 | TESTDBA |    Sleep |       0 | | NULL |
| 1180437 | ur | 202.103.96.68:32825 | Test1 |    Sleep |       1 | | NULL |
| 1180469 | ur | 202.103.96.68:58073 | TESTDBA |    Sleep |       0 | | NULL |
| 1180472 | ur | 83.136.93.131:47613 | Test2 |    Sleep |       8 | | NULL |
| 1180475 | Root | localhost | NULL |    Query | 0 | NULL | Show Processlist |
+---------+------+---------------------+---------+---------+------+-------+-------------------+
8 rows in Set (0.00 sec)

Mysql>

You can then see a list of MySQL data connections like above, and each one will have a process ID number (in the first column of the table above). We just need to enter this command:

Mysql> kill 1180421;
Query OK, 0 rows Affected (0.00 sec)

Mysql>

1180421 of them are the process numbers you find in the process list and you want to kill.

This problem is caused by :

The number of connections exceeds the MySQL setting value, which is related to max_connections and wait_timeout . The larger the value of the Wait_timeout, the longer the idle wait for the connection, which results in a greater number of current connections.

Workaround :

Modify the MySQL configuration file/etc/my.cnf, set it to max_connections=1000,wait_timeout=5. If you do not have this setting, you can add it yourself and restart the MySQL service after you modify it. If you do not routinely report this error, you should optimize the server for overall performance

Note:

To prevent the issue of too many connections, MySQL manual has the following description:

mysqld actually allows max_connections+1 clients to connect. The extra connection is reserved for use by accounts that has the SUPER privilege. SUPERby granting the privilege to administrators and not to normal users (who should not need it), an administrator can C Onnect to the server and use to SHOW PROCESSLIST diagnose problems even if the maximum number of unprivileged clients is connected.

Therefore, you must give the root user only super privileges, and all database connected accounts cannot give super permissions. The previous error can not be logged in is due to our application directly configured by the root user

The final way to solve the problem is to summarize:

1. Modify the configuration file/etc/my.cnf, adjust the connection parameters

2. Check the program code and close the link without closing it in time

MySQL tip "Too many connections" solution