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:[email protected]: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.
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