442      阿裏雲  技術社區[雲棲]

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用戶登錄驗證方式來排查問題。

最後更新：2017-10-28 15:33:21

  上一篇：  阿裏雲服務器怎麼搭建sql server（即 MS SQL）？

  下一篇：  要采購阿裏雲虛擬主機/萬網空間，這些門道你必須知道！