MySQL错误::“访问被拒绝的用户根”@“本地主机”

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

$ ./mysqladmin -u root -p ' redactions '输入密码:

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?

我该怎么解决这个问题呢?

7 个解决方案

#1

1. Open & Edit /etc/my.cnf
2. 打开和编辑/etc/my.cnf
3. Add skip-grant-tables under [mysqld]
4. 下添加skip-grant-tables(mysqld)
5. Restart Mysql
6. 重新启动Mysql
7. You should be able to login to mysql now using the below command mysql -u root -p
8. 现在应该可以使用下面的命令mysql -u root -p登录到mysql了
9. Run mysql> flush privileges;
10. 运行mysql >冲洗特权;
11. Set new password by ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword';
12. 通过“NewPassword”标识的用户“root”@“localhost”设置新密码;
13. Go back to /etc/my.cnf and remove/comment skip-grant-tables
14. 回到/etc/my.cnf，删除/注释skip-grant表
15. Restart Mysql
16. 重新启动Mysql
17. Now you will be able to login with the new password mysql -u root -p
18. 现在您可以使用新的密码mysql -u root -p登录

#2

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_plugin to mysql_native_password and setting the root password to root (feel free to change it in the query)

通过一个查询，我们将auth_plugin更改为mysql_native_password，并将根密码设置为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 + D or by typing exit)

(按Ctrl + D或键入exit退出mysql控制台)

#3

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.

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

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.

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

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.

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

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

现在有了根访问权限，可以更改根密码。

Step 4: Changing the Root Password

步骤4:更改根密码

mysql> FLUSH PRIVILEGES;

Now we can actually change the root password.

现在我们可以修改根密码了。

For MySQL 5.7.6 and newer as 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 older as 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_password with your new password of choice.

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

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

注意:如果ALTER USER命令不起作用，那么通常会出现更大的问题。但是，您可以尝试更新……设置为重置根密码。

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

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

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

步骤5:正常重启数据库服务器

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启动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服务器进行管理访问。确保您选择的新根密码牢固、安全，并将其保存在安全的地方。

#4

For Ubuntu/Debian users

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

运行以下命令作为根(没有任何密码)进行连接

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

#5

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工作台,你可以去左栏,管理下选择“用户权限”,单击用户帐户下根,在正确的部分单击选项卡“账户限制”增加最大查询、更新等,然后点击选项卡“管理角色”和检查盒子给帐户访问。希望会有帮助!

#6

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

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

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中使用以下操作设置根密码。

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 sqmyld_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

#7

'Access denied for user 'root'@'localhost' (using password: YES)' This error would happens only if you have changed the password recently but have not changes made into the config.inc.php file of phpmyadmin.

用户“root”@“localhost”(使用密码:YES)的访问权限被拒绝。如果您最近更改了密码，但没有对config.inc进行更改，则此错误将会发生。phpmyadmin的php文件。

So I have solve my problem by changing my password into /XAMPP/xamppfiles/phpmyadmin/config.inc.php file.

因此，我通过将密码更改为/XAMPP/xamppfiles/phpmyadmin/config.inc来解决我的问题。php文件。