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?

Try to make a SSH tunnel http://stackoverflow.com/questions/3577555/ssh-tunnel-for-python-mysqldb-connection — Igor Hatarist, Jan 30 '12 at 21:04

score 20 · Answer 1 · answered Aug 08 '15 at 06:00

20

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

answered Aug 08 '15 at 06:00

jsjc

1,003
2
12
24

what are the changes needed here if the mysql db on remote host is a amazon rds – Coder 477 Apr 26 '16 at 09:48
in sshhost.domain.com you must use the SSH machine you want to connect which can see RDS then change mysqlhost.domain.com to your rds.db.domain.com. Should work like that. – jsjc Apr 26 '16 at 15:38
This is by far the best answer for this problem. I was stuck for 4 days until I ran across this solution! – Maciek Semik Jul 26 '17 at 17:52
2

Excellent Answer! I just wanted to add that you can also connect with pymysql instead of MySQLdb if you prefer. Everything else is the same :) Thanks, man! – Brad Ahrens Apr 21 '18 at 16:04
okay, for aws rds, we need to use the configure remote bind address with mysql dns (private dns) and the mysql connector hostname with localhost. took me a while to notice. thanx – Sriram Arvind Lakshmanakumar Oct 12 '21 at 06:39

rapadura · Accepted Answer · 2012-01-30T21:33:46.713

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 user@host.com -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.

Thanks. That did the trick. I had to use 127.0.0.1 instead of localhost, in case anyone else is having a similar problem. — rjf, Jan 31 '12 at 14:38

ssh first with mysqldb in python

2 Answers2

Linked