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