4

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.

1
  • 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, 2012 at 15:29

2 Answers 2

7

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

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.

1
  • 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, 2012 at 9:09
1

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .