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

Answer 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