mysql user creation with % to host , can only log on from remote host

Question

Using MySQL v5.1.52

After creating a mysql user with the following commands:

create user 'user1'@'%' identified by 'pass1';
grant select on db1.* to 'user1';
flush privileges;

Then doing a 'mysql -uuser1 -p' in the command line then entering the password I still get this error:

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

However when connecting from a remote system (with the -h) to the above server using the above credentials it works.

Why can I not log on locally with this user?

Here is the user/host outpout:

mysql> select User,Host from mysql.user;
+----------------+-----------------+
| User           | Host            |
+----------------+-----------------+
| user1          | %               |

Answer 1

When connecting to your localhost it's going you use sockets instead of ports to connect. Those grants need a specific 'localhost' grant. To use your % grant locally connect specifying you want to connect via TCP

mysql -u user -p --proto=TCP

Further reading

Answer 2

In MySQL 'localhost' and '127.0.0.1' have different meanings. The former will attempt to connect via the socket file, the latter will go via TCP.

To connect to a user defined with @% (any host), you'll want to connect using TCP, as suggested in @atxdba's answer.

An alternative way would be to provide the host '127.0.0.1':

mysql -h127.0.0.1 -uuser -p

The mysql command line will also try to read from user configs in the [client] section of an option file (which is located in /etc/my.cnf usually, as well as any overrides like ~/.my.cnf).

So if one of those files has a section like this:

[client]
host=127.0.0.1

Then mysql will automatically try to connect via TCP.