Oops!! Frustrated with ‘Access denied for user ‘root’@’localhost’ (using password yes) in MySQL?

This error may occur due to many reasons which include insufficient privileges for the user to access the database, when MySQL root password is wrong, and so on.

At Bobcares, we often get requests to fix MySQL errors, as a part of our Server Management Services.

Today, let’s see how our Support Engineers fix this MySQL for our customers.

Why do access denied errors occur in MySQL?

This error may occur due to different reasons in different scenarios. Some of them are:

• When a user who doesn’t exist on the MySQL server tries to access the database.
• When there is no privilege for the user.
• If the username or password is wrong.

Today, we’ll discuss in detail how we fix this Access denied for user ‘root’@’localhost’ (using password yes) in MySQL.

How do we fix ‘access denied for user ‘root’@’localhost’ (using password yes) in MySQL’?

Let’s see, how we fix this access denied error for our customers in detail.

Solution 1: User is not able to log in

Recently, one of our customers approached us saying that he is getting an error like the one shown below while he is trying to log in to MySQL.

So, our Support Engineers checked in detail and found that the root password was wrong. So, to reset the password we did the following:

1. Initially, we opened /etc/mysql/my.cnf
2. Then we added skip-grant-tables under [mysqld] section. Here we skipped grant tables as there is no other way to log in.

   By skipping the grant table option it allows anyone to log in from anywhere and can do anything on the database.

   Note: skip-grant-tables is dangerous, so we will remove this at the end.
3. Then, we restarted the MySQL service using the command below:

   service mysql restart

   Now MySQL is configured to skip the grant-tables. So, we can log in now.
4. We logged into MySQL using:

   mysql -u root 
5. Then, we flushed the privileges using:

   
6. After that, we set a new password using the command below: UPDATE user SET

   Password=PASSWORD('my_password') where USER='root';
   FLUSH PRIVILEGES;
7. Then, we removed skip-grant-tables from /etc/mysql/my.cnf
8. Finally, we restarted the MySQL service and logged in to MySQL using the new password.This fixes the error.

Solution 2: User does not have global privilege

If there is no global privilege then this error can happen. In such cases, our Engineers add the privilege as seen here:

GRANT ALL PRIVILEGES ON database.* TO 'user'@'localhost';

This fixes the error.

Solution 3: Change the Password

Another way to resolve the error is by changing the root password:

1. First, open the command line and open MySQL with the sudo command:

   sudo mysql
2. Then, we have to enter the account password.
3. Now, it is time to change the auth_plugin to mysql_native_password as well as root account password as seen here:

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

   Here, we have to replace new_password with a new secure password.

   Furthermore, the mysql_native_password method is a traditional authentication method that lets you log in.
4. Then, flush the privileges. This will tell the server to refresh the grant tables and apply the changes:

   FLUSH PRIVILEGES;
5. We can exit the console.
6. Finally, try logging in to mysql via the root account and the new password we set.

[Need more assistance to fix MySQL Error?- We’re available 24/7.]

Conclusion

In short, this error occurs mainly due to insufficient privileges to the user and can be fixed by granting the privilege. Also, today we discussed in detail how our Support Engineers fix this error for our customers.