2

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.

  • Can't you just login from the 202.54.10.20? – techraf Aug 31 '16 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. – Sizzling Code Aug 31 '16 at 7:37
  • Take a look at askubuntu.com/questions/766334/… – Charney Kaye Jan 11 '17 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/… – rathishDBA Apr 29 '17 at 19:23
3

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

  • 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' – Sizzling Code Aug 31 '16 at 7:57
  • Google for the new problem. There are dozens of reports and different causes. – techraf Aug 31 '16 at 8:05
  • ok. somehow i got there, but it says duplicate entry 'localhost-root' for key 'PRIMARY' – Sizzling Code Aug 31 '16 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 '16 at 8:16
0

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

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

Your Answer

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.