MySQL Error: Access Denied for User ‘root’@’localhost’

https://www.tutsmake.com/mysql-error-access-denied-for-user-rootlocalhost/

MySQL Error: Access Denied for User ‘root’@’localhost’

May 14, 2023 By Admin Leave a Commenton MySQL Error: Access Denied for User ‘root’@’localhost’

When you working with MySQL database and you may face access denied for user ‘root’@’localhost’ (using password: YES/NO) in linux ubuntu. In this tutorial, you will see some solutions for Access denied for user ‘root’@’localhost’ (using password: NO/YES).

PlayUnmuteRemaining Time -4:09Auto(360pLQ)ShareFullscreen[Fixed Out] Error Establishing A Database Connection In WordPress

When you try to connect to a mysql database and at this time you are getting some error connecting to mysql database with “using password no”:, like given below

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

OR

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

OR 

sudo mysql error 1045 (28000): access denied for user 'root'@'localhost' (using password: YES)

OR

Mysqli::real_connect(): (HY000/1045): Access denied for user 'root'@'localhost' (using password: Yes)

OR

mysqli::real_connect(): (hy000/1045): access denied for user phpmyadmin

Or, you are getting the error with “using password no”, like below given:

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

Or 

Mysqli::real_connect(): (HY000/1045): Access denied for user 'root'@'localhost' (using password: No)

OR

sudo mysql error 1045 (28000): access denied for user 'root'@'localhost' (using password: No)

OR

mysqli::real_connect(): (hy000/1045): access denied for user phpmyadmin

Now, open your terminal or command line and execute the following command into it log into MySQL; is as follows:

mysql -u root -p

You can also see this if you log into MySQL using an IDE like MySQL Workbench. Or even if you use phpMyAdmin.

How to Fix MySQL Error: Access Denied for User ‘root’@’localhost’

There are two solutions for fixing MySQL access denied for user ‘root’@’localhost’ (using password: yes / no) in Linux Ubuntu:

• Solution 1: Change Password using Sudo

• Solution 2: Edit My.cnf File

Solution 1: Change Password using Sudo

By using the following steps in first solution, you can fix mysql access denied for user root@localhost error in linux ubuntu:

Step 1: Open the Terminal

Firstly, open your terminal or command line.

Step 2: Start MySQL with Sudo

Execute the following command on terminal or command line to start mysql with sudo:sudo mysql:

sudo mysql

Step 3: Enter the password

Once you have executed above given command, you will a prompt for entering a password into your terminal or command line.

failed to start mysql.service: unit mysql.service not found.

Step 4: Update the root password

Execute the following command on terminal or command line to update the auth_plugin to mysql_native_password, and the password for the root account:

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

Replace your_new_password with a new secure password that you want to use for the root account.

Step 5: Flush the privileges

Now, execute the following command on terminal or command line to refresh the grant tables and apply your changes:

FLUSH PRIVILEGES;

Then execute the following command on terminal or command line to can confirm that the new authentication method, or plugin, is used by selecting from the mysql.user table:

SELECT user, plugin
FROM mysql.user

Or, you can execute from mysql console by executing the following command:

Exit

That’s it, now you can login to your mysql workbench with root user and new password:

mysql -u root -p

Solution 2: Edit My.cnf File

If the above solution did not for you work, you may need to edit the mysql.cnf file to allow for changes to the root user.

Step 1: Open the Terminal

Firstly, open your terminal or command line.

Step 2: Open the my.cnf file

Now, execute the following command to open my.cnf file:

sudo nano /etc/my.cnf
OR
sudo nano /etc/mysql/my.cnf

mysqli_real_connect(): (HY000/2002): No such file or directory

Step 3: Add Skip grant

To bypass the “access denied for user ‘root’@’localhost’ (using password yes)” error, add the following line to the [mysqld] section of the my.cnf file:

[mysqld]
skip-grant-tables

Step 4: Restart the MySQL server

Execute the following command on terminal or command line to restart mysql server for apply the above make changes:

sudo service mysql restart

OR

sudo systemctl restart mysql

Step 5: Login to the root

Execute the following command on terminal or command line to login as root user in mysql:

mysql -u root -p

Step 6: Flush the privileges

Now, execute the following command on terminal or command line to refresh the grant tables and apply your changes:

FLUSH PRIVILEGES;

Operation not allowed when innodb_forced_recovery > 0

Step 7: Set New Root Login Password

Execute the following command on terminal or command line to set new password for the root account:

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

OR

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

Replace your_new_password with a new secure password that you want to use for the root account.

Step 8: Remove the skip-grant-tables Option

After resetting the ‘root’ user password, you should remove the skip-grant-tables option from the my.cnf file to re-enable MySQL’s authentication system. Open the my.cnf file and delete the ‘skip-grant-tables’ line that you added earlier. Save the my.cnf file and exit the text editor.

Step 9: Restart the MySQL server

Execute the following command on terminal or command line to restart mysql server for apply the above make changes:

sudo service mysql restart

OR

sudo systemctl restart mysql

Step 10: Login to the root

That’s it, now you can login to your mysql workbench with root user and new password:

mysql -u root -p

Conclusion

In conclusion, “access denied for user ‘root’@’localhost’ (using password: yes/no)” can be a frustrating error to deal with, but there are several steps that you can take to troubleshoot and resolve the issue. By using these two solutions should hopefully solve the problem for you, and you will be able to identify and resolve the underlying cause of the error message.