2

I have a mysql server running on some server in our network. Now, only connections from localhost are accepted by the server and I can't connect to it remotely. Now, I want to set up a port forwarding, using ssh to be able to use some GUI tools with this server. However, somehow I can not get authenticated, when I connect via tunnel! It goes like this:

On the remote machine:

mysql -u exporter --password=test -h localhost -P 3306 mydatabase

Everything works perfectly and I get a connected mysql shell.

Now, on my local machine, to set up port forwarding:

ssh remotemachine -L127.0.0.1:33066:127.0.0.1:3306

TO connect to the remote mysql server, using this tunnel, on the local machine I do:

mysql -u exporter --password=test -h localhost -P 33066 mydatabase

Here is the error I get:

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

How is that even possible and what can I do about it? Note: the problem is totally reproducible with another mysql server on another remote machine.

Improve this question
3
  • Have you check the MySQL logs to see if they provide any additional useful info?
    – heavyd
    Commented Feb 10, 2010 at 13:25
  • There is absolutely nothing in the logs about this connection, which is kinda odd too. At least I can't find any in /var/log/mysql/whatever.log. May be the server was configured this way, I don't know.
    – maksymko
    Commented Feb 10, 2010 at 14:02
  • What if you remove the embedded password (test), so make MySQL prompt you for it? Then maybe you can see if MySQL is accepting the connection to start with. (As MySQL allows for password-less accounts, I assume that any prompt for credentials is really initiated by the server.)
    – Arjan
    Commented Feb 10, 2010 at 15:54

3 Answers 3

Reset to default
3

Did you try using

ssh -L 33066:localhost:3306 remotemachine

?

Please make also sure that inside the mysql privilege system, both 127.0.0.1 and localhost are allowed sources for your database user.

Improve this answer
6
  • Hmmm, SQLSTATE 28000 is "Invalid authorization specification", so I guess the connection is fine. Still, maybe "Access denied" might indeed also indicate it simply cannot connect at all. For debugging, one might also compare ssh -L 127.0.0.1:33066:www.google.com:80 remotemachine to ssh -L 33066:www.google.com:80 remotemachine and then see where http://localhost:33066 takes one.
    – Arjan
    Commented Feb 10, 2010 at 13:34
  • Yes, removing 127.0.0.1 from tunnel specification is no good. Result is the same.
    – maksymko
    Commented Feb 10, 2010 at 13:54
  • @Arjan van Bentem: forwarding the port to google worked as expected... Do you know, where at least I can find more details about this error? This failed connection attempt must have been logged somewhere, mustn't it?
    – maksymko
    Commented Feb 10, 2010 at 13:57
  • 2
    Is you local mysql user allowed to connect from 127.0.0.1 and localhost inside the mysql privilege system (maybe it's just a name vs IP issue) ?
    – Dominik
    Commented Feb 10, 2010 at 14:04
  • @Dominik OH MY GOD!!! It worked!!! It is unbelievable, never even thought of that, thank you! I'm accepting your answer, but please edit it to include this solution.
    – maksymko
    Commented Feb 12, 2010 at 8:08
1

Just tried to do the same myself today and found out that when you connect to localhost/127.0.0.1 the client automaticaly tries to connect to the process via a local socket instead of a normal tcp/ip connection, wich avoids the ssh tunnel.

You have to instead use your local network address (ex.: 192.168.0.35) instead of the loopback device address(127.0.0.1).

You will also need to add the -g parameter to your ssh command line to make that port forward available via non-loopback-device address 192.168.0.35:3306, otherwise it would just be available via 127.0.0.1:3306

Improve this answer
0

Cross-post of this answer

I had a similar issue, and per this StackOverflow answer, the solution was removing the 'any' user.

It seems that MySQL sees a difference between a user connecting via localhost and 127.0.0.1, which is part of the issue if I understand it correctly.

Improve this answer

You must log in to answer this question.

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