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 规则，采用最小匹配直到匹配成功。