Python SSHTunnel w/ Paramiko - CLI works, but not in script

Question

Context:
I'm trying to connect to a remote MySQL install through SSH and in Python. I'm using paramiko and SSHTunnel, and currently on py 2.7.

I have had success connecting and querying records in the remote DB using bash shell, paramiko's forward.py, and even SSHTunnel's CLI command.

Problem:
I'm running into issues when trying to migrate that over to a single script that will create the tunnel, and query the results. The issue appears to be with my formatting/structuring of SSHTunnel's syntax.

This is what I use to open the tunnel on the shell:

ssh -p SSH_PORT SSH_USER@SERVER_IP -L 33060:127.0.0.1:3306

This is what I use to open with paramiko's forward.py:

python t_forward.py SERVER_IP:SSH_PORT -r 3306 -u SSH_USER -p 33060 -K "/PATH/TO/PRIVATE/KEY"

note: I'm currently using a key with no passphrase (for testing/dev purposes)

This is what I used to open with SSHTunnel's command line:

python -m sshtunnel -U SSH_USER -L :33060 -R 127.0.0.1:3306 -p SSH_PORT SERVER_IP -K "/PATH/TO/PRIVATE/KEY"

Per above, all these are working, and my py script that uses MySQLdb to connect to the database and retrieve records works.

Where things break down is when I try to add the SSH connection string into the script. This is what it currently looks like:

server = SSHTunnelForwarder(
    ('SERVER_IP', SSH_PORT),
    ssh_username='SSH_USER',
    ssh_pkey='/PATH/TO/PRIVATE/KEY',
    remote_bind_address=('127.0.0.1', 3306),
    local_bind_address=('0.0.0.0', 33060)
)

The MYSQL connection line looks like this:

con = MySQLdb.connect(user='MYSQLDBUSER',passwd='MYSQLDBUSERPASS',db='DATABASE',host='127.0.0.1', port=33060)

Given that I am able to connect through BASH and through both forward.py and SSHTunnel's CLI, it doesn't seem to be an issue on the server, but rather that my SSHTunnelForwarder is not properly formatted.

Error Message:

Could not establish connection from ('127.0.0.1', 33060) to remote side of the tunnel

Looking at the var/log/auth.log on the server, I see that it's able to connect, it just seems to break down when the MySQLDB.connect kicks in.

auth.log messages when I get this error:

Oct  9 17:36:31 HOSTSERVER sshd[21141]: Accepted publickey for SSH_USER from SOURCE_IP port 32918 ssh2: RSA <LONG KEY>
Oct  9 17:36:31 HOSTSERVER sshd[21141]: pam_unix(sshd:session): session opened for user SSH_USER by (uid=0)
Oct  9 17:36:31 HOSTSERVER systemd-logind[1217]: New session 144 of user SSH_USER.
Oct  9 17:36:32 HOSTSERVER sshd[21141]: pam_unix(sshd:session): session closed for user SSH_USER

auth.log message when I use the SSHTunnel CLI (and the results work):

Oct  9 17:39:33 HOSTSERVER sshd[21625]: Accepted publickey for SSH_USER from SOURCE_IP port 44132 ssh2: RSA <LONG KEY>
Oct  9 17:39:33 HOSTSERVER sshd[21625]: pam_unix(sshd:session): session opened for user SSH_USER by (uid=0)
Oct  9 17:39:33 HOSTSERVER systemd-logind[1217]: Removed session 144.
Oct  9 17:39:33 HOSTSERVER systemd-logind[1217]: New session 145 of user SSH_USER.

It would seem that I'm missing something very basic here... Any help would be appreciated.

Answer 1

Found the answer!

It turns out that the script continues to execute before the connection is established. Therefore MySQLDB tries to connect to the port mapping before the tunnel is fully established.

A simple:

import time
...
sleep(1)
...

Does the trick.

In my case, I added the sleep(1) after "server.start()" and before the code that needs to access the remote DB.

Thanks to @kenster who made me look at the auth.log more carefully, which got me to think about timing more carefully.