Migrating to Amazon RDS

Task 1: Generating Order Data on the Café Website

I began by generating some order data on the café website. First, I needed to access the café web application.

I went to the top of the instructions, clicked on Details, and then selected Show.
I copied the CafeInstanceURL value (which looked something like 34.55.102.33/cafe) and pasted it into a new browser window.
- Note: I made sure to also copy the other values from the table and paste them into a text editor to use throughout the project.
On the cafe website, I navigated to the Menu section, added at least one of each item to my order, and then clicked Submit Order.
I then went to the Order History page and recorded the number of orders I had placed. This would be useful later when comparing this number with the orders in the migrated database.

Task 2: Creating an Amazon RDS Instance using the AWS CLI

This task involved creating an Amazon RDS instance using the AWS CLI. I needed to create several prerequisite components first.

Task 2.1: Connecting to the CLI Host Instance

On the AWS Management Console, I entered "EC2" in the Search bar and selected it to open the EC2 Management Console.
In the navigation pane, I chose Instances.
From the list of instances, I selected the CLI Host instance.
I clicked Connect.
On the EC2 Instance Connect tab, I clicked Connect.
- Note: I could have used an SSH client to connect to the EC2 instance if I preferred, following the guidance in "Connect to Your Linux Instance."

Task 2.2: Configuring the AWS CLI

Once connected to the CLI Host instance, I configured the AWS CLI by providing the necessary parameters.
I ran the following command:
```
aws configure
```
When prompted, I entered the following information:
- AWS Access Key ID: I entered the value for AccessKey from my notes
- AWS Secret Access Key: I entered the value for SecretKey
- Default region name: I entered the value for LabRegion
- Default output format: I typed "json"
Tip: In EC2 Instance Connect, I found I sometimes needed to use the context (right-click) menu to paste values into the terminal.

Task 2.3: Creating Prerequisite Components

Now I needed to create the infrastructure components required for the Amazon RDS instance.

First, I created the CafeDatabaseSG security group to protect the Amazon RDS instance:

aws ec2 create-security-group \
--group-name CafeDatabaseSG \
--description "Security group for Cafe database" \
--vpc-id <CafeInstance VPC ID>

After the command completed, I copied the GroupId from the output to my text editor for later use.

Next, I created the inbound rule for the security group:

aws ec2 authorize-security-group-ingress \
--group-id <CafeDatabaseSG Group ID> \
--protocol tcp --port 3306 \
--source-group <CafeSecurityGroup Group ID>

Note: I was careful to make the correct substitutions with the GroupId values.

To confirm the inbound rule was applied correctly, I ran:

aws ec2 describe-security-groups \
--query "SecurityGroups[*].[GroupName,GroupId,IpPermissions]" \
--filters "Name=group-name,Values='CafeDatabaseSG'"

The output showed that the CafeDatabaseSG security group now had an inbound rule allowing connections from TCP port 3306 if the source was an instance with the CafeSecurityGroup association.

Next, I created two private subnets and a database subnet group. I started with CafeDB Private Subnet 1:

aws ec2 create-subnet \
--vpc-id <CafeInstance VPC ID> \
--cidr-block 10.200.2.0/23 \
--availability-zone <CafeInstance Availability Zone>

I had to choose a CIDR block that was within the VPC range but didn't overlap with existing subnets:
- Cafe VPC IPv4 CIDR block: 10.200.0.0/20
- Cafe Public Subnet 1 IPv4 CIDR block: 10.200.0.0/24
I found that 10.200.2.0/23 worked well for this purpose.
From the output, I noted the SubnetId value for CafeDB Private Subnet 1.

Then I created CafeDB Private Subnet 2 in a different Availability Zone:

aws ec2 create-subnet \
--vpc-id <CafeInstance VPC ID> \
--cidr-block 10.200.10.0/23 \
--availability-zone <availability-zone>

For this second subnet, I used 10.200.10.0/23 and selected an Availability Zone different from the one ending in a.
From the output, I noted the SubnetId value for CafeDB Private Subnet 2.

Finally, I created the CafeDB Subnet Group:

aws rds create-db-subnet-group \
--db-subnet-group-name "CafeDB Subnet Group" \
--db-subnet-group-description "DB subnet group for Cafe" \
--subnet-ids <Cafe Private Subnet 1 ID> <Cafe Private Subnet 2 ID> \
--tags "Key=Name,Value= CafeDatabaseSubnetGroup"

Task 2.4: Creating the Amazon RDS MariaDB Instance

With the prerequisites in place, I created the CafeDBInstance:

aws rds create-db-instance \
--db-instance-identifier CafeDBInstance \
--engine mariadb \
--engine-version 11.4.5 \
--db-instance-class db.t3.micro \
--allocated-storage 20 \
--availability-zone <CafeInstance Availability Zone> \
--db-subnet-group-name "CafeDB Subnet Group" \
--vpc-security-group-ids <CafeDatabaseSG Group ID> \
--no-publicly-accessible \
--master-username root --master-user-password 'Re:Start!9'

The command immediately returned information about the database, but I knew the instance might take up to 10 minutes to become available.

I monitored the status of the database instance:

aws rds describe-db-instances \
--db-instance-identifier CafeDBInstance \
--query "DBInstances[*].[Endpoint.Address,AvailabilityZone,PreferredBackupWindow,BackupRetentionPeriod,DBInstanceStatus]"

This command showed me:
- Endpoint address
- Availability Zone
- Preferred backup window
- Backup retention period
- Status of the database
I noticed that automated backups occur daily during the preferred backup window and are retained for the duration specified by the backup retention period. The value of 1 for the backup retention period indicated that daily backups are retained for only 1 day by default, and the preferred backup window was set to a 30-minute time interval by default.
I watched as the status initially showed "creating," then changed to "modifying," "backing-up," and finally "available."
Once available, I recorded the endpoint address in this format: RDS Instance Database Endpoint Address: cafedbinstance.xxxxxxx.us-west-2.rds.amazonaws.com

Task 3: Migrating Application Data to the Amazon RDS Instance

Now I needed to migrate the data from the existing local database to my new Amazon RDS database.
I connected to the CafeInstance using EC2 Instance Connect, following the same steps as before.

I used the mysqldump utility to create a backup of the local cafe_db database:

mysqldump --user=root --password='Re:Start!9' \
--databases cafe_db --add-drop-database > cafedb-backup.sql

This command generated SQL statements in a file named cafedb-backup.sql.
I reviewed the contents of the backup file using the Linux less command:
```
less cafedb-backup.sql
```
- Tip: When using the less command, I could use the up and down arrow keys to move one line at a time, or Page up and Page down keys to navigate by pages. Pressing 'q' exits the command.
I noticed various SQL commands that would create the database, its tables and indexes, and populate it with the original data.

Next, I restored the backup to the Amazon RDS database:

mysql --user=root --password='Re:Start!9' \
--host=<RDS Instance Database Endpoint Address> \
< cafedb-backup.sql

Finally, I verified that the cafe_db was successfully created and populated in the Amazon RDS instance:
```
mysql --user=root --password='Re:Start!9' \
--host=<RDS Instance Database Endpoint Address> \
cafe_db
```
Then I ran this SQL statement to retrieve data from the product table:
```
select * from product;
```
The query returned rows from the table, and I confirmed that the information matched the number of items I had ordered at the beginning of the project.
I exited the interactive SQL session:
```
exit
```
I kept the CafeInstance SSH window open for later use.

Task 4: Configuring the Website to Use the Amazon RDS Instance

Now I was ready to configure the café website to use the Amazon RDS instance. This was straightforward because the application followed best practices by externalizing database connection information as parameters in Parameter Store.
I opened the AWS Systems Manager console by entering "Systems Manager" in the AWS Search bar.
In the left navigation pane, I chose Parameter Store.
From the My parameters list, I selected /cafe/dbUrl. I could see the current value of the parameter, along with its description and other metadata.
I clicked Edit.
In the Parameter details page, I replaced the text in the Value box with the RDS Instance Database Endpoint Address I had recorded earlier.
I clicked Save changes.
The dbUrl parameter now referenced the RDS DB instance instead of the local database.
Next, I tested the website to confirm it could access the new database correctly:
- In a new browser window, I navigated to the CafeInstanceURL that I had copied earlier.
- The website's home page loaded correctly.
- I clicked on the Order History tab and checked the number of orders in the database. This matched the number of orders I had recorded before the database migration.
As an optional step, I placed some new orders and verified the successful operation of the website.

Task 5: Monitoring the Amazon RDS Database

One of the benefits of using Amazon RDS is the ability to monitor database performance. Amazon RDS automatically sends metrics to CloudWatch every minute for each active database.
I opened the RDS Management Console by entering "RDS" in the AWS Search bar.
In the left navigation pane, I chose Databases.
From the Databases list, I selected cafedbinstance. This displayed detailed information about the database.
I clicked on the Monitoring tab, which showed key database instance metrics available from CloudWatch. Each metric included a graph showing monitoring over time.
The metrics included:
- CPUUtilization: The percent of CPU utilization
- DatabaseConnections: The number of database connections in use
- FreeStorageSpace: The amount of available storage space
- FreeableMemory: The amount of memory (RAM) available
- WriteIOPS: The average number of disk write I/O operations per second
- ReadIOPS: The average number of disk read I/O operations per second
Tip: Some metrics appeared on the second or third pages of charts. I could click 2 or 3 to the right of the CloudWatch search box to see additional metrics.
To monitor the DatabaseConnections metric in action, I created a connection to the database from the CafeInstance:
```
mysql --user=root --password='Re:Start!9' \
--host=<RDS Instance Database Endpoint Address> \
cafe_db
```
I ran a simple query:
```
 * from product;
```
In the Amazon RDS console, I clicked on the DatabaseConnections graph to open it in a larger view. The graph showed a line indicating that 1 connection was in use—the interactive SQL session from the CafeInstance.
Tip: If the graph didn't show any connections in use, I waited 1 minute (the sampling interval) and then clicked Refresh.
I closed the connection from the interactive SQL session:
```
exit
```
After waiting another minute and refreshing the DatabaseConnections graph, I could see that the number of connections in use had returned to 0.
With some extra time, I explored the other monitoring graphs to better understand the performance characteristics of my new RDS instance.