mysql new user access denied

Question

Just started with mysql. I login with root and follow the online reference to create a new user:

mysql> CREATE USER 'abc'@'%' IDENTIFIED BY '111111';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'abc'@'%' WITH GRANT OPTION;

then I quit and tried to login with the new user:

mysql --user=abc --password=111111 mysql

But got an error message:

Access denied for user 'abc'@'localhost' (using password: YES)

Did I missed something?

Answer 1

Run this to work

FLUSH PRIVILEGES

Once you have given the desired privileges for your user, you will need to FLUSH privileges in order to complete the setup and to make the new settings work. To do so, run this command within the SQL command prompt:

[EDIT]
If you want to connect from localhost also, you should create another account.

It is necessary to have both accounts for 'user' to be able to connect from anywhere as 'user'. Without the localhost account, the anonymous-user account for localhost that is created by mysql_install_db would take precedence when 'user' connects from the local host. As a result, 'user'would be treated as an anonymous user. The reason for this is that the anonymous-user account has a more specific Host column value than the 'user'@'%' account and thus comes earlier in the user table sort order. )

FYI: http://dev.mysql.com/doc/refman/5.5/en/adding-users.html

Answer 2

In my case the problem was in password. After making it containing lesser amount of special characters, it started working.