In this project, we set up an Amazon RDS MariaDB instance, migrated data from an EC2-hosted MariaDB database, and monitored the instance using CloudWatch metrics. This project covers the following tasks:
- Creating an Amazon RDS MariaDB instance using the AWS CLI
- Migrating data from an EC2-hosted MariaDB database to Amazon RDS
- Monitoring the RDS instance through the Amazon RDS Console and CloudWatch
- Prerequisites
- Architecture
- Task 1: Creating an Amazon RDS instance by using the AWS CLI
- Task 2: Migrating application data to the Amazon RDS instance
- Task 3: Configuring the website to use the Amazon RDS instance
- Task 4: Monitoring the Amazon RDS Database
- Cleaning Up Resources
- Conclusion
- References
Before starting the project, ensure you have the following:
- AWS CLI installed and configured with the appropriate permissions
- Amazon EC2 instance running with MariaDB installed
- Amazon RDS MariaDB instance created using the AWS CLI
- CloudWatch enabled for the RDS instance
- Access to MariaDB client to interact with the database
- Set up an Amazon RDS MariaDB instance using the AWS CLI.
- Migrate data from a MariaDB database on an EC2 instance to the Amazon RDS MariaDB instance.
- Use CloudWatch to monitor the RDS instance’s health and metrics.
- Open the EC2 Management Console.
- In the navigation pane, select Instances.
- Select the CLI Host instance.
- Choose Connect and then click Connect on the EC2 Instance Connect tab.
-
Connect to the EC2 instance.
-
Run the command to configure AWS CLI:
aws configure
When prompted, enter:
- AWS Access Key ID:
<AccessKey> - AWS Secret Access Key:
<SecretKey> - Default region name:
<LabRegion> - Default output format:
json
- AWS Access Key ID:
Run the following command to create a security group:
aws ec2 create-security-group \
--group-name CafeDatabaseSG \
--description "Security group for Cafe database" \
--vpc-id <CafeInstance VPC ID>- Replace
<CafeInstance VPC ID>with your VPC ID. - Save the
GroupIdfrom the output.
Run the following command to allow inbound traffic on TCP port 3306:
aws ec2 authorize-security-group-ingress \
--group-id <CafeDatabaseSG Group ID> \
--protocol tcp --port 3306 \
--source-group <CafeSecurityGroup Group ID>- Replace
<CafeDatabaseSG Group ID>and<CafeSecurityGroup Group ID>with their respective values.
Run this command to verify that the inbound rule is applied:
aws ec2 describe-security-groups \
--query "SecurityGroups[*].[GroupName,GroupId,IpPermissions]" \
--filters "Name=group-name,Values='CafeDatabaseSG'"Run this command to create the first private subnet:
aws ec2 create-subnet \
--vpc-id <CafeInstance VPC ID> \
--cidr-block 10.200.2.0/23 \
--availability-zone <CafeInstance Availability Zone>- Replace
<CafeInstance VPC ID>and<CafeInstance Availability Zone>with the appropriate values.
Run this command to create the second private subnet:
aws ec2 create-subnet \
--vpc-id <CafeInstance VPC ID> \
--cidr-block 10.200.10.0/23 \
--availability-zone <availability-zone>- Replace
<CafeInstance VPC ID>and<availability-zone>with the appropriate values.
Run this command to create the DB 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"- Replace
<Cafe Private Subnet 1 ID>and<Cafe Private Subnet 2 ID>with the respective subnet IDs.
Run the following command to create the RDS MariaDB instance:
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 <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'- Replace
<CafeInstance Availability Zone>and<CafeDatabaseSG Group ID>with the respective values.
To check the status of the database, run:
aws rds describe-db-instances \
--db-instance-identifier CafeDBInstance \
--query "DBInstances[*].[Endpoint.Address,AvailabilityZone,PreferredBackupWindow,BackupRetentionPeriod,DBInstanceStatus]"Repeat the command until the status shows available. Once available, you will find the endpoint address in the output.
- RDS Instance Database Endpoint Address:
<cafedbinstance.xxxxxxx.us-west-2.rds.amazonaws.com>
These steps should help you create the Amazon RDS instance using AWS CLI! Let me know if you need further clarification.
- First, you'll connect to the CafeInstance (EC2 instance) using EC2 Instance Connect. This instance will interact with the Amazon RDS instance through the MySQL protocol, as it is allowed by the associated security group (CafeDatabaseSG).
- On the CafeInstance, use the
mysqldumputility to create a backup of the local MySQL database (cafe_db). - The command:
mysqldump --user=root --password='Re:Start!9' --databases cafe_db --add-drop-database > cafedb-backup.sql
- This command:
- Uses
mysqldumpto create an SQL backup. - The
--databasesoption specifies the database to back up (cafe_db). --add-drop-databaseensures that aDROP DATABASEstatement is included to remove the database before restoring.> cafedb-backup.sqlredirects the output into a.sqlfile (cafedb-backup.sql).
- Uses
- Open the
cafedb-backup.sqlfile to verify its contents. - To view the file using the Linux
lesscommand:less cafedb-backup.sql
- Navigate with the arrow keys,
Page Up/Down, and pressqto exit.
- To restore the backup file to the Amazon RDS database, use the
mysqlcommand. This command connects to the RDS instance and runs the SQL statements from the backup file. - Command format:
mysql --user=root --password='Re:Start!9' --host=<RDS Instance Database Endpoint Address> < cafedb-backup.sql
- Replace
<RDS Instance Database Endpoint Address>with the actual endpoint address of the Amazon RDS instance.
- After restoring the backup, verify that the data was correctly transferred to the Amazon RDS instance.
- Connect to the Amazon RDS instance using the
mysqlcommand:mysql --user=root --password='Re:Start!9' --host=<RDS Instance Database Endpoint Address> cafe_db
- Query the
producttable to ensure the data has been restored:select * from product;
- Ensure the data returned matches the expected rows.
- After verifying the data, exit the MySQL session:
exit
- Keep the SSH window open for potential future use.
- In the AWS Management Console, search for and select Systems Manager to open the AWS Systems Manager Console.
- In the left navigation pane, click on Parameter Store under Application Management.
- In the My Parameters section, locate the
/cafe/dbUrlparameter. - Click on the parameter name to view its details.
- Select the Edit button to modify the value of the
dbUrlparameter.
- In the Value field, replace the current value with the endpoint address of the Amazon RDS instance that you recorded earlier.
- This action updates the website’s configuration to point to the RDS database instead of the local database.
- After updating the database URL, click on Save changes to save the new value.
- Open a new browser window and enter the URL for the café website (e.g., CafeInstanceURL) that you copied earlier.
- The homepage of the website should load successfully, confirming that the connection to the RDS instance is working.
- Click on the Order History tab on the website.
- Compare the number of orders displayed on the website with the number of orders you recorded before migrating the database to Amazon RDS.
- Both numbers should be the same, confirming that the data has been successfully migrated and is accessible from the new RDS instance.
- To further verify the functionality, place some new orders on the website.
- Ensure that the orders are placed successfully and that the data is being written to the Amazon RDS instance.
- Once the testing is complete, you can close the browser tab.
- In the AWS Management Console, search for and select RDS to open the Amazon RDS Management Console.
- In the left navigation pane, choose Databases.
- From the list of available database instances, select the cafedbinstance.
- Once the database instance details are displayed, navigate to the Monitoring tab.
- The Monitoring tab shows key metrics provided by Amazon RDS through CloudWatch, such as:
- CPUUtilization: The percentage of CPU being used by the database instance.
- DatabaseConnections: The number of active database connections.
- FreeStorageSpace: The available storage space in the database.
- FreeableMemory: The amount of free memory (RAM) available.
- WriteIOPS: The average number of write operations per second.
- ReadIOPS: The average number of read operations per second.
- Some of these metrics may appear across multiple pages, so use the pagination options to explore more metrics.
- To track the number of active database connections, observe the DatabaseConnections metric.
- The graph will show the number of active connections over time.
- To create a connection to the RDS instance, use an interactive SQL session.
- Open the terminal on your CafeInstance (EC2 instance) and enter the following command, replacing
<RDS Instance Database Endpoint Address>with the actual endpoint address of your Amazon RDS instance:mysql --user=root --password='Re:Start!9' --host=<RDS Instance Database Endpoint Address> cafe_db
- This command establishes a connection to the RDS instance, and the DatabaseConnections graph should now reflect one open connection.
- Once connected, run the following SQL query to retrieve data from the
producttable:select * from product;
- Verify that the query returns the expected data.
- To close the database connection, type
exitin the SQL session:exit - After closing the connection, wait for about a minute, then click Refresh in the DatabaseConnections graph.
- The graph should show that the number of active connections has decreased to zero.
- If time allows, explore the other available CloudWatch graphs to monitor additional performance metrics for the RDS instance.
To clean up all resources, delete them in the following order:
-
RDS Instance:
- Go to RDS Dashboard → Databases.
- Select your database and click Delete.
-
EC2 Instance:
- Go to EC2 Dashboard → Instances.
- Select the EC2 instance and click Terminate.
-
Backup Files (if any):
- Delete any files from your local system or S3 bucket.
This section provides the expected outputs and screenshots for each task to help visualize the steps and verify successful execution.
✅ Screenshot: RDS instance details in AWS Management Console
✅ Screenshot: Query results from the RDS instance
✅ Screenshot: Application configuration file update
✅ Screenshot: Web application running with RDS connection
✅ Screenshot: CloudWatch metrics (CPUUtilization, DatabaseConnections, FreeStorageSpace)
- Accessed the RDS Management Console and reviewed key database metrics.
- Monitored the DatabaseConnections metric to observe live connections.
- Used an interactive SQL session to test the connection and query the database.
- Learned how to use CloudWatch to monitor Amazon RDS performance and health metrics.
This project was inspired by AWS RDS migration tutorials and documentation.