MySQL 5.6 參數之 extra_port

MySQL 5.6 參數之 extra_port

一 前言
    作為MySQL DBA, 在營運MySQL的工作過程中,常常遇到Too many connections這個錯誤，這時作為資料庫維護人員都不能登陸資料庫進行維護 是多麼窘迫的事。可能有人會說可以修改設定檔，然後重啟資料庫來解決。恩，不過這個是很多種解決方案中 可用性最差的一種，本文介紹通過 5.6 新的特性--管理連接埠 來解決這個問題。
二 認識 extra_port
    MySQL 在5.6.14 版本之後引入一個參數extra_port 用來解決too many connections的問題。
percona 官方介紹:
從官方文檔介紹來看，在MySQL啟動的時候使用該參數指定一個連接埠號碼(不要和正常的資料庫服務連接埠衝突)，Percona Server會監聽來自該連接埠的請求。啟用該參數可以解決使用thread_pool特性時，由於所有的串連池worker忙於處理慢querey或者被鎖定導致DBA無法通過正常的連接埠串連DB, 以便DBA可以正常維護資料庫。
 具體用法如下:

mysql --port='extra-port-number' --protocol=tcp
Maridb 官方介紹 (和percona的介紹類似,讀者朋友可以自己翻譯）

Description: Extra port number to use for tcp-connections in a one-thread-per-connection manner. If set to 0, no other port is used. Introduced for the MariaDB 5.1 threadpool.
extra_max_connections  該參數主要是控制通過管理連接埠可以建立多少個串連。
 官方介紹:
    "This variable can be used to specify the maximum allowed number of connections plus one extra SUPER users connection on the extra_port. This can be used with the extra_port variable to access the server in case no new connections can be established due to all worker threads being busy or being locked when pool-of-threads feature is enabled."
三 extra_port 測試
  測試環境：5.6.26-74.0-log Percona Server
    參數設定：

max_connections = 1
 
extra_max_connections = 1

extra_port=13306
通過上面兩個參數，來設定整個資料庫的串連數。通過上面的參數配置,最多可以建立4個串連。
 測試過程，通過使用下面的命令，嘗試串連資料庫：
 情境1 類比Business Connectivity資料庫,在兩個不同的session下嘗試串連

mysql -uyang -pyang -h127.0.0.1 -P3306
情境2 類比DBA串連資料庫,在兩個不同的session下嘗試串連

mysql -uroot -h127.0.0.1 -P3306
情境3 類比DBA串連資料庫,在兩個不同的session下嘗試串連

mysql -S /srv/my_3306/run/mysql.sock
情境4 佔滿資料庫的所有串連數，然後使用extra_port 連接埠以root身份串連

mysql --port=13306 --protocol=tcp -uroot -h127.0.0.1
情境5 佔滿資料庫的所有串連數，然後使用extra_port 連接埠以業務身份串連

mysql --port=13306 --protocol=tcp -uyang -pyang -h127.0.0.1
情境6 這次不指定通訊協定，嘗試串連

mysql --port=13306 -uyang -pyang -h127.0.0.1
情境7 類比DBA和業務同時串連資料庫,在兩個不同的session下嘗試串連

mysql -uyang -pyang -h127.0.0.1 -P3306

mysql -S /srv/my_3306/run/mysql.sock
 
mysql -uroot -h127.0.0.1 -P3306
測試的結果
      情境1，只能串連1次，第2次報錯 ERROR 1040 (HY000): Too many connections
      情境2，只能串連2次，第3次報錯 ERROR 1040 (HY000): Too many connections
      情境3，只能串連2次，第3次報錯 ERROR 1040 (HY000): Too many connections
      情境4，可以串連2次，第3次報錯 ERROR 1040 (HY000): Too many connections
      情境5，無法串連，提示"ERROR 1040 (HY000): Too many connections"
      情境6, 在業務佔用串連池滿，以管理員可以串連2次，
      情境7  如果管理員先登陸 ，業務方無法串連； 如果業務方先登陸，管理員可以登陸一次，無法登陸第二次
      共可以串連4次。
四 總結
    1 如果不用extra_port參數，MySQL max_connection個串連全部被佔用，DBA仍可以root或者說是具有super許可權串連到DB對DB進行管理，但是只能串連一次。業務帳號只能登陸 max_connection 次。
    2 如果使用extra_port參數，MySQL max_connection個串連全部被佔用，DBA可以以管理員權限建立 extra_max_connections+1個串連。
    3 max_connections的特性
      MySQL無論如何都會保留一個用於管理員（SUPER）登陸的串連，用於管理員串連資料庫進行維護操作，即使當前串連數已經達到了max_connections。因此MySQL的實際最大可串連數為max_connections+1；這個參數實際起作用的最大值（實際最大可串連數）為16384，即該參數最大值不能超過16384，即使超過也以16384為準；增加max_connections參數的值，不會佔用太多系統資源。系統資源（CPU、記憶體）的佔用主要取決於查詢的密度、效率等；

本文永久更新連結地址：