The Error “‘Access denied for user ‘root’@’localhost’ “, is an error that is invoked when you are working on a MySQL and are trying to enter your password. Given below is the snippet of the error you might get:
'Access denied for user 'root'@'localhost' (using password: YES)'I would like to share with you the steps I took to fix the “‘Access denied for user ‘root’@’localhost'”
Why “Access denied for user ‘root’@’localhost'” Error is Seen?
The error, “Access denied for user ‘root’@’localhost'” is seen because, in Ubuntu operating systems running MySQL 5.7 or later versions, the root MySQL user is set to authenticate by using the auth_socket plugin by default instead of a password.
This allowed for greater security and usability in many cases, but in rest of the cases it can also complicate things when you need to allow an external program like phpMyAdmin to access the user.
ALTER USER offers numerous password option settings for password expiration management, allowing you to either manually expire an account password or set up a password expiration policy.
Policy choices do not cause the password to expire. Instead, they control how the server implements automatic account expiry depending on account password age. The password age of a specific account is calculated using the date and time of the most recent password update.
Note: Although it is possible to “reset” an expired password by setting it to its current value, it is preferable, as a matter of good policy, to choose a different password.
The detailed solution to fix the error “Access denied for user ‘root’@’localhost'”, is given below:
How to fix “Access denied for user ‘root’@’localhost'” Error?
To fix the error, you will have to run single query depending on you system to change the auth_plugin to mysql_native_password and setting the root password to “root” value. You can also manually set the root password in case the single query does not work.
To fix the error, “Access denied for user ‘root’@’localhost'”, you will have to follow one of the below mentioned methods:
Method 1: Without restating MySQL
You will have to run the below mentioned query depending on you system to change the auth_plugin to mysql_native_password and setting the root password to root.
sudo mysql
-- for MySQL
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';
-- for MariaDB
ALTER USER 'root'@'localhost' IDENTIFIED VIA mysql_native_password USING PASSWORD('root');
With this you should be able to log in with root. More information can be found in MySQL documentation, for which you can find the link here; or MariaDB documentation, for which you can find the link here.
The ALTER USER command is used to modify existing MariaDB accounts. You must have the global CREATE USER permission or the UPDATE privilege for the mysql database in order to utilize it. If the read only system variable is enabled, the global SUPER privilege is also needed. An error occurs if any of the provided user accounts do not yet exist. ALTER USER will still edit accounts that do not result in an error if an error occurs. For all users who have not been changed, just one error is generated. Source: MariaDB Alter User Documentation
Note: Although it is possible to “reset” an expired password by setting it to its current value, it is preferable, as a matter of good policy, to choose a different password.
This should fix the error, “Access denied for user ‘root’@’localhost'”
Method 2: With restating MySQL
To fix the error, “Access denied for user ‘root’@’localhost'”, you will have to follow the below mentioned steps:
Step 1: First open and edit the following path /etc/my.cnf or /etc/mysql/my.cnf, depending on your distribution.
Step 2: Next step will be to Add skip-grant-tables under [mysqld]
Step 3: Now restart your MySQL
Step 4: After the restart you should be able to log in to MySQL. now using the below command mysql -u root -p
Step 5: The run the mysql> flush privileges;
Step 6: Set new password by ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword';
Step 7: Go back to the following path /etc/my.cnf and remove or comment skip-grant-tables
Step 8: Then Restart your MySQL again.
Step 9: After following the above steps you will be able to login with the new password mysql -u root -p
This should fix the error, “Access denied for user ‘root’@’localhost'”
Conclusion
To fix the error “Access denied for user ‘root’@’localhost'”, you will have to run single query depending on you system to change the auth_plugin to mysql_native_password and setting the root password to “root”. You can also manually set the root password in case the single query does not work