溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
本篇內容主要講解“Mysql連接錯誤ERROR 1045 (28000): Access denied for user問題的解決方法”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓小編來帶大家學習“Mysql連接錯誤ERROR 1045 (28000): Access denied for user問題的解決方法”吧!
從192.168.111.99上連接遠程數據的時候報錯：
[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)
發現已經給192.168.111.99授權了，可為什么還是連不上?
和192.168.111.99相關的權限是下面幾行：
| 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>
刪除密碼為空的用戶：
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>
總結：
出現這個錯誤的原因是同一個用戶有不同主機范圍的權限時，按最匹配的一個主機驗證權限。
也就是說從192.168.111.99登陸服務器時，驗證順序為：
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）用戶授權時，按最小主機范圍授權，并且賦予密碼，只授權一次。
（2）如果將來主機范圍變化，重新授權時可刪除舊授權，否則可能因為兩次授權密碼不同導致類似問題。
到此，相信大家對“Mysql連接錯誤ERROR 1045 (28000): Access denied for user問題的解決方法”有了更深的了解，不妨來實際操作一番吧！這里是億速云網站，更多相關內容可以進入相關頻道進行查詢，關注我們，繼續學習！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
