MySQL8.0.14のリリースノートに以下の、管理用の接続が追加されたとの記載がありました。max_connectionに到達してしまい「Too many connections」が出た時の対策として活用出来ると思い、基本的な機能を確認してみました。
MySQL Server now permits a TCP/IP port to be configured specifically for administrative connections. This provides an alternative to the single administrative connection that is permitted on the network interfaces used for ordinary connections even when max_connections connections are already established. The administrative network interface has these characteristics:
Changes in MySQL 8.0.14 (2019-01-21, General Availability)
管理ネットワークインタフェースで TCP/IP 接続をリスニングする IP アドレス (セクション5.1.12.1「接続インタフェース」 を参照)。 デフォルトの admin_address 値はありません。 この変数が起動時に指定されない場合、サーバーは管理インタフェースを維持しません。 サーバーには、通常の (非管理) クライアント TCP/IP 接続を構成するための bind_address システム変数もあります。 セクション5.1.12.1「接続インタフェース」を参照してください。
admin_address が指定されている場合、その値は次の要件を満たす必要があります:
値は、単一の IPv4 アドレス、IPv6 アドレスまたはホスト名である必要があります。
値にワイルドカードアドレス書式 (*、0.0.0.0 または::) は指定できません。
MySQL 8.0.22 の時点では、値にネットワークネームスペース指定子が含まれる場合があります。
IP アドレスは、IPv4 または IPv6 アドレスとして指定できます。 値がホスト名の場合、サーバーは名前を IP アドレスに解決し、そのアドレスにバインドします。 ホスト名が複数の IP アドレスに解決される場合、サーバーは最初の IPv4 アドレス (存在する場合) または最初の IPv6 アドレスを使用します。
5.1.8 サーバーシステム変数
MySQLのマニュアルに、「mysqld では、実際には max_connections + 1 クライアント接続が許可されます。 追加の接続は、CONNECTION_ADMIN 権限 (または非推奨の SUPER 権限) を持つアカウントで使用するために予約されています。 通常のユーザー (必要ないユーザー) ではなく管理者に権限を付与することで、管理者はサーバーに接続し、権限のないクライアントの最大数が接続されている場合でも SHOW PROCESSLIST を使用して問題を診断できます。 」とあります。DBAが一人で障害対応する様なケースでは問題ないのですが、ペアで対応する場合等にはこちらの管理者用のネットワーク接続は有用になるケースもあるかと思います。
admin_address and admin_port
mysql> show variables like 'admin%';
+------------------------+-------------------------------+
| Variable_name | Value |
+------------------------+-------------------------------+
| admin_address | |
| admin_port | 33062 |
| admin_ssl_ca | |
| admin_ssl_capath | |
| admin_ssl_cert | |
| admin_ssl_cipher | |
| admin_ssl_crl | |
| admin_ssl_crlpath | |
| admin_ssl_key | |
| admin_tls_ciphersuites | |
| admin_tls_version | TLSv1,TLSv1.1,TLSv1.2,TLSv1.3 |
+------------------------+-------------------------------+
11 rows in set (0.04 sec)
基本動作 (admin_addressとadmin_portの設定無し)
Rootユーザーで最大接続数を１に変更し確認
mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 100 |
+-----------------+-------+
1 row in set (0.00 sec)
mysql> set global max_connections = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 1 |
+-----------------+-------+
1 row in set (0.00 sec)
mysql> SELECT user(),current_user();
+-----------------+----------------+
| user() | current_user() |
+-----------------+----------------+
| root@172.18.0.1 | root@% |
+-----------------+----------------+
1 row in set (0.00 sec)
「Non Rootユーザー接続」：ERROR 1040 (08004): Too many connectin
「⁺１となるRootユーザー」：接続可能
shinya@DESKTOP-8BDL7KA:~/git/rdbms-docker/mysql$ mysql -h 127.0.0.1 -u jeffrey -pPassword20221024-2
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1040 (08004): Too many connections
shinya@DESKTOP-8BDL7KA:~/git/rdbms-docker/mysql$ mysql -h 127.0.0.1 -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 8.0.27 MySQL Community Server - GPL
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
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> show processlist;
+----+-----------------+------------------+------+---------+------+------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+------------------+------+---------+------+------------------------+------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 5010 | Waiting on empty queue | NULL |
| 8 | root | 172.18.0.1:44344 | NULL | Sleep | 209 | | NULL |
| 18 | root | 172.18.0.1:44384 | NULL | Query | 0 | init | show processlist |
+----+-----------------+------------------+------+---------+------+------------------------+------------------+
3 rows in set (0.00 sec)
基本動作 (admin_addressとadmin_portの設定有り)
SET PERSISTで設定変更出来なかったので,オプションファイル(my.cnf)を直接書き換えて検証。
mysql> SELECT user(),current_user();
+-----------------+----------------+
| user() | current_user() |
+-----------------+----------------+
| root@172.18.0.1 | root@% |
+-----------------+----------------+
1 row in set (0.00 sec)
mysql> set persist admin_address='127.0.0.1';
ERROR 1238 (HY000): Variable 'admin_address' is a read only variable
mysql> set persist_only admin_address='127.0.0.1';
ERROR 1238 (HY000): Variable 'admin_address' is a non persistent read only variable
mysql>STEP1: my.cnfに以下ラインを追加してサービス再起動
- admin_addressが有効でDefaultポートの(33062)でListenされる設定になっている事を確認。
root@localhost [mysql]> set global max_connections = 1;
Query OK, 0 rows affected (0.00 sec)
root@localhost [mysql]> show variables like 'admin%';
+------------------------+-----------------+
| Variable_name | Value |
+------------------------+-----------------+
| admin_address | 127.0.0.1 |
| admin_port | 33062 |
| admin_ssl_ca | |
| admin_ssl_capath | |
| admin_ssl_cert | |
| admin_ssl_cipher | |
| admin_ssl_crl | |
| admin_ssl_crlpath | |
| admin_ssl_key | |
| admin_tls_ciphersuites | |
| admin_tls_version | TLSv1.2,TLSv1.3 |
+------------------------+-----------------+
11 rows in set (0.01 sec)。SERVICE_CONNECTION_ADMINが必要との事。(≠ CONNECTION_ADMIN）
$ mysql -h 127.0.0.1 -u jeffrey -pPassword20221024
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1040 (08004): Too many connections
$ mysql -h 127.0.0.1 -u jeffrey -pPassword20221024 -P33062
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1227 (42000): Access denied; you need (at least one of) the SERVICE_CONNECTION_ADMIN privilege(s) for this operation
オフィシャルマニュアルには、以下の様に記載されていました。
管理接続インタフェースには、次の特性があります:
サーバーは、admin_address システム変数が起動時にその IP アドレスを示すように設定されている場合にのみ、インタフェースを有効にします。 admin_address が設定されていない場合、サーバーは管理インタフェースを維持しません。
admin_port システム変数は、インタフェースの TCP/IP ポート番号 (デフォルトは 33062) を指定します。
管理接続の数に制限はありません。
接続は、SERVICE_CONNECTION_ADMIN 権限を持つユーザーにのみ許可されます。
create_admin_listener_thread システム変数を使用すると、DBA は起動時に管理インタフェースに独自のスレッドがあるかどうかを選択できます。 デフォルトは OFF です。つまり、メインインタフェース上の通常の接続のマネージャスレッドは、管理インタフェースの接続も処理します。
5.1.12.2 管理接続管理
STEP2: 権限をテストユーザーアカウントに付与し、SERVICE_CONNECTION_ADMIN 権限を付与。
root@localhost [mysql]> SHOW GRANTS for 'jeffrey'@'%';
+--------------------------------------------------+
| Grants for jeffrey@% |
+--------------------------------------------------+
| GRANT USAGE ON *.* TO `jeffrey`@`%` |
| GRANT ALL PRIVILEGES ON `POC`.* TO `jeffrey`@`%` |
+--------------------------------------------------+
2 rows in set (0.00 sec)
root@localhost [mysql]> GRANT SERVICE_CONNECTION_ADMIN on *.* to 'jeffrey'@'%';
Query OK, 0 rows affected (0.01 sec)
root@localhost [mysql]> SHOW GRANTS for 'jeffrey'@'%';
+--------------------------------------------------------+
| Grants for jeffrey@% |
+--------------------------------------------------------+
| GRANT USAGE ON *.* TO `jeffrey`@`%` |
| GRANT SERVICE_CONNECTION_ADMIN ON *.* TO `jeffrey`@`%` |
| GRANT ALL PRIVILEGES ON `POC`.* TO `jeffrey`@`%` |
+--------------------------------------------------------+
3 rows in set (0.00 sec)
root@localhost [mysql]> STEP3: 権限付与後に接続する事が出来る事を確認。
実際の運用では、調査したり接続をKillする等の権限も必要になるので管理者権限を付与しておく事になりそう。
$ mysql -h 127.0.0.1 -u jeffrey -pPassword20221024 -P33062
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1842
Server version: 8.0.30 MySQL Community Server - GPL
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
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.
jeffrey@127.0.0.1 [(none)]> SELECT user(),current_user();
+-------------------+----------------+
| user() | current_user() |
+-------------------+----------------+
| jeffrey@localhost | jeffrey@% |
+-------------------+----------------+
1 row in set (0.00 sec)