Python - SSH Tunnel Setup and MySQL DB Access

Question

I am trying to connect to my server from my local(windows) and access the MySQL DB

With the below code setting up the SSH tunnel through putty, I am not able to access the MySQL DB.

con = None
con = mdb.connect(user='user',passwd='password',db='database',host='127.0.0.1',port=3308)
cur = con.cursor()

With the below code, I am using paramiko to setup SSH tunnel which is successful but I am not able to connect to MySQL DB

ssh = paramiko.SSHClient()
ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
ssh.connect('host', username='username', password='password')

con = None
con = mdb.connect(user='user',passwd='password',db='database',host='127.0.0.1',port=3308)
cur = con.cursor()

Error:
Error 2003: Can't connect to MySQL server on '127.0.0.1' (10061)

Do I have change the MySQL connecting string settings to access MySQL DB using paramiko If not I need to add anymore parameter for paramiko to simulate SSH tunnel setup like putty.

jsjc · Accepted Answer · 2015-07-26 16:13:53Z

7

You can use sshtunnel wrapper for paramiko and save you headaches ;)

from sshtunnel import SSHTunnelForwarder
import MySQLdb

with SSHTunnelForwarder(
         ('host', 22),
         ssh_password="password",
         ssh_username="username",
         remote_bind_address=('127.0.0.1', 3308)) as server:

    con = None
    con = mdb.connect(user='user',passwd='password',db='database',host='127.0.0.1',port=server.local_bind_port)
    cur = con.cursor()

answered Jul 26, 2015 at 16:13

jsjc

1,0232 gold badges13 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Manuel G Over a year ago

yup, this one worked. In case you are using id_rsa just replace ssh_password parameter with ssh_private_key=PATH_TO_id_rsa

jsjc Over a year ago

Yep! that's another option ;)

62mkv Over a year ago

what is mdb ? I have an error which says "mdb not defined". after replacing it with "MySQLdb" (just a guess), I know have an error:

File "C:\Python27\lib\site-packages\MySQLdb\cursors.py", line 202, in execute     self.errorhandler(self, exc, value)   File "C:\Python27\lib\site-packages\MySQLdb\connections.py", line 36, in defaulterrorhandler     raise errorclass, errorvalue _mysql_exceptions.OperationalError: (2006, 'MySQL server has gone away')

62mkv Over a year ago

Ok, I got it - I was working with cur beyond the scope of "with .. server:" construction ! 4 spaces did the magic (as usual in Python !)

jsjc Over a year ago

This example was made directly for Earnests question thats why kept it as mdb, so I left things as per his sample. Glad you got it sorted!

Collectives™ on Stack Overflow

Python - SSH Tunnel Setup and MySQL DB Access

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related