Create and Connect to a MySQL Database with Amazon RDS via CLI and GUI
🚀 Overview:
The project aims to explore the process of creating and connecting to a MySQL database using Amazon RDS (Relational Database Service). Amazon RDS is a managed database service that simplifies the process of setting up, operating, and scaling relational databases in the cloud. By leveraging Amazon RDS, users can create MySQL database instances with ease, benefiting from features such as automated backups, high availability, and scalability.
In this project, we will delve into the steps involved in setting up a MySQL database instance on Amazon RDS, configuring security settings, and establishing connections to the database from various client applications. Through hands-on experimentation, we will gain insights into the capabilities of Amazon RDS and understand how it streamlines database management tasks for developers and organizations.
🔧 Problem Statement
Despite the popularity of MySQL as a relational database management system, setting up and managing MySQL databases can be a complex and time-consuming task, especially for developers and organizations without extensive database administration expertise. Additionally, traditional on-premises database solutions may lack scalability and fault tolerance, leading to performance bottlenecks and downtime. By addressing these objectives, the project aims to equip participants with the knowledge and skills required to leverage Amazon RDS for creating and managing MySQL databases in a cloud environment effectively. Additionally, it seeks to highlight the advantages of adopting cloud-based database solutions for modern application development and deployment scenarios.
💽 Techonology Stack
The architecture consists of the following three tiers:
VPC: AWS VPC
EC2 Instances: AWS EC2
Database: AWS RDS
Database Client Connection: SQL WorkBench
📌 Architecture Diagram
🌟 Project Requirements
Before you get started, make sure you have the following prerequisites in place:
AWS account with an IAM user credentials configured in.
An Infrastructure (VPC, Subnets, route table, Security groups, NACL...) ready to be use for the lab.
An EC2 instance with MySQL installed.
📋 Table of Contents
I - Infrastructure Configuration
Step 1: VPC Configuration
Step 2: Launch EC2 Instance
Step 3: MySQL installation on Instance
II - Instructions of Deployment
Step 1: Creation of the MySQL Database instance
Step 2: Download SQL client
Step 3: Connect to MySQL Database instance
Step 4: Clean Up
✨Infrastructure Configuration
You need to create all resources needed for the accomplishment of the project. We considered this part easy to do. So, we will present infrastructure that was prebuilt
Step 1: VPC Configuration
Here we declare our foundation or networking environment. We have here VPC, subnets, routable, security groups, NACL...
- VPC
- Web Security group
Here we will define the rule or way to access the EC@ instance that will be connect to the Database. Port 80 will be open from everywhere and port 22 SSh will allow access from our computer.
- Database Security group
We will be defining the firewall access to the Database. Here we set up access to the database by creating the security group where we gonna open access to port 3306 Mysql to everywhere and all traffic will depend from the security group of the instance.
- Database Subnet Group
We will have here to group subnets where we want the database to be launch in a group. For our case we will by grouping both private subnets. So, go to AWS Console navigate to RDS, choose option " Subnets group" and Create Subnet Group. You will be prompt to fill a name, Description and the VPC where to choose subnets.
Then you will have to chose the AZs Depending if you want to do Simple or Multi-AZ database you have to chose the number of AZs required. In our case we will choose only two AZs. You also have to know the Id of the private subnets you want to add to the group.
Finally hit " Create " and you will have a picture like bellow
Step 2: Launch EC2 Instance
In this step, we will use Amazon EC2 to create a EC2 Instance with name "web-server", Amazon Linux 2023 AMI, t2.micro instance type, One key pair, launch in the current VPC, on a public subnet and Web-SG, 8 GB of storage and we will add an SSM role. As a reminder, all of this is Free Tier eligible.
Open the AWS Management Console in a new browser window, so you can keep this step-by-step guide open. When the console opens, select EC2 from the left navigation pane and choose EC2 to open the launch Instance.
Give a name to the instance in this case will be : "web-server"
Network setting:
Volume Storage fixed to 8 Gb
Add the role SSM to connect to the instance via the console.
Finally Launch the instance
We will get to
Step 3:MySQL installation on Instance
To install MySQL on the instance we need to connect to the instance using the connect option.
As we are using Amazon Linux 2023 AI we need to install MySQL via the commands :
Update the system with command:
sudo yum update -y
Then Install MySQL with command:
sudo dnf install mariadb105-server
💼 Instructions of Deployment
Follow these steps to produce the deployment:
Step 1: Creation of MySQl database instance
In this step, we will use Amazon RDS to create a MySQL DB Instance with db.t2.micro DB instance class, 20 GB of storage, and automated backups enabled with a retention period of one day. As a reminder, all of this is Free Tier eligible.
a. Open the AWS Management Console in a new browser window, so you can keep this step-by-step guide open. When the console opens, select Database from the left navigation pane and choose RDS to open the Amazon RDS console.
b. In the Create database section, choose Create database.
c. You now have options to select your engine. For this tutorial, choose the MySQL icon, leave the default value of edition and engine version, and select the Free Tier template.
Settings:
Here you have to choice to define your username and password by yoursel or you can have AWS managed them for you. In our case we will be manage those credentials by ourselves. So we have to provide
DB instance identifier: Type a name for the DB instance that is unique for your account in the Region that you selected. For this tutorial, we will name it Database-1.
Master username: Type a username that you will use to log in to your DB instance. We will use masterUsername in this example.
Master password: Type a password that contains from 8 to 41 printable ASCII characters (excluding /,", and @) for your master user password.
Confirm password: Retype your password
Instance specifications:
DB instance class: Select db.t3.micro — 2vCPUs, 1 GiB RAM. This equates to 1 GB memory and 2 vCPUs. To see a list of supported instance classes, see Amazon RDS Pricing.
Storage type: Select General Purpose(SSD). For more information about storage, see Storage for Amazon RDS.
Allocated storage: Select the default of 20 to allocate 20 GB of storage for your database. You can scale up to a maximum of 64 TB with Amazon RDS for MySQL.
Enable storage autoscaling: If your workload is cyclical or unpredictable, you would enable storage autoscaling to enable Amazon RDS to automatically scale up your storage when needed. This option does not apply to this tutorial.
Multi-AZ deployment: Disable because we only have 2 AZs. Note that you will have to pay for Multi-AZ deployment. Using a Multi-AZ deployment will automatically provision and maintain a synchronous standby replica in a different Availability Zone. For more information, see High Availability Deployment.
Connectivity
Compute resource: Choose Don’t connect to an EC2 compute resource. You can manually set up a connection to a compute resource later.
Virtual Private Cloud (VPC): Select Default VPC. For more information about VPC, see Amazon RDS and Amazon Virtual Private Cloud (VPC).
Additional connectivity configurations
Subnet group: Choose the subnet group we created before.
Public accessibility: Choose Yes. This will allocate an IP address for your database instance so that you can directly connect to the database from your own device.
VPC security groups: Select the one we created before which is Database_sg
Availability Zone: Choose No preference. See Regions and Availability Zones for more details.
RDS Proxy: By using Amazon RDS Proxy, you can allow your applications to pool and share database connections to improve their ability to scale. Leave the RDS Proxy unchecked.
Port: Leave the default value of 3306.
Amazon RDS supports several ways to authenticate database users. Choose Password authentication from the list of options
Monitoring
Enhanced monitoring: Leave Enable enhanced monitoring unchecked to stay within the Free Tier. Enabling enhanced monitoring will give you metrics in real time for the operating system (OS) that your DB instance runs on. For more information, see Viewing DB Instance Metrics.
In the Additional configurations section
Database options
Database name: Enter a database name that is 1 to 64 alphanumeric characters. If you do not provide a name, Amazon RDS will not automatically create a database on the DB instance you are creating.
DB parameter group: Leave the default value. For more information, see Working with DB Parameter Groups.
Option group: Leave the default value. Amazon RDS uses option groups to enable and configure additional features. For more information, see Working with Option Groups.
Encryption: This option is not available in the Free Tier. For more information, see Encrypting Amazon RDS Resources.
Backup retention period: You can choose the number of days to retain the backup you take. For this tutorial, set this value to 1 day.
Backup window: Use the default of**No preference.**
Maintenance
Auto minor version upgrade: Select Enable auto minor version upgrade to receive automatic updates when they become available.
Maintenance Window: Select No preference.
Deletion protection: Turn off Enable deletion protection for this tutorial. When this option is enabled, you're prevented from accidentally deleting the database.
Finally create the Database
Your DB instance is now being created.
Note: Depending on the DB instance class and storage allocated, it could take several minutes for the new DB instance to become available.
The new DB instance appears in the list of DB instances on the RDS console. The DB instance will have a status of creating until the DB instance is created and ready for use. When the state changes to available, you can connect to a database on the DB instance.
Feel free to move on to the next step as you wait for the DB instance to become available.
Step 2: Download SQL client
Once the database instance creation is complete and the status changes to available, you can connect to a database on the DB instance using any standard SQL client. In this step, we will download MySQL Workbench, which is a popular SQL client.
a. Go to the Download MySQL Workbench page to download and install MySQL Workbench. For more information on using MySQL, see the MySQL Documentation.
Note: Remember to run MySQL Workbench from the same device from which you created the DB instance. The security group your database is placed in is configured to allow connection only from the device from which you created the DB instance. base on which Operating System you are using you have to choose. For our case we are using MAC.
b. You will be prompted to log in, sign up, or begin your download. You can choose No thanks, just start my download for a quick download.
After Downloading and install client to your computer. You can access it by double clicking on the icon and you will be promt to the image bellow.
Step 3: Connect to MySQL Database instance
To connect to the Database we can use two methods. We can go through the CLI if we want to use command line or via the GUI for access through a graphic or console.
With the CLI we have to use command
mysql -h hostname -u admin -p
The hostname is the endpoint of the database, the username will be admin and password
So the final command will be
mysql -h database-1.c0ywy57rsm6g.us-west-1.rds.amazonaws.com -u admin -p
We will be prompt to enter the password of the Database. Then we will have the image bellow:
To connect via the MySQL client using a clear graphic GUI. Go open the MySQL client downloaded before. Then, hit the "plus sign"
A dialog box appears. Enter the following:
Hostname: You can find your hostname on the Amazon RDS console as shown in the screenshot.
Port: The default value should be 3306.
Username: Type in the username you created for the Amazon RDS database. In this tutorial, it is 'masterUsername.'
Password: Choose Store in Vault (or Store in Keychain on MacOS) and enter the password that you used when creating the Amazon RDS database.
Choose OK.
You will now enter the database and you will be able to add or remove date.
Step 4: Clean Up
The clean up here will consist of deleting the Database and Ec2 Instance just by terminating each of them.
For the Database you must select the database then chose "Actions option then delete.
The process of deletion will follow. It will take some minutes be patient.
For the EC2 select the instance to delete then chose "Instance state" option then "Terminate instance".
🤝 Contributing
Your perspective is valuable! Whether you see potential for improvement or appreciate what's already here, your contributions are welcomed and appreciated. Thank you for considering joining us in making this project even better. Feel free to follow me for updates on this project and others, and to explore opportunities for collaboration. Together, we can create something amazing!
📄 License
This project is licensed under the Joebaho Cloud License