MYSQL8.0新特性---特权连接

数据库知多少 2021-04-26

914

MYSQL8.0新特性---特权连接

熟悉oracle数据库的DBA都知道，在很多情况下，Oracle hang导致sqlplus无法连接，从而无法获得Oracle系统和进程状态，使得定位问题缺少强有力的依据。幸运的是Oracle从10g版本开始推出了sqlplus -prelim选项，使得在数据库挂起时依然能使用sqlplus登录数据库，从而能获得数据库状态、诊断等信息。

在MYSQL数据库有没有这样的方式呢？其实从MySQL8.0.14版本开始，也提供了这样的方式，具体是怎么实现的，让我们一起来看看吧。

数据库信息

Current user: root@localhost

SSL: Not in use

Server version: 8.0.18 MySQL Community Server - GPL

Protocol version: 10

Connection: Localhost via UNIX socket

背景

使用过MySQL数据库的同学一定对“ERROR 1040 (HY000)：Too many connections”这个错误不陌生，出现超过连接报错的原因一般有两种：一种是单个用户连接数超过“max_user_connections”参数定义值；另外一种是所有前端的连接数超过“max_connections”这个参数的设定值。其实在第二种情况下，MySQL数据库还提供了一个额外连接，这个连接只允许 CONNECTION_ADMIN/SUPER权限的用户登录数据库，例如root用户，用root用户登录数据库之后，就可以进行故障定位。但是如果前端用root连接的数据库，那这种情况下我们是没有办法再使用root登录到数据库的，root登录依然会报出“ERROR 1040 (HY000): Too many connections ”的错误。有没有在这种特别的情况下依然可以正常登录到数据库进行问题的排查诊断呢？

答案是有的。这就是MySQL在8.0.14版本中引入的管理端口的功能。从MySQL的8.0.14版本开始，MySQL官方为了解决这个问题，就给数据库管理人员独立起了一个管理端口服务，这样应用用户和管理用户访问的端口进行隔离，互不影响。这样在数据库出现连接数满的情况下，DBA依然可以通过管理端口连接到数据库中进行紧急处理。

To allow for the need to perform administrative operations when max_connections are already established on the interfaces used for ordinary connections, the MySQL server permits a single administrative connection to users who have the CONNECTION_ADMIN privilege (or the deprecated SUPER privilege).
https://dev.mysql.com/doc/refman/8.0/en/administrative-connection-interface.html

实验验证

如果要启用MySQL8.0的管理端口，只需要在my.cnf配置文件中添加1个参数：

admin_address=127.0.0.1

我们在MySQL数据库的参数文件my.cnf中设置好这个参数，重启数据库，来看看效果；

root@MYSQL:[DB]((none))>show variables like '%admin%';
+---------------------------------+-----------+
| Variable_name                   | Value     |
+---------------------------------+-----------+
| admin_address                   | 127.0.0.1 |
| admin_port                      | 33062     |
| create_admin_listener_thread    | OFF       |
| log_slow_admin_statements       | ON        |
| persist_only_admin_x509_subject |           |
+---------------------------------+-----------+
5 rows in set (0.01 sec)

我们看到数据库参数admin_address已经是127.0.0.1地址了，admin_port参数是数据库默认的33062端口；

接下来我们模拟数据库上连接数被打满的情况：

调整当前连接数为一个较小的值，如3，我们使用持久化的方式去设置：

root@MYSQL:[DB]((none))>set persist max_connections=3;
Query OK, 0 rows affected (0.00 sec)
root@MYSQL:[DB]((none))>

我们重启数据库，再次查看当前的最大连接数：

root@MYSQL:[DB]((none))>show variables like '%max_connections%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| max_connections        | 3     |
| mysqlx_max_connections | 100   |
+------------------------+-------+
2 rows in set (0.00 sec)

可以看到，max_connections值已经是3了；这个时候，我们用root账号开启几个会话把连接数打满。几个会话连接到数据库之后，再有会话登录数据库已经提示1040错误了。

[mysql@Tdongkf base8018]$ mysql -uroot -p --socket=/mysql/my3307/mysql.sock
Enter password: 
ERROR 1040 (HY000): Too many connections
[mysql@Tdongkf base8018]$ 
[mysql@Tdongkf base8018]$

那我们就来使用官方推荐的方法来尝试登陆数据库，接下来就是见证奇迹的时刻：

[mysql@Tdongkf base8018]$ 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 14
Server version: 8.0.18 MySQL Community Server - GPL


Copyright (c) 2000, 2018, 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.


root@MYSQL:[DB]((none))>

哇~~，登录进去了，查看下：

root@MYSQL:[DB]((none))>show databases;
+--------------------+
| Database           |
+--------------------+
| dkf                |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)


root@MYSQL:[DB]((none))>use dkf
Database changed
root@MYSQL:[DB](dkf)>create table t (id int,name varchar(10));
Query OK, 0 rows affected (0.02 sec)
root@MYSQL:[DB](dkf)

操作完全不受影响了，该排查就排查，该调整就调整，再也不用和普通用户挤在一个端口下了。是不是瞬间感觉世界又美好了。

总之，MySQL8.0之后的版本更加方便我们对数据库进行管理了，对运维人员也越来越友好了，特权连接的方式记得在自己管理的数据库上开启，建议作为标准化配置写入到配置文件中。

完

=end=

mysql

文章转载自数据库知多少，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MYSQL8.0新特性---特权连接

完

评论