This error indicates that the user ‘root’ does not have the correct permissions to access the MySQL database. To resolve this issue, the user must be granted the appropriate privileges to access the database. This can be done by logging into the MySQL server as an administrator and running the GRANT command to grant the user the necessary privileges.
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).
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 [email protected] 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.
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
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;
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.
