3

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!

mcfly
  • 1,151
  • 4
  • 33
  • 55

3 Answers3

2

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.

Community
  • 1
  • 1
poke
  • 369,085
  • 72
  • 557
  • 602
  • Am I not doing the same thing with the first three lines of my code (after the imports)? I'm not sure I quite understand what to do differently. Thanks! – mcfly Jan 27 '14 at 16:36
  • No, you’re just connecting via SSH; that has no impact on other things though. You will need to tunnel the host’s MySQL port to some local port. – poke Jan 27 '14 at 16:40
  • I see (complete noob). I'm trying to use the "forward.py" with command but do not understand how to use this information. Everything I try errors out. Could you assist with this? – mcfly Jan 27 '14 at 16:43
  • Unfortunately I have neither knowledge about Paramiko, nor about if MySQL would be already fine with just having the port forwarded. I’d suggest you to try this with an external SSH client first and get the MySQL connection working then. If that works, you can try to apply the forward configuration with Paramiko. – poke Jan 27 '14 at 16:46
  • Thanks -- I used an SSH client and got it working flawlessly (after taking out paramiko). – mcfly Jan 27 '14 at 18:07
  • Good to hear that :) Glad I could help! – poke Jan 27 '14 at 19:04
0

If you still in need of connecting to a remote MySQL db via SSH I have used a library named sshtunnel, that wraps ands simplifies the use of paramiko (a dependency of the sshtunnel).

You can check my answer in another similar question with some sample code to use it.

Community
  • 1
  • 1
jsjc
  • 1,003
  • 2
  • 12
  • 24
-1

db = MySQLdb.connect(host="FTP_hostname",

Would the host not need to be 127.0.0.1 (localhost) as the tunnel is making the MySQL DB local to the machine that the python script is running on?

Rich107
  • 1