Mysql – Cannot login to thesql after setting incorrect thesql privileges/host

hosthostsMySQLusers

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.

Best Answer

/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

Related Solutions

Mysql – After setting root password why does MYSQL still allow me to login without a password

Looks like the password is set on the 'root'@'localhost' user entry, but not on the 'root'@'%' entry; password-free authentication would be allowed based on that.

For security purposes, reconsider allowing root access from anywhere. If you do need it, then just get rid of the localhost specifications:

drop user 'root'@'localhost';
drop user 'root'@'127.0.0.1';
drop user 'root'@'::1';

And set the password for the 'root'@'%' user:

set password for 'root'@'%' = password('passwordhere');

Mysql – Cannot login to thesql with non root user from command line

GRANT ... TO 'rails'@'localhost' ... REQUIRE SSL

For starters requiring SSL for connections to/from localhost is completely senseless. SSL or TLS provide transport security which you don't need for communications that won't leave the actual system, that will not provide any additional security and only extra overhead.

In addition the official MySQL libraries on Linux/UNIX treat the keyword localhost different from what you would expect and will use the Unix socket, not TCP. from the manual:

On Unix, MySQL programs treat the host name localhost specially, in a way that is likely different from what you expect compared to other network-based programs. For connections to localhost, MySQL programs attempt to connect to the local server by using a Unix socket file. This occurs even if a --port or -P option is given to specify a port number. ... You can also specify the connection protocol explicitly, by using the --protocol=TCP option.

Using the socket gives you much more performance than using TCP/IP and AFAIK the socket does not support SSL (and would not need to either).

If you do force a TCP connection, you'll need to GRANT access over TCP as well, with GRANT ... TO 'rails'@'127.0.0.1' ... , because granting connections from localhost implies the Unix socket.

Particularly unhelpful is that for error messages MySQL follows the convention to do a reverse DNS lookup for ip-addresses which will convert the ip-address 127.0.0.1 to localhost.

Best Answer

Related Solutions

Mysql – After setting root password why does MYSQL still allow me to login without a password

Mysql – Cannot login to thesql with non root user from command line

Related Topic