How to SSH Tunnel to a Remote MySQL Server with Python

Like all sysadmins, I write scripts to automate routine operations. Lately, though, I have needed to write scripts that automate routine operations on a remote system, and we need the security barriers to be a little higher than in the “old days”.

We’re accessing our database through an SSH tunnel, rather than via a regular encrypted socket. (The SSH connection will eventually require key pairs, and disallow regular passwords.)

If you don’t know what SSH tunnels are, there’s an explanation below.

So, I need to create scripts that will automatically log in to the server, open a tunnel, connect to the database server through this tunnel, and then execute SQL statements. It turns out to be a little difficult… but after some effort, the following script did what I needed:

#! /usr/bin/python
import subprocess as sp
import MySQLdb
import traceback
import sys
from nbstreamreader import NonBlockingStreamReader as NBSR
import os
import signal

try:
    print "Connecting to example.com"
    ssh_process = sp.Popen(['ssh','-L3308:localhost:3306','example.com'], 
        bufsize=0, stdin=sp.PIPE, stdout=sp.PIPE, stderr=sp.STDOUT )
except ValueError, OSError:
    ssh_process.terminate()
    exit()

nbsr = NBSR( ssh_process.stdout )

# delay until we're really logged in
while ssh_process.poll()==None:
    output = nbsr.readline(0.5)
    if output:
        # print output.strip()
        # should probably run this before we try to start another one
        if (output.find('bind: Address already in use') != -1):
            print "Critical error, cannot bind to the address."
            print "Killing the errant process.  Please run this script again."
            sp.call(["lsof","-iTCP@localhost:3308","-t"])
            (pid,err) = sp.communicate()
            pid = int(pid)
            os.kill(pid, signal.SIGQUIT)
            ssh_process.terminate()
            exit()
            break
        if (output.find('Welcome to Ubuntu') != -1):
            print "SSH connection established."
            break


try:
    print "Connecting to database"
    db = MySQLdb.connect( 
        host='127.0.0.1',
        port=3308,
        user='abcdefgh',
        passwd='********', 
        db='test_schema' )
except MySQLdb.Error as e:
    traceback.print_exc()
    ssh_process.terminate()
    exit()

try:
    print "Sending query."
    q = """SELECT name FROM test_table"""
    cur = db.cursor()
    cur.execute( q )
    name = cur.fetchone()
    print name[0]
except MySQLdb.Error as e:
    traceback.print_exc()
    cur.close()
    db.close()
    ssh_process.terminate()

print "Completed."
cur.close()
db.close()
ssh_process.terminate()

The nonblocking strream reader is at: http://eyalarubas.com/python-subproc-nonblock.html

Note that this is test code. It’s not production code. The passwords and other information should be in configuration files, not in the code.

Next step is to turn this into a decorator, so we can create the function to perform the database operations, and wrap it with code that will transform it to execute the operations remotely.

(It’s also possible to do the encryption on the MySQL server’s socket – and require that specific certificates are provided. I’m not certain if one is better than the other.)

SSH tunnels

SSH has a feature where it can forward a local port to a specific port on the remote machine, creating an encrypted tunnel for your traffic. This is done with the -L option. The following forwards port 3308 on the local machine to port 3306 on the remote machine; 3306 is what MySQL runs on:

ssh -L3308:localhost:3306 remotemachine.com

SSH manages this connection, and when you log out of the remote machine, the tunnel is also taken down. What’s nice about this is, you don’t have a socket permanently open. It’s only available when you’re logged on. You can also tunnel anything, so unencrypted services available only on the server can be used remotely. It’s like a temporary VPN.

Here’s a diagram showing SSH and SSH with a tunnel.

The script above uses the subprocess library to execute ssh, and build the tunnel.

    ssh_process = sp.Popen(['ssh','-L3308:localhost:3306','example.com'], 
        bufsize=0, stdin=sp.PIPE, stdout=sp.PIPE, stderr=sp.STDOUT )