MySQL错误1045(28000):用户'bill'@'localhost'的访问被拒绝(使用密码:是) [英] MySQL ERROR 1045 (28000): Access denied for user 'bill'@'localhost' (using password: YES)
问题描述
首先,我要提一提的是,我经历了许多建议的问题,但没有找到相关的答案.这就是我在做什么.
First let me mention that I've gone through many suggested questions and found no relevent answer. Here is what I'm doing.
我已连接到我的Amazon EC2实例.我可以使用以下命令以MySQL root登录:
I'm connected to my Amazon EC2 instance. I can login with MySQL root with this command:
mysql -u root -p
然后我创建了一个主机%的新用户账单
Then I created a new user bill with host %
CREATE USER 'bill'@'%' IDENTIFIED BY 'passpass';
授予用户帐单所有特权:
Granted all the privileges to user bill:
grant all privileges on *.* to 'bill'@'%' with grant option;
然后我从root用户退出,并尝试使用bill登录:
Then I exit from root user and try to login with bill:
mysql -u bill -p
输入正确的密码并出现此错误:
entered the correct password and got this error:
错误1045(28000):用户'bill'@'localhost'的访问被拒绝(使用密码:是)
ERROR 1045 (28000): Access denied for user 'bill'@'localhost' (using password: YES)
推荐答案
您可能有一个匿名用户''@'localhost'
或''@'127.0.0.1'
.
You probably have an anonymous user ''@'localhost'
or ''@'127.0.0.1'
.
按照手册:
当可能有多个匹配项时,服务器必须确定哪个 他们使用.它可以按以下方式解决此问题:(...)
When multiple matches are possible, the server must determine which of them to use. It resolves this issue as follows: (...)
- 当客户端尝试连接时,服务器将按排序顺序浏览 [表mysql.user]表的行.
- 服务器使用与客户端主机名和用户名匹配的第一行.
- When a client attempts to connect, the server looks through the rows [of table mysql.user] in sorted order.
- The server uses the first row that matches the client host name and user name.
(...) 服务器使用排序规则,该规则将最特定的主机值排在最前. 文字主机名 [例如'localhost'] 和IP地址是最具体的.
(...) The server uses sorting rules that order rows with the most-specific Host values first. Literal host names [such as 'localhost'] and IP addresses are the most specific.
因此,当从localhost
连接时,这样的匿名用户将屏蔽"任何其他用户,例如'[any_username]'@'%'
.
Hence, such an anonymous user would "mask" any other user like '[any_username]'@'%'
when connecting from localhost
.
'bill'@'localhost'
确实与'bill'@'%'
匹配,但是会事先匹配(例如)''@'localhost'
.
'bill'@'localhost'
does match 'bill'@'%'
, but would match (e.g.) ''@'localhost'
beforehands.
推荐的解决方案是删除该匿名用户(无论如何通常这样做是一件好事).
The recommended solution is to drop this anonymous user (this is usually a good thing to do anyways).
以下编辑与主要问题无关.这些只是为了回答此主题中其他评论中提出的一些问题.
编辑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)
编辑2
除了重新激活网络功能外,其他设置完全相同,现在我创建了一个匿名用户''@'localhost'
.
Exact same setup, except I re-activated networking, and I now create an anonymous user ''@'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)
编辑3
与编辑2中的情况相同,现在提供了匿名用户的密码.
Same situation as in edit 2, now providing the anonymous user's password.
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,来自一个人可以通过套接字认证为'bill'@'%'
.
Conclusion 1, from edit 1: One can authenticate as 'bill'@'%'
through a socket.
结论2,来自无论是通过TCP还是通过套接字进行连接都不会对身份验证过程产生影响(显然,除了'something'@'localhost'
之外,其他人不能通过套接字进行连接).
Conclusion 2, from edit 2: Whether one connects through TCP or through a socket has no impact on the authentication process (except one cannot connect as anyone else but 'something'@'localhost'
through a socket, obviously).
结论3,来自尽管我指定了-ubill
,但已被授予匿名用户访问权限.这是由于上面建议的排序规则".请注意,在大多数默认安装中,存在没有密码的匿名用户(并且应该固定/移除).
Conclusion 3, from edit 3: Although I specified -ubill
, I have been granted access as an anonymous user. This is because of the "sorting rules" advised above. Notice that in most default installations, a no-password, anonymous user exists (and should be secured/removed).
这篇关于MySQL错误1045(28000):用户'bill'@'localhost'的访问被拒绝(使用密码:是)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!