今天突然接到個(gè)問(wèn)題，網(wǎng)頁(yè)報(bào)錯(cuò)：503 Service Temporarily Unavailable。經(jīng)過(guò)查詢(xún)發(fā)現(xiàn)是某個(gè)用戶(hù)的連接超級(jí)多，已經(jīng)將數(shù)據(jù)庫(kù)連接占滿(mǎn)。處理方案，即時(shí)殺掉堵塞的進(jìn)程，之后可以擴(kuò)大max_connections參數(shù)。
二、處理方法
1.查詢(xún)連接情況
-
root@localhost > show processlist;
-
…...
-
1001 rows in set (0.00 sec)
-
root@localhost > show variables like '%proces%';
-
Empty set (0.00 sec)
2.檢查參數(shù)
-
root@localhost > show global status like 'Max_used_connections';
-
+----------------------+-------+
-
| Variable_name | Value |
-
+----------------------+-------+
-
| Max_used_connections | 1001 |
-
+----------------------+-------+
-
1 row in set (0.00 sec)
3.通過(guò)命令生成殺進(jìn)程腳本
-
root@localhost > select concat('KILL ',id,';') from information_schema.processlist where user=’sam' into outfile '/tmp/a.txt
腳本內(nèi)容如下：
-
+------------------------+
-
| concat('KILL ',id,';') |
-
+------------------------+
-
| KILL 31964612; |
-
| KILL 31964609; |
-
| KILL 31964611; |
-
…...
-
| KILL 31966619; |
-
| KILL 31966620; |
-
+------------------------+
-
991 rows in set (0.02 sec)
-
root@localhost >
4.執(zhí)行上面生成的KILL腳本
-
root@localhost > source /tmp/a.txt
-
Query OK, 0 rows affected (0.00 sec)
-
Query OK, 0 rows affected (0.00 sec)
-
……
5.檢查連接狀況，恢復(fù)正常
-
root@localhost > show processlist;
6.修改Max_used_connections參數(shù)（注：記得要修改my.cnf文件，下次重啟動(dòng)后仍然有效）
-
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)
三、總結(jié)
Mysql的參數(shù)學(xué)習(xí)之max_connections，一個(gè)控制連接數(shù)的參數(shù)。此問(wèn)題背后肯定存在著某些問(wèn)題，不要只是一味地調(diào)大參數(shù)。后來(lái)經(jīng)過(guò)對(duì)語(yǔ)句的分析，最終此問(wèn)題定位為安全部門(mén)在做安全測(cè)試，導(dǎo)致問(wèn)題產(chǎn)生。2017年只剩下最后1周了，提前祝大家元旦快樂(lè)。Happy every day.
