[Mysql] excessive number of connections, emergency treatment
Problem description Today I suddenly received a problem. The web page reported an error: 503 Service Temporarily Unavailable. After the query, it is found that a user has so many connections that the database connection has been filled up. The solution is to kill the blocked process immediately, and then you can expand the max_connections parameter.
Second, the treatment method 1. Query the connection
Root@localhost > show processlist
... ...
1001 rows in set (0.00 sec)
Root@localhost > show variables like'% proces%'
Empty set (0.00 sec)
two。 Check parameters
Root@localhost > show global status like 'Max_used_connections'
+-+ +
| | Variable_name | Value |
+-+ +
| | Max_used_connections | 1001 | |
+-+ +
1 row in set (0.00 sec)
3. Generate a kill process script through a command
Root@localhost > select concat ('KILL', id,';') from information_schema.processlist where user='sam' into outfile'/ tmp/a.txt
The script reads as follows:
+-- +
| | concat ('KILL', id,';') | |
+-- +
| | KILL 31964612; | |
| | KILL 31964609; | |
| | KILL 31964611; | |
... ...
| | KILL 31966619; | |
| | KILL 31966620; | |
+-- +
991 rows in set (0.02 sec)
Root@localhost >
4. Execute the KILL script generated above
Root@localhost > source / tmp/a.txt
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
……
5. Check the connection status and return to normal
Root@localhost > show processlist
6. Modify Max_used_connections parameters (Note: remember to modify the my.cnf file, which will still be valid after the next reboot)
Mysql > set GLOBAL max_connections=2000
Query OK, 0 rows affected (0.00 sec)
Mysql > show variables like'% max_connections%'
+-+ +
| | Variable_name | Value |
+-+ +
| | max_connections | 2000 | |
+-+ +
1 row in set (0.00 sec)
Third, summarize the parameter learning max_connections of Mysql, a parameter that controls the number of connections. There must be some problems behind this problem, so don't just adjust the parameters blindly. Later, after the analysis of the statement, the final positioning of this problem is that the security department is doing security testing, resulting in the problem. There is only one week left in 2017. I wish you all a happy New Year's Day in advance. Happy every day.
