PROBLEM:

You granted access privileges to a user connecting from anywhere using this command:

mysql> GRANT ALL PRIVILEGES ON *.* TO prueba@'%' IDENTIFIED BY 'password'

Then, when you try to access to mysql server using:

# mysql -u prueba -ppassword

you get the following error:

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

The problem is that MySQL, by default, creates anonymous users that are granted access from localhost that have higher precedence (see here for details):

mysql> select User, Host, Password from mysql.user;
+---------+-----------+-------------------------------------------+
| User    | Host      | Password                                  |
+---------+-----------+-------------------------------------------+
| root    | localhost |                                           |
| root    | helicon   |                                           |
| root    | 127.0.0.1 |                                           |
| root    | ::1       |                                           |
|         | localhost |                                           |
|         | apollo    |                                           |
| prueba  | %         | *AC52124462B6D4C373263062D60ED9CD452E574D |
+---------+-----------+-------------------------------------------+
7 rows in set (0.00 sec)

SOLUTION:

Just remove the anonymous accounts and you will be able to login with the user you previously defined:

mysql > DROP USER ''@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql > DROP USER ''@'apollo';
Query OK, 0 rows affected (0.00 sec)

Share!