Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

After setting root password why does MYSQL still allow me to login without a password from the command line? I can type "mysql" at a root unix prompt and it asks for no password and still allows me root access. I am not understanding why "mysql -u root" is not NOW asking me for the password that I set on the account.

Also, I cannot login to the mysql from a remote machine as 'root'. Didn't I configure it correctly below? I get the error: "Host...is not allowed to connect to this MySQL server. Didn't I configure it for '%' ?

Here is my user table:

mysql> select host,user,password from user;
+-----------+------+-------------------------------------------+
| host      | user | password                                  |
+-----------+------+-------------------------------------------+
| localhost | root | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| %         | root |                                           |
| 127.0.0.1 | root |                                           |
| ::1       | root |                                           |
| localhost |      |                                           |
| %         |      |                                           |
+-----------+------+-------------------------------------------+
6 rows in set (0.00 sec)
share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

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');
share|improve this answer
    
That was EVERYTHING I needed and then some. Thanks. I was unsure what was safe to remove or not safe and you answered my question. Great answer. :-) –  djangofan Apr 10 '11 at 21:05
add comment

Well, the answer is right in that table - you need to remove the "root" lines that have no password listed.

Also, looks like you allow connections from anywhere (?!?!) when using no user or pass. Probably a bad idea. I'd do something like:

mysql> use mysql;
mysql> delete from user where password = "";
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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