Cannot log in with created user in mysql

Question

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

Solution 1

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

Solution 2

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.

Comments

[Dave Drager]

Do they show that you've added them? SELECT * from mysql.user;

[Xavi Gil]

They do show up in that select.

[AWesley]

Are you sure? The documentation indicates that '%' will match any hostname dev.mysql.com/doc/refman/5.1/en/account-names.html

[Craig]

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]

Try changing the command you are using to "sql -u brian -ppassword --host=127.0.0.1"

[sybreon]

It's bitten us at work before. Add user@localhost should work.

[Adesso]

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.