Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have set up MySQL port forwarding in my machine as:

ssh -f -N -L :9906:localhost:3306 <ssh login>

Now, if I try to connect to MySQL from my localhost as:

mysql -u<user> -p<password> -P9906 -h127.0.0.1 <schema> 

It works fine. But, if I take out the -h parameter in my local, i.e, if I connect to MySQL as below from localhost:

mysql -u<user> -p<password> -P9906 <schema> 

I get the following error:

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

Why this behaviour?

I have tried to set the port forwarding for both localhost as well as 127.0.0.1, but the error still remains.

Also, on the server, have given privilege to the user for both localhost as well as 127.0.0.1.

share|improve this question
    
Check your my.ini. Does it exist? Is it in the correct place and accessible? Have you changed something in there related to the hostname? –  Synetech Dec 5 '12 at 15:29

2 Answers 2

up vote 4 down vote accepted

There are four(4) SCENARIOS to pay attention to

SCENARIO #1

When you specify -P<port number> you must be using TCP/IP. Thus, -h<IPAddress> would work.

SCENARIO #2

When you specify -P<port number> without -h<IPAddress>, localhost is assumed. With that happening, TCP/IP is not used but the mysql socket file is used to authenticate.

SCENARIO #3

When you specify -h127.0.0.1 without -P<port number>, localhost is assumed. With that happening, TCP/IP is not used but the mysql socket file is used to authenticate.

SCENARIO #4

When you specify -h127.0.0.1 with -P<port number>, localhost is not assumed. TCP/IP will be used but the user must be defined properly.

Given the error message:

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

and they way you tried to login

mysql -u -p -P9906 -h127.0.0.1

You hit SCENARIO #4

SUGGESTION

In mysql.user, make sure you have

  • user@localhost (ok)
  • user@127.0.0.1, (ok)
  • user@% (not recommended but can do)

with the same password (or different passwords if so desired)

Once you can successfully login, 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

Adding the right user lets you authenticate properly. Your goal should be to setup users in mysql.user in such a way that USER() and CURRENT_USER() have identical output or at least as host-specific as possible.

share|improve this answer
    
I think this answer is off topic. The issue here is not the authorization for @'localhost', @'127.0.0.1' or @'%', but the fact that MySQL connects through the socket file when using localhost like I explained in my answer below. –  ssssteffff Dec 22 '12 at 9:09

MySQL client connects to socket file if no hostname is specified (or if localhost hostname is specified using -hlocalhost, even if you specify a port. So when you try to connect to your remote database using mysql -u -p -P9906, you are actually trying to access to your local MySQL server instance. Enter SHOW DATABASES and your local databases list will be printed.

So, to access the remote instance through port forwarding, you have to specify the host (127.0.0.1) to avoid connecting to socket file.

Here is MySQL documentation :

On Unix, MySQL programs treat the host name localhost specially, in a way that is likely different from what you expect compared to other network-based programs. For connections to localhost, MySQL programs attempt to connect to the local server by using a Unix socket file. This occurs even if a --port or -P option is given to specify a port number. To ensure that the client makes a TCP/IP connection to the local server, use --host or -h to specify a host name value of 127.0.0.1, or the IP address or name of the local server. You can also specify the connection protocol explicitly, even for localhost, by using the --protocol=TCP option.

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.