How to create Databases on AWS

On this post we will continue with the preparation for the AWS CERTIFIED SOLUTIONS ARCHITECT EXAM. Remember the exercises we are solving here are from AWS Certified Solutions Architect Official Study Guide: Associate Exam by Joe Baron and others. We are going to resolve the exercises from Chapter 7, Databases and AWS.

EXERCISE 7.1: Create a MySQL Amazon RDS Instance

Launch a new Amazon RDS DB Instance, and select MySQL Community Edition instance as the database engine.

We can launch a MySQL instance running just one command on AWS CLI as shown below:

aws rds create-db-instance \
     --db-instance-identifier mydbinstance \
     --db-instance-class db.t2.micro \
     --engine MySQL \
     --allocated-storage 5 \
     --master-username masterawsuser \
     --master-user-password masteruserpassword \
     --backup-retention-period 1

Return to the list of the Amazon RDS instances. You will see the status of your Amazon RDS database as Creating . It may take up to 20 minutes to create your new Amazon RDS instance

Lets try to connect now using mysql utility. If you don’t have the mysql client in your Ubuntu Linux machine you can install it with:

 sudo apt-get install mysql-client-core-5.7

You can connect with this syntax:

mysql -u <username> -h <hostname>  -p

if connection fails, it is quite likely the security group associated to the DB instance is not allowing connections on the default tcp port 3306. In that case, check our previous post on how to modify security groups and allow traffic on port 3306.

After that you should be able to connect successfully and get the mysql prompt as shown below:

$mysql -u masterawsuser -h mydbinstance-repl.czquc0z9elkd.sa-east-1.rds.amazonaws.com -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
...
mysql> 

Success!

EXERCISE 7.3: Create a Read Replica

We have skip exercise 7.2 as it requires Multi-AZ which is not eligible for free tier. so we will go straight to exercise 7.3

In this exercise, you will create a read replica of your existing MySQL DB server. Just run the following code:

aws rds create-db-instance-read-replica --db-instance-identifier mydbinstance-repl --source-db-instance-identifier mydbinstance

You can check on the AWS Management Console if the replica is creating, it will take a few minutes

Once the replica creation is finished. We can check if we can connect to our replica db instance:

mysql -u masterawsuser -h mydbinstance-repl.czquc0z9elkd.sa-east-1.rds.amazonaws.com -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
mysql> 

Now, we will also try to promote the read instance using the console as shown below:

When you promote a Read Replica, the new DB instance that is created retains the backup retention period, the backup window, the option group, and the parameter group of the former Read Replica source. The promotion process can take several minutes or longer to complete, depending on the size of the Read Replica. Once you promote the Read Replica to a new DB instance, it’s just like any other DB instance. For example, you can create Read Replicas from the new DB instance and perform point-in-time restore operations. Because the promoted DB instance is no longer a Read Replica, you can’t use it as a replication target. If a source DB instance has several Read Replicas, promoting one of the Read Replicas to a DB instance has no effect on the other replicas.

Remember to delete the DB instances when you are done to avoid charges.

EXERCISE 7.4: Read and Write from a DynamoDB Table

In this exercise, you will create an Amazon DynamoDB table and then read and write to it.

Create a new table named UserProfile with a partition key of userID of type String

aws dynamodb create-table --table-name UserProfile --attribute-definitions AttributeName=userID,AttributeType=S --key-schema AttributeName=userID,KeyType=HASH --provisioned-throughput ReadCapacityUnits=1,WriteCapacityUnits=1

After the table has been created, view the list of items in the table.

$ aws dynamodb scan --table-name UserProfile
 {
     "Count": 0, 
     "Items": [], 
     "ScannedCount": 0, 
     "ConsumedCapacity": null
 }

Using the AWS CLI, create and save a new item in the table. Set the userID to U01

aws dynamodb put-item  --table-name UserProfile \
     --item '{
         "userID": {"S": "U01"} }' \
     --return-consumed-capacity TOTAL

Perform a scan on the table to retrieve the new item.

aws dynamodb scan --table-name UserProfile
 {
     "Count": 1, 
     "Items": [
         {
             "userID": {
                 "S": "U01"
             }
         }
     ], 
     "ScannedCount": 1, 
     "ConsumedCapacity": null
 }

You have now created a simple Amazon DynamoDB table, put a new item, and retrieved it using Scan.

Delete the DynamoDB table

Let’s delete it:

aws dynamodb delete-table --table-name UserProfile

This concludes the exercises related to RDS and DynamoDB on AWS. I hope it was useful for you, see you on the next blog post!