Forum
MySQL Error: Access Denied for User ‘root’@’localhost’
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:
Copy
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:
Copy
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:
Copy
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:
Copy
SELECT user, plugin
FROM mysql.user
Or, you can execute from mysql console by executing the following command:
Copy
Exit
That’s it, now you can login to your mysql workbench with root user and new password:
Copy
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:
Copy
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:
Copy
[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:
Copy
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:
Copy
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:
Copy
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:
Copy
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:
Copy
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:
Copy
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.
