故障分析 | MySQL 管理端口登录异常排查及正确使用技巧

--创建一个单独的 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 参数为 1
mysql> 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@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
 
Copyright (c) 2000, 2023, 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> \s
--------------
mysql  Ver 8.0.33 for Linux on x86_64 (MySQL Community Server - GPL)
 
Connection id:          16
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         8.0.33 MySQL Community Server - GPL
Protocol version:       10
Connection:             Localhost via UNIX socket            --使用的socket连接
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
UNIX socket:            /data/mysql/data/3306/mysqld.sock
Binary data as:         Hexadecimal
Uptime:                 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

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

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

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

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

--创建一个单独的 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    |
+-----------+