MySQL too many connections錯誤的原因及解決

Posted on by

   今天中午，開發測試環境的MySQL服務報瞭一個too many connections的錯誤，從問題上看，可能是連接池被打滿瞭，導致所有的連接都不可用瞭。

   在這種情況下，最為直接的辦法就是重新設置最大連接數，查看my.cnf文件，裡面關於連接數的參數有兩個，分別是：

max_connections:最大連接數

max_user_connections:用戶最大連接數

其中，第一個參數確定的是該實例的最大連接數，第二個參數確定的是單個用戶的最大連接數。

   一般的線上環境，為瞭保險起見，一般這兩個參數不能設置為相等，可以將max_user_connections參數設置的稍微小一點，留有一定的餘量，這樣可以防止單個用戶占用完所有的連接池。

    看到上面這個問題，我的第一反應是，登陸上去，kill掉一些連接，然而，當我想辦法登陸的時候，發現已經沒法登陸瞭，連DBA的管理賬號dba_admin都已經無法建立連接瞭。查看錯誤日志：

2019-08-12T06:02:42.928412Z 0 [Warning] Too many connections
2019-08-12T06:02:42.930181Z 0 [Warning] Too many connections
2019-08-12T06:02:44.595199Z 0 [Warning] Too many connections
2019-08-12T06:02:44.597160Z 0 [Warning] Too many connections
2019-08-12T06:02:44.782042Z 0 [Warning] Too many connections

   全部都是too many connections，幸虧是開發環境，這樣的話，我們可以使用停止數據庫服務的方法來進行重啟，然後重新增加最大連接數，當然，這個增加的量需要考量，一般情況下，需要跟wait_timeout的值結合起來設置，也就是等待超時時間，一般情況下，如果wait_timeout的值比較大，往往會造成連接數比較多的情況，而一個連接會消耗大約2M左右的內存，如果你設置的連接比較多，很可能出現內存耗盡的情況，而wait_timeout的值如果設置的比較小，連接會不停的創建和銷毀，這樣會浪費一定的IO資源。

  所以，如何得到一個平衡的值成為關鍵，在MySQL官方文檔中有如下解釋：

   從官方文檔的這個說明來看，幾個GB的內存完全可以支持你把連接數設置在500-1000之間，事實上，這個區間的值能夠滿足大部分的需求。

   除此之外，官方文檔上還說瞭一個特點，挺好的，就是

mysqld actually permits max_connections + 1 client connections. The extra connection is reserved for use by accounts that have the SUPER privilege. By granting the privilege to administrators and not to normal users (who should not need it), an administrator who also has the PROCESS privilege can connect to the server and use SHOW PROCESSLIST to diagnose problems even if the maximum number of unprivileged clients are connected. 

   翻譯過來就是事實上，允許的最大連接數是max_connections的值+1個連接，最後多的這個鏈接，是給super權限的用戶用的，這樣在連接被耗盡的時候，可以使用super權限登陸上去，進行show processlist的方法來查看連接，並kill掉一些不需要的連接，讓服務重新可用。

     然而，實際應用中，我們經常為瞭操作方便，而給一些不必要的賬號分配super權限，導致擁有super權限的普通用戶占用瞭多餘的這一個連接，在今天這個問題中，這樣的設置卻無濟於事，還是無法登陸，因為，畢竟一個連接太少瞭，如果有其他的賬號已經占用瞭個這個super的連接，那麼這個實例還是無法訪問。

   這樣的情況下，除瞭重啟mysql實例，修改my.cnf文件中的最大連接數，有沒有不停止服務而進行處理方法呢，答案是有的。但是有一個前提，你的服務要是Percona-server的，如果你使用的官方版本的MySQL，那這個辦法是行不通的，據說MariaDB也可以使用這個辦法，沒有測試過，不太懂，有興趣的可以測一測。

   從Percona Server 5.5.29開始，您可以簡單地添加extra_port到您的my.cnf，並且下次重新啟動時，端口將變為可用，並將偵聽與常規連接相同的bind_address。如果未設置extra_port變量，則默認情況下不會有其他端口可用。您還可以定義extra_max_connections此端口將處理的連接數。此默認值為1。

    以上所述，是Percona-Server官網上的描述，可以通過另外2個參數來實現連接池占用完之後的登陸問題：

mysql:(none) 22:12:51>>show variables like '%extra%';
+----------------------------------+-------+
| Variable_name                    | Value |
+----------------------------------+-------+
| extra_max_connections            | 10    || extra_port                       | 43130 |
+----------------------------------+-------+
2 rows in set (0.00 sec)

   就是這兩個參數，一個是extra_max_connections，另外一個是extra_port參數，這兩個參數使我們擁有額外的連接和額外的端口去連接服務器，從而使用show processlist觀察連接情況，殺掉一些不必要的連接，使得服務器重新可用。

 但是需要註意的是，在Percona Server 8.0.14及更新版本中已刪除extra_port，  因為MySQL社區已實現瞭admin_port，它復制瞭此功能。因此，請確保在升級到Percona Server 8.0.14時編輯my.cnf，如果已經在那裡定義瞭extra_port，還請及時更新！

   最後我們看看連接的情況吧，在這個例子中，我們將extra_port設置成為瞭43130：

[root@ log]# /usr/local/mysql/bin/mysql -udba_admin -p -h127.0.0.1 -P4313
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 71920
Server version: 5.7.16-10-log Percona Server (GPL), Release 10, Revision a0c7d0d

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

[email protected]:(none) 22:40:59>>exit
Bye

---------------------------------------------------------------------------

[root@ log]# /usr/local/mysql/bin/mysql -udba_admin -p -h127.0.0.1 -P43130
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 71941
Server version: 5.7.16-10-log Percona Server (GPL), Release 10, Revision a0c7d0d

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

[email protected]:(none) 22:41:05>>

簡單總結一下

  • 如果使用MySQL官方版本：

   發生連接數超過最大值的情況的時候，首先使用super全新的賬戶進行登陸，查看是否可以直接登陸，如果不行的話，那就隻能停服務，然後重新設置連接數，在重啟服務。

  • 如果使用Percona-Server或者MariaDB：

   可以嘗試提前配置extra_port來防止出現連接數被占滿的情況。如果一旦發生瞭這種情況而沒有提前進行配置，那麼在停止服務之後最好進行補充，以防止此類情況再次發生。

以上就是MySQL too many connections錯誤的原因及解決的詳細內容，更多關於MySQL too many connections錯誤的資料請關註WalkonNet其它相關文章！

推薦閱讀：

    None Found