Day 44 of 90 Days of DevOps Challenge: Relational Database Service (RDS) in AWS

Introduction to Amazon RDS

Amazon Relational Database Service (Amazon RDS) simplifies the process of setting up, operating, and scaling relational databases in the cloud. RDS supports several database engines, including MySQL, PostgreSQL, MariaDB, and SQL Server. It automates database tasks such as backups, patching, and scaling, allowing you to focus on your application development.


Today's Task Overview:

In today's challenge, we will focus on setting up an Amazon RDS instance for MySQL, and connecting it from an EC2 instance using an IAM role.

Task 1: Create a Free Tier RDS Instance of MySQL

Step-by-Step Guide:

  1. Navigate to the RDS Console:

    • Go to the AWS Management Console and search for RDS.

    • Click on Create Database.

  2. Select Database Engine:

    • Choose MySQL as your database engine.

    • Select the Free Tier option.

  3. Configure Database Settings:

    • DB Instance Identifier: Enter a name for your RDS instance.

    • Master Username: Choose a username (e.g., admin).

    • Master Password: Set a strong password for the database.

  4. Instance Configuration:

    • Select the t2.micro instance type.

    • Leave storage settings as default to stay within the free tier.

  5. Connectivity:

    • Make sure the Publicly Accessible option is enabled, as you will connect from an EC2 instance.

    • Set VPC Security Group to allow inbound connections from your EC2 instance (you can either create a new security group or use an existing one).

  6. Launch the RDS Instance:

    • Review the configuration and click Create Database.
  7. Wait for the RDS instance to launch: This may take a few minutes. Once it's up, note down the endpoint (this will be used to connect from EC2).


Task 2: Create an EC2 Instance and Assign an IAM Role for RDS Access

Step-by-Step Guide:

  1. Create an EC2 Instance:

    • Navigate to the EC2 Console and click on Launch Instance.

    • Choose Amazon Linux 2 as the AMI.

    • Select t2.micro instance type (free tier).

    • In Configure Instance Details, leave the default settings, but make sure to place the instance in the same VPC as your RDS instance.

  2. Create an IAM Role with RDS Access:

    • Go to the IAM Console and click Roles > Create Role.

    • Choose EC2 as the trusted entity.

    • In Permissions, select the AmazonRDSFullAccess policy to grant your EC2 instance access to RDS.

    • Name the role (e.g., RDS-EC2-Role) and click Create Role.

  3. Assign the Role to EC2:

    • Once the role is created, go back to the EC2 Console.

    • Right-click on your running instance, choose Instance Settings > Attach/Replace IAM Role.

    • Select the role you created (RDS-EC2-Role) and assign it to the instance.

  4. Install MySQL Client on EC2:

    • SSH into your EC2 instance:

        ssh -i your-key.pem ec2-user@your-ec2-public-ip
      
    • Install the MySQL client:

        #!/bin/bash
      
        # Update package index
        sudo apt update -y
      
        # Install MySQL Server
        sudo apt install mysql-server -y
      
        # Secure MySQL installation (auto answers with defaults)
        sudo mysql -e "UPDATE mysql.user SET authentication_string = PASSWORD('your_root_password') WHERE User = 'root';"
        sudo mysql -e "DELETE FROM mysql.user WHERE User='';"
        sudo mysql -e "DROP DATABASE IF EXISTS test;"
        sudo mysql -e "FLUSH PRIVILEGES;"
      
        # Create a new MySQL user (replace 'newuser' and 'password' with desired values)
        sudo mysql -e "CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';"
        sudo mysql -e "GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'localhost' WITH GRANT OPTION;"
        sudo mysql -e "FLUSH PRIVILEGES;"
      
        # Start MySQL service
        sudo systemctl start mysql
        sudo systemctl enable mysql
      
        # Check MySQL service status
        sudo systemctl status mysql
      


Task 3: Connect to the RDS Instance from EC2

Step-by-Step Guide:

  1. Get the RDS Endpoint:

  2. Connect to the RDS from EC2:

    • In your EC2 instance terminal, run the following command to connect to the MySQL database:

        mysql -h your-rds-endpoint -P 3306 -u admin -p
      
    • You’ll be prompted to enter the Master Password that you set while creating the RDS instance.

  3. Verify the Connection:

    • After entering the password, you should be connected to the RDS MySQL instance.

    • Run basic MySQL commands like:

        SHOW DATABASES;
      

Conclusion

Today, you took a significant step towards mastering AWS RDS and connecting it programmatically using an IAM role from an EC2 instance. This hands-on experience will be highly valuable when automating cloud-based database management and optimizing access control. Remember, cloud databases like RDS simplify database management and allow for easy scaling and high availability.

Keep practicing, and see you tomorrow for the next day of the challenge! 🚀