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

Deepin20.1 system installs MySQL8.0.23 (super detailed MySQL8 installation tutorial)

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

Table of contents

Preface

Connection Management

Additional connections

Management Port

Summarize

Deepin20.1 system installs MySQL8.0.23 (super detailed MySQL8 installation tutorial)

A brief analysis of Docker private image library and Alibaba Cloud object storage OSS

W3C Tutorial (6): W3C CSS Activities

Detailed explanation of the use of docker tag and docker push

Use CSS3 to implement button hover flash dynamic special effects code

A brief analysis of the differences between undo, redo and binlog in MySQL

Detailed explanation of JavaScript state container Redux

How to solve the abnormal error ERROR: 2002 in mysql

MySQL calculates the number of days, months, and years between two dates

Nginx configures the same domain name to support both http and https access

Recommend

How to install MySQL server community version MySQL 5.7.22 winx64 in win10

XHTML Getting Started Tutorial: XHTML Web Page Image Application

MySQL 5.6.22 installation and configuration method graphic tutorial

The main differences between MySQL 4.1/5.0/5.1/5.5/5.6

What is MIME TYPE? MIME-Types type collection

How to limit the input box to only input pure numbers in HTML

A practical record of handling the ddgs and qW3xT.2 mining viruses implanted in Linux servers

Detailed explanation of Nginx's control over access volume

A possible bug when MySQL executes the sum function on the window function

About Tomcat combined with Atomikos to implement JTA

How to restore a database and a table from a MySQL full database backup

Detailed tutorial on installing MySQL 5.7.20 on RedHat 6.5/CentOS 6.5

Solution to automatically submitting the form and jumping to other pages after pressing Enter on the web page

Detailed explanation of MySQL row locks when encountering composite primary keys and multi-column indexes

Some tips on deep optimization to improve website access speed