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