共计 4989 个字符,预计需要花费 13 分钟才能阅读完成。
本文次要记录了 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 参数为 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
账号能够通过 33062 端口登录?
[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
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 33062
tcp 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.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
再次尝试
尝试应用 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 治理端口配置总结
- 通过
admin_address
设置为固定的 IP 地址,例如127.0.0.1
,防止设置为hostname
引起的不确定因素。 - MySQL 部署好之后,新建能够通过
admin_address
地址登录的管理员账号,例如root@'127.0.0.1'
。
一些优化倡议
- 最小化权限配置,除管理员之外其余账号一律不容许配置
super
或者service_connection_admin
权限。 - 利用端(Tomcat、JBoss、Wildfly 等)配置数据源连接池,申明
initialSize
、maxActive
属性值,管制连接数的有限增长。 -
及时优化 SQL,避免因性能问题引起的并发操作导致数据库连接数打满。
对于 SQLE
爱可生开源社区的 SQLE 是一款面向数据库使用者和管理者,反对多场景审核,反对标准化上线流程,原生反对 MySQL 审核且数据库类型可扩大的 SQL 审核工具。
SQLE 获取
类型 | 地址 |
---|---|
版本库 | https://github.com/actiontech/sqle |
文档 | https://actiontech.github.io/sqle-docs/ |
公布信息 | https://github.com/actiontech/sqle/releases |
数据审核插件开发文档 | https://actiontech.github.io/sqle-docs-cn/3.modules/3.7_audit… |