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

   今天中午，開發(fā)測試環(huán)境的MySQL服務(wù)報了一個too many connections的錯誤，從問題上看，可能是連接池被打滿了，導(dǎo)致所有的連接都不可用了。

   在這種情況下，最為直接的辦法就是重新設(shè)置最大連接數(shù)，查看my.cnf文件，里面關(guān)于連接數(shù)的參數(shù)有兩個，分別是：

max_connections:最大連接數(shù)

max_user_connections:用戶最大連接數(shù)

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

   一般的線上環(huán)境，為了保險起見，一般這兩個參數(shù)不能設(shè)置為相等，可以將max_user_connections參數(shù)設(shè)置的稍微小一點(diǎn)，留有一定的余量，這樣可以防止單個用戶占用完所有的連接池。

    看到上面這個問題，我的第一反應(yīng)是，登陸上去，kill掉一些連接，然而，當(dāng)我想辦法登陸的時候，發(fā)現(xiàn)已經(jīng)沒法登陸了，連DBA的管理賬號dba_admin都已經(jīng)無法建立連接了。查看錯誤日志：

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，幸虧是開發(fā)環(huán)境，這樣的話，我們可以使用停止數(shù)據(jù)庫服務(wù)的方法來進(jìn)行重啟，然后重新增加最大連接數(shù)，當(dāng)然，這個增加的量需要考量，一般情況下，需要跟wait_timeout的值結(jié)合起來設(shè)置，也就是等待超時時間，一般情況下，如果wait_timeout的值比較大，往往會造成連接數(shù)比較多的情況，而一個連接會消耗大約2M左右的內(nèi)存，如果你設(shè)置的連接比較多，很可能出現(xiàn)內(nèi)存耗盡的情況，而wait_timeout的值如果設(shè)置的比較小，連接會不停的創(chuàng)建和銷毀，這樣會浪費(fèi)一定的IO資源。

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

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

   除此之外，官方文檔上還說了一個特點(diǎn)，挺好的，就是

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. 

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

     然而，實(shí)際應(yīng)用中，我們經(jīng)常為了操作方便，而給一些不必要的賬號分配super權(quán)限，導(dǎo)致?lián)碛衧uper權(quán)限的普通用戶占用了多余的這一個連接，在今天這個問題中，這樣的設(shè)置卻無濟(jì)于事，還是無法登陸，因?yàn)?，畢竟一個連接太少了，如果有其他的賬號已經(jīng)占用了個這個super的連接，那么這個實(shí)例還是無法訪問。

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

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

    以上所述，是Percona-Server官網(wǎng)上的描述，可以通過另外2個參數(shù)來實(shí)現(xiàn)連接池占用完之后的登陸問題：

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)

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

 但是需要注意的是，在Percona Server 8.0.14及更新版本中已刪除extra_port，  因?yàn)镸ySQL社區(qū)已實(shí)現(xiàn)了admin_port，它復(fù)制了此功能。因此，請確保在升級到Percona Server 8.0.14時編輯my.cnf，如果已經(jīng)在那里定義了extra_port，還請及時更新！

   最后我們看看連接的情況吧，在這個例子中，我們將extra_port設(shè)置成為了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.

mysql--dba_admin@127.0.0.1:(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.

mysql--dba_admin@127.0.0.1:(none) 22:41:05>>

簡單總結(jié)一下

• 如果使用MySQL官方版本：

   發(fā)生連接數(shù)超過最大值的情況的時候，首先使用super全新的賬戶進(jìn)行登陸，查看是否可以直接登陸，如果不行的話，那就只能停服務(wù)，然后重新設(shè)置連接數(shù)，在重啟服務(wù)。

• 如果使用Percona-Server或者M(jìn)ariaDB：

   可以嘗試提前配置extra_port來防止出現(xiàn)連接數(shù)被占滿的情況。如果一旦發(fā)生了這種情況而沒有提前進(jìn)行配置，那么在停止服務(wù)之后最好進(jìn)行補(bǔ)充，以防止此類情況再次發(fā)生。

以上就是MySQL too many connections錯誤的原因及解決的詳細(xì)內(nèi)容，更多關(guān)于MySQL too many connections錯誤的資料請關(guān)注腳本之家其它相關(guān)文章！