Solution for "Too connector connections" prompt in MySQL

Today, the MySQL on the production server encountered a strange error "Too connector connections ". I usually encounter this problem. I basically modify the max_connections parameter of/etc/My. CNF and then restart the database. However
Databases on production servers cannot be restarted at will.

No way, you have to find a way to manually release some useless connections.
Log on to the MySQL prompt and run the "show processlist" command to obtain the MySQL connection to 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>

Then, you can see the Mysql Data Connection list like above, and each one will have a process ID (in the first column of the table above ). Run the following command:

Mysql> kill 1180421;
Query OK, 0 rows affected (0.00 Sec)

Mysql>

1180421 indicates the process number you have found in the Process List and want to kill.

The cause of this problem is:

The number of connections exceeds the value set by MySQL. It is related to max_connections and wait_timeout. The larger the value of wait_timeout, the longer the idle waiting time for the connection, which leads to the larger number of current connections.

Solution:

Modify the MySQL configuration file/etc/My. CNF and set it to max_connections = 1000 and wait_timeout = 5. If you do not have this setting, you can add it by yourself. After modification, restart the MySQL service. If this error is not reported frequently, the overall performance of the server should be optimized.

 

Note:

To prevent logon failures during too connector connections, MySQL Manual provides the following instructions:

Mysqld actually allowsmax_connections+1Clients to connect. The extra connection is reserved for use by accounts that haveSUPERPrivilege.
By grantingSUPERPrivilege to administrators and not to normal users (who shocould not need it), an administrator can connect to the server and useSHOW PROCESSLISTTo diagnose problems even if
Maximum number of unprivileged clients are connected.

Therefore, only the super permission of the root user must be granted, and the super permission cannot be granted to all database connection accounts. The error reported above cannot be logged on because the root user directly configured in our application

 

To sum up, the final solution is as follows:

1. modify the configuration file/etc/My. CNF and adjust the connection parameters.

2. Check the program code and close links that are not closed in time.