MySQL错误1045（28000）：用户&#39；bill&#39；@&#39；localhost&#39；的访问被拒绝（使用密码：是）

我会带着你远行 2023-03-02 07:38 52阅读 0赞

本文翻译自：[MySQL ERROR 1045 (28000): Access denied for user 'bill'@'localhost' (using password: YES)][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.* **这是我在做什么。**

*I'm connected to my Amazon EC2 instance.* **我已连接到我的Amazon EC2实例。** *I can login with MySQL root with this command:* **我可以使用以下命令以MySQL root登录：**

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;

*Then I exit from root user and try to login with bill:* **然后，我从root用户退出并尝试使用bill登录：**

mysql -u bill -p

*entered the correct password and got this error:* **输入正确的密码并收到此错误：**

> *ERROR 1045 (28000): Access denied for user 'bill'@'localhost' (using password: YES)* **错误1045（28000）：用户'bill'@'localhost'的访问被拒绝（使用密码：是）**

--------------------

### \#1楼 ###

参考：[https://stackoom.com/question/hDHI/MySQL错误-用户-bill-localhost-的访问被拒绝-使用密码-是][https_stackoom.com_question_hDHI_MySQL_-_-bill-localhost-_-_-]

--------------------

### \#2楼 ###

*Okay, I'm not sure but probably this is my.cnf file inside mysql installation directory is the culprit.* **好的，我不确定，但是可能这是mysql安装目录中的my.cnf文件是罪魁祸首。** *Comment out this line and the problem might be resolved.* **注释掉这一行，可能会解决问题。**

bind-address = 127.0.0.1

--------------------

### \#3楼 ###

*When you type `mysql -u root -p` , you're connecting to the mysql server over a local unix socket.* **当您键入`mysql -u root -p` ，您正在通过本地unix套接字连接到mysql服务器。**

*However the grant you gave, `'bill'@'%'` only matches TCP/IP connections curiously enough.* **但是，您授予的授权`'bill'@'%'`仅与TCP / IP连接足够奇怪地匹配。**

*If you want to grant access to the local unix socket, you need to grant privileges to 'bill'@'localhost' , which curiously enough is not the same as 'bill'@'127.0.0.1'* **如果要授予对本地unix套接字的访问权限，则需要向'bill'@'localhost'授予特权，这很奇怪，它与'bill'@'127.0.0.1'不同**

*You could also connect using TCP/IP with the mysql command line client, as to match the privileges you already granted, eg run `mysql -u root -p -h 192.168.1.123` or whichever local IP address your box have.* **您还可以使用TCP / IP与mysql命令行客户端进行连接，以匹配您已授予的特权，例如，运行`mysql -u root -p -h 192.168.1.123`或您的计算机具有的任何本地IP地址。**

--------------------

### \#4楼 ###

*When you ran* **当你跑**

mysql -u bill -p

*and got this error* **并得到这个错误**

ERROR 1045 (28000): Access denied for user 'bill'@'localhost' (using password: YES)

*mysqld is expecting you to connect as `bill@localhost`* **mysqld希望您以`bill@localhost`连接**

*Try creating `bill@localhost`* **尝试创建`bill@localhost`**

CREATE USER bill@localhost IDENTIFIED BY 'passpass';
    grant all privileges on *.* to bill@localhost with grant option;

*If you want to connect remotely, you must specify either the DNS name, the public IP, or 127.0.0.1 using TCP/IP:* **如果要远程连接，则必须使用TCP / IP指定DNS名称，公共IP或127.0.0.1：**

mysql -u bill -p -hmydb@mydomain.com
    mysql -u bill -p -h10.1.2.30
    mysql -u bill -p -h127.0.0.1 --protocol=TCP

*Once you login, please run this* **登录后，请运行此命令**

SELECT USER(),CURRENT_USER();

*[USER()][USER] reports how you attempted to authenticate in MySQL* **[USER（）][USER]报告您如何尝试在MySQL中进行身份验证**

*[CURRENT\_USER()][CURRENT_USER] reports how you were allowed to authenticate in MySQL from the **mysql.user** table* **[CURRENT\_USER（）][CURRENT_USER]报告如何通过mysql.user表在MySQL中进行身份验证**

*This will give you a better view of how and why you were allowed to login to mysql.* **这将使您更好地了解如何以及为什么允许您登录mysql。** *Why is this view important to know?* **为什么要了解这种观点很重要？** *It has to do with the user authentication ordering protocol.* **它与用户身份验证排序协议有关。**

*Here is an example: I will create an anonymous user on my desktop MySQL* **这是一个示例：我将在桌面MySQL上创建一个匿名用户**

*OK watch me login as anonymous user:* **OK，请看我以匿名用户身份登录：**

C:\MySQL_5.5.12>mysql -urol -Dtest -h127.0.0.1 --protocol=TCP
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 12
    Server version: 5.5.12-log MySQL Community Server (GPL)
    
    Copyright (c) 2000, 2010, 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> select user(),current_user();
    +---------------+----------------+
    | user()        | current_user() |
    +---------------+----------------+
    | rol@localhost | @%             |
    +---------------+----------------+
    1 row in set (0.00 sec)
    
    mysql>

*Authentication ordering is very strict.* **身份验证顺序非常严格。** *It checks from the most specific to the least.* **它从最具体到最少检查。** *[**I wrote about this authentiation style in the DBA StackExchange**][I wrote about this authentiation style in the DBA StackExchange] .* **[我在DBA StackExchange中撰写了有关这种身份验证样式的文章][I wrote about this authentiation style in the DBA StackExchange] 。**

*Don't forget to explicitly call for TCP as the protocol for mysql client when necessary.* **不要忘记在必要时显式调用TCP作为mysql客户端的协议。**

--------------------

### \#5楼 ###

*You probably have an anonymous user `''@'localhost'` or `''@'127.0.0.1'` .* **您可能有一个匿名用户`''@'localhost'`或`''@'127.0.0.1'` 。**

*As per [the manual][] :* **按照[手册][the manual] ：**

> *When multiple matches are possible, the server must determine which of them to use.* **当可能有多个匹配项时，服务器必须确定要使用哪个匹配项。** *It resolves this issue as follows: (...)* **它可以解决此问题，如下所示：（...）**
> 
>  *  *When a client attempts to connect, the server looks through the rows \[of table mysql.user\] in sorted order.* **当客户端尝试连接时，服务器*将按*排序顺序浏览*\[表mysql.user\]*的行。**
>  *  *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** .* **（...）服务器使用排序规则，该规则首先对具有最特定Host值的行进行排序。** *Literal host names \[such as 'localhost'\] and IP addresses are the most specific.* **文字主机名*\[例如'localhost'\]*和IP地址是最具体的。**

*Hence, such an anonymous user would "mask" any other user like `'[any_username]'@'%'` when connecting from `localhost` .* **因此，当从`localhost`连接时，这样的匿名用户将“屏蔽”任何其他用户，例如`'[any_username]'@'%'` 。**

*`'bill'@'localhost'` does match `'bill'@'%'` , but would match (eg) `''@'localhost'` beforehands.* **`'bill'@'localhost'`确实与`'bill'@'%'`匹配，但事先会与（例如） `''@'localhost'`匹配。**

*The recommended solution is to drop this anonymous user (this is usually a good thing to do anyways).* **推荐的解决方案是删除该匿名用户（无论如何通常这样做是一件好事）。**

--------------------

*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*** **编辑1**

*Authenticating as `'bill'@'%'` through a socket.* **通过套接字认证为`'bill'@'%'` 。**

***Edit 2*** **编辑2**

*Exact same setup, except I re-activated networking, and I now create an anonymous user `''@'localhost'` .* **完全相同的设置，除了我重新激活了网络外，现在我创建了一个匿名用户`''@'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*** **编辑3**

*Same situation as in edit 2, now providing the anonymous user's password.* **与编辑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)

*Conclusion 1, from edit 1: One can authenticate as `'bill'@'%'` through a socket.* **结论1，来自编辑1：一个人可以通过套接字认证为`'bill'@'%'` 。**

*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).* **结论2，来自编辑2：无论是通过TCP连接还是通过套接字连接都不会对身份验证过程产生任何影响（除了一个人不能通过套接字连接之外，只能通过套接字连接`'something'@'localhost'` ）。**

*Conclusion 3, from edit 3: Although I specified `-ubill` , I have been granted access as an anonymous user.* **结论3，来自编辑3：尽管我指定了`-ubill` ，但已授予我匿名用户访问权限。** *This is because of the "sorting rules" advised above.* **这是由于上面建议的“排序规则”。** *Notice that in most default installations, [a no-password, anonymous user exists][a no-password_ anonymous user exists] (and should be secured/removed).* **请注意，在大多数默认安装中， [存在无密码的匿名用户][a no-password_ anonymous user exists] （并且应予以保护/删除）。**

--------------------

### \#6楼 ###

*MySQL account names consist of a user name and a host name, The name 'localhost' in host name indicates the local host also You can use the wildcard characters “%” and “\_” in host name or IP address values.* **MySQL帐户名称由用户名和主机名组成。主机名中的名称“ localhost”也表示本地主机。您可以在主机名或IP地址值中使用通配符“％”和“ \_”。** *These have the same meaning as for pattern-matching operations performed with the LIKE operator.* **这些具有与使用LIKE运算符执行的模式匹配操作相同的含义。** *For example, a host value of '%' matches any host name, whereas a value of '%.mysql.com' matches any host in the mysql.com domain.* **例如，主机值'％'匹配任何主机名，而值'％.mysql.com'匹配mysql.com域中的任何主机。** *'192.168.1.%' matches any host in the 192.168.1 class C network.* **“ 192.168.1。％”与192.168.1 C类网络中的任何主机匹配。**

*Above was just introduction:* **以上只是介绍：**

*actually both users `'bill'@'localhost'` and `'bill'@'%'` are different MySQL accounts, hence both should use their own authentication details like password.* **实际上，两个用户`'bill'@'localhost'`和`'bill'@'%'`都是不同的MySQL帐户，因此两者都应使用自己的身份验证详细信息，例如密码。**

*For more information refer [http://dev.mysql.com/doc/refman//5.5/en/account-names.html][http_dev.mysql.com_doc_refman_5.5_en_account-names.html]* **有关更多信息，请参见[http://dev.mysql.com/doc/refman//5.5/en/account-names.html][http_dev.mysql.com_doc_refman_5.5_en_account-names.html]**

[MySQL ERROR 1045 _28000_ Access denied for user _bill_localhost_ _using password_ YES]: https://oldbug.net/q/hDHI/MySQL-ERROR-1045-28000-Access-denied-for-user-bill-localhost-using-password-YES
[https_stackoom.com_question_hDHI_MySQL_-_-bill-localhost-_-_-]: https://stackoom.com/question/hDHI/MySQL%E9%94%99%E8%AF%AF-%E7%94%A8%E6%88%B7-bill-localhost-%E7%9A%84%E8%AE%BF%E9%97%AE%E8%A2%AB%E6%8B%92%E7%BB%9D-%E4%BD%BF%E7%94%A8%E5%AF%86%E7%A0%81-%E6%98%AF
[USER]: http://dev.mysql.com/doc/refman/5.5/en/information-functions.html#function_user
[CURRENT_USER]: http://dev.mysql.com/doc/refman/5.5/en/information-functions.html#function_current-user
[I wrote about this authentiation style in the DBA StackExchange]: https://dba.stackexchange.com/a/10897/877
[the manual]: http://dev.mysql.com/doc/refman/5.5/en/connection-access.html
[a no-password_ anonymous user exists]: http://dev.mysql.com/doc/refman/5.6/en/default-privileges.html
[http_dev.mysql.com_doc_refman_5.5_en_account-names.html]: http://dev.mysql.com/doc/refman//5.5/en/account-names.html