AWS-RDS-Setup

AWS RDS Setup and Access

How to Setup AWS RDS and Access it

Architecture

Step 1: Create an RDS DB Instance

Prerequisite

Before starting this step, you need to have a VPC that contains both public subnets with corresponding Security Groups. Also, make sure that the Security Group that will be used also contains the Inbound rule for port 3306 which is the port for MySQL Database.

On VPC > Security Group
Select the Security Group that you will use for this exercise
On the tab Inbound Rules add new rule for Custom TCP Rule, Port Range is 3306, Source is Anywhere. Then, Save. This new Inbound Rule will be create

Launching a MySQL DB Instance

On AWS Management Console, open Amazon RDS Console: https://console.aws.amazon.com/rds/.
From navigation pane choose Databases > Create database "
Choose Standard Create > MySQL
For Templates section, choose Free tier
For Settings section, configure as followed:
- DB instance identifier: tutorial-db-instance
- Master username: admin
- Auto generate a password: Disable this option
- Master password: choose password then retype the same password to confirm
In DB Instance Size, set these values:
- DB instance performance type: Burstable
- DB instance class: db.t2.micro
In the Storage and Availability & Durability section, use the default values
In the Connectivity section, open the Additional connectivity configuration and set these values:
- Virtual Private Cloud (VPC): choose an existing VPC that you created before with public subnets or creating new one. For this workshop, you need to be able to access the database publicly therefore make sure to have the internet connection on your security group Important Note: The VPC must have subnets in different Availability Zone
- Subnet group: the DB subnet group of the chosen VPC
- Publicly Access: Yes
- VPC Security Group: Choose an existing VPC Security Group that is configured for the private access (such as the security group of the Jump Box Web Server workshop). Remove other security groups, such as the default security group, by choosing the X associated with each.
- Availability Zone: No Preference
- Database port: 3306
Open Additional Configuration, enter sample for Initial database name. Then keep the rest of the configuraton default.
If you get an error that the database cannot be created because your VPC chosen doesn't support DNS/Hostname resolution, please follow these steps:

Go to VPC page and select the VPC that you used. Then, Actions > Edit DNS resolution
Make sure DNS resolution is Enable
Then, Actions > Edit DNS Hostnames, make sure DNS hostname is Enable

Choose Create database to create your Amazon RDS MySQL DB instance. The new DB will appear on the Databases list with the status Creating
Wait until your new database is completely finished (status is "Available") creating before connecting to it.
Click on the newly created database to get the following details for connection:

Endpoint: in this exercise, tutorial-db-instance.cmwix84nntpy.us-east-1.rds.amazonaws.com
Port: 3306

Step 2: Test the connection to the new AWS RDS Database using MySQL Workbench

Download MySQL Workbench from: https://www.mysql.com/products/workbench/
Install MySQL Workbench on your local computer.
Launch MySQL Workbench > Welcome page > Click the + sign next to MySQL Connections
On the Setup New Conection window, enter the following details:
- Connection Name: choose any name that corresponds to the exercise, for example AWS RDS DB Connect
- Hostname: enter the Endpoint details of the AWS RDS DB from step above, for example tutorial-db-instance.cmwix84nntpy.us-east-1.rds.amazonaws.com
- Port: enter port 3306 for MySQL DB connection
- Username: enter the Master username from above step, in this case, admin
- Password: click on Store in Vault and enter the password for DB from above step
- Then click Test Connection
You should get the message Successfully made the MySQL connection message which indicates that you can successfully connect to the AWS RDS Database.
From here, you can work on your database hosted on AWS RDS.

Step 3: Connect to the AWS RDS Database using Python

For this step you can either create a new Linux instance on AWS and run the python commands there. Or you can also do this on your local PC.
Open jupyter notebook or any Python terminal
Enter the following script in the jupyter notebook

#Install pymysql package
pip install pymysql

#Import pymysql package to do database programming with Python. Here we set the values for varaiables
#database_instance_endpoint = the db endpoint from AWS RDS Interface
#port = 3306 which is the port for MySQL
#user = the Master Username defined in AWS RDS DB
#password = password defined when creating DB
import pymysql
database_instance_endpoint="tutorial-db-instance.cmwix84nntpy.us-east-1.rds.amazonaws.com"
port=3306
dbname="sample"
user="admin"
password="password"

# Now we will connect to the AWS RDS Database using the command pymysql.connect with the Database details from above.
# Then we store this value in the variable "connection"
connection = pymysql.connect(database_instance_endpoint,
                      user = user,
                      port = port,
                      passwd = password,
                      database = dbname)

# Declare varaiable for mycur which is a cursor. We need a cursor to query the database
# Using cursor, we can connect to the database
mycur = connection.cursor()

# Using MySQL query to create a new table called "students" with columns: id, firstname, lastname, grade with id as PRIMARY KEY
# Store the MySQL command in a variable "create_table_query"
create_table_query="""CREATE TABLE IF NOT EXISTS `students` (
                    `id` int(11) NOT NULL AUTO_INCREMENT,
                    `firstname` varchar(255) NOT NULL,`lastname` varchar(255) NOT NULL,`grade` varchar(10),
                     PRIMARY KEY (`id`)
                     ) ENGINE=INNODB;"""

# Using cursor to execute the command to create the table				
mycur.execute(create_table_query)

# Using MySQL command to insert a data row into the students table.
# Keey this MySQL command in the variable "insert_query"
insert_query="INSERT INTO `students` (`id`, `firstname`, `lastname`) VALUES (%s, %s, %s)"

# Using cursor to execute the insert query command to add more data into the table
mycur.execute(insert_query, ('12345', 'Tata', 'Tutu'))
mycur.execute(insert_query, ('34567', 'Momo', 'Meme'))

# Run the commit command to commit the change into the database
connection.commit()

# Check the result by query all the data from the students table
mycur.execute("SELECT * FROM students")

# Run the command to display the database table
mycur.fetchall()

Shutima/AWS-RDS-Setup-Connection-Using-Python