今天公司同事在测试服务器上死活不能用一个账号在本地登陆,但是远程就可以,于是我帮忙看了下,测试服务器的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 (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.
看到是可以登陆的,主机设置成127.0.0.1就可以登陆了,可以看到有10.10.2.226登陆的时候他会解析成主机名,但是host字段linux-0fdr对应的USER是空的或者是root,并没有jxq2用户,所以导致登陆失败,如何解决呢,很简单,解决方法如下:
mysql> delete from user where user is NULL;
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.
本文转自wiliiwin 51CTO博客，原文链接:http://blog.51cto.com/wiliiwin/199303