How to access Aurora MySQL from Python code via a bastion server
This example assumes you have a bastion server setup that will allow connection to follow thru to an existing MySQL server. The python code is run on an EC2 server that has security group setup on the bastion server to allow connections from the EC2 server.
We need the following info:
-
On the EC2 server make sure the SSHTunnerForwarder and pymysql libraries are installed using pip install
-
Get the following info:
-
bastion server name
-
pem file to connect to bastion server along with the userid
-
Aurora mysql connection endpoint with the port
-
Aurora userid and password
-
Database name you want to connect to
-
Ensure that the localhost resolves to 127.0.0.1 if not then figure out what the local ip is
-
Here is a sample code that demonstrate how the python code can be constructed:
import pymysql
from sshtunnel import SSHTunnelForwarder
#ssh bastion host information
ssh_host = ‘<bastion server name>’ ### bastion server name
ssh_username = ‘ec2-user’
ssh_pkey = ‘<location of the pem file>’ ### pem file for the bastion server
ssh_port = 22
# setting up the ssh tunnel
ssh_tunnel = SSHTunnelForwarder(
(ssh_host, ssh_port),
ssh_username=ssh_username,
ssh_pkey=ssh_pkey,
remote_bind_address=(‘<Mysql connection end point server name>’, 3306) # MySQL end point and my sql port
)
#start the ssh tunnel
ssh_tunnel.start()
#mysql database connection info
db_user = ‘<mysql user id >’
db_password = ‘<password for the mysql userid>’
db_name = ‘<name of the mysql database’
db_host = ‘127.0.0.1’
#connection string to mysql via ssh_tunnel
mysql_conn = pymysql.connect(
host=db_host, # Use localhost, as the tunnel forwards the connection
port=ssh_tunnel.local_bind_port,
user=db_user,
password=db_password,
db=db_name
)
#connect connect and run the query via cursor
cursor = mysql_conn.cursor()
cursor.execute(‘SELECT * FROM <table name>’)
for row in cursor.fetchall():
print(row)
#close mysql connection and stop the tunnel
mysql_conn.close()
ssh_tunnel.stop()
Related Posts
Backing up Aurora MySQL
Creating an EMR cluster using CLI
