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.

Using this command

GRANT ALL PRIVILEGES ON *.* to brian@'%' identified by 'password';

I try to login with:

 mysql -u brian -ppassword

The error is:

ERROR 1045 (28000): Access denied for user 'brian'@'localhost' (using password: YES)

I am doing this as root and I did try to flush privileges.

I tried this with countless users but it does not seem to work. I can create a user with no password and login works. Command line and from phpmyadmin

share|improve this question
    
Do they show that you've added them? SELECT * from mysql.user; –  Dave Drager Sep 11 '09 at 17:21
    
They do show up in that select. –  Brian G Sep 11 '09 at 17:25

6 Answers 6

The wildcard hostname is only matching TCP connections, not the socket connection used by localhost; you'll need an entry for % and for localhost.

The reason it's working without a password is probably because you have different credentials set in ~/.my.cnf

share|improve this answer
    
James: My first reaction on reading your note of Mar 15 was "Get out of here! If that were true, I'd know it!" Wrong. I didn't know it, and you've just educated me to something I can't believe I'd not already known. I've run into the problem before a couple times, and every time, I appear to have accidentally solved it, by doing what you suggested above, but not understanding why I had to do so. Thanks very much for the wisdom. –  user62664 Dec 6 '10 at 9:04

No network connection is made when you do:

mysql -u brian -ppassword # with no -h

Instead, a socket connection is made. Socket connects do not match the host wildcard '%'. They only match the host 'localhost' as a security measure. You can force a network connection like this:

mysql -u brian -ppassword -h `hostname`

That works most of the time, but I have seen a few systems where it did not. If it works for you, it is a good way to avoid maintaining pairs of grants for each user ('user'@'%' and 'user'@'localhost')

share|improve this answer

Anything different happen if you specify the host?

mysql -ubrian -ppassword -hlocalhost

mysql -ubrian -ppassword -h127.0.0.1

Try checking:

SELECT user, host, password, PASSWORD('password') FROM mysql.user

And make sure the password in the db and the result of the PASSWORD function are the same?

share|improve this answer

You need to explicitly add user@localhost. '%' does not match localhost.

share|improve this answer
    
Are you sure? The documentation indicates that '%' will match any hostname dev.mysql.com/doc/refman/5.1/en/account-names.html –  theotherreceive Sep 11 '09 at 18:09
2  
Yes. localhost has special meaning in mysql it means you are connecting via the local socket. '%' matches any host arriving via a TCP connection. localhost is not the same as 127.0.0.1. –  Craig Sep 11 '09 at 19:02
    
Try changing the command you are using to "sql -u brian -ppassword --host=127.0.0.1" –  Craig Sep 11 '09 at 19:12
    
It's bitten us at work before. Add user@localhost should work. –  sybreon Sep 12 '09 at 3:36

You definatly used

... identified by 'password';

and not

... identified by password 'password';

as the latter expects a password hash value rather than the plaintext password. This would explain why you're unable to login with any of the passwords you set.

share|improve this answer

I usually do it in 2 commands:

CREATE USER 'user1'@'localhost' IDENTIFIED BY 'pass1';
GRANT ALL ON *.* TO 'user1'@'localhost';

Doesn't usually give me any trouble

share|improve this answer

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.