Connecting to MySQL database via SSH (over a jump host) using python 3

Question

I am trying to build a connection between python and my MySQL-Database (which is in AWS).

The SSH tunnel works fine, the matters happens if its about starting the connection to the database afterwards. Here is my code:

from sqlalchemy import create_engine from sshtunnel import SSHTunnelForwarder

Here is my code:

tunnel = SSHTunnelForwarder(
    ('ssh-host', 22),
    ssh_pkey="my-keypair",
    ssh_username="my-user",
    ssh_password="my-pw",
    remote_bind_address=('database-host', db-port)
    )

tunnel.start()
print("SSH connection created..")   #until here it works fine.
port = str(tunnel.local_bind_port)
engine = create_engine("mysql+pymysql://db-username:db-pw@db-url"+port+"/dbname",encoding='latin1', echo=True, pool_recycle=280)
connection = engine.connect()

After I try to connect to the database it shows following error:

sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (2003, "Can't connect to MySQL server on db-url

Before I created a jump host, the connection string worked fine.

I'm assuming you used localhost for db-url cuz I'm not sure why you found it necessary to obfuscate that in your example above? — Ashaman Kingpin
– Ashaman Kingpin, Commented Oct 14, 2019 at 11:27
Also what is database-host for the remote_bind_address field, is that localhost? — Ashaman Kingpin
– Ashaman Kingpin, Commented Oct 14, 2019 at 11:29
1) no, it is not my localhost. it references to the url of a current database which is in AWS. So a RDS service of amazon. 2) The database-host is the same value like db-url. A url which references to the database in AWS. It was not perfectly chosen names from my side I should give them a unique name — Murti
– Murti, Commented Oct 14, 2019 at 18:09
But it should be localhost, though, right? I get that remote_bind_address should be the AWS database host, but when you are connecting in the create_engine call, db_url should be localhost. That's the point of the SSH tunnel forwarder, it listens on your loopback address, when you connect to it, it tunnels your connection the database server running on the other side of the tunnel. — Ashaman Kingpin
– Ashaman Kingpin, Commented Oct 14, 2019 at 19:01

Ashaman Kingpin · Accepted Answer · 2019-10-15 11:50:19Z

With this statement:

tunnel = SSHTunnelForwarder(
    ('ssh-host', 22),
    ssh_pkey="my-keypair",
    ssh_username="my-user",
    ssh_password="my-pw",
    remote_bind_address=('database-host', db-port)
    )

An SSH tunnel is established to the ssh server at ssh-host on port 22 and the remote tunnel endpoint is instructed to connect to the database server running on database-host on port db-port (e.g. 3306). But the essential point to note is that this tunnel is only established once the client makes the connection.

But how does the client make the connection, you may ask? Well, part of the functionality of the SSHTunnelForwarder is that it listens for connections on a random port on the localhost. So when you connect to localhost using your client application (this is what happens in the create_engine() and engine.connect() calls), the SSH tunnel will read data from that connection, push it across the tunnel and deliver it to the remote endpoint on that side of the tunnel (i.e. the database server at database-host:db-port).

So what you are missing in your code is that the database connection should be targeting localhost, as in the following (you should be able to use the IP address 127.0.0.1 or the hostname localhost):

engine = create_engine("mysql+pymysql://db-username:[email protected]:"+port+"/dbname",encoding='latin1', echo=True, pool_recycle=280)

Collectives™ on Stack Overflow

Connecting to MySQL database via SSH (over a jump host) using python 3

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related