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='' 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.

  • Can't you just login from the
    – techraf
    Aug 31, 2016 at 7:32
  • @techraf Yes i could login from remotely, but the websites that were present locally on the server stopped working as cant access the mysql from local.. Its very strage, as i can access the mysql remotely but the local user cant access it. Aug 31, 2016 at 7:37
  • Take a look at askubuntu.com/questions/766334/… Jan 11, 2017 at 20:29
  • Take a look at this page, it has some possible causes and workarounds to fix this issue, it may help: rathishkumar.in/2017/04/… Apr 29, 2017 at 19:23

4 Answers 4


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

    use mysql;
    update user set Host='localhost' where user='root';
    flush privileges;
  5. Restart MySQL the regular way

  • 1
    Thankyou for reply back, i have tried this, but after the skip-grant command, i am getting this error when i try mysql -uroot can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' Aug 31, 2016 at 7:57
  • Google for the new problem. There are dozens of reports and different causes.
    – techraf
    Aug 31, 2016 at 8:05
  • ok. somehow i got there, but it says duplicate entry 'localhost-root' for key 'PRIMARY' Aug 31, 2016 at 8:09
  • @SizzlingCode I would have to create a new environment and recreate your steps to debug further. I won't be able to do for a few hours.
    – techraf
    Aug 31, 2016 at 8:16

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;

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:


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=''; --CHANGE the host to the one you see in the query
select user, host from mysql.user; --check again 

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

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';


Then simply exit and again login with your new password:


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:


Then simply exit:


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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .