MySQL 除 root 用户外，用户 'test'@'localhost' 的访问被拒绝（使用密码：YES）

Access denied for user 'test'@'localhost' (using password: YES) except root user

提问by user3061726

I am facing problem with mysql non root/admin user, I am following the below steps for creating user and its privileges, correct me if i am doing wrong,

我正面临 mysql 非 root/admin 用户的问题，我正在按照以下步骤创建用户及其权限，如果我做错了，请纠正我，

i am installing mysqlon RHEL 5.7 64bit, packages are mentioned below, once i done the rpm installwe are

我安装mysql上RHEL 5.7 64bit，包在下面所提到的，一旦我做了rpm install我们

1. creating mysql db using mysql_install_db, then
2. starting the mysql service then
3. using mysql_upgradealso we are doing to the server.

1. 使用创建 mysql 数据库mysql_install_db，然后
2. 然后启动mysql服务
3. 使用mysql_upgrade我们也在对服务器做。

After this process i can login as rootbut with a non-root user I am not able to log into the server:

在此过程之后，我可以root以非 root 用户身份登录，但无法登录服务器：

[root@clustertest3 ~]# rpm -qa | grep MySQL
MySQL-client-advanced-5.5.21-1.rhel5
MySQL-server-advanced-5.5.21-1.rhel5


[root@clustertest3 ~]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[root@clustertest3 ~]# ls -ld /var/lib/mysql/mysql.sock
srwxrwxrwx 1 mysql mysql 0 Nov  30 11:09 /var/lib/mysql/mysql.sock

mysql> CREATE USER 'golden'@'%' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON * . * TO 'golden'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT USER(),CURRENT_USER();
+----------------+----------------+
| USER()         | CURRENT_USER() |
+----------------+----------------+
| root@localhost | root@localhost |
+----------------+----------------+
1 row in set (0.00 sec)

[root@clustertest3 ~]# mysql -ugolden -p
Enter password:
ERROR 1045 (28000): Access denied for user 'golden'@'localhost' (using password: YES)

This is the problem I am facing, is there any solution to this?

这是我面临的问题，有什么解决方案吗？

回答by Alberto Megía

Do not grant all privileges over all databases to a non-root user, it is not safe (and you already have "root" with that role)

不要将所有数据库的所有权限授予非 root 用户，这是不安全的（并且您已经拥有该角色的“root”）

GRANT <privileges> ON database.* TO 'user'@'localhost' IDENTIFIED BY 'password';

This statement creates a new user and grants selected privileges to it. I.E.:

此语句创建一个新用户并为其授予选定的权限。IE：

GRANT INSERT, SELECT, DELETE, UPDATE ON database.* TO 'user'@'localhost' IDENTIFIED BY 'password';

Take a look at the docsto see all privileges detailed

查看文档以查看详细的所有权限

EDIT: you can look for more info with this query (log in as "root"):

编辑：您可以使用此查询查找更多信息（以“root”身份登录）：

select Host, User from mysql.user;

To see what happened

看看发生了什么

回答by Makarand Kulkarni

If you are connecting to the MySQL using remote machine(Example workbench) etc., use following steps to eliminate this error on OS where MySQL is installed

如果您使用远程机器（示例工作台）等连接到 MySQL，请使用以下步骤在安装 MySQL 的操作系统上消除此错误

mysql -u root -p

CREATE USER '<<username>>'@'%%' IDENTIFIED BY '<<password>>';
GRANT ALL PRIVILEGES ON * . * TO '<<username>>'@'%%';
FLUSH PRIVILEGES;

Try logging into the MYSQL instance.
This worked for me to eliminate this error.

尝试登录到 MYSQL 实例。
这对我来说消除了这个错误。

回答by Noam Rathaus

Try:

尝试：

CREATE USER 'golden'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON * . * TO 'golden'@'localhost';
FLUSH PRIVILEGES;

Or even better use: mysql_setpermissionto create the user

甚至更好的用法：mysql_setpermission创建用户

回答by Gene

It looks like you're trying to make a user 'golden'@'%' but a different user by the name of 'golden'@'localhost' is getting in the way/has precedence.

看起来您正试图让用户成为 'golden'@'%' 但另一个名为 'golden'@'localhost' 的用户正在妨碍/具有优先权。

Do this command to see the users:

执行此命令以查看用户：

SELECT user,host FROM mysql.user;

You should see two entries:

您应该看到两个条目：

1) user= golden, host=%

1）用户=黄金，主机=%

2) user= golden, host=localhost

2）用户=黄金，主机=本地主机

Do these Command:

执行这些命令：

DROP User 'golden'@'localhost';
DROP User 'golden'@'%';

Restart MySQL Workbench.

重新启动 MySQL 工作台。

Then do your original commands again:

然后再次执行您的原始命令：

CREATE USER 'golden'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON * . * TO 'golden'@'%';

Then when you go to try to sign in to MySQL, type it in like this:

然后当你去尝试登录 MySQL 时，像这样输入：

Hit 'Test Connection' and enter your password 'password'.

点击“测试连接”并输入您的密码“密码”。

回答by ako

First I created the user using :

首先，我使用以下方法创建了用户：

CREATE user user@localhost IDENTIFIED BY 'password_txt';

After Googling and seeing this, I updated user's password using :

在谷歌搜索并看到这个之后，我使用以下方法更新了用户的密码：

SET PASSWORD FOR 'user'@'localhost' = PASSWORD('password_txt');

and I could connect afterward.

然后我可以连接。

回答by Yorro

For anyone else who did all the advice but the problem still persists.

对于其他人谁做了所有的建议，但问题仍然存在。

Check for stored procedure and view DEFINERS. Those definers may no longer exists.

检查存储过程并查看 DEFINERS。那些定义者可能不再存在。

My problem showed up when we changed the wildcard host (%) to IP specific, making the database more secure. Unfortunately there are some views that are still using 'user'@'%' even though 'user'@'172....' is technically correct.

当我们将通配符主机 (%) 更改为特定于 IP，使数据库更安全时，我的问题就出现了。不幸的是，即使 'user'@'172....' 在技术上是正确的，仍有一些视图仍在使用 'user'@'%'。

回答by 4af2e9eb6

I also have the similar problem, and later on I found it is because I changed my hostname (not localhost).

我也有类似的问题，后来我发现这是因为我更改了主机名（不是localhost）。

Therefore I get it resolved by specifying the --host=127.0.0.1

因此，我通过指定 --host=127.0.0.1

mysql -p mydatabase --host=127.0.0.1

回答by Antonio Marcos

According way you create your user, MySQL interprets a different manner. For instance, if you create a user like this:

根据您创建用户的方式，MySQL 以不同的方式解释。例如，如果您创建这样的用户：

create user user01 identified by 'test01';

MySQL expects you give some privilege using grant all on <your_db>.* to user01;

MySQL 期望你给一些特权使用 grant all on <your_db>.* to user01;

Don't forget to flush privileges;

不要忘记 flush privileges;

But, if you create user like that (by passing an IP address), you have to change it to:

但是，如果您像这样创建用户（通过传递 IP 地址），则必须将其更改为：

create user 'user02'@'localhost' identified by 'teste02';

so, to give some privileges you have to do that:

所以，要给予一些特权，你必须这样做：

grant all on <your_db>.* to user02@localhost;
flush privileges;

回答by RyanT

Make sure the user has a localhost entry in the users table. That was the problem I was having. EX:

确保用户在用户表中有一个 localhost 条目。这就是我遇到的问题。前任：

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

回答by routeburn

For annoying searching getting here after searching for this error message:

对于在搜索此错误消息后到达此处的烦人搜索：

Access denied for user 'someuser@somewhere' (using password: YES)

用户“someuser@somewhere”的访问被拒绝（使用密码：YES）

The issue for me was not enclosing the password in quotes. eg. I needed to use -p'password'instead of -ppassword

我的问题不是用引号括起来密码。例如。我需要使用-p'password'而不是-ppassword