Cannot login to mysql after setting incorrect mysql privileges/host

Question

On CentOS 5.x I used webmin panel and I was in mysql settings, I was trying to allow "root" to login from my home ip in addition to localhost. In the hosts list I had "any" or "localhost", I tried to make it "localhost,84.xx.xx.xx" thinking it will allow me to login from my local computer with mysql administrator program.

After I saved the entered data I cannot do much on mysql

Webmin says DBI connect failed : Access denied for user ''@'localhost' to database 'mysql'

In SSH mysql says ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'mysql'

My guess is that mysql doesn't allow root to connect from any host anymore because I entered incorrect hosts.

I tried uninstalling and reinstalling mysql but no change. Is there a way to reset this? I have no database data, I can delete all if needed.

Thanks.

Solution 1

Log in at a terminal as root and stop the mysql daemon.

/etc/init.d/mysqld stop

Then start up the mysql daemon and skip the grant tables which stores the passwords and other priviledge information

mysqld_safe --skip-grant-tables &

You should see mysqld start up successfully. Now you should be able to connect to mysql without a password.

mysql --user=root mysql

and then update the relevant information:

update user set Password=PASSWORD('new-password') where user='root';
flush privileges;
exit;

and then restart the mysql daemon as you would normally do

/etc/init.d/mysqld restart

Also take a look at the MySQL documentation to help you out if you get stuck

Solution 2

You need to start mysql with --skip-grant-tables option. Have a look at this page. After that, you should be able to connect to mysql database. You can then fix the error.

Comments

[adrianTNT]

After mysqld_safe --skip-grant-tables it gets stuck at Starting mysqld daemon with databases from /var/lib/mysql

[thanosk]

It is not stuck it is running without problems. But I should have written : mysqld_safe --skip-grant-tables & so it goes to the background and releases the prompt. Kill that process and start it again. Sorry for my mistake

[adrianTNT]

After I did the mysqld_safe --skip-grant-tables & I was able to login in webmin again, I fixed the bad hosts format then saved and restarted mysql. It works now. Thank you.