MySQL登录验证方式

前些天接到一个客户发来的信息：自建MySQL实例之后，使用 mysql -uroot@'192.168.3.6' 无法登录，密码没有问题；后面将所有其他不相干的mysql.user表数据删除后，可以正常登录。
因为客户并没有保存详细的登录及报错信息，导致完全复原客户场景。

借这个机会好好说说MySQL数据库登录验证的步骤，在登录遇到问题时提供参考。

当尝试连接到MySQL server时，服务器会根据用户名、密码来验证您的身份。如果没有，服务器将完全拒绝对您的访问。否则，服务器接受连接，然后进入等待请求阶段。

连接验证阶段

连接请求到达服务器之后，MySQL会将用户名、密码、Host参数信息与mysql.users.(user、password、host)进行验证。当完全匹配时，才允许登录。

以用户baiyang 为例，下表显示了各种组合User和Host值，涵盖了MySQL登录验证的所有情况。

上面可以发现user.user 字段值baiyang出现多次，但是对应的user.host却不重复；用户连接请求怎么与mysql.user进行匹配呢？

下面给出mysql的做法：

1：每当数据库加载mysql.user数据后，会对表进行排序

2：当客户端尝试连接时，MySQL server 端按照排序顺序进行匹配验证

3：使用第一条用户名和客户端主机名匹配的行响应客户端的请求

MySQL server 对mysql.user怎么排序的呢？

哪一行数据的mysql.host越精确，哪一行数据越在前。

如当前mysql.user的数据为：

root@localhost:test 02:51:53> select user,host from mysql.user where user = 'baiyang';
+---------+---------------+
| user    | host          |
+---------+---------------+
| baiyang | %             |
| baiyang | 127.0.0.1     |
| baiyang | 172.19.151.9 |
| baiyang | localhost     |
+---------+---------------+
4 rows in set (0.00 sec)

加载到MySQL server 后，顺序发生了变化，其结果集可以用SQL来表示：

root@localhost:(none) 02:58:30> select user,host from mysql.user where user = 'baiyang' order by host desc;
+---------+---------------+
| user    | host          |
+---------+---------------+
| baiyang | localhost     |
| baiyang | 172.19.151.9 |
| baiyang | 127.0.0.1     |
| baiyang | %             |
+---------+---------------+

不管是从本地客户端还是从远程客户端连接，都会按照上面的排序规则进行验证：匹配上的第一条用户信息响应客户端。

举个例子：分别在本地、远程客户端访问数据库，查看匹配上的用户到底是哪个。

本地连接

1：不指定 host 的连接
默认 host 为 localhost ，那么客户端是以 user='baiyang' host='localhost' 的连接方式去请求MySQL server；mysql.user 中存在| baiyang | localhost | 的用户信息，那么将以此来做匹配。

# /mysql/bin/mysql -phahahehe -ubaiyang -e "SELECT CURRENT_USER();"
Warning: Using a password on the command line interface can be insecure.
+-------------------+
| CURRENT_USER()    |
+-------------------+
| baiyang@localhost |
+-------------------+

2：指定 host='127.0.0.1' 的连接，客户端是以 user='baiyang' host='127.0.0.1' 的连接方式去请求MySQL server；mysql.user 中存在| baiyang | 127.0.0.1 | 的用户信息，那么将以此来做匹配。

# /mysql/bin/mysql -phahahehe -ubaiyang -h 127.0.0.1 -e "SELECT CURRENT_USER();"
Warning: Using a password on the command line interface can be insecure.
+-------------------+
| CURRENT_USER()    |
+-------------------+
| baiyang@127.0.0.1 |
+-------------------+

远程连接

MySQL Server所在服务器、远程客户端均是阿里云ECS服务器
有公网和内网两个IP

MySQL Server mysql.user表中有

+---------+---------------+
| user    | host          |
+---------+---------------+
| baiyang | 172.19.151.94 |
| baiyang | %             |
+---------+---------------+

两个用于远程连接的用户信息，远程登录用户认证方式如下：

1：139.224.x.x 是 MySQL Server的公网IP，当使用公网IP进行连接时，客户端的IP同为公网IP 101.132.x.x ，该IP在mysql.user没有精确匹配的host，所以认证用户为 baiyang@%

# mysql -h139.224.13.72 -ubaiyang -phahahehe -e "SELECT CURRENT_USER();" -P3308
+----------------+
| CURRENT_USER() |
+----------------+
| baiyang@%      |
+----------------+

2：172.19.65.170 是MySQL Server的内网IP，因此客户端的IP同为内网IP 172.19.151.9，该IP在mysql.user有精确匹配的host，所以认证用户为 baiyang@baiyang@172.19.151.9

# mysql -h172.19.65.170 -ubaiyang -phahahehe -e "SELECT CURRENT_USER();" -P3308
+-----------------------+
| CURRENT_USER()        |
+-----------------------+
| baiyang@172.19.151.9 |
+-----------------------+

以上，只要密码是正确的，就能连接的上数据库；若连接有问题，可以根据报错信息、MySQL用户登录验证方式来排查问题。