First ssh with mysqldb in python

Question

I am trying to connect to a MySQL database on a remote server using MySQLdb in python. The problem is that first I need SSH to the host, and then from there I need to connect to the MySQL server. The problem that comes up with me is that MySQLdb does not seem to have a way to establish an SSH connection before connecting to the SQL server. I checked the documentation, but I was out of luck.

This is how I connect:

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

But I really need 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') 

This, of course, is simply composed. Can anybody give any recommendations?

Answer 1

Install the ssh tunnel before using MySQLdb.connect. The tunnel will make it look as if you had local mysql running, set up something like this

 ssh [e-mail] -L 9990:localhost:3306 

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

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

Pay attention to 9990.

Add your public user ssh key to host.com so you don’t have to enter a password every time you want to configure the tunnel (use public key authentication).

If you need to do this in python, there are python-to-ssh binding libraries that you could call from python to configure the tunnel for you.

Answer 2

I prefer to keep the tunnel in python code, I hated creating tunnels manually or separately, thanks to the sshtunnel library, which is very easy to use.

Here is a simple example 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')