mysql over ssh tunel

Aigarzs · 05-12-2013, 02:45 PM

I have made ssh tunnel from my laptop to my home pc

Code:

ssh linuxuser@homenet -L 3307:homenet:3306 -N

I can run mysql from my laptop connecting to localhost:3307

Code:

mysql -h localhost -P 3307 -u mysqluser -p

and in command prompt it works fine, I can issue commands and view outputs.

But connection over ssh tunnel is refused when I want to use it from other software:

1) MySQL client (SQLyog) connection to localhost:3307 gives

Code:

error No.2013 "Lost connection to MySQL server during query";

2) Using java

Code:

connection = DriverManager.getConnection("jdbc:mysql://localhost:3307/mydatabase",
                                   "mysqluser", "mysqlpass");

gives error message

Code:

Communications link failure
The last packet successfully received from the server was 16,139 milliseconds ago. The last packet sent successfully to the server was 16,075 milliseconds ago.
SQLErrorCode: 0
SQLState: 08S01

I can still successfully connect from SQLyog and from java directly to homenet:3306; It is not a firewall issue, since I have turned both firewalls off.
I have increased connection timeout in my.cnf

Code:

connect_timeout=15

It is permitted to user mysqluser to connect from any host

Code:

| Host     | User     |
      |    %     | mysqluser|
      |localhost | root     |

jlinkels · 05-12-2013, 06:23 PM

That is strange indeed because you seemed to have followed the correct procedure.

First thing is to check the mysql log on the server. IIRC a client will receive a "connection error" when authentication is not correct. This disguises the real error but is perhaps done for a reason.

To grant access from all hosts, the wildcard string is 'mysqluser'@'%' Did you use that in your grant statement like that?

jlinkels

Aigarzs · 05-13-2013, 02:52 PM

Thank you jlinkels for your reply.

How can I make mysql to log all refused connections and other errors?
The only log file for mysql I have found is located at /var/lib/mysql/[Hostname].err, and there is logged only mysqld_safe starting and shutdown entries, nothing else.

Running on Slackware 13.1.0.

I think that user privileges are set correctly, as I can connect with mysqluser to mysql server when not using tunnel.

Do I understand correctly, that when I use tunnel, mysql thinks that I am connecting from localhost?

How do I verify that tunnel is set up and running corectly and not broken by some rooter or something like that?
I used from server

Code:

netstat -nt

and that only shows connection to 22 port, not the tunnel to 3306 port.

Aigarzs · 05-13-2013, 03:26 PM

Also I have found, that I can create tunnel and mysql connection when connecting from laptop inside home network.

Code:

 ssh 192.168.0.101 -L 3307:192.168.0.101:3306 -N

Everything works nice.

Problem stays when I am connecting from outside to my static IP.

Only difference that I see is router in between. Router have forwarded ports 22 and 3306 to my desktop PC.

Can router break or prohibit tunnel somehow? SSH connection to 22 port is working.

jlinkels · 05-13-2013, 09:02 PM

You should really get the MySQL logging active. Check the MySQL website about how to do it. It is well documented. Don't forget to turn it off after you solved the problem, some those logs can grow quickly in size.

Not sure what exactly the source IP is of a tunneled connection. Never looked at it. No, routers and firewalls should have no influence because you are tunneling. I would start a tcpdump on port 3307 or 3306 on the MySQL host to look what exactly comes in. From whom, what is answered, etc. and take it from there together with the logs.

Somehow it seems to be related with the client that you are using. When MySQL client works properly it should almost be guranteed that everything else works as well. That is so strange about this problem.

jlinkels