Using SSHTunnelForwarder to connect to a MySQL db via SSH

Question

I am trying to connect to a mysql db over ssh in python, but am getting an error. I have the following saved in a python file 'forward.py'. My code is as follows:

forward.py

from sshtunnel import SSHTunnelForwarder
import MySQLdb

with SSHTunnelForwarder(
      ('ssh_host', 22),
      ssh_password="ssh_password",
      ssh_username="ssh_username",
      remote_bind_address=('mysql_host', 3306)) as server:

     con = MySQLdb.connect(user='mysql_username',passwd='mysql_password',db='mysql_db_name',host='mysql_host',port=server.local_bind_port)

When I run forward.py in terminal, I receive the following error:

_mysql_exceptions.OperationalError: (2003, "Can't connect to MySQL server on     
'mysql_host' (60)")

It is worth noting that the values are hardcoded values for security, i.e. 'mysql_host' is an actual host that I can ssh to just fine when I run

ssh mysql_host

via terminal. I can also connect just fine using Sequel Pro with the same values, as per: http://screencast.com/t/0niuWlMDb

Can anyone point me in the right direction? Thanks in advance

Solution 1

I think your issue lies in the fact that you're creating a local SSH tunnel but attempting to connect directly to a remote host, which defeats the purpose of using a SSH tunnel.

I believe if you change your connection string from host='mysql_host' to host='127.0.0.1' you will have solved your issue.

If you look at the debugging / connection settings of your Sequel Pro instance you will see the actual connection commands being used and should hopefully confirm that this is the case.

Further reference material on SSH forwarding : http://blog.trackets.com/2014/05/17/ssh-tunnel-local-and-remote-port-forwarding-explained-with-examples.html

Solution 2

If you're using SSH tunnel forwarding you need to connect to the 'localhost:3306' instead of your actual 'mysql-host:3306'.

Let me explain what happened:

1. You establish connection with SSH to the remote mysql host
2. Your client setup tunnel and listen for TCP:3306 on your local host: localhost:3306
3. Any traffic to localhost:3306 will be transparently redirected to the mysql_host:3306 through your SSH tunnel.

Try using 'localhost:3306' in your config. Take a look here for few examples, it may help you too.