Too many connections problem solving in MySQL

Today, my MySQL met a mistake that wasn't so strange "Too Many connections" . Normally encountered this problem, I basically modify /etc/my.cnf 's

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 MySQL at the prompt, the data Show Processlist This command can be obtained so connect to this server on the MySQL Connection:

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 parameter file my.cnf, set to Max_connections=1000,wait_timeout=5, if there is no such entry can be added by itself, the modification will restart the MySQL service can be, or frequently reported this error, the server to do overall performance optimization

Note:

to prevent the occurrence Too many connections when you can't sign in, MySQL manual as the following note:

mysqld actually allows max_connections+1 clients to connect. The extra connection isreserved for use by accounts that has the SUPER privilege. SUPER by granting the privilege to administrators and not tonormal users (who should isn't need it), an Administrat Or can connect to theserver and use to SHOW PROCESSLIST diagnose problems even if the maximum number ofunprivileged clients is connected.

so , must only give Root User's SUPER permissions, and the accounts for all database connections do not 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 parameter file my.cnf, adjust the connection parameters

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

This article is from the "12224534" blog, please be sure to keep this source http://12234534.blog.51cto.com/12224534/1950850

Too many connections problem solving in MySQL