MySQL 8.0新特性 — 管理端口的使用簡介
前言
下面這個報錯，相信大多數童鞋都遇見過；那麼碰到這個問題，我們應該怎麼辦呢？在MySQL 5.7及之前版本，出現“too many connection”報錯，超級用戶root也無法登錄上去，除瞭重啟實例，沒有其他更好的解決辦法；不過在MySQL 8.0版本中，是對連接管理做瞭一些優化，下面我們就來看一下。
ERROR 1040 (HY000): Too many connections
連接管理
在MySQL 8.0版本中，對連接管理這一塊，是先後做瞭兩個比較大的改變：一個是允許額外連接，另一個是專用的管理端口。
額外連接
在MySQL 8.0版本中，在當前連接數達到最大連接數時，服務端允許1個額外連接，可以讓具有CONNECTION_ADMIN權限的用戶連接進來，下面簡單測試一下。
（1）為瞭方便測試，先調整最大連接數
mysql> set global max_connections=3; Query OK, 0 rows affected (0.00 sec)
（2）多開幾個會話，以達到最大連接數
mysql> show processlist; +----+-----------------+-----------------+------+---------+--------+------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-----------------+-----------------+------+---------+--------+------------------------+------------------+ | 15 | event_scheduler | localhost | NULL | Daemon | 154190 | Waiting on empty queue | NULL | | 54 | root | localhost | NULL | Query | 0 | starting | show processlist | | 55 | test | 127.0.0.1:59120 | NULL | Sleep | 19 | | NULL | | 56 | test | 127.0.0.1:59136 | NULL | Sleep | 9 | | NULL | +----+-----------------+-----------------+------+---------+--------+------------------------+------------------+ 4 rows in set (0.00 sec) mysql> show global status like 'threads_connected'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Threads_connected | 3 | +-------------------+-------+ 4 rows in set (0.01 sec)
（3）普通用戶test嘗試連接，報錯too many connections
$ mysql -utest -p -h127.0.0.1 -P10080 Enter password: ERROR 1040 (08004): Too many connections
（4）超級用戶root嘗試連接成功
$ mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 60 Server version: 8.0.20 MySQL Community Server - GPL Copyright (c) 2000, 2020, 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>
（5）再次查看當前連接數，為max_connections+1
+----+-----------------+-----------------+------+---------+--------+------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-----------------+-----------------+------+---------+--------+------------------------+------------------+ | 15 | event_scheduler | localhost | NULL | Daemon | 155064 | Waiting on empty queue | NULL | | 54 | root | localhost | NULL | Query | 0 | starting | show processlist | | 55 | test | 127.0.0.1:59120 | NULL | Sleep | 893 | | NULL | | 56 | test | 127.0.0.1:59136 | NULL | Sleep | 883 | | NULL | | 60 | root | localhost | NULL | Sleep | 141 | | NULL | +----+-----------------+-----------------+------+---------+--------+------------------------+------------------+ 5 rows in set (0.00 sec) mysql> show global status like 'threads_connected'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Threads_connected | 4 | +-------------------+-------+ 4 rows in set (0.00 sec)
（6）超級用戶root再次嘗試連接，也報錯too many connections
$ mysql -uroot -p Enter password: ERROR 1040 (HY000): Too many connections
通過上面測試可知，在MySQL 8.0中，允許的連接數為max_connections+1，其中這1個額外連接，隻允許具有CONNECTION_ADMIN權限的用戶使用。通過這1個額外連接，DBA可以使用超級用戶root連接，進行kill會話等管理操作，以避免直接重啟實例，降低成本，提高效率。
管理端口
額外連接，在一定程度上，提供瞭出現too many connection問題時的臨時解決手段，但額外數量隻有1個，難免會有一些意外，出現類似”連接被搶用”、“終端異常掉線”等情況。因此，在MySQL 8.0.14版本中，又推出瞭一個非常重要的新特性——管理端口；它允許具有SERVICE_CONNECTION_ADMIN權限的用戶，通過特定的IP和PORT連接上來，且沒有連接數限制。
（1）先介紹下相關參數
admin_address：監聽IP地址 admin_port：監聽端口 create_admin_listener_thread：是否創建一個單獨的線程來監聽管理連接
（2）通過配置上述參數，即可啟用管理端口
mysql> show global variables like 'admin%'; +---------------+-----------+ | Variable_name | Value | +---------------+-----------+ | admin_address | 127.0.0.1 | | admin_port | 33062 | +---------------+-----------+ 2 rows in set (0.00 sec) # netstat -lntp | grep 33062 tcp 0 0 127.0.0.1:33062 0.0.0.0:* LISTEN 20042/mysqld
（3）接下來進行測試
mysql> show processlist; +----+-----------------+-----------------+------+---------+--------+------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-----------------+-----------------+------+---------+--------+------------------------+------------------+ | 15 | event_scheduler | localhost | NULL | Daemon | 168750 | Waiting on empty queue | NULL | | 54 | root | localhost | NULL | Query | 0 | starting | show processlist | | 55 | test | 127.0.0.1:59120 | NULL | Sleep | 14579 | | NULL | | 56 | test | 127.0.0.1:59136 | NULL | Sleep | 14569 | | NULL | +----+-----------------+-----------------+------+---------+--------+------------------------+------------------+ 4 rows in set (0.00 sec) mysql> show global status like 'threads_connected'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Threads_connected | 3 | +-------------------+-------+ 1 row in set (0.00 sec)
（4）普通用戶test嘗試連接，報錯too many connections
$ mysql -utest -p -h127.0.0.1 -P10080 Enter password: ERROR 1040 (08004): Too many connections
（5）超級用戶root嘗試通過管理端口連接成功
$ mysql -uroot -p -h127.0.0.1 -P33062 Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 62 Server version: 8.0.20 MySQL Community Server - GPL Copyright (c) 2000, 2020, 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>
（6）繼續多開幾個會話，使用超級用戶root，通過管理端口連接成功，不受最大連接數max_connections限制
mysql> show processlist; +----+-----------------+-----------------+------+---------+--------+------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-----------------+-----------------+------+---------+--------+------------------------+------------------+ | 15 | event_scheduler | localhost | NULL | Daemon | 169035 | Waiting on empty queue | NULL | | 54 | root | localhost | NULL | Query | 0 | starting | show processlist | | 55 | test | 127.0.0.1:59120 | NULL | Sleep | 14864 | | NULL | | 56 | test | 127.0.0.1:59136 | NULL | Sleep | 14854 | | NULL | | 62 | root | 127.0.0.1:47660 | NULL | Sleep | 151 | | NULL | | 63 | root | 127.0.0.1:47760 | NULL | Sleep | 52 | | NULL | | 64 | root | 127.0.0.1:47768 | NULL | Sleep | 43 | | NULL | | 65 | root | 127.0.0.1:47780 | NULL | Sleep | 35 | | NULL | | 66 | root | 127.0.0.1:47790 | NULL | Sleep | 24 | | NULL | | 67 | root | 127.0.0.1:47800 | NULL | Sleep | 16 | | NULL | | 68 | root | 127.0.0.1:47808 | NULL | Sleep | 8 | | NULL | +----+-----------------+-----------------+------+---------+--------+------------------------+------------------+ 11 rows in set (0.00 sec) mysql> show global status like 'threads_connected'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Threads_connected | 10 | +-------------------+-------+ 1 row in set (0.00 sec)
可以說，有瞭管理端口這個新功能，DBA再也不用擔心too many connections的問題。
總結
在MySQL 8.0版本中，為瞭應對too many connections的場景，先後推出瞭額外連接和管理端口兩個新功能，可以讓DBA方便、快速地解決問題；不過，這始終是一個臨時應急手段，最根本的原因還是要排查應用端的配置(並發限流、SQL性能、連接池配置等等)，以徹底規避此類問題。
以上就是MySQL 8.0新特性 — 管理端口的使用簡介的詳細內容，更多關於MySQL 8.0新特性 — 管理端口的資料請關註WalkonNet其它相關文章！