Amazon RDS is an AWS service that fully-managed open-source databases like MySQL, PostgreSQL, and MariaDB and fully-managed commercial databases like Oracle and SQL Server.
Amazon RDS can connect to Amazon EC2, Amazon ECS, Amazon EKS, and many more. For networking, Amazon RDS connects to Amazon VPC.
In this blog, the Task we perform are:
Creating a MySQL Database on Amazon RDS
Installing MySQL Client on Ubuntu and Connecting to Amazon RDS
Create Read Replica in RDS
Amazon RDS Proxy
Creating a MySQL Database on Amazon RDS
Go to Database service and click RDS. Click create a database for the next step.
Choose standard create for the can setting configuration. Choose MySQL for the RDS engine type and MySQL version.
Fill in the database instance name, username, and password for can use the RDS database.
We choose to "don't connect to an EC2 compute resource" and Use your created VPC(test-vpc-1)
Wait until the RDS instance database is available. While waiting to create the RDS instance database, click view credential details.
Open the database you just created, select "VPC Security Groups" in Connectivity & Security:
Edit the inbound rule in the security group must open port 3306 for MySQL/Aurora.
Installing MySQL Client on Ubuntu and Connecting to Amazon RDS
Copy username, password and endpoint are used to database in running EC2 instance.
Go to EC2, connect to EC2 instance and instead update the operation system. For Amazon Linux 2, run sudo apt-get update
After updated, Run sudo apt-get install mysql-client
Run this command : mysql -h <Endpoint url> -u <username> -p <password>
When insert password, you type your password and ENTER. If successful, see Welcome to the MySQL monitor.
Type create database command create table. Type show databases; and see results.
Create Read Replica in RDS
Choose Your Source Database: Pick the DB instance you want to use as the source for your read replica.
Initiate Replica Creation: Give your read replica a name in the DB instance identifier field.
Configure Your Replica: Specify the destination region for the read replica in the AWS Region field.
Availability Options: Note: Creating your read replica as a Multi-AZ DB instance is independent of whether the source database is a Multi-AZ DB instance.
After a little while, you can view your newly created read replica on the Databases page in the RDS console. Look for "Replica" in the Role column.
Run this command : mysql -h <Endpoint url of read replicas> -u <username> -p <password>
When insert password, you type your password and ENTER. If successful, see Welcome to the MySQL monitor.
If you want to create database under read replica, error is coming because it has only read permission
Amazon RDS Proxy
Amazon RDS Proxy is like a smart assistant for your database, making it work better and safer. It helps applications become more scalable, handle failures in the database more smoothly, and ensures better security.
When many applications are connected to a database, it can use up a lot of memory and resources. Amazon RDS Proxy lets applications share and manage these connections, making the database and the application work more efficiently. It reduces the time it takes for the database to recover after a failure, making sure your application stays available and responsive.
Open the RDS dashboard, go to Proxies and click Create proxy:
Name the proxy, select the engine family MariaDB and MySQL
Under Target group configuration, select the database the proxy will connect to. You can leave the maximum connections to 100 percent because we’re using only 1 proxy. If you have many proxies, you can share the percentage between them.
Under Connectivity, for Secrets Manager secret(s), need to create a new secret
Click the "Create a new secret", choose the secret type and set your credentials
In Configure Secret, create Secret name
Successfully stored the secret (My-secret-database)
In Create Proxy, under Connectivity, Secret Manager Secret, select the secret we created (My-secret-database)
then, RDS Proxy (my-first-proxy01) is successfully created
Copy the Proxy Endpoint url
Run this command : mysql -h <Proxy Endpoint url > -u <username> -p <password>
When insert password, you type your password and ENTER. If successful, see Welcome to the MySQL monitor.
