mysql - 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?

Answer 1

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')