Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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          | %               |
share|improve this question

2 Answers 2

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

share|improve this answer
    
Thanks that did it. Strange on Ubuntu it seems to work without the --proto=TCP but on RHEL I guess you need it. –  Tom Gee Jan 16 '12 at 18:12
    
It's worth pointing out this is probably bad practice for production configurations. –  atxdba Jan 16 '12 at 18:14
    
how so? what would be the correct syntax to add users in that case? –  Tom Gee Jan 16 '12 at 18:16
    
Security. If you only want a user to connect via localhost then specify @'localhost' in the grant. Using '%' potentially allows connections from any internet address (assuming your port is open to that). It's more prudent to restrict the credentials to your network. e.g. 'user'@'10.1.%' –  atxdba Jan 16 '12 at 18:24

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.