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 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.

share|improve this question
    
Have you check the MySQL logs to see if they provide any additional useful info? –  heavyd Feb 10 '10 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 Feb 10 '10 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 Feb 10 '10 at 15:54
add comment

2 Answers

up vote 3 down vote accepted

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.

share|improve this answer
    
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 Feb 10 '10 at 13:34
    
Yes, removing 127.0.0.1 from tunnel specification is no good. Result is the same. –  maksymko Feb 10 '10 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 Feb 10 '10 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 Feb 10 '10 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 Feb 12 '10 at 8:08
show 1 more comment

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

share|improve this answer
add comment

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.