Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

It seems that the solution to accepting users from localhost is to create a copy of the user with @'localhost' (in addition to user@'%'). I'm trying very hard to understand the logic here, because it seems kind of bat-nuts crazy that the 'any host' wildcard, '%', wouldn't also accept localhost connections. Creating a second user isn't very practical when dealing with a large number of users. If a user changes their own password, it would then leave the other one unaffected.

Is there any sort of workaround to allowing user@'%' to accept localhost connections?

share|improve this question

2 Answers 2

The localhost style of connecting to mysqld forces the mysql client to authenticate via the socket file.

Connecting via the wildcard % demands the connecting to mysqld via TCP/IP.

If have myuser@'%' with a password of mys3cr3t, and you do not want to add myuser@'localhost', I would like to suggest the following:

Connect to mysql like this

mysql --protocol=tcp -umyuser -pmys3cr3t

Using --protocol=tcp forces the mysql client program to use the TCP/IP to connect

Once you connect, run this query

SELECT USER(),CURRENT_USER();

USER() reports how you attempted to authenticate in MySQL

CURRENT_USER() reports how you were allowed to authenticate in MySQL

I wrote about this in the DBA StackExchange back on Jan 18, 2012.

share|improve this answer

You need to remove the anonymous users, as MySQL's pattern matching will match them first.

Run the mysql_secure_installation script or remove them manually.

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.