# How to Use Amazon RDS to Set Up and Manage MySQL Databases in AWS: A Complete Guide for DevOps Engineers

Are you a DevOps learner who wants to use Amazon Relational Database Service (Amazon RDS) to set up and manage MySQL databases in AWS? If yes, then this blog post is for you. In this post, I’ll explain what Amazon RDS is and how to use it to create, configure, and connect to MySQL databases in the cloud. By the end of this post, you’ll be able to:

* Create a free tier RDS instance of MySQL
    
* Create an EC2 instance
    
* Create an IAM role with RDS access
    
* Assign the role to EC2 so that your EC2 instance can connect with RDS
    
* Connect your EC2 instance to your RDS instance using a MySQL client
    

## What is Amazon RDS

Amazon Relational Database Service (Amazon RDS) is a collection of managed services that makes it simple to set up, operate, and scale databases in the cloud. Amazon RDS supports several popular database engines, such as MySQL, PostgreSQL, Oracle, SQL Server, and MariaDB. With Amazon RDS, you can enjoy the benefits of a fully managed database service, such as:

* Automated backups, restores, and snapshots
    
* Automated software patching and upgrades
    
* Automated scaling of storage and compute capacity
    
* Automated failover and high availability
    
* Automated encryption and security
    
* Automated monitoring and metrics
    

Amazon RDS also provides you with the flexibility to customize your database settings, such as:

* Choosing the database engine and version
    
* Choosing the instance type and size
    
* Choosing the storage type and size
    
* Choosing the availability zone and region
    
* Choosing the security group and VPC
    
* Choosing the parameter group and option group
    
* Choosing the maintenance window and backup window
    

With Amazon RDS, you can focus on your application logic and data, without worrying about the underlying infrastructure and administration of your database. You can also leverage the integration of Amazon RDS with other AWS services, such as Amazon EC2, Amazon S3, Amazon Lambda, and Amazon CloudFormation.

## Creating a Free Tier RDS Instance of MySQL

In this section, I’ll show you how to create a free tier RDS instance of MySQL using the AWS Management Console. A free tier RDS instance is an instance that is eligible for the AWS Free Tier, which allows you to use certain AWS services for free for 12 months. A free tier RDS instance of MySQL has the following specifications:

* Database engine: MySQL 8.0
    
* Instance type: db.t2.micro
    
* Storage type: General Purpose SSD (gp2)
    
* Storage size: 20 GB
    
* Backup retention period: 7 days
    
* Multi-AZ deployment: No
    

To create a free tier RDS instance of MySQL, follow these steps:

1. Sign in to the AWS Management Console and open the Amazon RDS console at [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/).
    
    ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1699966711961/965d8714-b161-4dfa-bae2-6a349954892e.png align="center")
    
2. In the navigation pane, choose **Databases** and then choose **Create database**.
    
    ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1699966746740/ebcea9a4-60d6-4010-b8e6-bf6ef44811fb.png align="center")
    
3. On the **Create database** page, choose **Standard create** and then choose **MySQL** as the database engine.
    
    ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1699966854618/929dab54-d8d7-4c0a-b484-c0ac4ab22e07.png align="center")
    
4. Under **Templates**, choose **Free tier**.
    
    ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1700015562484/d8641760-14ef-48e5-97a0-e0e2fe999692.png align="center")
    
5. Under **Settings**, enter a name for your database instance, such as `my-mysql-db`.
    
    ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1700015667907/e88b9048-2b38-4867-a408-b8a9b6f8ad87.png align="center")
    
6. Under **Credentials Settings**, enter a username and a password for your database, such as `admin` and `Password1234`. Remember these credentials as you’ll need them later to connect to your database.
    
    ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1700015721286/e590d94f-c0d1-495e-a1ed-d3c2d04c9e2d.png align="center")
    
7. Under **DB instance size**, choose **Burstable classes (includes t classes)** and then choose **db.t2.micro** as the instance type.
    
    ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1700015791073/315f569b-1190-4ed7-a5a4-6f321ac6328f.png align="center")
    
8. Under **Storage**, choose **General Purpose (SSD)** as the storage type and enter `20` as the allocated storage. Make sure that **Enable storage autoscaling** is disabled.
    
    ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1700015860227/c85e6326-9f5f-4a3a-bb99-524b273e4e06.png align="center")
    
9. Under **Connectivity**, expand **Additional connectivity configuration** and choose **Publicly accessible** as the access option. This will allow you to connect to your database from your EC2 instance or any other device over the internet. You can also choose **No** if you want to restrict the access to your database within your VPC.
    
10. Under **VPC security group**, choose **Create new** and enter a name for your security group, such as `my-mysql-sg`. This will create a new security group that allows inbound traffic on port 3306 (the default port for MySQL) from any IP address. You can also choose **Choose existing** and select an existing security group that has the same rule. You can also modify the rule later to limit the access to your database from specific IP addresses or CIDR blocks.
    
    ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1700016878140/0ee9cb3b-bebe-4ad7-8a64-9e9c91fac9db.png align="center")
    
11. Under **Database authentication**, choose **Password authentication** as the authentication method. This will allow you to connect to your database using the username and password that you entered earlier. You can also choose **IAM authentication** if you want to use AWS Identity and Access Management (IAM) to manage access to your database.
    
    ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1700016931149/c824654c-412b-4045-876c-1b99c126e1b9.png align="center")
    
12. Under **Additional configuration**, enter a name for your database, such as `mydb`. This will create a default database in your instance that you can use to store your data. You can also leave this blank and create your own databases later using the MySQL client.
    
13. Under **Backup**, choose **Enable automatic backups** and enter `7` as the backup retention period. This will enable the automated backup feature of Amazon RDS, which will take daily snapshots of your database and store them for 7 days. You can also choose **Disable automatic backups** if you don’t want to use this feature. You can also modify the backup window and the backup encryption settings later.
    
    ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1700017074280/009742be-e3db-42e5-8819-31cc558f24cb.png align="center")
    
14. Under **Monitoring**, choose **Enable Enhanced monitoring** and enter `60` as the granularity. This will enable the enhanced monitoring feature of Amazon RDS, which will collect and display metrics about your database instance every 60 seconds. You can also choose **Disable Enhanced monitoring** if you don’t want to use this feature. You can also modify the monitoring role and the additional metrics settings later.
    
    ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1700017181213/a1ea82fb-773e-40ee-a97d-80fbf00fc408.png align="center")
    
15. Under **Log exports**, choose **Enable log exports** and select the logs that you want to export to Amazon CloudWatch Logs, such as **General log**, **Slow query log**, and **Error log**. This will enable the log export feature of Amazon RDS, which will send the selected logs to CloudWatch Logs for analysis and troubleshooting. You can also choose **Disable log exports** if you don’t want to use this feature.
    
    ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1700017213593/d6c14666-9791-4f55-91af-6294c1183981.png align="center")
    
16. Under **Maintenance**, choose **Enable auto minor version upgrade**. This will enable the auto minor version upgrade feature of Amazon RDS, which will automatically apply minor version patches and updates to your database engine. You can also choose **Disable auto minor version upgrade** if you don’t want to use this feature. You can also modify the maintenance window and the deletion protection settings later.
    
    ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1700017248095/b6370528-e08b-4cae-a882-cad34b22cb17.png align="center")
    
17. Choose **Create database** to create your database instance. This will take a few minutes to complete. You can monitor the progress of the creation on the **Databases** page of the Amazon RDS console. You can also view the details of your database instance, such as the endpoint, the port, the status, and the metrics, on the same page.
    

Congratulations! You have successfully created a free tier RDS instance of MySQL using the AWS Management Console.

## Creating an EC2 Instance

An EC2 instance is a virtual server that runs in the AWS cloud. You can use an EC2 instance to run applications, host websites, or perform any other tasks that require compute resources. You can also use an EC2 instance to connect to your RDS instance using a MySQL client, as I’ll show you in this section.

To create an EC2 instance using the AWS Management Console, follow these steps:

1. Sign in to the AWS Management Console and open the Amazon EC2 console at [https://console.aws.amazon.com/ec2/](https://console.aws.amazon.com/ec2/).
    
2. In the navigation pane, choose **Instances** and then choose **Launch Instance**.
    
3. On the **Choose an Amazon Machine Image (AMI)** page, choose **Amazon Linux 2 AMI (HVM), SSD Volume Type** as the AMI. This is a Linux-based AMI that comes with the AWS CLI and other tools pre-installed. You can also choose another AMI that suits your needs, such as Windows or Ubuntu.
    
4. On the **Choose an Instance Type** page, choose **t2.micro** as the instance type. This is a free tier eligible instance type that provides a small amount of CPU and memory resources. You can also choose another instance type that suits your needs, such as t3 or m5.
    
5. On the **Configure Instance Details** page, leave the default settings and choose **Next: Add Storage**.
    
6. On the **Add Storage** page, leave the default settings and choose **Next: Add Tags**.
    
7. On the **Add Tags** page, choose **Add Tag** and enter `Name` as the key and `my-ec2-instance` as the value. This will assign a name tag to your instance, which will help you to identify it later. You can also add other tags as per your requirements.
    
8. On the **Configure Security Group** page, choose **Create a new security group** and enter a name for your security group, such as `my-ec2-sg`. This will create a new security group that allows inbound traffic on port 22 (the default port for SSH) from any IP address. You can also choose **Select an existing security group** and select an existing security group that has the same rule. You can also modify the rule later to limit the access to your instance from specific IP addresses or CIDR blocks.
    
9. Choose **Review and Launch** to review your instance settings and then choose **Launch** to launch your instance. This will take a few minutes to complete. You can monitor the progress of the launch on the **Instances** page of the Amazon EC2 console. You can also view the details of your instance, such as the public IP address, the status, and the metrics, on the same page.
    
10. When your instance is running, choose **Connect** to connect to your instance using SSH. You’ll need to select a key pair that you have access to, or create a new one if you don’t have one. A key pair consists of a public key and a private key that are used to encrypt and decrypt the communication between your instance and your device. You’ll need to download and save the private key file (.pem) to your device and change its permissions to make it readable only by you. You’ll also need to use a terminal or a shell program that supports SSH, such as PuTTY or Git Bash. You’ll see a command like this that you can use to connect to your instance:
    

```bash
ssh -i "my-key-pair.pem" ec2-user@ec2-18-222-111-222.us-east-2.compute.amazonaws.com
```

where `my-key-pair.pem` is the name of your private key file, `ec2-user` is the default user name for the Amazon Linux 2 AMI, and [`ec2-18-222-111-222.us-east-2.compute.amazonaws.com`](http://ec2-18-222-111-222.us-east-2.compute.amazonaws.com) is the public DNS name of your instance. You can also use the public IP address of your instance instead of the public DNS name.

1. When you connect to your instance, you’ll see a welcome message like this:
    

```bash
       __|  __|_  )
       _|  (     /   Amazon Linux 2 AMI
      ___|\___|___|

https://aws.amazon.com/amazon-linux-2/
[ec2-user@ip-172-31-41-222 ~]$
```

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1700019382364/b2c557e4-e3a5-4d94-bc21-75e5ddfa4795.png align="center")

This means that you have successfully connected to your EC2 instance. You can now run commands on your instance, such as `ls`, `pwd`, and `sudo yum update`.

Congratulations! You have successfully created an EC2 instance using the AWS Management Console.

## Creating an IAM Role with RDS Access and Assigning it to EC2

An IAM role is a set of permissions that you can use to grant access to AWS resources and services. You can create an IAM role and assign it to your EC2 instance, so that your instance can access your RDS instance without using any credentials. This is a secure and convenient way to connect to your RDS instance from your EC2 instance.

To create an IAM role with RDS access and assign it to your EC2 instance using the AWS Management Console, follow these steps:

1. Sign in to the AWS Management Console and open the IAM console at [https://console.aws.amazon.com/iam/](https://console.aws.amazon.com/iam/).
    
2. In the navigation pane, choose **Roles** and then choose **Create Role**.
    
    ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1700019447042/003561e7-f7a2-4d32-8a97-491efad680b3.png align="center")
    
3. On the **Create role** page, choose **AWS service** as the type of trusted entity and choose **EC2** as the service that will use this role. Then choose **Next: Permissions**.
    
    ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1700019554483/3320e26b-64b1-4616-8a73-d23779268cf0.png align="center")
    
4. On the **Add Permissions** page, search for **AmazonRDSFullAccess** and select the checkbox next to it. This is a managed policy that grants full access to Amazon RDS. You can also choose another policy that suits your needs, such as **AmazonRDSReadOnlyAccess** or **AmazonRDSCustomPolicy**. You can also create your own custom policy with specific permissions for Amazon RDS.
    
    ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1700019696895/db37d96b-3d2a-42ad-8d7d-4d2232c2104e.png align="center")
    
5. Choose **Next: Tags** and then choose **Next: Review**.
    
6. On the **Review** page, enter a name for your role, such as `my-ec2-rds-role`. You can also enter an optional description for your role. Then choose **Create role**.
    
    ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1700019796475/77555c0b-0c54-48c7-bf00-98cb7bf4b4d5.png align="center")
    
7. On the **Roles** page, search for the role that you just created and select it. You’ll see the details of your role, such as the policy, the trust relationship, and the ARN.
    
    ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1700019849548/e35182f6-4ae5-48a3-a185-4caf32429572.png align="center")
    
8. Open the Amazon EC2 console in a new tab or window at [https://console.aws.amazon.com/ec2/](https://console.aws.amazon.com/ec2/).
    
9. In the navigation pane, choose **Instances** and select the instance that you created in the previous section. Then from Actions choose **Security**, and **Modify IAM Role**.
    
    ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1700020070964/55e71e5c-bafd-49c1-b8e3-4333644d49d9.png align="center")
    
10. On the **Modify IAM Role** page, choose the role that you created in the previous steps from the **IAM role** dropdown list. Then choose **Apply**.
    
    ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1700020122014/164e283c-46a0-4f1e-9ca9-1994e70d0da9.png align="center")
    

Congratulations! You have successfully created an IAM role with RDS access and assigned it to your EC2 instance using the AWS Management Console.

## Connecting your EC2 Instance to your RDS Instance using a MySQL Client

A MySQL client is a software program that allows you to interact with a MySQL server, such as your RDS instance, using commands or queries. You can use a MySQL client to create and manage databases, tables, and data on your RDS instance. You can also use a MySQL client to perform basic operations on your MySQL database, such as inserting, updating, deleting, and selecting data.

To connect your EC2 instance to your RDS instance using a MySQL client, follow these steps:

1. Connect to your EC2 instance using SSH, as you did in the previous section.
    
2. Install the MySQL client on your EC2 instance using the `sudo yum install mysql` command. This will install the MySQL client and its dependencies on your instance. You should see an output like this:
    
    ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1700020350345/53174229-bf7b-45c7-9b29-f3d8ee58954a.png align="center")
    

This means that the MySQL client has been installed successfully on your instance.

1. Connect to your RDS instance using the MySQL client using the `mysql` command. The syntax of the command is:
    

```bash
mysql -h hostname -P port -u username -p
```

where `hostname` is the endpoint of your RDS instance, `port` is the port number of your RDS instance, `username` is the username of your database, and `-p` prompts you to enter the password of your database. For example, to connect to the RDS instance that you created in the previous section, you can run the following command:

```bash
mysql -h my-mysql-db.cwqfjzqfjzqf.us-east-2.rds.amazonaws.com -P 3306 -u admin -p
```

where [`my-mysql-db.cwqfjzqfjzqf.us-east-2.rds.amazonaws.com`](http://my-mysql-db.cwqfjzqfjzqf.us-east-2.rds.amazonaws.com) is the endpoint of your RDS instance, `3306` is the port number of your RDS instance, `admin` is the username of your database, and `-p` prompts you to enter the password of your database. You can find the endpoint and the port number of your RDS instance on the **Databases** page of the Amazon RDS console.

You’ll be prompted to enter the password of your database. Enter the password that you entered when you created your RDS instance and press **Enter**. You should see a message like this:

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1700020741729/24d7bc70-c1a7-43a0-a55d-49cf47c34626.png align="center")

This means that you have successfully connected to your RDS instance using the MySQL client. You can now run commands or queries on your MySQL database using the MySQL client. For example, you can run the following command to show the databases on your RDS instance:

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1700020787177/e699c93f-da3f-45ea-9b88-b0ebcdf930ea.png align="center")

You can see that there are five databases on your RDS instance, including the default database `mydb` that you created when you created your RDS instance. You can also run other commands or queries on your MySQL database, such as creating tables, inserting data, updating data, deleting data, and selecting data.

Congratulations! You have successfully connected your EC2 instance to your RDS instance using a MySQL client.

## Conclusion

In this blog post, I showed you how to use Amazon RDS to set up and manage MySQL databases in AWS. You learned how to:

* Create a free tier RDS instance of MySQL
    
* Create an EC2 instance
    
* Create an IAM role with RDS access
    
* Assign the role to EC2 so that your EC2 instance can connect with RDS
    
* Connect your EC2 instance to your RDS instance using a MySQL client
    

By using Amazon RDS, you can enjoy the benefits of a fully managed database service, such as automated backups, scaling, failover, encryption, monitoring, and patching. You can also customize your database settings, such as the engine, the instance type, the storage, the security, and the authentication. You can also leverage the integration of Amazon RDS with other AWS services, such as Amazon EC2, Amazon S3, Amazon Lambda, and Amazon CloudFormation.

I hope you enjoyed this blog post and learned something new. If you have any questions or feedback, please feel free to leave a comment below. I’d love to hear from you.

Thank you for reading and happy coding!