Run ssh port forwarding in background before connecting to mySql server

Question

I am writing a python script that will open up an ssh connection to do port forwarding, something like this:

import pexpect
import sqlalchemy as sql

child = pexpect.spawnu('ssh -L 3306:remote.db.host:3306 username@hostname')
child.expect (u'password:')
child.sendline ('xxxxxxxx')

while child.isalive():
    try:
        engine = sql.create_engine('mysql+pymysql://ro:xxxx@127.0.0.1:3306/testdb')
        connection = engine.connect()
        #run queries 
        connection.close()
        break
    finally:
        child.close()

This script is spiting out this error and I don't know what to do.

sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (2003, "Can't connect to MySQL server on '127.0.0.1' ([Errno 61] Connection refused)")

If I use 'child.interact()' after child.sendline(..), I will end up logged in to the forwarding server. But what I really want is to go on and run a sql query to fetch data from the remote database on localhost port 3306.

So, how can I put the ssh connection to run in the background? I need to make sure that connection is made before I can connect to mysql server.

@AlastairMcCormack Yes. Unfortunately it didn't work for me. The terminal just hangs in there without doing anything and I have to manually kill it. Without python I usually execute this cmd - "ssh -L 3306:remote.db.host:3306 username@hostname" and type in my password in the shell. Once authenticated I access the database in mysql workbench by connecting to localhost:3306. — user2714753, Mar 18 '16 at 21:52
pypi.python.org/pypi/sshtunnel is a native Python tunnel. It doesn't run in your terminal.... — Alastair McCormack, Mar 18 '16 at 22:08
@AlastairMcCormack I used it in a Python script as mentioned here - http://stackoverflow.com/questions/12989866/python-ssh-tunnel-setup-and-mysql-db-access/31639144#31639144 and the terminal just got hanging in there without doing anything and finally I had to kill it manually. — user2714753, Mar 18 '16 at 22:17

penchant · Accepted Answer · 2016-03-28T21:50:10.867

2

You are nearly there. Try using the -N argument to your ssh command so it returns immediately after creating the tunnel: ssh -LN 3306:remote.db.host:3306 username@hostname.

I would also use ssh keys (potentially sans passphrase or use a keyring of some sort) so you don't have to use pexpect.

try creating a bash script to create the tunnel and run the python script like so:

#!/bin/bash

ssh -LN 3306:remote.db.host:3306 username@hostname
python your_script_name

edited Mar 28 '16 at 21:50

answered Mar 18 '16 at 23:03

penchant

2,263
3
14
20

I used the option as -N -L and now I get this error: sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (2003, "Can't connect to MySQL server on '127.0.0.1' ([Errno 61] Connection refused)") – user2714753 Mar 18 '16 at 23:23
if I use the argument as -LN I get the below error: raise EOF('End Of File (EOF). Empty string style platform.') pexpect.exceptions.EOF: End Of File (EOF). Empty string style platform. During handling of the above exception, another exception occurred: Traceback (most recent call last):.. ...raise EOF(msg) pexpect.exceptions.EOF: End Of File (EOF). Empty string style platform. command: /usr/bin/ssh args: ['/usr/bin/ssh', '-LN', '3306:remote.db.host:3306', 'username@host'] ..before (last 100 chars): "Bad local forwarding specification 'N'\r\n". – user2714753 Mar 18 '16 at 23:34
I'm not sure what to say about the pexepect stuff. I would try to make it work with a passphraseless ssh key first to be sure it's working properly. – penchant Mar 18 '16 at 23:59
also, can you check and see if the sockets from the ssh command are open on your client and server? You can use `netstat` or `ss` to do this. check it before and after to make sure the sockets are actually opened. You might want to put a sleep in your python code after the call to initiate the tunnel – penchant Mar 19 '16 at 00:00
If I manually create the tunnel and run this command - sudo lsof -i -n | egrep '\' I see the following: TCP xx.xxx.17.87:62082->xx.x.xxx.133:ssh (ESTABLISHED) TCP [::1]:mysql (LISTEN) TCP 127.0.0.1:mysql (LISTEN) But if I run my python script I only see the following, it doesn't say anything about mysql listening. TCP xx.xxx.17.87:62557->xx.x.xxx.133:ssh (ESTABLISHED) – user2714753 Mar 20 '16 at 00:05
honestly don't know why it wouldn't work at this point... if it were me, I would just wrap the ssh call to create the tunnel and the call to python in a bash script that calls both. i'll update the answer, this might help.. – penchant Mar 28 '16 at 21:47
Your suggestion actually worked after I restarted my machine. Don't know why it didn't work earlier. Thanks a lot for your help. Here is the command I used. ssh -NL 3306:remote.db.host:3306 username@hostname – user2714753 Mar 30 '16 at 00:47

Run ssh port forwarding in background before connecting to mySql server

1 Answers1