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:
Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/.
In the navigation pane, choose Databases and then choose Create database.
On the Create database page, choose Standard create and then choose MySQL as the database engine.
Under Templates, choose Free tier.
Under Settings, enter a name for your database instance, such as my-mysql-db.
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.
Under DB instance size, choose Burstable classes (includes t classes) and then choose db.t2.micro as the instance type.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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:
Sign in to the AWS Management Console and open the Amazon EC2 console at https://console.aws.amazon.com/ec2/.
In the navigation pane, choose Instances and then choose Launch Instance.
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.
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.
On the Configure Instance Details page, leave the default settings and choose Next: Add Storage.
On the Add Storage page, leave the default settings and choose Next: Add Tags.
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.
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.
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.
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:
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 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.
- When you connect to your instance, you’ll see a welcome message like this:
__| __|_ )
_| ( / Amazon Linux 2 AMI
___|\___|___|
https://aws.amazon.com/amazon-linux-2/
[ec2-user@ip-172-31-41-222 ~]$
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:
Sign in to the AWS Management Console and open the IAM console at https://console.aws.amazon.com/iam/.
In the navigation pane, choose Roles and then choose Create Role.
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.
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.
Choose Next: Tags and then choose Next: Review.
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.
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.
Open the Amazon EC2 console in a new tab or window at https://console.aws.amazon.com/ec2/.
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.
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.
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:
Connect to your EC2 instance using SSH, as you did in the previous section.
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:
This means that the MySQL client has been installed successfully on your instance.
- Connect to your RDS instance using the MySQL client using the
mysql command. The syntax of the command is:
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:
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 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:
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:
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!
