关于mysql:MySQL-管理端口登录异常排查及正确使用技巧

本文次要记录了MySQL治理端口无奈登录的排查过程，以及预防 too many connections 的一些倡议。

作者：吕虎桥

爱可生DBA 团队成员，次要负责 DMP 平台和 MySQL 数据库的日常运维及故障解决。

本文起源：原创投稿

• 爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明起源。

背景形容

MySQL 8.0.14 版本中引入了 admin_port 参数，用于提供一个治理端口来解决 too many connections 报错。最近一套 MySQL 8.0 实例呈现 too many connections 报错，尝试通过治理端口登录，然而依然提醒该报错。跟业务部门协商之后，调大了连接数，重启数据库复原业务。为什么配置了 admin_port 却没有失效呢，带着疑难做了如下测试。

场景复现

治理端口相干参数

--创立一个独自的 listener 线程来监听 admin 的连贯申请create_admin_listener_thread    = 1           --监听地址admin_address = localhost   --监听端口，默认为 33062，也能够自定义端口admin_port = 33062         --配置好参数，重启数据库失效systemctl restart mysqld_3306 --测试 root 账号是否能够通过 33062 端口登录[root@mysql ~]# mysql -uroot -p -S /data/mysql/data/3306/mysqld.sock -P33062 -e 'select version()'Enter password:+-----------+| version() |+-----------+| 8.0.33    |+-----------+

模仿故障景象

调小 max_connections 参数，模仿呈现 too many connections 报错。

--更改 max_connections 参数为 1mysql> set global max_connections = 1; --模仿连接数被打满[root@mysql ~]# mysql -uroot -p -S /data/mysql/data/3306/mysqld.sock -e 'select version()'Enter password:ERROR 1040 (HY000): Too many connections --root 账号应用 33062 端口登录仍然报错[root@mysql ~]# mysql -uroot -p -S /data/mysql/data/3306/mysqld.sock -P33062 -e 'select version()'Enter password:ERROR 1040 (HY000): Too many connections

故障剖析

疑难

为啥连接数没打满的状况下，root 账号能够通过 33062 端口登录？

[root@mysql ~]# mysql -uroot -p -S /data/mysql/data/3306/mysqld.sock -P33062Enter password:Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 16Server version: 8.0.33 MySQL Community Server - GPL Copyright (c) 2000, 2023, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> \s--------------mysql  Ver 8.0.33 for Linux on x86_64 (MySQL Community Server - GPL) Connection id:          16Current database:Current user:           root@localhostSSL:                    Not in useCurrent pager:          stdoutUsing outfile:          ''Using delimiter:        ;Server version:         8.0.33 MySQL Community Server - GPLProtocol version:       10Connection:             Localhost via UNIX socket            --应用的socket连贯Server characterset:    utf8mb4Db     characterset:    utf8mb4Client characterset:    utf8mb4Conn.  characterset:    utf8mb4UNIX socket:            /data/mysql/data/3306/mysqld.sockBinary data as:         HexadecimalUptime:                 1 hour 6 min 54 sec Threads: 3  Questions: 25  Slow queries: 0  Opens: 142  Flush tables: 3  Open tables: 74  Queries per second avg: 0.006

socket 连贯会疏忽指定的端口，即使是指定一个不存在的端口也是能够登录的，也就是说 socket 连贯并没有通过治理端口登录，所以在连接数打满的状况下，应用 socket 登录仍然会报错。

[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    |+-----------+

登录地址

netstat 查看 33062 端口是监听在 127.0.0.1，并不是参数里边配置的 localhost。

[root@mysql ~]# netstat -nlp |grep 33062tcp        0      0 127.0.0.1:33062         0.0.0.0:*               LISTEN      2204/mysqld

查看 MySQL 官网文档发现 admin_address 反对设置为 IPv4、IPv6 或者 hostname。如果该值是主机名，则服务器将该名称解析为 IP 地址并绑定到该地址。如果一个主机名能够解析多个 IP 地址，如果有 IPv4 地址，服务器应用第一个 IPv4 地址，否则应用第一个 IPv6 地址，所以这里把 localhost 解析为了 127.0.0.1。

If admin_address is specified, its value must satisfy these requirements:

• The value must be a single IPv4 address, IPv6 address, or host name.
• The value cannot specify a wildcard address format (*, 0.0.0.0, or ::).
• As of MySQL 8.0.22, the value may include a network namespace specifier.

An IP address can be specified as an IPv4 or IPv6 address. If the value is a host name, the server resolves the name to an IP address and binds to that address. If a host name resolves to multiple IP addresses, the server uses the first IPv4 address if there are any, or the first IPv6 address otherwise.

指定 admin_address 为主机名，测试成果。

--批改 admin_address 值为主机名 mysql  vim /data/mysql/etc/3306/my.cnfadmin_address                               = mysql  --hosts 配置[root@mysql ~]# grep -i mysql /etc/hosts192.168.100.82 mysql  --重启数据库systemctl restart mysql_3306  --查看治理端口监听的地址，监听地址变更为主机名 mysql 对应的IP地址[root@mysql ~]# netstat -nlp |grep 33062tcp        0      0 192.168.100.82:33062    0.0.0.0:*               LISTEN      1790/mysqld   

再次尝试

尝试应用 127.0.0.1 地址登录。

--root 账号无奈通过 127.0.0.1 地址登录，因为没有受权 root 账号从 127.0.0.1 地址登录[root@mysql ~]# 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 --默认 root 账号只容许从 localhost 登录mysql> select user,host from mysql.user where user='root';+------+-----------+| user | host      |+------+-----------+| root | localhost |+------+-----------+

故障解决

设置 admin_address 为 127.0.0.1，并增加治理账号。

--创立一个独自的 listener 线程来监听 admin 的里连贯申请create_admin_listener_thread = 1--监听地址，倡议设置为一个固定的 IP 地址admin_address = 127.0.0.1    --监听端口，默认为 33062，也能够自定义端口admin_port = 33062  --新建治理账号create user root@'127.0.0.1' identified by 'xxxxxxxxx';grant all on *.* to root@'127.0.0.1' with grant option;flush privileges;  --测试登陆胜利[root@mysql ~]# mysql -uroot -p -h127.0.0.1 -P33062 -e 'select version()'Enter password:+-----------+| version() |+-----------+| 8.0.33    |+-----------+

MySQL 治理端口配置总结

1. 通过 admin_address 设置为固定的 IP 地址，例如 127.0.0.1，防止设置为 hostname 引起的不确定因素。
2. MySQL 部署好之后，新建能够通过 admin_address 地址登录的管理员账号，例如 root@'127.0.0.1'。

一些优化倡议

1. 最小化权限配置，除管理员之外其余账号一律不容许配置 super 或者 service_connection_admin 权限。
2. 利用端（Tomcat、JBoss 、Wildfly 等）配置数据源连接池，申明 initialSize、maxActive 属性值，管制连接数的有限增长。

3. 及时优化 SQL，避免因性能问题引起的并发操作导致数据库连接数打满。

   对于 SQLE

   爱可生开源社区的 SQLE 是一款面向数据库使用者和管理者，反对多场景审核，反对标准化上线流程，原生反对 MySQL 审核且数据库类型可扩大的 SQL 审核工具。

SQLE 获取