MySQL ERROR 1045 (28000): 用户 'bill'@'localhost' 访问被拒绝（使用密码：是）

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

提问by Ali

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. I can login with MySQL root with this command:

我已连接到我的 Amazon EC2 实例。我可以使用以下命令使用 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:

授予用户 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”的访问被拒绝（使用密码：是）

回答by RandomSeed

You probably have an anonymous user ''@'localhost'or ''@'127.0.0.1'.

您可能有一个匿名用户''@'localhost'或''@'127.0.0.1'.

As per 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.
• 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. Literal host names [such as 'localhost']and IP addresses are the most specific.

当可能有多个匹配项时，服务器必须确定使用其中的哪个匹配项。它解决这个问题如下：(...)

• 当客户端尝试连接时，服务器按排序顺序查看[表 mysql.user]的行。
• 服务器使用与客户端主机名和用户名匹配的第一行。

(...) 服务器使用排序规则，首先对具有最具体 Host 值的行进行排序。文字主机名[例如 'localhost']和 IP 地址是最具体的。

Hence, such an anonymous user would "mask" any other user like '[any_username]'@'%'when connecting from localhost.

因此，这样的匿名用户会像'[any_username]'@'%'从localhost.

'bill'@'localhost'does match 'bill'@'%', but would match (e.g.) ''@'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'@'%'通过套接字进行身份验证。

    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

编辑 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. 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).

结论 3，来自编辑 3：虽然我指定了-ubill，但我已被授予匿名用户的访问权限。这是因为上面建议的“排序规则”。请注意，在大多数默认安装中，存在无密码的匿名用户（并且应该受到保护/删除）。

回答by Edgar Aviles

Try:

尝试：

~$ mysql -u root -p
Enter Password:

mysql> grant all privileges on *.* to bill@localhost identified by 'pass' with grant option;

回答by RolandoMySQLDBA

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 [email protected]
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()reports how you attempted to authenticate in MySQL

USER()报告您尝试在 MySQL 中进行身份验证的方式

CURRENT_USER()reports how you were allowed to authenticate in MySQL from the mysql.usertable

CURRENT_USER()从mysql.user表报告您如何被允许在 MySQL 中进行身份验证

This will give you a better view of how and why you were allowed to login to mysql. Why is this view important to know? It has to do with the user authentication ordering protocol.

这将使您更好地了解允许登录 mysql 的方式和原因。为什么了解这种观点很重要？它与用户身份验证排序协议有关。

Here is an example: I will create an anonymous user on my desktop MySQL

这是一个例子：我将在我的桌面 MySQL 上创建一个匿名用户

mysql> select user,host from mysql.user;
+---------+-----------+
| user    | host      |
+---------+-----------+
| lwdba   | %         |
| mywife  | %         |
| lwdba   | 127.0.0.1 |
| root    | 127.0.0.1 |
| lwdba   | localhost |
| root    | localhost |
| vanilla | localhost |
+---------+-----------+
7 rows in set (0.00 sec)

mysql> grant all on *.* to x@'%';
Query OK, 0 rows affected (0.02 sec)

mysql> select user,host from mysql.user;
+---------+-----------+
| user    | host      |
+---------+-----------+
| lwdba   | %         |
| mywife  | %         |
| x       | %         |
| lwdba   | 127.0.0.1 |
| root    | 127.0.0.1 |
| lwdba   | localhost |
| root    | localhost |
| vanilla | localhost |
+---------+-----------+
8 rows in set (0.00 sec)

mysql> update mysql.user set user='' where user='x';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> select user,host from mysql.user;
+---------+-----------+
| user    | host      |
+---------+-----------+
|         | %         |
| lwdba   | %         |
| mywife  | %         |
| lwdba   | 127.0.0.1 |
| root    | 127.0.0.1 |
| lwdba   | localhost |
| root    | localhost |
| vanilla | localhost |
+---------+-----------+
8 rows in set (0.00 sec)

mysql>

OK watch me login as anonymous user:

好的，看我以匿名用户身份登录：

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.

认证顺序非常严格。它从最具体到最少进行检查。我在 DBA StackExchange 中写了关于这种身份验证风格的文章。

Don't forget to explicitly call for TCP as the protocol for mysql client when necessary.

不要忘记在必要时显式调用 TCP 作为 mysql 客户端的协议。

回答by garrettmac

Super late to this

超级迟到

I tried all of these other answers and ran many different versions of mysql -u root -pbut never just ran

我尝试了所有这些其他答案并运行了许多不同版本mysql -u root -p但从未运行过

---

---

mysql -u root -p

mysql -u root -p

And just pressing [ENTER]for the password.

只需按[ENTER]密码即可。

---

---

Once I did that it worked. Hope this helps someone.

一旦我这样做了，它就起作用了。希望这可以帮助某人。

回答by Li Yingjun

If you forget your password or you want to modify your password.You can follow these steps :

如果您忘记密码或想修改密码。您可以按照以下步骤操作：

1 :stop your mysql

[root@maomao ~]# service mysqld stop
Stopping MySQL: [ OK ]

2 :use “--skip-grant-tables” to restart mysql

[root@mcy400 ~]# mysqld_safe --skip-grant-tables
[root@cy400 ~]# Starting mysqld daemon with databases from /var/lib/mysql

3 : open a new window and input mysql -u root

[root@cy400 ~]# mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.

4 : change the user database

mysql> use mysql
Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed

5 : modify your password your new password should be input in "（）"

mysql> update user set password=password('root123') where user='root';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0

6 : flush

mysql> flush privileges;

7: quit

mysql> quit
Bye

8: restart mysql

[root@cy400 ~]# service mysqld restart;
Stopping MySQL: [ OK ]
Starting MySQL: [ OK ]

1：停止你的mysql

[root@maomao ~]# service mysqld stop
停止 MySQL: [ OK ]

2：使用“--skip-grant-tables”重启mysql

[root@mcy400 ~]# mysqld_safe --skip-grant-tables
[root@cy400 ~]# 使用 /var/lib/mysql 中的数据库启动 mysqld 守护进程

3：打开一个新窗口并输入mysql -u root

[root@cy400 ~]# mysql -u root
欢迎使用 MySQL 监视器。命令以 ; 结尾 或\g。

4：更改用户数据库

mysql> use mysql
读取表信息以完成表名和列名 您可以关闭此功能以更快地启动 -A Database changed

5 : 修改密码 在“（）”中输入新密码

mysql> update user set password=password('root123') where user='root';
查询确定，3 行受影响（0.00 秒）
匹配行数：3 更改：3 警告：0

6：冲洗

mysql> 刷新权限；

7：退出

mysql> 退出
再见

8：重启mysql

[root@cy400 ~]# service mysqld restart;
停止 MySQL：[确定]
启动 MySQL：[确定]

Bingo！ You can connect your database with your username and new password:

Bingo！您可以使用您的用户名和新密码连接您的数据库：

[root@cy400 ~]# mysql -u root -p <br>
Enter password: admin123 <br>
Welcome to the MySQL monitor.  Commands end with ; or \g. <br>
Your MySQL connection id is 2 <br>
Server version: 5.0.77 Source distribution <br>
Type 'help;' or '\h' for help. Type '\c' to clear the buffer. <br>
mysql> quit <br>
Bye

回答by nos

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, e.g. run mysql -u root -p -h 192.168.1.123or whichever local IP address your box have.

您还可以使用 TCP/IP 与 mysql 命令行客户端进行连接，以匹配您已经授予的权限，例如运行mysql -u root -p -h 192.168.1.123或您的机器拥有的任何本地 IP 地址。

回答by mstram

A related problem in my case was trying to connect using :

在我的情况下，一个相关的问题是尝试使用以下方法进行连接：

mysql -u mike -p mypass

WhitespaceIS apparently allowed between the -u #uname# but NOTbetween the -p and #password#

空白显然是允许的-u＃UNAME＃之间，但不之间的-p和＃密码＃

Therefore needed:

因此需要：

mysql -u mike -pmypass

Otherwise with white-space between -p mypassmysql takes 'mypass' as the dbname

否则 -p mypassmysql之间的空格将 'mypass' 作为数据库名称

回答by mimoralea

Save yourself of a MAJOR headache... Your problem might be that you are missing the quotes around the password. At least that was my case that detoured me for 3 hours.

让自己免于头疼...您的问题可能是您缺少密码周围的引号。至少那是我绕路了3个小时的情况。

[client]
user = myusername
password = "mypassword"   # <----------------------- VERY IMPORTANT (quotes)
host = localhost

http://dev.mysql.com/doc/refman/5.7/en/option-files.html

http://dev.mysql.com/doc/refman/5.7/en/option-files.html

Search for "Here is a typical user option file:" and see the example they state in there. Good luck, and I hope to save someone else some time.

搜索“这是一个典型的用户选项文件：”并查看他们在那里陈述的示例。祝你好运，我希望能拯救别人一些时间。

回答by fevangelou

The solution is to delete the anonymous (Any) user!

解决办法是删除匿名（Any）用户！

I also faced the same issue on a server setup by someone else. I normally don't choose to create an anonymous user upon installing MySQL, so hadn't noticed this. Initially I logged in as "root" user and created a couple of "normal" users (aka users with privileges only on dbs with their username as prefix), then logged out, then went on to verify the first normal user. I couldn't log in. Neither via phpMyAdmin, nor via shell. Turns out, the culprit is this "Any" user.

我在其他人设置的服务器上也遇到了同样的问题。我通常不会在安装 MySQL 时选择创建匿名用户，所以没有注意到这一点。最初，我以“root”用户身份登录并创建了几个“普通”用户（也就是仅在以用户名为前缀的 dbs 上具有特权的用户），然后注销，然后继续验证第一个普通用户。我无法登录。既不能通过 phpMyAdmin，也不能通过 shell。事实证明，罪魁祸首是这个“任何”用户。

回答by David Refaeli

I had a somewhat similar problem - on my first attempt to enter MySQL, as root, it told me access denied. Turns out I forgot to use the sudo...

我有一个有点类似的问题 - 在我第一次尝试进入 MySQL 时root，它告诉我拒绝访问。原来我忘记使用sudo...

So, if you fail on rootfirst attempt, try:

因此，如果您root第一次尝试失败，请尝试：

sudo mysql -u root -p

and then enter your password, this should work.

然后输入您的密码，这应该可以工作。