Lately I wrote a short article about MySQL tunneling via SSH in order to start safe MySQL replication. Afterwards I noticed some problems with creating a new SSH tunnel for MySQL connection on a quite different environment. After creating SSH tunnel and trying to connect via this tunnel to the SSH server I received SSH error on tunnel error-log:
channel 2: open failed: connect failed: Connection refused
channel 3: open failed: connect failed: Connection refused
ERROR 2013 (HY000): Lost connection to MySQL server during query
in the MySQL terminal.
First of all We have to make sure, that our tunnel is working properly, so We just kill the current tunnel and create new one without “-f” and “-N” options:
ssh -p 2345 [email protected] -L 4406:mysqlmaster-server.com:3306
If everything is ok, then We can assume that tunnel is working fine. We can also try to create another tunnel to some other service on different target port and then just try if this other service is working via the tunnel – just to exclude any problems with SSH tunneling.
My problem was that MySQL was configured in the way it was blocking any connections outside localhost. It is default MySQL configuration – We can achieve it via my.cnf entries:
bind-address = 127.0.0.1
So in order to make our MySQL accessible via our tunnel We have to comment out the skip-networking line and make sure that We are connecting to the correct IP addr in our tunnel. For example If we have in our my.cnf this line:
ssh -p 2345 -f [email protected] -L 4406:127.0.0.1:3306 -N
(notice that 127.0.0.1 in the above command).
If We would bind our MySQL to some other IP, like:
bind-address = 192.168.0.12
Then We should change our tunneling parameters:
ssh -p 2345 -f [email protected] -L 4406:192.168.0.12:3306 -N
After commenting out that skip-networking our security depends on IP address we are binding the MySQL to. If it’s local IP addres in DMZ, than there is no security breaches here. Unwise would be to bind to the WAN address and leave MySQL port opened without any SSL encryption or without filtering traffic by the client IP addr…