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

  • Do they show that you've added them? SELECT * from mysql.user; Sep 11, 2009 at 17:21
  • They do show up in that select.
    – Brian G
    Sep 11, 2009 at 17:25

6 Answers 6


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

  • Are you sure? The documentation indicates that '%' will match any hostname dev.mysql.com/doc/refman/5.1/en/account-names.html Sep 11, 2009 at 18:09
  • 3
    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
    – Craig
    Sep 11, 2009 at 19:02
  • Try changing the command you are using to "sql -u brian -ppassword --host="
    – Craig
    Sep 11, 2009 at 19:12
  • It's bitten us at work before. Add user@localhost should work.
    – sybreon
    Sep 12, 2009 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.


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


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?


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')


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

  • 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, 2010 at 9:04

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.