Python and Connecting to MySQL over SSH

Question

I am trying to connect to a MySQL database on someone else's "machine". When I use Navicat for MySQL, I have no problem connecting to it. I am trying to do the same with Python so that I do not have to use the GUI interface. I know all my info below is correct (even though I swap fake info) -- can anyone spot where I went wrong? The error I get is OperationalError: (2005, "Unknown MySQL server host 'FTP_hostname' (0)")

My code (using paramiko for the SSH):

import MySQLdb
import paramiko
import time

ssh = paramiko.SSHClient()
ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
ssh.connect('SSH_hostname', 22, username='me', password='pswrd')

time.sleep(1)

db = MySQLdb.connect(host="FTP_hostname", 
                     user="root", 
                     passwd="pswrd2",
                     db="MyDB")
cur = db.cursor()

Again, I put all this into Navicat and connect no problem. Hoping you can help! Thanks!

Answer 1

MySQL, like most databases, by default runs locally and disallows access from outside networks. As such, you cannot connect to it from an external computer.

Navicat, being a software explicitely for remote administration of databases, likely connects via SSH and tunnels the MySQL connection over it. That way it can act as if the database was installed locally, and for the database it looks as if it was accessed locally.

You could try to do the same by creating a tunnel using Paramiko; see also this question.