ssh first with mysqldb in python

+1 vote
I want to connect to a MySQL database on a remote server using MySQLdb in python. The problem is that first I need to SSH into the host, and then from there, I need to connect to the MySQL server. The problem I'm having, though, is that MySQLdb does not seem to have a way of establishing an SSH connection before connecting to the SQL server. I've checked the documentation but have not had any luck.

I am trying it in the following way:

conn = MySQLdb.connect(host = 'mysqlhost.domain.com:3306', user = 'user', passwd = 'password', db = 'dbname')
But what I really need is something like this:

conn = MySQLdb.connect(sshhost = 'sshhost.domain.com', sshuser = 'sshusername', sshpasswd = 'sshpasswd', host = 'mysqlhost.domain.com:3306', user = 'user', passwd = 'password', db = 'dbname')
 

But this doesn't help much. Can anyone provide any suggestions?
May 23, 2019 in Python by SDeb
• 13,300 points
2,803 views

1 answer to this question.

0 votes
Setup an ssh tunnel before you use MySQLdb.connect. The tunnel will make it appear as though you have the mysql running locally, set it up something like this

ssh user@host.com -L 9990:localhost:3306
here your local port 9990 will bind to 3306 on the remote host, -L stands for local, then 9990:localhost:3306 means LOCALPORT:

conn = MySQLdb.connect(host = 'mysqlhost.domain.com:9990', user = 'user', passwd = 'password', db = 'dbname')

notice the 9990.

Add your public ssh key of user to the host.com so that you dont have to type the password each time you want to setup the tunnel (use public key authentication).

If you need to do this within python there is python-to-ssh binding libraries you could call from within python to setup the tunnel for you.
answered May 23, 2019 by ana1504.k
• 7,910 points

Related Questions In Python

+1 vote
2 answers

How to print first character of each word in upper case of a string in Python

class Solution:     def firstAlphabet(self, s):             self.s=s              k=''              k=k+s[0]              for i in range(len(s)):                     if ...READ MORE

answered Oct 28, 2020 in Python by Anurag
12,435 views
0 votes
1 answer

Create an empty list in python with certain size

Try this instead: lst = [None] * 10 The ...READ MORE

answered Aug 2, 2018 in Python by bug_seeker
• 15,510 points
28,146 views
0 votes
1 answer

How to replace values with None in Pandas data frame in Python?

Actually in later versions of pandas this ...READ MORE

answered Aug 30, 2018 in Python by Priyaj
• 58,020 points
11,777 views
0 votes
1 answer

How can I logarithmic axes with matplotlib in python?

You can use the Axes.set_yscale method. That allows you ...READ MORE

answered Oct 15, 2018 in Python by charlie_brown
• 7,720 points
2,331 views
0 votes
1 answer

Python, mysqldb and unicode

What you get is a bytestring. You ...READ MORE

answered Aug 1, 2019 in Python by SDeb
• 13,300 points
1,786 views
0 votes
1 answer

ProgrammingError: not all arguments converted during string formatting

Sorted!!!! just found the solution, 1 - apparently ...READ MORE

answered Sep 10, 2018 in Python by Priyaj
• 58,020 points
20,324 views
0 votes
2 answers

How do I connect to a MySQL Database in Python?

connect mysql database with python import MySQLdb db = ...READ MORE

answered Mar 28, 2019 in Python by rajesh
• 1,270 points
1,911 views
0 votes
1 answer

ProgrammingError: not all arguments converted during string formatting

Sorted!!!! just found the solution, 1 - apparently ...READ MORE

answered Sep 20, 2018 in Python by Priyaj
• 58,020 points
5,733 views
0 votes
1 answer

Power Math in Python

When you use "^" operator, it is ...READ MORE

answered Nov 12, 2018 in Python by ana1504.k
• 7,910 points
779 views
0 votes
1 answer

% operator in Python

When you use '(%g,%g)', it is the ...READ MORE

answered Nov 12, 2018 in Python by ana1504.k
• 7,910 points
988 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP