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

Also check to see if the user was in mysql.user which it is.

Show grants for brian shows:

| GRANT ALL PRIVILEGES ON *.* TO 'brian'@'%' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' | 

Answer 1

You probably have this perpetual MySQL problem where one of the default users in the user table is '' @ localhost, which winds up denying all localhost users later in the table. What I would do is mysqldump the mysql database and look for this entry in the User table; if found, delete it and flush privileges.

For more details see https://dev.mysql.com/doc/refman/5.5/en/connection-access.html.

It is a common misconception to think that, for a given user name, all rows that explicitly name that user are used first when the server attempts to find a match for the connection. This is not true. The preceding example illustrates this, where a connection from h1.example.net by jeffrey is first matched not by the row containing 'jeffrey' as the User column value, but by the row with no user name. As a result, jeffrey is authenticated as an anonymous user, even though he specified a user name when connecting.