MySQL - access denied for user root@localhost

Question

I was trying to give remote access to MySQL, as MySQL was only working locally.

I followed this guide http://www.cyberciti.biz/tips/how-do-i-enable-remote-access-to-mysql-database-server.html

I used command like this from tutorial:

mysql> update user set Host='202.54.10.20' where user='root';

I think this messed up the local mysql.

Now I cannot login with:

$ mysql -u root -p

i get this error:

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

Now I know the password is right, as it used to work before. but I accidentally locked myself out..

I don't know what to do anymore.

The os used by the server is Ubuntu.

Answer 1

You can try the regular recovery procedure:

1. Stop MySQL service

2. Run the service using:

   sudo mysqld_safe --skip-grant-tables &
   

3. Connect (password won't be required):

   mysql -uroot
   

4. Revert your changes (be careful this time and check with select what is the current state, host should be 202.54.10.20):

   use mysql;
   update user set Host='localhost' where user='root';
   flush privileges;
   

5. Restart MySQL the regular way

Answer 2

If you are getting this error in Workbench then follow this steps.

First simply log in with your current password:

sudo mysql -u root -p

Then change your password because having low strength password gives error sometimes.

ALTER USER 'root'@'localhost' IDENTIFIED BY 'new-strong-password';

FLUSH PRIVILEGES;

Then simply exit and again login with your new password:

quit

sudo mysql -u root -p

Once you successfully logged in type the command:

use mysql;

It should show a message like 'Database changed' then type:

UPDATE user SET plugin='mysql_native_password' WHERE User='root';

After that type:

UPDATE mysql.user set authentication_string=PASSWORD('new-strong-password') where user='root';

Then type:

FLUSH PRIVILEGES;

Then simply exit:

quit

Now try to log in with your new password in your WORKBENCH. Hope it will work. Thank you.

Answer 3

Try this command:

SHOW GRANTS FOR 'root'@'localhost';

This may also happen if you have no proper privileges. so you can even type the following command:

GRANT ALL PRIVILEGES ON `%`.* TO '[user]'@'[hostname]' IDENTIFIED BY '[password]' WITH GRANT OPTION;

Answer 4

I had the same problem (updated incorrectly the user host from virtualmin) and fixed it with steps similarly to the answer from @techraf but following similar steps of this site to avoid the can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' error in ubuntu 16.04:

https://coderwall.com/p/j9btlg/reset-the-mysql-5-7-root-password-in-ubuntu-16-04-lts

I modified the steps to update the host instead of the password:

# Stop MySQL
sudo service mysql stop
# Make MySQL service directory.
sudo mkdir /var/run/mysqld
# Give MySQL user permission to write to the service directory.
sudo chown mysql: /var/run/mysqld
# Start MySQL manually, without permission checks or networking.
sudo mysqld_safe --skip-grant-tables --skip-networking &
# Log in without a password.
mysql -uroot mysql

then in mysql:

select user, host from mysql.user; --just to check
UPDATE mysql.user SET host='localhost' WHERE User='root' AND Host='202.54.10.20'; --CHANGE the host to the one you see in the query
select user, host from mysql.user; --check again 
commit;
exit;

And then in the console:

# Turn off MySQL.
sudo mysqladmin -S /var/run/mysqld/mysqld.sock shutdown
# Start the MySQL service normally.
sudo service mysql start