How to connect to AWS RDS MySql database with Python

Question

I am currently trying to connect to my MySql database created on AWS with a python program using the library PyMySQL

# !/usr/bin/env python
# -*- coding: utf-8 -*-
import pymysql

host = 'admin.cjp8hsqu4je0.us-east-2.rds.amazonaws.com'
user = 'admin'
password = '12345678'
database = 'admin'

connection = pymysql.connect(host, user, password, database)
with connection:
    cur = connection.cursor()
    cur.execute("SELECT VERSION()")
    version = cur.fetchone()
    print("Database version: {} ".format(version[0]))

When I run the above code I get the following error:

Traceback (most recent call last):
  File "C:\Users\SuperPC\AppData\Local\Programs\Python\Python38-32\lib\site-packages\pymysql\connections.py", line 581, in connect
    sock = socket.create_connection(
  File "C:\Users\SuperPC\AppData\Local\Programs\Python\Python38-32\lib\socket.py", line 808, in create_connection
    raise err
  File "C:\Users\SuperPC\AppData\Local\Programs\Python\Python38-32\lib\socket.py", line 796, in create_connection
    sock.connect(sa)
socket.timeout: timed out

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "bot.py", line 10, in <module>
    connection = pymysql.connect(host, user, password, database)
  File "C:\Users\SuperPC\AppData\Local\Programs\Python\Python38-32\lib\site-packages\pymysql\__init__.py", line 94, in Connect
    return Connection(*args, **kwargs)
  File "C:\Users\SuperPC\AppData\Local\Programs\Python\Python38-32\lib\site-packages\pymysql\connections.py", line 325, in __init__
    self.connect()
  File "C:\Users\SuperPC\AppData\Local\Programs\Python\Python38-32\lib\site-packages\pymysql\connections.py", line 630, in connect
    raise exc
pymysql.err.OperationalError: (2003, "Can't connect to MySQL server on 'admin.cjp8hsqu4je0.us-east-2.rds.amazonaws.com' (timed out)")

What am I doing wrong? The arguments I gave to the function are correct. Could it be a problem with the MySql driver?

Answer 1

If you want to access your RDS from outside of AWS over internet, than it should be set to be publicly available:

Also it should be placed in a public subnet (e.g. default VPC) and have opened inbound rules in its security group (good practice is to limit access to only selected IPs or IP range, rather then using 0.0.0.0/0):

Hope this helps.

Answer 2

This request timed out which indicates that you are unable to connect due to a firewall rule.

If you are running this on an EC2 instance or container then:

• Ensure that the RDS instance is configured to allow inbound access to either the subnet range for the instance/container on port 3306 using its security group.

If you are running this externally then either you will need to:

• Connect via a VPN to your VPC and update the security group of RDS to whitelist your on-premise CIDR range
• If you cannot use a VPN the RDS will need to be created to be publicly accessible with a security group whitelisting inbound access to your public IP address.

If you are using a Lambda the following should be done:

• Migrate your Lambda into the VPC, attach a security group to it allowing outbound access (by default it should allow outbound access to everything). It should sit in private subnets
• Update the security group of the RDS database to allow inbound access on port 3306 to either the LAmbda subnets or the security group attached to the Lambda.

More information on configuring the Lambda within a VPC is in this document.

Answer 3

Finally I get the ultimate solution:

If you have an EC2 instance that is properly connected to the RDS instance , you can open a ssh tunnel between them and use pymysql (python library).

Here is the amazing code that saves the day! (I add some code and comments, but you can get the original code using the link below) LInk to the savior's github

First of all, install this libraries:

• !pip install sshtunnel

• !pip install PyMySQL

    # Import libraries
    from sshtunnel import SSHTunnelForwarder
    import pymysql
  
    # SSH (ec2_public_dns, ec2_user, pem_path, remote_bind_address=(rds_instance_access_point, port))
    with SSHTunnelForwarder(('ec2-52-202-194-76.public-ec2-instance.amazonaws.com'), ssh_username="ec2-user", ssh_pkey="~/ssh-tunnel-rds.pem", remote_bind_address=('private-rds-instance.ckfkidfytpr4.us-east-1.rds.amazonaws.com', 3306)) as tunnel:
        print("****SSH Tunnel Established****")
  
        db = pymysql.connect(
            host='127.0.0.1', user="rdsuser",password="rdspassword",
            port=tunnel.local_bind_port, database="dbName"
        )
        # Run sample query in the database to validate connection
        try:
            # Print all the databases
            with db.cursor() as cur:
                # Print all the tables from the database
                cur.execute('SHOW TABLES FROM dbName')
                for r in cur:
                    print(r)
  
                # Print all the data from the table
                cur.execute('SELECT * FROM table_name')
                for r in cur:
                    print(r)
        finally:
            db.close()
  
    print("YAYY!!")