Ubuntu – MySQL – access denied for user root@localhost

MySQLpermissionsserver

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.

Best Answer

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

Related Question