MySQL 错误：：'用户'root'@'localhost' 的访问被拒绝

MySQL Error: : 'Access denied for user 'root'@'localhost'

提问by Harsh Trivedi

$ ./mysqladmin -u root -p'redacted'
Enter password:

$ ./mysqladminü根- P“编校的”
输入密码：

mysqladmin: connect to server at 'localhost' failed error:
'Access denied for user 'root'@'localhost' (using password: YES)'

mysqladmin: 在 'localhost' 连接到服务器失败错误：
'访问被拒绝用户 'root'@'localhost'（使用密码：YES）'

How can I fix this?

我怎样才能解决这个问题？

回答by user1878906

All solutions I found were much more complex than necessary and none worked for me. Here is the solution that solved my problem. No need to restart mysqld or start it with special privileges.

我发现的所有解决方案都比必要的复杂得多，而且没有一个对我有用。这是解决我的问题的解决方案。无需重新启动 mysqld 或以特殊权限启动它。

sudo mysql

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';

With a single query we are changing the auth_pluginto mysql_native_passwordand setting the root password to root(feel free to change it in the query)

通过单个查询，我们将auth_plugin更改为mysql_native_password并将 root 密码设置为root （在查询中随意更改）

Now you should be able to login with root. More information can be found in mysql documentation

现在你应该可以用 root 登录了。可以在mysql 文档中找到更多信息

(exit mysql console with Ctrl + Dor by typing exit)

（使用Ctrl + D或输入exit退出 mysql 控制台）

回答by Kishore Venkataramanan

1. Open & Edit /etc/my.cnfor /etc/mysql/my.cnf, depending on your distro.
2. Add skip-grant-tablesunder [mysqld]
3. Restart Mysql
4. You should be able to login to mysql now using the below command mysql -u root -p
5. Run mysql> flush privileges;
6. Set new password by ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword';
7. Go back to /etc/my.cnf and remove/commentskip-grant-tables
8. Restart Mysql
9. Now you will be able to login with the new password mysql -u root -p

1. 打开并编辑/etc/my.cnf或/etc/mysql/my.cnf，具体取决于您的发行版。
2. skip-grant-tables在下面添加[mysqld]
3. 重启Mysql
4. 您现在应该可以使用以下命令登录到 mysql mysql -u root -p
5. 跑 mysql> flush privileges;
6. 通过设置新密码 ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword';
7. 返回 /etc/my.cnf 并删除/注释skip-grant-tables
8. 重启Mysql
9. 现在您将可以使用新密码登录 mysql -u root -p

回答by Blairg23

I tried many steps to get this issue corrected. There are so many sources for possible solutions to this issue that is is hard to filter out the sense from the nonsense. I finally found a good solution here:

我尝试了很多步骤来纠正这个问题。这个问题的可能解决方案有很多来源，很难从废话中过滤掉意义。我终于在这里找到了一个很好的解决方案：

Step 1: Identify the Database Version

步骤 1：确定数据库版本

$ mysql --version

You'll see some output like this with MySQL:

你会在 MySQL 中看到一些这样的输出：

$ mysql  Ver 14.14 Distrib 5.7.16, for Linux (x86_64) using  EditLine wrapper

Or output like this for MariaDB:

或者像这样为 MariaDB 输出：

mysql  Ver 15.1 Distrib 5.5.52-MariaDB, for Linux (x86_64) using readline 5.1

Make note of which database and which version you're running, as you'll use them later. Next, you need to stop the database so you can access it manually.

记下您正在运行的数据库和版本，因为稍后您将使用它们。接下来，您需要停止数据库，以便您可以手动访问它。

Step 2: Stopping the Database Server

步骤 2：停止数据库服务器

To change the root password, you have to shut down the database server beforehand.

要更改 root 密码，您必须事先关闭数据库服务器。

You can do that for MySQL with:

您可以通过以下方式为 MySQL 做到这一点：

$ sudo systemctl stop mysql

And for MariaDB with:

而对于 MariaDB 来说：

$ sudo systemctl stop mariadb

Step 3: Restarting the Database Server Without Permission Checking

第 3 步：在没有权限检查的情况下重启数据库服务器

If you run MySQL and MariaDB without loading information about user privileges, it will allow you to access the database command line with root privileges without providing a password. This will allow you to gain access to the database without knowing it.

如果您在不加载有关用户权限的信息的情况下运行 MySQL 和 MariaDB，它将允许您以 root 权限访问数据库命令行，而无需提供密码。这将允许您在不知情的情况下访问数据库。

To do this, you need to stop the database from loading the grant tables, which store user privilege information. Because this is a bit of a security risk, you should also skip networking as well to prevent other clients from connecting.

为此，您需要停止数据库加载存储用户权限信息的授权表。因为这有点安全风险，您还应该跳过网络以防止其他客户端连接。

Start the database without loading the grant tables or enabling networking:

在不加载授权表或启用网络的情况下启动数据库：

$ sudo mysqld_safe --skip-grant-tables --skip-networking &

The ampersand at the end of this command will make this process run in the background so you can continue to use your terminal.

此命令末尾的与号将使此进程在后台运行，以便您可以继续使用终端。

Now you can connect to the database as the root user, which should not ask for a password.

现在您可以以 root 用户身份连接到数据库，该用户不应要求输入密码。

$ mysql -u root

You'll immediately see a database shell prompt instead.

您将立即看到一个数据库 shell 提示。

MySQL Prompt

MySQL 提示

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

mysql>

MariaDB Prompt

MariaDB 提示

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

MariaDB [(none)]>

Now that you have root access, you can change the root password.

现在您拥有 root 访问权限，您可以更改 root 密码。

Step 4: Changing the Root Password

步骤 4：更改根密码

mysql> FLUSH PRIVILEGES;

Now we can actually change the root password.

现在我们实际上可以更改root密码。

For MySQL 5.7.6 and neweras well as MariaDB 10.1.20 and newer, use the following command:

对于MySQL 5.7.6 和更高版本以及MariaDB 10.1.20 和更高版本，请使用以下命令：

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

For MySQL 5.7.5 and olderas well as MariaDB 10.1.20 and older, use:

对于MySQL 5.7.5 及更早版本以及MariaDB 10.1.20 及更早版本，请使用：

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password');

Make sure to replace new_passwordwith your new password of choice.

确保替换new_password为您选择的新密码。

Note:If the ALTER USERcommand doesn't work, it's usually indicative of a bigger problem. However, you can try UPDATE ... SETto reset the root password instead.

注意：如果ALTER USER命令不起作用，通常表明存在更大的问题。但是，您可以尝试UPDATE ... SET重置 root 密码。

[IMPORTANT] This is the specific line that fixed my particular issue:

[重要] 这是解决我的特定问题的特定行：

mysql> UPDATE mysql.user SET authentication_string = PASSWORD('new_password') WHERE User = 'root' AND Host = 'localhost';

Remember to reload the grant tables after this.

请记住在此之后重新加载授权表。

In either case, you should see confirmation that the command has been successfully executed.

无论哪种情况，您都应该看到命令已成功执行的确认信息。

Query OK, 0 rows affected (0.00 sec)

The password has been changed, so you can now stop the manual instance of the database server and restart it as it was before.

密码已更改，因此您现在可以停止数据库服务器的手动实例并像以前一样重新启动它。

Step 5: Restart the Database Server Normally

第五步：正常重启数据库服务器

The tutorial goes into some further steps to restart the database, but the only piece I used was this:

本教程进一步介绍了重新启动数据库的一些步骤，但我使用的唯一部分是：

For MySQL, use:$ sudo systemctl start mysql

对于 MySQL，使用：$ sudo systemctl start mysql

For MariaDB, use:

对于 MariaDB，请使用：

$ sudo systemctl start mariadb

Now you can confirm that the new password has been applied correctly by running:

现在您可以通过运行以下命令来确认新密码已正确应用：

$ mysql -u root -p

The command should now prompt for the newly assigned password. Enter it, and you should gain access to the database prompt as expected.

该命令现在应该提示输入新分配的密码。输入它，您应该可以按预期访问数据库提示。

Conclusion

结论

You now have administrative access to the MySQL or MariaDB server restored. Make sure the new root password you choose is strong and secure and keep it in safe place.

您现在已恢复对 MySQL 或 MariaDB 服务器的管理访问权限。确保您选择的新 root 密码强大且安全，并将其保存在安全的地方。

回答by sonrad10

None of the above answers helped me with this problem, so here's the solution I found.

以上答案都没有帮助我解决这个问题，所以这是我找到的解决方案。

The relevant part:

相关部分：

In Ubuntu systems running MySQL 5.7 (and later versions), the root MySQL user is set to authenticate using the auth_socket plugin by default rather than with a password. This allows for some greater security and usability in many cases, but it can also complicate things when you need to allow an external program (e.g., phpMyAdmin) to access the user.

In order to use a password to connect to MySQL as root, you will need to switch its authentication method from auth_socket to mysql_native_password. To do this, open up the MySQL prompt from your terminal:

sudo mysql

Next, check which authentication method each of your MySQL user accounts use with the following command:

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

Output

+------------------+-------------------------------------------+-----------------------+-----------+
| 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 | *CC744277A401A7D25BE1CA89AFF17BF607F876FF | mysql_native_password | localhost |
+------------------+-------------------------------------------+-----------------------+-----------+
4 rows in set (0.00 sec)

In this example, you can see that the root user does in fact authenticate using the auth_socket plugin. To configure the root account to authenticate with a password, run the following ALTER USER command. Be sure to change password to a strong password of your choosing, and note that this command will change the root password you set in Step 2:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

Then, run FLUSH PRIVILEGES which tells the server to reload the grant tables and put your new changes into effect:

FLUSH PRIVILEGES;

Check the authentication methods employed by each of your users again to confirm that root no longer authenticates using the auth_socket plugin:

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

Output

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

You can see in this example output that the root MySQL user now authenticates using a password. Once you confirm this on your own server, you can exit the MySQL shell:

exit

在运行 MySQL 5.7（及更高版本）的 Ubuntu 系统中，root MySQL 用户默认设置为使用 auth_socket 插件而不是密码进行身份验证。在许多情况下，这允许更高的安全性和可用性，但是当您需要允许外部程序（例如，phpMyAdmin）访问用户时，它也会使事情复杂化。

为了使用密码以 root 身份连接到 MySQL，您需要将其身份验证方法从 auth_socket 切换到 mysql_native_password。为此，请从终端打开 MySQL 提示符：

sudo mysql

接下来，使用以下命令检查每个 MySQL 用户帐户使用的身份验证方法：

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

输出

+------------------+-------------------------------------------+-----------------------+-----------+
| 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 | *CC744277A401A7D25BE1CA89AFF17BF607F876FF | mysql_native_password | localhost |
+------------------+-------------------------------------------+-----------------------+-----------+
4 rows in set (0.00 sec)

在此示例中，您可以看到 root 用户实际上使用 auth_socket 插件进行了身份验证。要将 root 帐户配置为使用密码进行身份验证，请运行以下 ALTER USER 命令。请务必将密码更改为您选择的强密码，并注意此命令将更改您在步骤 2 中设置的 root 密码：

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

然后，运行 FLUSH PRIVILEGES 告诉服务器重新加载授权表并使您的新更改生效：

FLUSH PRIVILEGES;

再次检查每个用户使用的身份验证方法，以确认 root 不再使用 auth_socket 插件进行身份验证：

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

输出

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

您可以在此示例输出中看到 root MySQL 用户现在使用密码进行身份验证。在您自己的服务器上确认后，您可以退出 MySQL shell：

exit

回答by Fab

For Ubuntu/Debian users

对于 Ubuntu/Debian 用户

Run the following to connect as root(without any password)

运行以下连接作为root（没有任何密码）

sudo /usr/bin/mysql --defaults-file=/etc/mysql/debian.cnf

If you don't want to add --defaults-fileeach time you want to connect as root, you can copy /etc/mysql/debian.cnfinto your home directory:

如果您不想--defaults-file每次都以 as 连接时添加root，则可以复制/etc/mysql/debian.cnf到您的主目录中：

sudo cp /etc/mysql/debian.cnf ~/.my.cnf

and then:

进而：

sudo mysql

回答by Pico12

After trying all others answers, this it what finally worked for me

在尝试了所有其他答案之后，这最终对我有用

sudo mysql -- It does not ask me for any password

-- Then in MariaDB/MySQL console:
update mysql.user set plugin = 'mysql_native_password' where User='root';
FLUSH PRIVILEGES;
exit;

I found the answer in this post : https://medium.com/@chiragpatel_52497/solved-error-access-denied-for-user-root-localhost-of-mysql-programming-school-6e3611838d06

我在这篇文章中找到了答案：https: //medium.com/@chiragpatel_52497/solved-error-access-denied-for-user-root-localhost-of-mysql-programming-school-6e3611838d06

回答by Amit Kr

For new linux users this could be a daunting task. Let me update this with mysql 8(the latest version available right now is 8.0.12 as on 12-Sep-2018)

对于新的 linux 用户来说，这可能是一项艰巨的任务。让我用 mysql 8 更新它（目前可用的最新版本是 2018 年 9 月 12 日的 8.0.12）

1. Open "mysqld.cnf" configuration file at "/etc/mysql/mysql.conf.d/".
2. Add skip-grant-tablesto the next line of [mysql] text and save.
3. Restart mysql service as "sudo service mysql restart". Now your mysql is free of any authentication.
4. Connect to mysql client(also known as mysql-shell) as mysql -u root -p. There is no password to be keyed in as of now.
5. run sql command flush privileges;
6. Reset the password now as ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPassword';
7. Now let's get back to the normal state; remove that line "skip-grant-tables" from "mysqld.cnf" and restart service.

1. 在“/etc/mysql/mysql.conf.d/”打开“mysqld.cnf”配置文件。
2. 将skip-grant-tables添加到[mysql] 文本的下一行并保存。
3. 将 mysql 服务重新启动为“sudo service mysql restart”。现在您的 mysql 没有任何身份验证。
4. 以 mysql -u root -p 的身份连接到 mysql 客户端（也称为 mysql-shell）。目前没有密码需要输入。
5. 运行sql命令刷新权限；
6. 现在将密码重置为ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPassword';
7. 现在让我们回到正常状态；从“mysqld.cnf”中删除该行“skip-grant-tables”并重新启动服务。

That's it.

就是这样。

回答by Colleen Purcell

In your MySQL workbench, you can go to the left sidebar, under Management select "Users and Privileges", click root under User Accounts, the in the right section click tab "Account Limits" to increase the max queries, updates, etc, and then click tab "Administrative Roles" and check the boxes to give the account access. Hope that helps!

在您的 MySQL 工作台中，您可以转到左侧边栏，在管理下选择“用户和权限”，在用户帐户下单击 root，在右侧部分单击选项卡“帐户限制”以增加最大查询、更新等，以及然后单击选项卡“管理角色”并选中复选框以授予帐户访问权限。希望有帮助！

回答by Sadee

I did this to set my root password in initial set up of MySQL in OSx. Open a terminal.

我这样做是为了在 OSx 中的 MySQL 初始设置中设置我的 root 密码。打开一个终端。

sudo sh -c 'echo /usr/local/mysql/bin > /etc/paths.d/mysql'

Close the terminal and open a new terminal. And followings are worked in Linux, to set root password.

关闭终端并打开一个新终端。以下是在 Linux 中工作的，用于设置 root 密码。

sudo /usr/local/mysql/support-files/mysql.server stop
sudo mysqld_safe --skip-grant-tables

(sudo mysqld_safe --skip-grant-tables : This did not work for me in first time. But second try, out was success.)

（ sudo mysqld_safe --skip-grant-tables ：这在第一次对我不起作用。但第二次尝试，成功了。）

Then login to MySQL

然后登录MySQL

mysql -u root

FLUSH PRIVILEGES;

Now change the password:

现在更改密码：

ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpassword';

Restart MySQL:

重启 MySQL：

sudo /usr/local/mysql/support-files/mysql.server stop
sudo /usr/local/mysql/support-files/mysql.server start

回答by klor

In my case under Debian 10, the error

在我的 Debian 10 下，错误

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

was solved by (GOOD WAY)

被（GOOD WAY）解决了

sudo mysql -u root -p mysql

BAD WAY:

坏方法：

mysql -u root -p mysql