你可能有一个匿名用户''@'localhost'
或''@'127.0.0.1'
.
根据the manual:
当可能存在多个匹配时,服务器必须确定
- 当客户端try 连接时,服务器会按排序顺序查看第[of table mysql.user]行.
- 服务器使用与客户端主机名和用户名匹配的第一行.
(...)
因此,当从localhost
连接时,这样的匿名用户会"屏蔽"任何其他用户,比如'[any_username]'@'%'
.
'bill'@'localhost'
与'bill'@'%'
匹配,但与''@'localhost'
匹配.
建议的解决方案是删除这个匿名用户(无论如何,这通常是一件好事).
Below edits are mostly irrelevant to the main question. These are only meant to answer some questions raised in other comments within this thread.
Edit 1
通过套接字验证为'bill'@'%'
.
root@myhost:/home/mysql-5.5.16-linux2.6-x86_64# ./mysql -ubill -ppass --socket=/tmp/mysql-5.5.sock
Welcome to the MySQL monitor (...)
mysql> SELECT user, host FROM mysql.user;
+------+-----------+
| user | host |
+------+-----------+
| bill | % |
| root | 127.0.0.1 |
| root | ::1 |
| root | localhost |
+------+-----------+
4 rows in set (0.00 sec)
mysql> SELECT USER(), CURRENT_USER();
+----------------+----------------+
| USER() | CURRENT_USER() |
+----------------+----------------+
| bill@localhost | bill@% |
+----------------+----------------+
1 row in set (0.02 sec)
mysql> SHOW VARIABLES LIKE 'skip_networking';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| skip_networking | ON |
+-----------------+-------+
1 row in set (0.00 sec)
Edit 2
完全相同的设置,只是我重新激活了网络,现在我创建了一个匿名用户''@'localhost'
.
root@myhost:/home/mysql-5.5.16-linux2.6-x86_64# ./mysql
Welcome to the MySQL monitor (...)
mysql> CREATE USER ''@'localhost' IDENTIFIED BY 'anotherpass';
Query OK, 0 rows affected (0.00 sec)
mysql> Bye
root@myhost:/home/mysql-5.5.16-linux2.6-x86_64# ./mysql -ubill -ppass \
--socket=/tmp/mysql-5.5.sock
ERROR 1045 (28000): Access denied for user 'bill'@'localhost' (using password: YES)
root@myhost:/home/mysql-5.5.16-linux2.6-x86_64# ./mysql -ubill -ppass \
-h127.0.0.1 --protocol=TCP
ERROR 1045 (28000): Access denied for user 'bill'@'localhost' (using password: YES)
root@myhost:/home/mysql-5.5.16-linux2.6-x86_64# ./mysql -ubill -ppass \
-hlocalhost --protocol=TCP
ERROR 1045 (28000): Access denied for user 'bill'@'localhost' (using password: YES)
Edit 3
与编辑2中的情况相同,现在提供匿名用户的密码.
root@myhost:/home/mysql-5.5.16-linux2.6-x86_64# ./mysql -ubill -panotherpass -hlocalhost
Welcome to the MySQL monitor (...)
mysql> SELECT USER(), CURRENT_USER();
+----------------+----------------+
| USER() | CURRENT_USER() |
+----------------+----------------+
| bill@localhost | @localhost |
+----------------+----------------+
1 row in set (0.01 sec)
结论1,来自编辑1:可以通过插座进行身份验证.
结论2,来自编辑2:一个人是通过TCP连接还是通过套接字连接对身份验证过程没有影响(显然,除了一个人不能像其他人一样通过套接字连接外,'something'@'localhost'
个人不能通过套接字连接).
结论3,来自编辑3:虽然我指定了-ubill
,但我已被授予作为匿名用户的访问权限.这是因为上面建议的"排序规则".请注意,在大多数默认安装中,a no-password, anonymous user exists(并且应该被保护/移除).