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-groupVerify 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.sqlReview 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.sqlVerify 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:
- CPUUtilization
- DatabaseConnections
- FreeStorageSpace
- FreeableMemory
- WriteIOPS
- 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.
Top comments (0)