首先让我提一下,我已经看了很多建议的问题,但没有找到相关的答案。这就是我正在做的。

我连接到Amazon EC2实例。我可以用这个命令登录MySQL根目录:

mysql -u root -p

然后我用host %创建了一个新的用户帐单

CREATE USER 'bill'@'%' IDENTIFIED BY 'passpass';

授予用户bill的所有权限:

grant all privileges on *.* to 'bill'@'%' with grant option;

然后我退出root用户,尝试用bill登录:

mysql -u bill -p

输入正确的密码并得到以下错误:

错误1045(28000):用户“账单”@“localhost”(使用密码:YES)的访问被拒绝


当前回答

这是以下两者之间的区别:

CREATE USER 'bill'@'%' IDENTIFIED BY 'passpass';

and

CREATE USER 'bill'@'localhost' IDENTIFIED BY 'passpass';

检查:

mysql> select user,host from mysql.user;
+---------------+----------------------------+
| user          | host                       |
+---------------+----------------------------+
| bill          | %                          | <=== created by first
| root          | 127.0.0.1                  |
| root          | ::1                        |
| root          | localhost                  |
| bill          | localhost                  | <=== created by second
+---------------+----------------------------+

命令

mysql -u bill -p

隐式访问'bill'@'localhost',而不是'bill'@'%'。

“bill”@“localhost”没有权限

你会得到错误:

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

解决问题:

CREATE USER 'bill'@'localhost' IDENTIFIED BY 'passpass';

grant all privileges on . to 'bill'@'localhost' with grant option;

其他回答

当你的密码包含一些特殊字符,如@,$等,也会发生这种情况。 为了避免这种情况,你可以把password用单引号括起来:

$ mysql -usomeuser -p's0mep@$$w0Rd'

或者在输入时不用密码。请留空,然后在终端要求时输入。这是推荐的方法。

$ mysql -usomeuser -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 191
Server version: 5.5.46-0ubuntu0.14.04.2 (Ubuntu)

Copyright (c) 2000, 2015, 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>

这是以下两者之间的区别:

CREATE USER 'bill'@'%' IDENTIFIED BY 'passpass';

and

CREATE USER 'bill'@'localhost' IDENTIFIED BY 'passpass';

检查:

mysql> select user,host from mysql.user;
+---------------+----------------------------+
| user          | host                       |
+---------------+----------------------------+
| bill          | %                          | <=== created by first
| root          | 127.0.0.1                  |
| root          | ::1                        |
| root          | localhost                  |
| bill          | localhost                  | <=== created by second
+---------------+----------------------------+

命令

mysql -u bill -p

隐式访问'bill'@'localhost',而不是'bill'@'%'。

“bill”@“localhost”没有权限

你会得到错误:

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

解决问题:

CREATE USER 'bill'@'localhost' IDENTIFIED BY 'passpass';

grant all privileges on . to 'bill'@'localhost' with grant option;

在我的情况下,服务器托管我的代码是发送'@blablabla.amazonaws.com'在用户名的ed时,试图登录MySQL。所以我不得不将%.amazonws.com添加到我的主机的用户配置(dreamhost)中,它通过了

I discovered yet another case that appears on the surface to be an edge case; I can export to the file system, via SELECT INTO .. OUTFILE as root, but not as regular user. While this may be a matter of permissions, I've looked at that, and see nothing especially obvious. All I can say is that executing the query as a regular user who has all permissions on the data base in question returns the access denied error that led me to this topic. When I found the transcript of a successful use of SELECT INTO … OUTFILE in an old project, I noticed that I was logged in as root. Sure enough, when I logged in as root, the query ran as expected.

更新:在v8.0.15(可能是这个版本),PASSWORD()函数不起作用。

你必须:

确保你已经停止MySQL。 使用特权绕过安全模式运行服务器:sudo mysqld_safe——skip-grant-tables 登录:mysql -u root 更新mysql。SET authentication_string=null WHERE user ='root'; mysql> FLUSH PRIVILEGES; mysql >退出; 再次登录:mysql -u root mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'yourpasswd'