In this repo I will show how to create and connect to a mySQL DB running on AWS RDS. The automation job will consist on connecting to the Database and insert records to it using python3, boto3 and mysql.connector libraries; for last we will introduce a way to secure sensible information with AWS SSM -Secrets manager- that I will utilize as a secret store for the RDS's enpoint, user and password.
- We will create a DB that will be only accesible from an specific VPC, meaning that the RDS Database will only accept connections originating from a private IP attached to one of our EC2 instances.
Image 1. Architecture part 1.
Image 1 shows the initial architecture for the infrastructure that will be deployed in this tutorial. On image 2, I will add a method for securing DB's sensible authentication information.
Image 2. Architecture part 2.
The following are the required steps to deploy a MYSQL DB with AWS RDS.
- Go to AWS CONSOLE > RDS DATABASE > CREATE DATABASE.
- Select MySQL engine as show below.
Image 3. MySQL Engine for RDS
- Select a free tier RDS for testing purposes and set up user and password for your DB
Image 4. Free tier elegible, user/password
- Make sure you put No as public access
Image 5. Deny public access to DB
- Click create
At this point you must wait for a couple of minutes for AWS to set up the environment you just configure. When the process of setting up your DB is complete, you will see the status as available for your DB.
Image 6. DB available.
The important information to be able to connect to his EC2 instance is the endpoint and port for connection. You could check this information on your AWS console. Sensitive information such as user and password is not shown on the AWS console interface, so make sure you kept this info secure.
Image 7. Endpoint and port for RDS instance.
From the public EC2 instance, I will try to connect to my RDS Database. for this purpose you must follow along the following steps:
1.Make sure you have configure your AWS CLI keys on AWS IAM, giving to this the least privileges; and set them up correctly on your EC2 with $aws configure command.
2.Open a rule on the security group of the RDS instance allowing connections comming from the private IP belonging to the public EC2, the port is the one shown on figure 7.
3.Install MySQL Client on your EC2. The command for installing is:
$sudo yum install mysql
4.Make sure python3 and boto3 library is installed if you want to follow along.
- Connect to your DB using the following syntax.
PROMPT> mysql -h DBendpoint -P 3306 -u mymasteruser -p
It will ask for your password, after entering the correct password you will have the following prompt.
Image 8. Connection successfull from your public EC2 to your private RDS Instance. Hurray!!
The next part of this tutorial will create a database named "movies" and a table that will contain information of movies like tittle, director, genre and year of release. Execute the following commands to accomplish that task.
- Create the DB "movies"
CREATE DATABASE movies;
- Confirm the DB was created
SHOW DATABASE;
Figure. 9 Creation of DB "movies"
- Define tittle, director, genre and year of realse together with their data types.
In the process of creating a table, you need to specify the following information:
a. Column names – We are creating the title, genre, director, and release year columns for our table. b. Varchar of the columns containing characters – Specifies the maximum number of characters stored in the column. c. The integer of the columns containing numbers – Defines numeric variables holding whole numbers. d. Not null rule – Indicates that each new record must contain information for the column. e. Primary key – Sets a column that defines a record
Use the database movies with the following command:
USE movies;
Create a table using the CREATE command. Using the information from our movies example, the command is:
CREATE TABLE movies(title VARCHAR(50) NOT NULL,genre VARCHAR(30) NOT NULL,director VARCHAR(60) NOT NULL,release_year INT NOT NULL,PRIMARY KEY(title));
Figura 10. Commands to define table within DB movies.
Create the following script on your ec2 linux instance and name it with a .py extension
Figure 11. Script to connect and insert attributes on your DB's tables.
I think you will not have any problem trying to figure out what is the purpose of the variables declared and the methods called. As you see we could connect and insert attributes thanks to mysql.connector library. Bit as you must imagining right now there is a security problem cause we are saving some sensible information for the database connection in plain text. To overcome this we will use SSM Secrets manager that will encrypt this sensible data and will protect our DB against user and password leaks.
Secrets manager has an advatage over AWS parameter store that is another service of AWS for store sensible data, and that is its integration with RDS. On the following figure you could see that there exist an specific option to configure a secret for RDS. You must go to AWS Secrets Manager>Create new Secret and select Credentials for RDS Database.
Figure 12. Store authentication credentials for RDS database.
Click next and give your secret a name.
Figure 13.Name for secret
You can check on the AWS secrets manager console what information is stored for your recently created secret, as you may imagine. The secret will store the DB endpoint,user, password and port used by the RDS.
Figure 14. Info stored as secret.
Figure 15. Script modified for integrating with secrets manager.
After executing the script with python3, we can connect to the DB again following steps on "How to connect to the RDS DB" section to check if there is another field on the table that we created on the DB movies.
Figure 16. New entry inserted using pytohn3 and boto3 with mysql.connector libraries