Shulou(Shulou.com)06/01 Report--

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.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.