MySQLdb + SSH Tunnel

[Riley Crane]

OVERVIEW:
I am running a script on one machine that connects to a MySQL database
on another machine that is outside of our university's domain.
According to the administrator, network policies do not allow the
compute nodes to access machines outside of our university's domain.

COMPUTERS:
A = compute node within university (I do not have shell access)
B = 2nd machine within university that does not block outside
connections (I have root access)
C = machine outside of university (I have root access)
mysqldb on A cannot connect to C ....but.....
mysqldb on A can connect to B

WHAT I WOULD LIKE TO DO:
Is it possible to set something up where A talks to a port on B, and
that port is actually nothing more than 3306 on C? Can I do this with
an SSH tunnel?

Can anyone please give precise instructions?

 

[Emile van Sebille]

On 7/12/2009 12:18 PM Riley Crane said...

OVERVIEW:
I am running a script on one machine that connects to a MySQL database
on another machine that is outside of our university's domain.
According to the administrator, network policies do not allow the
compute nodes to access machines outside of our university's domain.

COMPUTERS:
A = compute node within university (I do not have shell access)
B = 2nd machine within university that does not block outside
connections (I have root access)

....so B can talk to C presumably...

C = machine outside of university (I have root access)
mysqldb on A cannot connect to C ....but.....
mysqldb on A can connect to B



WHAT I WOULD LIKE TO DO:
Is it possible to set something up where A talks to a port on B, and
that port is actually nothing more than 3306 on C? Can I do this with
an SSH tunnel?

Can anyone please give precise instructions?

ssh with something like...

ssh -lroot -L3306:C:3306 B

Watch out for other instances of mysql on A or B...

Emile

 

[Lawrence D'Oliveiro]

ssh with something like...

ssh -lroot -L3306:C:3306 B

Watch out for other instances of mysql on A or B...

You can use a non-default local port and specify that in your local
connection parameters. Similarly you can tell the remote server to use a
non-default port in its /etc/my.cnf.

 

[R C]

Got it working. Thanks for your help

1) login to B
2) setup a tunnel in the shell machine-B> ssh -L
B_ip_address:B_port:C_ip_address:C_port user@C_ip_address

for example:
machine-B has ip 1.1.1.1
machine-C has ip 2.2.2.2

then I would type:
machine-B> ssh -L 1.1.1.1:3307:2.2.2.2:3306 [email protected]

now the code that is running on machine-A would use MySQLdb in the
following way

import MySQLdb
connection = MySQLdb.connect
(user='myname',passwd='mypass',db='mydb',host='1.1.1.1',port=3307)

NOTE: the port is an integer, NOT a string

 

[Riley Crane]

Got it working. Thanks for your help!

1) login to B
2) setup a tunnel in the shell machine-B> ssh -L
B_ip_address:B_port:C_ip_address:C_port user@C_ip_address

for example:
machine-B has ip 1.1.1.1
machine-C has ip 2.2.2.2

then I would type:
machine-B> ssh -L 1.1.1.1:3307:2.2.2.2:3306 [email protected]

now the code that is running on machine-A would use MySQLdb in the
following way

import MySQLdb
connection = MySQLdb.connect
(user='myname',passwd='mypass',db='mydb',host='1.1.1.1',port=3307)

NOTE: the port is an integer, NOT a string