Troubleshooting MySQL admin_port Configuration and Connection Issues

1 Background Description

MySQL 8.0.14 introduced the admin_port parameter to provide a management port for handling too many connections errors. A recent MySQL 8.0 instance reported the error, and after increasing the connection limit and restarting the database the service recovered. However, configuring admin_port did not work, prompting further testing.

2 Scenario Reproduction

Management Port Related Parameters

-- Create a dedicated listener thread for admin connections
create_admin_listener_thread    = 1

-- Listening address
admin_address = localhost

-- Listening port, default is 33062 (can be customized)
admin_port = 33062

-- Apply parameters and restart the database
systemctl restart mysqld_3306

-- Test if root can log in via port 33062
[root@mysql ~]# mysql -uroot -p -S /data/mysql/data/3306/mysqld.sock -P33062 -e 'select version()'
Enter password:
+-----------+
| version() |
+-----------+
| 8.0.33    |
+-----------+

Simulating the Fault

Reduce max_connections to trigger the too many connections error.

-- Change max_connections to 1
mysql> set global max_connections = 1;

-- Simulate saturated connections
[root@mysql ~]# mysql -uroot -p -S /data/mysql/data/3306/mysqld.sock -e 'select version()'
Enter password:
ERROR 1040 (HY000): Too many connections

-- Root login via admin_port still fails
[root@mysql ~]# mysql -uroot -p -S /data/mysql/data/3306/mysqld.sock -P33062 -e 'select version()'
Enter password:
ERROR 1040 (HY000): Too many connections

3 Fault Analysis

Question

Why can the root account log in through port 33062 when connections are not saturated?

[root@mysql ~]# mysql -uroot -p -S /data/mysql/data/3306/mysqld.sock -P33062
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 8.0.33 MySQL Community Server - GPL
...
Connection: Localhost via UNIX socket

Socket connections ignore the specified port; they always use the UNIX socket, so the admin port is bypassed.

[root@mysql ~]# netstat -nlp |grep 33063
[root@mysql ~]# mysql -uroot -p -S /data/mysql/data/3306/mysqld.sock -P33063 -e 'select version()'
Enter password:
+-----------+
| version() |
+-----------+
| 8.0.33    |
+-----------+

Login Address

Running netstat shows that port 33062 is bound to 127.0.0.1 , not the configured localhost . MySQL resolves localhost to 127.0.0.1 , which explains the discrepancy.

tcp        0      0 127.0.0.1:33062          0.0.0.0:*               LISTEN      2204/mysqld

The official documentation states that admin_address can be an IPv4, IPv6, or hostname. If a hostname resolves to multiple IPs, MySQL uses the first IPv4 address; otherwise the first IPv6 address. Hence localhost resolves to 127.0.0.1 .

Testing with Hostname

Changing admin_address to a hostname (e.g., mysql ) and updating /etc/hosts to map it to a fixed IP demonstrates the effect.

-- Modify admin_address to hostname 'mysql'
vim /data/mysql/etc/3306/my.cnf
admin_address = mysql

-- Verify hosts entry
grep -i mysql /etc/hosts
192.168.100.82 mysql

-- Restart database
systemctl restart mysql_3306

-- Verify listening address
netstat -nlp |grep 33062
tcp        0      0 192.168.100.82:33062   0.0.0.0:*               LISTEN      1790/mysqld

Attempt with 127.0.0.1

Login via 127.0.0.1 fails because the root account is only allowed from localhost :

mysql -uroot -p -h127.0.0.1 -P33062 -e 'select version()'
Enter password:
ERROR 1130 (HY000): Host '127.0.0.1' is not allowed to connect to this MySQL server

mysql> select user,host from mysql.user where user='root';
+------+-----------+
| user | host      |
+------+-----------+
| root | localhost |
+------+-----------+

4 Fault Resolution

Set admin_address to a fixed IP (e.g., 127.0.0.1 ) and create a management account that can log in via the admin port.

-- Enable admin listener thread
create_admin_listener_thread = 1

-- Fixed listening address
admin_address = 127.0.0.1

-- Listening port (default 33062)
admin_port = 33062

-- Create admin user
create user root@'127.0.0.1' identified by 'xxxxxxxxx';
grant all on *.* to root@'127.0.0.1' with grant option;
flush privileges;

-- Test successful login
mysql -uroot -p -h127.0.0.1 -P33062 -e 'select version()'
+-----------+
| version() |
+-----------+
| 8.0.33    |
+-----------+

5 Admin Port Configuration Summary

Set admin_address to a fixed IP (e.g., 127.0.0.1 ) instead of a hostname to avoid resolution ambiguities.

After MySQL deployment, create an administrator account that can log in via the admin_address (e.g., root@'127.0.0.1' ).

6 Some Optimization Suggestions

Minimize privileges: only the admin should have SUPER or SERVICE_CONNECTION_ADMIN rights.

Configure application data source pools (Tomcat, JBoss, WildFly, etc.) with appropriate initialSize and maxActive to control connection growth.

Optimize SQL statements to prevent performance issues that cause connection saturation.