今天公司同事在測試服務器上死活不能用一個賬號在本地登陸,但是遠程就可以,于是我幫忙看了下,測試服務器的IP是10.10.2.226,錯誤如下:

linux-0fdr:/home1/mysql_data # mysql -h 10.10.2.226 -u jxq2 -pjxq2
ERROR 1045 (28000): Access denied for user?'jxq2'@'linux-0fdr'?(using password: YES)

通過網上搜索這個錯誤號發現了,問題的所在,先用root用戶登陸,查看下用戶表的情況,如下:

mysql> use mysql
mysql> select host,user from user;
+------------+--------------+
| host?????? | user???????? |
+------------+--------------+
| %????????? | bacula?????? |?
| %????????? | count??????? |?
| %????????? | import?????? |?
| %????????? | interface??? |?
| %????????? | jcore??????? |?
| %????????? | jxq2???????? |?
| %????????? | jxq2admin??? |?
| %????????? | lnsms??????? |?
| %????????? | manage?????? |?
| %????????? | manage_admin |?
| %????????? | old_jxq????? |?
| %????????? | root???????? |?
| %????????? | trac???????? |?
| %????????? | wlf????????? |?
| %????????? | xxt????????? |?
| %????????? | yanjun?????? |?
| %????????? | ywt????????? |?
| 127.0.0.1? | count??????? |?
| 127.0.0.1? | interface??? |?
| 127.0.0.1? | jxq2???????? |?
| 127.0.0.1? | manage?????? |?
| 127.0.0.1? | root???????? |?
| linux-0fdr |????????????? |?
| linux-0fdr | root???????? |?
| localhost? |????????????? |?
| localhost? | bacula?????? |?
| localhost? | root???????? |?
| localhost? | trac???????? |?
+------------+--------------+

發現user字段有的是空的,其實就是因為這個導致了本地不能登陸,為了測試是不是這個導致的,我用下面的命令看下:

linux-0fdr:/home1/mysql_data # mysql -h 127.0.0.1 -u jxq2 -pjxq2
Welcome to the MySQL monitor.? Commands end with ; or \g.
Your MySQL connection id is 23753
Server version: 5.1.34-community-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

看到是可以登陸的,主機設置成127.0.0.1就可以登陸了,可以看到有10.10.2.226登陸的時候他會解析成主機名,但是host字段linux-0fdr對應的USER是空的或者是root,并沒有jxq2用戶,所以導致登陸失敗,如何解決呢,很簡單,解決方法如下:

mysql> delete from user where user is NULL;

OK,然后我們再來登陸看看,

mysql -h 10.10.2.226 -u jxq2 -pjxq2
Welcome to the MySQL monitor.? Commands end with ; or \g.
Your MySQL connection id is 23767
Server version: 5.1.34-community-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

看到可以登陸了,呵呵問題解決了.

本文轉自wiliiwin 51CTO博客，原文鏈接:http://blog.51cto.com/wiliiwin/199303