Migrating to Amazon RDS

A lab on migrating to Amazon RDS typically involves transferring an existing on-premises or cloud-hosted database to Amazon's Relational Database Service (RDS). The goal is to reduce the operational burden of managing databases by leveraging AWS's managed service for automated backups, patching, monitoring, and scaling.

Objective:

• Create an Amazon RDS MariaDB instance by using the AWS CLI.
• Migrate data from a MariaDB database on an EC2 instance to an Amazon RDS MariaDB instance.
• Monitor the Amazon RDS instance by using Amazon CloudWatch metrics.

Summary:

• Creating an Amazon RDS instance by using the AWS CLI
• Migrating application data to the Amazon RDS instance
• Configuring the website to use the Amazon RDS instance
• Monitoring the Amazon RDS database

The application database runs in an Amazon Elastic Compute Cloud (Amazon EC2) Linux, Apache, MySQL, and PHP (LAMP) instance along with the application code. The instance has a T3 small instance type and runs in a public subnet so that internet clients can access the website. A CLI Host instance resides in the same subnet to facilitate the instance's administration by using the AWS Command Line Interface (AWS CLI).

Creating an Amazon RDS instance by using the AWS CLI:

• Configure the AWS CLI.
• Create the following prerequisite components required to build the Amazon RDS instance:
• A security group firewall for the Amazon RDS instance
• Two private subnets and a database subnet group
• Create the Amazon RDS MariaDB instance.
• On the AWS Management Console, in the Search bar, enter and choose EC2 to open the EC2 Management Console.
• In the navigation pane, choose Instances.
• From the list of instances, select the CLI Host instance.
• Choose Connect.
• On the EC2 Instance Connect tab, choose Connect.
• To set up the AWS CLI profile with credentials, in the EC2 Instance Connect terminal, run the following command: ‘aws configure’
• Enter the user's access key and secret access key.
• Create Security Group: Set up CafeDatabaseSG to protect the RDS instance.
• Add Inbound Rule: Allow only MySQL requests (TCP protocol, port 3306) from instances in CafeSecurityGroup.
• Create Private Subnets: Establish two private subnets (CafeDB Private Subnet 1 and CafeDB Private Subnet 2).
• Create Database Subnet Group: Define a CafeDB Subnet Group for RDS, associating it with the private subnets.
• Create CafeDatabaseSG Security Group: Run command: aws ec2 create-security-group \ --group-name CafeDatabaseSG \ --description "Security group for Cafe database" \ --vpc-id

Output: Note the GroupId for future use.

• Create Inbound Rule for CafeDatabaseSG:
  Run command:
  aws ec2 authorize-security-group-ingress \
  --group-id \
  --protocol tcp --port 3306 \
  --source-group

• Verify Inbound Rule:
  Run command:
  aws ec2 describe-security-groups \
  --query "SecurityGroups[*].[GroupName,GroupId,IpPermissions]" \
  --filters "Name=group-name,Values='CafeDatabaseSG'"

• Create CafeDB Private Subnet 1
  Run command:
  aws ec2 create-subnet \
  --vpc-id \
  --cidr-block 10.200.2.0/23 \
  --availability-zone

Output: Note the SubnetId for future use.

• Create CafeDB Private Subnet 2
  Run command:
  aws ec2 create-subnet \
  --vpc-id \
  --cidr-block 10.200.10.0/23 \
  --availability-zone
  Output: Note the SubnetId for future use.

• Create DB Subnet Group
  Run command:
  aws rds create-db-subnet-group \
  --db-subnet-group-name "CafeDB Subnet Group" \
  --db-subnet-group-description "DB subnet group for Cafe" \
  --subnet-ids \
  --tags "Key=Name,Value= CafeDatabaseSubnetGroup"

• Create the CafeDBInstance

Run the following command to create the MariaDB instance with the specified configuration:
Run command:
aws rds create-db-instance \
--db-instance-identifier CafeDBInstance \
--engine mariadb \
--engine-version 10.5.13 \
--db-instance-class db.t3.micro \
--allocated-storage 20 \
--availability-zone \
--db-subnet-group-name "CafeDB Subnet Group" \
--vpc-security-group-ids \
--no-publicly-accessible \
--master-username root --master-user-password 'Re:Start!9'

Key settings:

• DB instance identifier: CafeDBInstance
• Engine: MariaDB (version 10.5.13)
• Instance class: db.t3.micro
• Allocated storage: 20 GB
• Security group: CafeDatabaseSG
• Username: root

• Password: Re:Start!9
  The creation of the database instance may take up to 10 minutes.

• Monitor the Status of the DB Instance

After running the creation command, monitor the status of the database by running the following command:
Run command:

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

What to watch: The command will return information such as the endpoint address, availability zone, backup window, retention period, and the status of the instance.
Initially, the status will show as creating and then progress through modifying, backing-up, and finally to available.

• Continue running the status command every few minutes until the status of the database shows available. Run command: aws rds describe-db-instances \ --db-instance-identifier CafeDBInstance \ --query "DBInstances[*].[DBInstanceStatus]"

Migrating application data to the Amazon RDS instance:

• Connect to the EC2 Instance (CafeInstance)

• In the terminal, run the following command to create a backup of the local cafe_db database:
  Run Command:
  mysqldump --user=root --password='Re:Start!9' \
  --databases cafe_db --add-drop-database > cafedb-backup.sql

• Review the Backup File
  You can review the contents of the backup using the less command:
  Run Command:
  less cafedb-backup.sql

Use arrow keys or Page Up/Down to navigate and q to quit.

• Restore the Backup to the Amazon RDS Database
  Run the following command to restore the backup to the RDS instance. Replace with your actual RDS instance endpoint:
  Run Command
  mysql --user=root --password='Re:Start!9' \
  --host= \
  < cafedb-backup.sql

• Verify the Data Migration
  Open an interactive MySQL session to the RDS instance:
  Run Command:
  mysql --user=root --password='Re:Start!9' \
  --host= \
  cafe_db

Once inside the MySQL session, verify the data in the product table by running the following SQL query:
sql
Run Command:
select * from product;

Ensure that the returned data matches the original database.

• Exit the MySQL Session After verifying the data, exit the MySQL session by entering: Run Command exit

Note: Keep the SSH window open for future tasks.

Configuring the website to use the Amazon RDS instance

• Open AWS Systems Manager
  In the AWS Management Console, search for Systems Manager in the search bar.
  Navigate to Systems Manager.

• Access Parameter Store
  In the left navigation pane, choose Parameter Store.

• Edit the /cafe/dbUrl Parameter
  From the My parameters list, select /cafe/dbUrl.
  Choose Edit to modify the parameter value.

• Update the Database URL
  In the Parameter details page, replace the existing value with the RDS Instance Database Endpoint Address.
  The format should be like:
  Run Command:

cafedbinstance.xxxxxxx.us-west-2.rds.amazonaws.com

Click Save changes to update the parameter.

• Test the Website

Open a new browser window and paste the CafeInstanceURL that you saved earlier (e.g., http://ec2-xx-xx-xx-xx.compute-1.amazonaws.com).
The café website’s homepage should load.

• Verify the Database Connection Go to the Order History tab on the website. Check the number of orders displayed. It should match the number from the local database before the migration.

Monitoring the Amazon RDS database

• Open the Amazon RDS Console
  In the AWS Management Console, search for RDS.
  Go to the RDS Management Console.

• Select the Database
  In the left navigation pane, choose Databases.
  From the list, select cafedbinstance.
  You will now see detailed information about the database.

• View Monitoring Metrics
  Click on the Monitoring tab.
  This tab displays key metrics, including:

1. CPUUtilization
2. DatabaseConnections
3. FreeStorageSpace
4. FreeableMemory
5. WriteIOPS
6. ReadIOPS

• Monitor DatabaseConnections Metric
  Look for the DatabaseConnections graph. If needed, go to page 2 or 3 of the metrics charts.
  This graph tracks the number of active database connections.

• Connect to the RDS Database
  In the CafeInstance terminal window, run the following command to open a MySQL session:
  Run Command
  mysql --user=root --password='Re:Start!9' \
  --host= \
  cafe_db

Replace with your RDS instance endpoint.

• Run an SQL Query Inside the MySQL session, run the following SQL query to retrieve data from the product table: sql Run Command: select * from product;

The query should return the data from the product table.

• Check the DatabaseConnections Graph
  In the RDS console, click the DatabaseConnections graph.
  You should now see 1 active connection from the interactive SQL session.
  If the graph does not update, wait 1 minute and click Refresh.

• Close the MySQL Session
  In the CafeInstance terminal window, exit the MySQL session:

Run Command
exit

• Monitor the Connections After Disconnecting
  Wait for 1 minute, then refresh the DatabaseConnections graph in the RDS console.
  The number of connections should now show as 0.

• Explore Other Metrics
  You can explore additional metrics such as CPUUtilization, FreeStorageSpace, WriteIOPS, and ReadIOPS by reviewing their graphs on the Monitoring tab.