mysql 登录验证_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 值

Host 值

允许连接

'baiyang'

'h1.example.net'

baiyang，连接 h1.example.net

''

'h1.example.net'

任何用户，连接 h1.example.net

'baiyang'

'%'

baiyang，从任何主机连接

''

'%'

任何用户，从任何主机连接

'baiyang'

'%.example.net'

baiyang，从example.net域中的任何主机连接

'baiyang'

'x.example.%'

baiyang，从连接 x.example.net， x.example.com， x.example.edu，等; 这可能没有用

'baiyang'

'172.19.65.170'

baiyang，从主机与IP地址进行连接 172.19.65.170

'baiyang'

'172.19.65.%'

baiyang，从172.19.65 C类子网中的任何主机连接

'baiyang'

'172.19.65.0/255.255.255.0'

与上一个例子相同

上面可以发现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

139.224.x.x

172.19.65.17

CLient

101.132.x.x

172.19.151.9

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用户登录验证方式来排查问题。