How to solve Error “Access denied for user ‘root’@’localhost’” of MySql

This solution does not need to drop any tables or no need to change any user permission. just two line of code and then you will able to login into MySql console.
Without wasting much time let’s jump into it.

Solution 1 :
Follow given steps.

  • ~ sudo su
  • ~ mysql -u root -p
  • enter your password
  • ~ update mysql.user set plugin = ‘mysql_native_password’ where User=’root’;
  • ~ FLUSH PRIVILEGES;
  • ~ exit;

Solution 2:

Start the MySQL server instance or daemon with the –skip-grant-tables option (security setting).

$ mysqld –skip-grant-tables
Execute these statements.

$ mysql -u root mysql
$mysql> UPDATE user SET Password=PASSWORD(‘my_password’) where USER=’root’;
$mysql> FLUSH PRIVILEGES;
If you face the unknown field Password error above use:

update user set authentication_string=password(‘my_password’) where user=’root’;
Finally, restart the instance/daemon without the –skip-grant-tables option.

$ /etc/init.d/mysql restart
You should now be able to connect with your new password.

$ mysql -u root -p
Enter password: my_password

Solution 3:

Open & Edit /etc/my.cnf or /etc/mysql/my.cnf, depending on your distro.
Add skip-grant-tables under [mysqld]
Restart Mysql

You should be able to login to mysql now using the below command

mysql -u root -p

Run mysql> flush privileges;
Set new password by ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘NewPassword’;
Go back to /etc/my.cnf and remove/comment skip-grant-tables
Restart Mysql
Now you will be able to login with the new password mysql -u root -p

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.