本篇內容主要講解“連接錯誤ERROR 1045 (28000): Access denied for user問(wèn)題的解決方法”，感興趣的朋友不妨來(lái)看看。本文介紹的方法操作簡(jiǎn)單快捷，實(shí)用性強。下面就讓小編來(lái)帶大家學(xué)習“Mysql連接錯誤ERROR 1045 (28000): Access denied for user問(wèn)題的解決方法”吧!
從192.168.111.99上連接遠程數據的時(shí)候報錯：
[mysql@LVS01 mysql_5621]$ mysql -uroot -p123 -h292.168.111.10 -P 5621
Warning: Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'192.168.111.99' (using password: YES
查看遠程主機權限：
mysql> use mysql;
Database changed
mysql> select user,host,password from user;
+-------+----------------+-------------------------------------------+
| user | host | password |
+-------+----------------+-------------------------------------------+
| root | localhost | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| root | mysql-svr1 | |
| root | 127.0.0.1 | |
| root | ::1 | |
| repl | 192.168.110.20 | *A424E797037BF97C19A2E88CF7891C5C2038C039 |
| repl | 192.168.111.20 | *A424E797037BF97C19A2E88CF7891C5C2038C039 |
| repl | 192.168.111.30 | *A424E797037BF97C19A2E88CF7891C5C2038C039 |
| repl | 192.168.111.10 | *A424E797037BF97C19A2E88CF7891C5C2038C039 |
| user1 | % | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| root | 192.168.111.20 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| root | 192.168.111.30 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| root | 192.168.111.10 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| admin | 192.168.111.10 | *4ACFE3202A5FF5CF467898FC58AAB1D615029441 |
| root | 192.168.111.% | |
| root | 192.168.111.99 | |
| root | % | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+-------+----------------+-------------------------------------------+
16 rows in set (0.00 sec)
發(fā)現已經(jīng)給192.168.111.99授權了，可為什么還是連不上?
和192.168.111.99相關(guān)的權限是下面幾行：
| root | 192.168.111.% | |
| root | 192.168.111.99 | |
| root | % | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
嘗試使用空密碼登陸成功：
[mysql@LVS01 mysql_5621]$ mysql -uroot -h292.168.111.10 -P 5621
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 20216
Server version: 5.6.21-log Source distribution
Copyright (c) 2000, 2014, 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>
刪除密碼為空的用戶(hù)：
mysql> drop user root@'192.168.111.%';
Query OK, 0 rows affected (0.31 sec)
mysql> drop user root@'192.168.111.99';
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host,password from user;
+-------+----------------+-------------------------------------------+
| user | host | password |
+-------+----------------+-------------------------------------------+
| root | localhost | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| root | mysql-svr1 | |
| root | 127.0.0.1 | |
| root | ::1 | |
| repl | 192.168.110.20 | *A424E797037BF97C19A2E88CF7891C5C2038C039 |
| repl | 192.168.111.20 | *A424E797037BF97C19A2E88CF7891C5C2038C039 |
| repl | 192.168.111.30 | *A424E797037BF97C19A2E88CF7891C5C2038C039 |
| repl | 192.168.111.10 | *A424E797037BF97C19A2E88CF7891C5C2038C039 |
| user1 | % | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| root | 192.168.111.20 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| root | 192.168.111.30 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| root | 192.168.111.10 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| admin | 192.168.111.10 | *4ACFE3202A5FF5CF467898FC58AAB1D615029441 |
| root | % | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+-------+----------------+-------------------------------------------+
14 rows in set (0.00 sec)
mysql>
再次使用密碼登陸成功：
[mysql@LVS01 mysql_5621]$ mysql -uroot -p123 -h292.168.111.10 -P 5621
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 20312
Server version: 5.6.21-log Source distribution
Copyright (c) 2000, 2014, 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>
總結：
出現這個(gè)錯誤的原因是同一個(gè)用戶(hù)有不同主機范圍的權限時(shí)，按最匹配的一個(gè)主機驗證權限。
也就是說(shuō)從192.168.111.99登陸時(shí)，驗證順序為：
root@'192.168.111.99'
root@'192.168.111.%'
root@'%'
參考文檔：
http://dev.mysql.com/doc/refman/5.7/en/connection-access.html
When multiple matches are possible, the server must determine which of them to use. It resolves this issue as follows:
Whenever the server reads the user table into memory, it sorts the rows.
When a client attempts to connect, the server looks through the rows in sorted order.
The server uses the first row that matches the client host name and user name.
The server uses sorting rules that order rows with the most-specific Host values first. Literal host names and IP addresses are the most specific. (The specificity of a literal IP address is not affected by whether it has a netmask, so 192.168.1.13 and 192.168.1.0/255.255.255.0 are considered equally specific.) The pattern '%' means “any host” and is least specific. The empty string '' also means “any host” but sorts after '%'. Rows with the same Host value are ordered with the most-specific User values first (a blank User value means “any user” and is least specific). For rows with equally-specific Host and User values, the order is indeterminate.
建議：
（1）用戶(hù)授權時(shí)，按最小主機范圍授權，并且賦予密碼，只授權一次。
（2）如果將來(lái)主機范圍變化，重新授權時(shí)可刪除舊授權，否則可能因為兩次授權密碼不同導致類(lèi)似問(wèn)題。
免責聲明：本站發(fā)布的內容（圖片、視頻和文字）以原創(chuàng )、來(lái)自本網(wǎng)站內容采集于網(wǎng)絡(luò )互聯(lián)網(wǎng)轉載等其它媒體和分享為主，內容觀(guān)點(diǎn)不代表本網(wǎng)站立場(chǎng)，如侵犯了原作者的版權，請告知一經(jīng)查實(shí)，將立刻刪除涉嫌侵權內容，聯(lián)系我們QQ:712375056，同時(shí)歡迎投稿傳遞力量。
Copyright ? 2009-2022 56dr.com. All Rights Reserved. 特網(wǎng)科技 特網(wǎng)云 版權所有 特網(wǎng)科技 粵ICP備16109289號
域名注冊服務(wù)機構:阿里云計算有限公司（萬(wàn)網(wǎng)） 域名服務(wù)機構：煙臺帝思普網(wǎng)絡(luò )科技有限公司（DNSPod） CDN服務(wù)：阿里云計算有限公司 百度云 中國互聯(lián)網(wǎng)舉報中心 增值電信業(yè)務(wù)經(jīng)營(yíng)許可證B2
建議您使用Chrome、Firefox、Edge、IE10及以上版本和360等主流瀏覽器瀏覽本網(wǎng)站
