MySQL 用户 'root'@'localhost' 的访问被拒绝（使用密码：YES） - 没有权限？

Access Denied for User 'root'@'localhost' (using password: YES) - No Privileges?

提问by Chase

I am continuously receiving this error.

我不断收到此错误。

I am using mySQL Workbench and from what I am finding is that root's schema privileges are null. There are no privileges at all.

我正在使用 mySQL Workbench，我发现 root 的架构权限为空。根本没有特权。

I am having troubles across platforms that my server is used for and this has been all of a sudden issue.

我在使用我的服务器的平台上遇到了问题，这是一个突然的问题。

[email protected]apparently has a lot of access but I am logged in as that, but it just assigns to localhost anyways - localhost has no privileges.

[email protected]显然有很多访问权限，但我是这样登录的，但它只是分配给 localhost - localhost 没有特权。

I have done a few things like FLUSH HOSTS, FLUSH PRIVILEGES, etc but have found no success from that or the internet.

我做了一些事情，比如FLUSH HOSTS，FLUSH PRIVILEGES等等，但没有发现从那个或互联网上取得成功。

How can I get root its access back? I find this frustrating because when I look around people expect you to "have access" but I don't have access so I can't go into command line or anything and GRANTmyself anything.

我怎样才能获得root访问权限？我觉得这令人沮丧，因为当我环顾四周时，人们希望您“有访问权限”，但我没有访问权限，因此我无法进入命令行或任何东西，而GRANT我自己也无法进入任何东西。

When running SHOW GRANTS FOR rootthis is what I get in return:

运行时SHOW GRANTS FOR root这是我得到的回报：

Error Code: 1141. There is no such grant defined for user 'root' on host '%'

错误代码：1141。没有为主机 '%' 上的用户 'root' 定义此类授权

采纳答案by Andy Jones

Use the instructions for resetting the root password- but instead of resetting the root password, we'll going to forcefully INSERT a record into the mysql.user table

使用重置 root 密码的说明- 但不是重置 root 密码，我们将强行将记录插入 mysql.user 表

In the init file, use this instead

在 init 文件中，改用这个

INSERT INTO mysql.user (Host, User, Password) VALUES ('%', 'root', password('YOURPASSWORD'));
GRANT ALL ON *.* TO 'root'@'%' WITH GRANT OPTION;

回答by KeitelDOG

If you have that same problem in MySql 5.7.+ :

如果您在 MySql 5.7.+ 中遇到同样的问题：

Access denied for user 'root'@'localhost'

it's because MySql 5.7 by default allow to connect with socket, which means you just connect with sudo mysql. If you run sql :

这是因为 MySql 5.7 默认允许连接套接字，这意味着您只需连接sudo mysql. 如果您运行 sql ：

SELECT user,authentication_string,plugin,host FROM mysql.user;

then you will see it :

然后你会看到它：

+------------------+-------------------------------------------+-----------------------+-----------+
| user             | authentication_string                     | plugin                | host      |
+------------------+-------------------------------------------+-----------------------+-----------+
| root             |                                           | auth_socket           | localhost |
| mysql.session    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| debian-sys-maint | *497C3D7B50479A812B89CD12EC3EDA6C0CB686F0 | mysql_native_password | localhost |
+------------------+-------------------------------------------+-----------------------+-----------+
4 rows in set (0.00 sec)

To allow connection with root and password, then update the values in the table with command :

要允许使用 root 和密码连接，然后使用命令更新表中的值：

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Current-Root-Password';
FLUSH PRIVILEGES;

Then run the select command again and you'll see it has changed :

然后再次运行 select 命令，您将看到它已更改：

+------------------+-------------------------------------------+-----------------------+-----------+
| user             | authentication_string                     | plugin                | host      |
+------------------+-------------------------------------------+-----------------------+-----------+
| root             | *2F2377C1BC54BE827DC8A4EE051CBD57490FB8C6 | mysql_native_password | localhost |
| mysql.session    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| debian-sys-maint | *497C3D7B50479A812B89CD12EC3EDA6C0CB686F0 | mysql_native_password | localhost |
+------------------+-------------------------------------------+-----------------------+-----------+
4 rows in set (0.00 sec)

And that's it. You can run this process after running and completing the sudo mysql_secure_installationcommand.

就是这样。您可以在运行并完成sudo mysql_secure_installation命令后运行此过程。

For mariadb, use

对于 mariadb，请使用

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('manager');

to set password. More at https://mariadb.com/kb/en/set-password/

设置密码。更多信息请访问https://mariadb.com/kb/en/set-password/

回答by Dan Kaiser

It didn't like my user privilege so I SUDO it. (in bash << sudo set user and password) (this gives username of root and sets the password to nothing) (On Mac)

它不喜欢我的用户权限，所以我 SUDO 它。（在 bash << sudo set user and password）（这给出了 root 用户名并将密码设置为空）（在 Mac 上）

sudo mysql -uroot -p

回答by aashish

Try the following commands

试试下面的命令

~$ sudo /etc/init.d/mysql stop
~$ sudo mysqld_safe --skip-grant-tables &
~$ mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 1 to server version: 4.1.15-Debian_1-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

mysql> use mysql;

mysql> update user set password=PASSWORD("root") where User='root';

mysql> flush privileges;

mysql> quit

~$ sudo /etc/init.d/mysql stop

Stopping MySQL database server: mysqld

STOPPING server from pid file /var/run/mysqld/mysqld.pid

mysqld_safe[6186]: ended

[1]+  Done                    mysqld_safe --skip-grant-tables

~$ sudo /etc/init.d/mysql start

~$ mysql -u root -p

* MySQL Community Server 5.6.35 is started
~$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.35 MySQL Community Server (GPL)

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

回答by ArifMustafa

for the people who are facing below error in mysql 5.7+ version -

对于在 mysql 5.7+ 版本中面临以下错误的人 -

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

1. Open new terminal

2. sudo /etc/init.d/mysql stop... MySQL Community Server 5.7.8-rc is stopped

3. sudo mysqld_safe --skip-grant-tables &this will skipp all grant level privileges and start the mysql in safe mode Sometimes the process got stucked just because of

1. 打开新终端

2. sudo /etc/init.d/mysql stop... MySQL 社区服务器 5.7.8-rc 已停止

3. sudo mysqld_safe --skip-grant-tables &这将跳过所有授予级别的权限并在安全模式下启动 mysql 有时这个过程只是因为

grep: write error: Broken pipe 180102 11:32:28 mysqld_safe Logging to '/var/log/mysql/error.log'.

grep：写入错误：管道损坏 180102 11:32:28 mysqld_safe 记录到“/var/log/mysql/error.log”。

Simply press Ctrl+Z or Ctrl+C to interrupt and exit process

只需按 Ctrl+Z 或 Ctrl+C 即可中断并退出进程

1. mysql -u root

4. mysql -u root

Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.8-rc MySQL Community Server (GPL)

欢迎使用 MySQL 监视器。命令以 ; 结尾 或\g。您的 MySQL 连接 ID 是 2 服务器版本：5.7.8-rc MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

版权所有 (c) 2000, 2015，Oracle 和/或其附属公司。版权所有。

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

Oracle 是 Oracle Corporation 和/或其附属公司的注册商标。其他名称可能是其各自所有者的商标。

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

输入“帮助；” 或 '\h' 寻求帮助。键入 '\c' 清除当前输入语句。

1. mysql> use mysql;

5. 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

读取表信息以完成表名和列名您可以关闭此功能以使用 -A 更快地启动

Database changed

数据库已更改

1. mysql> update user set authentication_string=password('password') where user='root';Query OK, 4 rows affected, 1 warning (0.03 sec) Rows matched: 4 Changed: 4 Warnings: 1

2. mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)

3. mysql> quitBye

4. sudo /etc/init.d/mysql stop

6. mysql>update user set authentication_string=password('password') where user='root';查询 OK，4 行受影响，1 个警告（0.03 秒）匹配的行数：4 更改：4 警告：1

7. mysql>flush privileges;查询正常，0 行受影响（0.00 秒）

8. mysql>quit再见

9. sudo /etc/init.d/mysql stop

..180102 11:37:12 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended . * MySQL Community Server 5.7.8-rc is stopped arif@ubuntu:~$ sudo /etc/init.d/mysql start .. * MySQL Community Server 5.7.8-rc is started

..180102 11:37:12 来自 pid 文件 /var/run/mysqld/mysqld.pid 的 mysqld_safe mysqld 结束。* MySQL Community Server 5.7.8-rc 已停止 arif@ubuntu:~$ sudo /etc/init.d/mysql start .. * MySQL Community Server 5.7.8-rc 已启动

1. mysql -u root -p

   Enter password:

   Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.8-rc MySQL Community Server (GPL)

10. mysql -u root -p

    输入密码：

    欢迎使用 MySQL 监视器。命令以 ; 结尾 或\g。您的 MySQL 连接 ID 是 2 服务器版本：5.7.8-rc MySQL Community Server (GPL)

after mysql 5.7+ version the column password replaced by name authentication_string from the mysql.user table.

在 mysql 5.7+ 版本之后，列密码被 mysql.user 表中的名称 authentication_string 替换。

hope these steps will help anyone, thanks.

希望这些步骤可以帮助任何人，谢谢。

回答by Nirojan Selvanathan

I was using ubuntu 18 and simply installed MySQL (password:root) with the following commands.

我使用的是 ubuntu 18 并使用以下命令简单地安装了 MySQL（密码：root）。

sudo apt install mysql-server
sudo mysql_secure_installation

When I tried to log in with the normal ubuntu user it was throwing me this issue.

当我尝试使用普通的 ubuntu 用户登录时，它向我抛出了这个问题。

ERROR 1698 (28000): Access denied for user 'root'@'localhost'

But I was able to login to MySQL via the super user. Using the following commands I was able to log in via a normal user.

但是我能够通过超级用户登录到 MySQL。使用以下命令，我可以通过普通用户登录。

sudo mysql    
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';
exit;

Then you should be able to login to Mysql with the normal account.

然后你应该可以用普通帐户登录Mysql了。

回答by Parth mehta

A simple way to reset root password on Linux systems :

在 Linux 系统上重置 root 密码的简单方法：

sudo dpkg-reconfigure mysql-server-5.5

Checkout some other reasons for Access denied :

查看拒绝访问的其他一些原因：

https://dev.mysql.com/doc/refman/5.7/en/problems-connecting.html

https://dev.mysql.com/doc/refman/5.7/en/problems-connecting.html

回答by Shridhar S Chini

I faced this problem while installing Testlink on Ubuntu server, I followed these steps

我在 Ubuntu 服务器上安装 Testlink 时遇到了这个问题，我按照以下步骤操作

mysql -u root
use mysql;
update user set password=PASSWORD("root") where User='root';
flush privileges;
quit

Now stop the instance and start again i.e

现在停止实例并重新开始即

sudo /etc/init.d/mysql stop
sudo /etc/init.d/mysql start

回答by PrestonDocks

in mysql 5.7 the password field has been replaced with authentication_string so you would do something like this instead

在 mysql 5.7 中，密码字段已替换为 authentication_string，因此您可以执行以下操作

update user set authentication_string=PASSWORD("root") where User='root';

See this link MySQL user DB does not have password columns - Installing MySQL on OSX

请参阅此链接MySQL 用户数据库没有密码列 - 在 OSX 上安装 MySQL

回答by Tanmoy Bhowmick

If you are getting this error in Workbench but you are able to log in from terminal then follow this steps.

如果您在 Workbench 中收到此错误，但您可以从终端登录，请按照以下步骤操作。

First simply log in with your current password:

首先只需使用您当前的密码登录：

sudo mysql -u root -p

Then change your password because having low strength password gives error sometimes.

然后更改您的密码，因为低强度密码有时会出错。

ALTER USER 'root'@'localhost' IDENTIFIED BY 'new-strong-password';

FLUSH PRIVILEGES;

Then simply exit and again login with your new password:

然后只需退出并再次使用您的新密码登录：

quit

sudo mysql -u root -p

Once you successfully logged in type the command:

成功登录后，输入以下命令：

use mysql;

It should show a message like 'Database changed' then type:

它应该显示类似“数据库已更改”的消息，然后键入：

UPDATE user SET plugin='mysql_native_password' WHERE User='root';

After that type:

在该类型之后：

UPDATE mysql.user set authentication_string=PASSWORD('new-strong-password') where user='root';

Then type:

然后输入：

FLUSH PRIVILEGES;

Then simply exit:

然后简单地退出：

quit

Now try to log in with your new password in your WORKBENCH. Hope it will work. Thank you.

现在尝试在您的 WORKBENCH 中使用您的新密码登录。希望它会起作用。谢谢你。