Python code to establish an ssh to remote server and then connect to MYSQL

Question

I am trying to write some python code to establish an ssh connection to a remote server and then execute some MYSQL queries (I have left these out of the example for simplicity). As far as I can gather the ssh is working but I can't seem to get into the database. Can anyone help?

import MySQLdb
import decimal
import operator
import os
import paramiko



mykey = paramiko.RSAKey.from_private_key_file("C:/Users/Desktop/keyname.pem")

ssh = paramiko.SSHClient()
ssh.load_system_host_keys()
ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())

ssh.connect('ec2-blah-blah.compute.amazonaws.com', username='ec2-user', pkey = mykey) 

conn = MySQLdb.connect(
    host="127.0.0.1",
    port = 3306,
    user="root",
    passwd="password",
    db="MyDB")

The errors I get are:

File "C:\Python27\lib\site-packages\MySQLdb__init__.py", line 81, in Connect return Connection(*args, **kwargs) File "C:\Python27\lib\site-packages\MySQLdb\connections.py", line 187, in in it super(Connection, self).init(*args, **kwargs2) _mysql_exceptions.OperationalError: (2003, "Can't connect to MySQL server on '12 7.0.0.1' (10061)")

You can't do that. What you are doing is open a SSH connection to your remote computer, and then trying to access to a local MySQL server, you are not accessing the remote MySQL server — jabaldonedo
It seems you are trying to create a ssh tunnel. but you need to first check the paramiko abilities about tunnels. — pylover

xgord xgord · Accepted Answer · 2013-06-18T22:44:31

The ip address "127.0.0.1" that you use in your connection object refers to localhost, which is the computer the program is running on. You can't remotely connect to the localhost of another computer. You need to replace the host ip address with the ip address of the remote server.

Also you should only need the MySQLdb.connect object, not the ssh object if you connect correctly.

Python code to establish an ssh to remote server and then connect to MYSQL

1 Answers