MySQL账号登录ip验证机制

我们通过Mysql客户端登录MySQL服务器的时候，基本都是使用的命令 mysql -hxxx.xxx.xxx.xxx -uxxx -pxxxxxx 进行访问的。

但是我们可能会遇到这样的情况，我们明明输入的是正确的密码。但是就是登录不上，服务端却返回了

ERROR 1045 (28000): Access denied for user xxx

之类的报错，但是换一个机器使用mysql客户端就可以登录上mysql服务器。

前后两次输入的命令都是一样的，用户名和密码都是一样的。为什么一个机器可以登录MySQL服务器，另外一个却不可以呢。

唯一的区别就是使用不同的机器，难道访问MySQL服务器还是认机器的吗？

说对了，访问MySQL服务器还真的是认机器的。MySQL的账号信息是存放在mysql.user这个系统表里面的。以mysql5.7为例，查看一下mysql.user这张系统表。

发现它是一张有主键表，主键列有两个User和Host，是复合主键。因此mysql.user表里面可以是有多条记录的User列是相同。

猜测MySQL的登录认证的时候是User 和 Host 两个维度的，但是我们使用mysql客户端的时候我们只通过 -u和-p参数输入了User和Password信息。

在登录的时候，MySQL到底是使用mysql.user表的哪一行记录进行认证的呢？对应的优先级又是怎样的呢？做一个简单的试验验证一下。

在MySQL服务器（ip为 10.xxx.xxx.133）上新建多个用户，用户名相同，host和密码不同；语句如下。(注，xxx.xxx.是固定的ip前缀，如192.168.)

create user 'drs_user' identified by 'drs_pwd_0'; create user 'drs_user'@'127.0.0.1' identified by 'drs_pwd_1'; create user 'drs_user'@'localhost' identified by 'drs_pwd_2'; create user 'drs_user'@'xxx.xxx.218.181' identified by 'drs_pwd_3' create user 'drs_user'@'xxx.xxx.218.%' identified by 'drs_pwd_4'

查看mysql.user系统表如下

mysql> select user, host from mysql.user where user = 'drs_user'; +----------+-----------------+ | user | host | +----------+-----------------+ | drs_user | % | | drs_user | xxx.xxx.218.% | | drs_user | xxx.xxx.218.181 | | drs_user | 127.0.0.1 | | drs_user | localhost | +----------+-----------------+

准备三个vm作为访问MySQL的客户端，ip分别为’xxx.xxx.218.181’，‘xxx.xxx.218.42’和’xxx.xxx.219.30’.分别使用drs_user进行登录验证

在’xxx.xxx.218.181’上

> mysql -h10.xxx.xxx.133 -udrs_user -pdrs_pwd_0 mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1045 (28000): Access denied for user 'drs_user'@'xxx.xxx.218.181' (using password: YES) > > mysql -h10.xxx.xxx.133 -udrs_user -pdrs_pwd_1 mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1045 (28000): Access denied for user 'drs_user'@'xxx.xxx.218.181' (using password: YES) > > mysql -h10.xxx.xxx.133 -udrs_user -pdrs_pwd_2 mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1045 (28000): Access denied for user 'drs_user'@'xxx.xxx.218.181' (using password: YES) > > mysql -h10.xxx.xxx.133 -udrs_user -pdrs_pwd_3 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 17545 Server version: 5.7.26-log 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. mysql>

此时MySQL服务器使用的是 (drs_user | xxx.xxx.218.181)这一行进行登录认证的

在’xxx.xxx.218.42’上

> mysql -h10.xxx.xxx.133 -udrs_user -pdrs_pwd_0 mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1045 (28000): Access denied for user 'drs_user'@'xxx.xxx.218.42' (using password: YES) > > mysql -h10.xxx.xxx.133 -udrs_user -pdrs_pwd_1 mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1045 (28000): Access denied for user 'drs_user'@'xxx.xxx.218.42' (using password: YES) > > mysql -h10.xxx.xxx.133 -udrs_user -pdrs_pwd_2 mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1045 (28000): Access denied for user 'drs_user'@'xxx.xxx.218.42' (using password: YES) > > mysql -h10.xxx.xxx.133 -udrs_user -pdrs_pwd_3 mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1045 (28000): Access denied for user 'drs_user'@'xxx.xxx.218.42' (using password: YES) > > mysql -h10.xxx.xxx.133 -udrs_user -pdrs_pwd_4 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 17545 Server version: 5.7.26-log 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. mysql>

此时MySQL服务器使用的是 (drs_user | xxx.xxx.218.%)这一行进行登录认证的

在’xxx.xxx.219.30’上

>mysql -hxxx.xxx.xxx..133 -udrs_user -pdrs_pwd_0 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 17694 Server version: 5.7.26-log 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. mysql>

此时MySQL服务器使用的是 (drs_user | %)这一行进行登录认证的

参考官方文档(https://dev.mysql.com/doc/refman/5.7/en/connection-access.html)的描述：

简而言之：MySQL 对于同一个User，会根据 Host 的匹配精度按降序排列，当客户端尝试登录 MySQL 的时候，会按照顺序依次这个 User下面所有的 Host 规则，采用最小匹配直到匹配成功。

MySQL

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。