3

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.

Earnest Jason
  • 51
  • 1
  • 4

1 Answers1

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()
jsjc
  • 1,003
  • 2
  • 12
  • 24
  • 3
    yup, this one worked. In case you are using id_rsa just replace `ssh_password` parameter with `ssh_private_key=PATH_TO_id_rsa` – Manuel G Jul 29 '15 at 23:16
  • Yep! that's another option ;) – jsjc Jul 30 '15 at 02:25
  • 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 Aug 04 '15 at 10:45
  • 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 !) – 62mkv Aug 04 '15 at 11:00
  • 1
    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! – jsjc Aug 04 '15 at 16:25