MySQL 8.0 New Features - Introduction to the Use of Management Port

Preface
Connection Management

Additional connections
Management Port

Summarize

Preface

I believe most of you have encountered the following error message. So what should we do if we encounter this problem? In MySQL 5.7 and earlier versions, a "too many connections" error appears, and the super user root cannot log in. There is no better solution except restarting the instance. However, in MySQL 8.0, some optimizations have been made to connection management. Let's take a look.

ERROR 1040 (HY000): Too many connections

Connection Management

In MySQL 8.0, two major changes were made to connection management: one was to allow additional connections, and the other was a dedicated management port.

Additional connections

In MySQL 8.0, when the current number of connections reaches the maximum number of connections, the server allows one additional connection, allowing users with CONNECTION_ADMIN privileges to connect. Let's do a simple test.

(1) To facilitate testing, first adjust the maximum number of connections

mysql> set global max_connections=3;
Query OK, 0 rows affected (0.00 sec)

(2) Open more sessions to reach the maximum number of connections

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) Ordinary user test tries to connect and reports an error message "too many connections"

$ mysql -utest -p -h127.0.0.1 -P10080
Enter password: 
ERROR 1040 (08004): Too many connections

(4) Super user root attempts to connect successfully

$ 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) Check the current number of connections again, which is 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) The super user root tries to connect again, and the error message "too many connections" is also displayed.

$ mysql -uroot -p
Enter password: 
ERROR 1040 (HY000): Too many connections

From the above test, we can see that in MySQL 8.0, the number of connections allowed is max_connections+1, and this additional connection is only allowed to users with CONNECTION_ADMIN privileges. Through this additional connection, the DBA can use the super user root to connect and perform management operations such as killing the session to avoid directly restarting the instance, reducing costs and improving efficiency.

Management Port

To a certain extent, the extra connection provides a temporary solution to the problem of too many connections. However, there is only one extra connection, so there may be some accidents, such as "the connection is snatched away" and "the terminal is disconnected abnormally". Therefore, in MySQL 8.0.14 version, a very important new feature - management port was introduced; it allows users with SERVICE_CONNECTION_ADMIN privileges to connect through a specific IP and PORT, with no limit on the number of connections.

(1) First introduce the relevant parameters

admin_address: Listening IP address admin_port: Listening port create_admin_listener_thread: Whether to create a separate thread to listen for management connections

(2) By configuring the above parameters, the management port can be enabled

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) Next, test

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) Ordinary user test tries to connect and reports an error message "too many connections"

$ mysql -utest -p -h127.0.0.1 -P10080
Enter password: 
ERROR 1040 (08004): Too many connections

(5) The super user root attempts to connect successfully through the management port

$ 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) Continue to open several more sessions, use the super user root, connect successfully through the management port, and are not limited by the maximum number of connections 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)

It can be said that with the new management port function, DBAs no longer have to worry about the problem of too many connections.

Summarize

In MySQL 8.0, in order to deal with the scenario of too many connections, two new features, additional connections and management ports, were introduced, allowing DBAs to solve the problem conveniently and quickly; however, this is always a temporary emergency measure. The most fundamental reason is to check the configuration of the application side (concurrency limit, SQL performance, connection pool configuration, etc.) to completely avoid such problems.

The above is the detailed content of the introduction to the use of the new feature of MySQL 8.0 - management port. For more information about the new feature of MySQL 8.0 - management port, please pay attention to other related articles on 123WORDPRESS.COM!

You may also be interested in:

MySQL 8.0.24 version installation and configuration method graphic tutorial
MySQL 8.0.24 installation and configuration method graphic tutorial
Some improvements in MySQL 8.0.24 Release Note
Detailed steps for Java to connect to MySQL 8.0 JDBC (IDEA version)
Detailed analysis of MySQL 8.0 memory consumption
Detailed graphic description of MySql8.023 installation process (first installation)
MySQL 8.0 New Features - Introduction to Check Constraints
mysql8.0.23 msi installation super detailed tutorial
MySQL 8.0.23 free installation version configuration detailed tutorial
How to install mysql8.0.23 under win10 and solve the problem of "the service does not respond to the control function"
Basic operations on invisible columns in MySQL 8.0

<<: The Complete Guide to Grid Layout in CSS

>>: How to design the homepage of Tudou.com