ssh first with mysqldb in python

Question

I'm trying to connect to a MySQL database on a remote server using MySQLdb in python. The problem is that first I need to SSH into the host, and then from there, I need to connect to the MySQL server. The problem I'm having, though, is that MySQLdb does not seem to have a way of establishing an SSH connection before connecting to the SQL server. I've checked the documentation but have not had any luck.

This is how I'm connecting:

conn = MySQLdb.connect(host = 'mysqlhost.domain.com:3306', user = 'user', passwd = 'password', db = 'dbname')

But what I really need is something like this:

conn = MySQLdb.connect(sshhost = 'sshhost.domain.com', sshuser = 'sshusername', sshpasswd = 'sshpasswd', host = 'mysqlhost.domain.com:3306', user = 'user', passwd = 'password', db = 'dbname')

Which is of course just made up. Can anyone make any recommendations?

Solution 1

Setup an ssh tunnel before you use MySQLdb.connect. The tunnel will make it appear as though you have the mysql running locally, set it up something like this

ssh [email protected] -L 9990:localhost:3306

here your local port 9990 will bind to 3306 on the remote host, -L stands for local, then 9990:localhost:3306 means LOCALPORT:

conn = MySQLdb.connect(host = 'mysqlhost.domain.com:9990', user = 'user', passwd = 'password', db = 'dbname')

notice the 9990.

Add your public ssh key of user to the host.com so that you dont have to type the password each time you want to setup the tunnel (use public key authentication).

If you need to do this within python there is python-to-ssh binding libraries you could call from within python to setup the tunnel for you.

Solution 2

I prefer keeping the tunnel within the python code, I did hate to create tunnels manually, or separately, thanks to sshtunnel library its very simple to use.

Here is some simple sample that will work for what you want.

import MySQLdb
from sshtunnel import SSHTunnelForwarder

with SSHTunnelForwarder(
         ('sshhost.domain.com', 22),
         ssh_password="sshpasswd",
         ssh_username="sshusername",
         remote_bind_address=('mysqlhost.domain.com', 3306)) as server:

    conn = MySQLdb.connect(host='127.0.0.1',
                           port=server.local_bind_port,
                           user='user',
                           passwd='password',
                           db='dbname')