用户“root”@“localhost”的访问权限(使用密码:YES) (Mysql::Error)

I tried to deploy web application on my server and I am getting this mysql database exception "Access denied for user 'root'@'localhost' (using password: YES) (Mysql::Error)"

我试着在我的服务器上部署web应用程序，并且我得到了这个mysql数据库异常“访问被拒绝的用户根”@“localhost”(使用密码:是的)(mysql::错误)

I tried to access the database from the command prompt using mysql -u root -p I am able to do all the database operations.

我尝试使用mysql -u root -p从命令提示符访问数据库，我可以执行所有数据库操作。

what is the error

是什么错误

java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: YES)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2928)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:771)
    at com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:3649)
    at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1176)
    at com.mysql.jdbc.Connection.createNewIO(Connection.java:2558)
    at com.mysql.jdbc.Connection.<init>(Connection.java:1485)
    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:266)
    at java.sql.DriverManager.getConnection(DriverManager.java:620)
    at java.sql.DriverManager.getConnection(DriverManager.java:200)
    at com.mpigeon.DbConnection.DbConn(DbConnection.java:26)
    at com.mpigeon.CheckLoginHome.doGet(CheckLoginHome.java:39)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:617)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)

12 个解决方案

#1

You need to grant access to root from localhost. Check this ubuntu help

您需要授权从本地主机访问根。检查这个ubuntu帮助

#2

try using root like..

试着用根像. .

mysql -uroot

then you can check different user and host after you logged in by using

然后，您可以在使用后检查不同的用户和主机

select user,host,password from mysql.user;

#3

check you are putting blank space in password.

检查您是否在密码中放置空格。

#4

I faced the same error after upgrading MySQL server from 5.1.73 to 5.5.45 There is another way to fix that error.

在将MySQL服务器从5.1.73升级到5.5.5.45之后，我遇到了同样的错误。

In my case I was able to connect to MySQL using root password but MySQL actively refused to GRANT PRIVILEGES to any user;

在我的例子中，我可以使用root密码连接到MySQL，但是MySQL拒绝向任何用户授予特权;

1. Connect to MySQL as root

   以root用户身份连接到MySQL

   mysql -u root -p
   

   then enter your MySQL root password;

   然后输入你的MySQL根密码;

2. Select database;

   选择数据库;

   use mysql;
   

3. Most probably there is only one record for root in mysql.user table allowing to connect only from localhost (that was in my case) but by the default there should be two records for root, one for localhost and another one for 127.0.0.1;

   很可能mysql中只有一条root用户记录。允许仅从localhost连接的user表(在我的例子中是这样)，但是默认情况下root应该有两个记录，一个是localhost，另一个是127.0.0.1;

4. Create additional record for root user with Host='127.0.0.1' if it's not there;

   为不存在的根用户创建附加记录，如果不存在，则使用Host='127.0.0.1';

       SET @s = CONCAT('INSERT INTO mysql.user SELECT ',
               REPLACE((SELECT GROUP_CONCAT(COLUMN_NAME) 
                        FROM INFORMATION_SCHEMA.COLUMNS
                        WHERE TABLE_NAME = 'user' AND TABLE_SCHEMA = 'mysql')
               ,"Host","'127.0.0.1'"),
               ' FROM mysql.user WHERE User="root"'); 
   
        PREPARE stmt FROM @s; 
        EXECUTE stmt;
   

5. Additionally to that you can execute mysql_upgrade -u -p to see if everything is ok.

   此外，还可以执行mysql_upgrade -u -p，以查看是否一切正常。

#5

My application is using Mura CMS and I faced this issue. However the solution was the password mismatch between my mysql local server and the password in the config files. As soon as I synched them it worked.

我的应用程序是使用Mura CMS，我面临这个问题。但是解决方案是mysql本地服务器和配置文件中的密码不匹配。我一同步它们就成功了。

#6

I solved this problem by deleting the empty users creating by MySQL. I only have root user and my own user. I deleted the rest.

我通过删除MySQL创建的空用户解决了这个问题。我只有root用户和我自己的用户。我删除了休息。

#7

Update user table in mysql DB. And set some password where it is blank, i was using root user so i set password for root user.

更新mysql DB中的用户表。在空白的地方设置一些密码，我使用root用户，所以我为root用户设置密码。

update mysql.user set password = PASSWORD('123456') where password = '';
flush privileges;

And then again tried from ATG CIM by providing password and it worked fine.

然后在ATG CIM上再次尝试，提供密码，效果很好。

http://i.stack.imgur.com/3Lchp.png

http://i.stack.imgur.com/3Lchp.png

#8

I got this problem today while installing SugarCRM (a free CRM).

我今天在安装SugarCRM(免费的CRM)时遇到了这个问题。

The system was not able to connect to the database using the root user. I could definitively log in as root from the console... so what was the problem?

系统无法使用根用户连接到数据库。我可以从控制台作为根登录……那么问题是什么呢?

I found out that in my situation, I was getting exactly the same error, but that was because the password was sent to mysql directly from the $_POST data, in other words, the < character from my password was sent to mysql as < which means the password was wrong.

我发现在我的情况下，我得到了完全相同的错误，但那是因为密码是直接从$_POST数据发送到mysql的，换句话说，我密码中的 <字符被作为<发送到mysql;这意味着密码是错误的。< p>

Everything else did not help a bit. The list of users in mysql were correct, including the anonymous user (which appears after the root entries.)

其他的一切都无济于事。mysql中的用户列表是正确的，包括匿名用户(出现在根条目之后)。

#9

I googled a lot but did not find a definite answer to my problem. I used KeyPass to generate a strong password and could use it successfully on mysql workbench to connect but not from the command line. So I changed the psw to an easy one and it worked on the command line. I have managed to create a strong password that was able to connect from the terminal. So my advise is, try with an easy password first before trying all kind of things.

我在谷歌上搜索了很多，但没有找到一个明确的答案。我使用KeyPass生成一个强密码，可以在mysql workbench中成功地使用它进行连接，但不能从命令行进行连接。所以我把psw换成了一个简单的，它在命令行上运行。我已经创建了一个强大的密码，可以从终端连接。所以我的建议是，在尝试所有事情之前先尝试一个简单的密码。

#10

I was running UTs and I started receiving error messages. I am not sure what was the problem. But when I changed my encoding style in INTELLIJ to UTF8 it started working again.

我正在运行UTs，我开始接收错误消息。我不知道是什么问题。但是当我将INTELLIJ的编码风格改为UTF8时，它又开始工作了。

access denied for user 'root'@'localhost' (using password yes) hibernate

用户“root”@“localhost”(使用密码yes)的访问被拒绝。

this is my URL db.url=jdbc:mysql://localhost:3306/somedb?useUnicode=true&connectionCollation=utf8_general_ci&characterSetResults=utf8&characterEncoding=utf8

这是我的URL db.url=jdbc: //localhost:3306/somedb?useUnicode=true&connectionCollation=utf8_general_ci&characterSetResults=utf8。

#11

From my answer here, thought this might be useful:

根据我的回答，我认为这可能有用:

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

#12

Update the empty password in the table mysql.user of mysql

更新表mysql中的空密码。mysql用户

use mysql;
select host,user,password from mysql.user;
update mysql.user set password = PASSWORD('123456') where password = '';
flush privileges;