Day 44: Relational Database Service in AWS

Created a table in Free tier RDS instance of MySQL database , connecting through EC2 mysql-client by assigning IAM role with RDS access.

What is AWS RDS?

AWS RDS stands for Amazon Relational Database Service. It is a managed database service provided by Amazon Web Services (AWS) that simplifies the setup, operation, and scaling of relational databases in the cloud. RDS allows you to quickly deploy and manage several types of relational databases, including MySQL, PostgreSQL, Oracle, SQL Server, and MariaDB.

Here are some key features and aspects of AWS RDS:

Managed Service: AWS RDS is a fully managed service, meaning AWS handles routine database tasks such as provisioning, patching, backup, recovery, and scaling, allowing you to focus on your applications and data.
Multiple Database Engines: RDS supports various popular relational database engines, including MySQL, PostgreSQL, Oracle, SQL Server, and MariaDB. This allows you to choose the database engine that best fits your application requirements.
High Availability and Durability: RDS offers built-in features for high availability and durability. Multi-AZ (Availability Zone) deployments replicate your database across multiple availability zones to provide failover support and data redundancy. Automated backups and snapshots enable point-in-time recovery and data protection.
Scalability: RDS allows you to easily scale your database resources up or down based on demand. You can vertically scale by changing the instance type or horizontally scale by adding read replicas to offload read traffic from the primary database.
Security: RDS offers various security features to protect your data, including network isolation with VPC (Virtual Private Cloud), encryption at rest using AWS Key Management Service (KMS), encryption in transit with SSL/TLS, IAM authentication for database access control, and database parameter groups for fine-grained configuration.
Monitoring and Management: RDS provides comprehensive monitoring and management capabilities through Amazon CloudWatch metrics, enhanced monitoring, RDS events, and performance insights. You can monitor database performance, set up alarms, and automate maintenance tasks.
Cost-Effective: AWS RDS offers a pay-as-you-go pricing model with no upfront costs or long-term commitments. You can choose from various pricing options, including On-Demand Instances, Reserved Instances, and the AWS Free Tier for eligible new AWS customers.

Overall, AWS RDS simplifies database administration tasks and enables you to focus on building scalable, secure, and reliable applications without the overhead of managing the underlying infrastructure.

TASK

1.Creating a Free tier RDS instance of MySQL

Creating a free tier RDS (Relational Database Service) instance of MySQL involves several steps. Here's a step-by-step guide:

Sign in to the AWS Management Console: Go to the AWS Management Console (https://aws.amazon.com/console/) and sign in with your AWS account credentials.
Navigate to the RDS service: Once logged in, navigate to the "RDS" service by typing "RDS" in the search bar and selecting it from the results.
Click on "Create database": In the RDS dashboard, click on the "Create database" button.
Choose a database creation method: Select "Standard Create" to have more options available for configuration. But due to budget problem i am selecting "Easy create"
Select Engine options: Choose "MySQL" as the database engine.
Select a Use Case: For a free tier instance, choose "Dev/Test" or "Standard" depending on your requirements.
Specify DB Details:
- Templates: Choose "Free tier" to ensure you're within the free tier limits.
- DB Instance Class: Select "Burstable classes (includes t2.micro)" which is eligible for the free tier.
- Give Password:
- Multi-AZ deployment: For the free tier, choose "No" to keep it single AZ.
- Storage Type: Choose "General Purpose (SSD)".
- Allocated Storage: Set the storage size within the free tier limits.
- DB Instance Identifier: Give your instance a name.
Configure Advanced Settings:
- Network & Security: Configure VPC, Subnet group, Public accessibility, Security group, etc.
- Database options: Configure database name, port, parameter group, etc.
- Backup: Configure backup settings.
- Maintenance: Set maintenance preferences.
- Monitoring: Enable enhanced monitoring if needed.
- Performance Insights: Enable if needed.
- Log exports: Set up log exports if required.
Add Tags (Optional): Add tags for better organization if necessary.
Review and Launch: Review your configurations to ensure everything is set as per your requirements.
Launch the Instance: Click on the "Create database" button to launch your RDS instance.
Wait for Deployment: Wait for AWS to provision your RDS instance. This process usually takes a few minutes.
Access Your Database: Once the instance is created and available, you can access it using the endpoint provided in the RDS dashboard. You can use MySQL client tools or connect your application to this endpoint.

Remember, even though it's in the free tier, you still need to manage your instance properly to avoid unexpected charges. Always monitor your usage and be mindful of any resources you consume beyond the free tier limits.

2. Creating a EC2 instance and installing mysql-client

1. Navigate to the EC2 service:

click on " Launch instance" and fill all the necessary details and Launch instance.

Connect instance through SSH:
1. Update and install mysql-client:

3.Create an IAM role with RDS access

To create an IAM role with RDS access for EC2 instances, follow these step-by-step instructions:

Sign in to the AWS Management Console: Go to the AWS Management Console (https://aws.amazon.com/console/) and sign in with your AWS account credentials.
Navigate to the IAM service: Once logged in, navigate to the "IAM" service by typing "IAM" in the search bar and selecting it from the results.
Click on "Roles" in the left navigation pane: Roles manage access to AWS services and resources.
Click on "Create role" button: This will start the process of creating a new IAM role.
Choose the trusted entity:
- For AWS service, select "AWS service" as the trusted entity.
- For the use case, select the service that will assume this role. Since you want to grant access to an EC2 instance, select "EC2" from the list of services.
Attach policies:
- Search for policies that grant the necessary permissions for RDS access. For example, you might search for "AmazonRDSReadOnlyAccess" to grant read-only access to RDS.
- Check the box next to the policy you want to attach.
Click on "Next: Tags" (Optional): Here you can add tags to your role for better organization. Tags are key-value pairs.
Click on "Next: Review": Review the details of your role to ensure everything is correct.
Enter a role name: Give your role a descriptive name that reflects its purpose, for example, "EC2_RDS_Access".
Click on "Create role": This will create the IAM role with the specified permissions.

Once the role is created, you need to attach it to your EC2 instances. You can do this during the instance creation process or by modifying the instance's IAM role afterwards. When an EC2 instance is launched with this role, it will be able to access RDS resources according to the permissions granted by the attached policies.

Update IAM role:

4.Creating a table and inserting data in RDS instance of mysql

Connect to the RDS Instance: Use the MySQL client to connect to your RDS instance. Replace <endpoint> with the endpoint of your RDS instance, <username> with the username for your MySQL database, and <password> with the corresponding password:
```
 mysql -h <endpoint> -u <username> -p
```
You will be prompted to enter the password for the MySQL user.

Create the Table: Once connected to the MySQL prompt, execute the following SQL command to create the "learners" table:

Show Databases:

Create database:

 create database aws;
 use aws;
 CREATE TABLE learners (
     id INT AUTO_INCREMENT PRIMARY KEY,
     name VARCHAR(50),
     age INT
 );

This command creates a table named "learners" with three columns: "id" (auto-incremented primary key), "name" (varchar), and "age" (int).
Insert Data into the Table: After creating the table, you can insert data into it using the following SQL command:
```
 INSERT INTO learners (name, age) VALUES
     ('John', 25),
     ('Emily', 30),
     ('Michael', 28);
```
This command inserts three rows of data into the "learners" table.
Verify Data Insertion: You can verify that the data has been inserted successfully by executing a SELECT query:
```
 SELECT * FROM learners;
```
This will display all the rows in the "learners" table, confirming that the data insertion was successful.

That's it! You've now created a table named "learners" and inserted data into it in your RDS instance of MySQL using an EC2 MySQL client.