# Create and Connect to a MySQL Database with Amazon RDS via CLI and GUI # **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 ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1715049143422/fc44fa26-2ee1-479d-bbcb-da4d270a2a7d.jpeg align="center") ## ๐ŸŒŸ 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:](http://127.0.0.1:8000/vpc-flow-logs-on-aws/#-VPC-Configuration) VPC Configuration [Step 2:](http://127.0.0.1:8000/vpc-flow-logs-on-aws/#-VPC-Configuration) Launch EC2 Instance [Step 3:](http://127.0.0.1:8000/vpc-flow-logs-on-aws/#-web-page-output) MySQL installation on Instance II - **Instructions of Deployment** [Step 1:](http://127.0.0.1:8000/vpc-flow-logs-on-aws/#-IAM-Role-Creation) Creation of the MySQL Database instance [Step 2:](http://127.0.0.1:8000/vpc-flow-logs-on-aws/#-Log-Group-Creation) Download SQL client [Step 3:](http://127.0.0.1:8000/vpc-flow-logs-on-aws/#-Flow-Logs_creation) Connect to MySQL Database instance [Step 4:](http://127.0.0.1:8000/vpc-flow-logs-on-aws/#-Checking-Logs) 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 ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1713926820700/66569bb8-fa21-4f7a-8072-e37755ed47e3.jpeg align="center") * 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. ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1715043348754/3008e927-3e21-439e-811e-cb4770239908.jpeg align="center") * 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. ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1715043548379/39598d44-4082-4cee-975e-47ba41dcb8ba.jpeg align="center") * 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. ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1714969348885/c64f3c25-8430-4382-878f-4093abbff3b1.jpeg align="center") 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. ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1714969545441/fd282145-dd95-44a4-8571-5368ddf010ea.jpeg align="center") Finally hit " Create " and you will have a picture like bellow ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1714969641472/036a7aca-aafc-4ceb-baa7-77565ccdc8bd.jpeg align="center") [Step 2:](http://127.0.0.1:8000/vpc-flow-logs-on-aws/#-VPC-Configuration) **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 wi](https://console.aws.amazon.com/console/home?region=us-east-1)ndow, 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" ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1715044753771/35acc753-48ac-4a19-99cb-72538767d262.jpeg align="center") ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1715044764335/2f9a9239-e451-4498-8907-3c1d6a8bbd92.jpeg align="center") ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1715044853997/7f879a7e-49a6-49bd-a7a5-09a26d9f473a.jpeg align="center") Network setting: ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1715044866091/911375bf-e813-4f65-8b32-9b4f17f8333a.jpeg align="center") Volume Storage fixed to 8 Gb ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1715044877080/5e3ba8e8-598c-401b-b7ba-567d4d103851.jpeg align="center") Add the role SSM to connect to the instance via the console. ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1715044886114/f810680a-166e-44cc-8b0f-763b9903ccbc.jpeg align="center") Finally Launch the instance ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1715044894905/85919381-7208-44ca-9308-37f97402e091.jpeg align="center") We will get to ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1715045415639/666b4e17-72fb-407b-a05c-2d9fac5324ba.jpeg align="center") [Step 3:](http://127.0.0.1:8000/vpc-flow-logs-on-aws/#-web-page-output)**MySQL installation on Instance** To install MySQL on the instance we need to connect to the instance using the connect option. ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1715045518131/f892e25d-84b5-4504-9c9a-4cfd7c26580d.jpeg align="center") ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1715045530760/1e651815-16a2-4eb5-8f91-f8f99a679aa3.jpeg align="center") As we are using Amazon Linux 2023 AI we need to install MySQL via the commands : Update the system with command: ```bash sudo yum update -y ``` Then Install MySQL with command: ```bash sudo dnf install mariadb105-server ``` ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1715046005171/35bc24b2-0b12-42c6-8ef4-331625ea0890.jpeg align="center") ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1715046014965/3c79f1ff-6636-44da-913d-60986fc3426b.jpeg align="center") ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1715046025038/2290b0a7-06be-477f-9a88-82bfa6de7144.jpeg align="center") ## ๐Ÿ’ผ 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 wi](https://console.aws.amazon.com/console/home?region=us-east-1)ndow, 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.** ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1714970110761/fba36bda-a2b4-4235-a743-f191ee235700.jpeg align="center") b. In the **Create database** section, choose **Create database**. ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1714970759720/bcc8c70d-ce73-4194-9605-f4b999869f0c.jpeg align="center") 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. ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1714970810891/845f2ff2-4c96-4b55-9dc5-f2c9f04aff70.jpeg align="center") ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1714970836338/002e1723-3704-4f14-8f0e-80bce4d33733.jpeg align="center") **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 ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1714971479532/fbdf1fd7-fd3c-4170-927f-e63e76adbde2.jpeg align="center") **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. ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1714971817489/d439e7bd-3c3d-4ee1-8805-795ad56b5a74.jpeg align="center") * **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. ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1714971843728/db7f6c67-6dc0-4917-8da5-07cd2a5b6488.jpeg align="center") * **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). ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1714972039087/5b8eb6ad-4b0d-4173-b346-9c2bcc1febbd.jpeg align="center") **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. ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1714972635406/a8b5c3ee-dc80-4f11-83af-3000c846dfdf.jpeg align="center") ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1714972642796/6f6a170a-aec3-461e-9be4-96260e13cd28.jpeg align="center") 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.](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_Monitoring.html) ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1714972985559/1ae0063d-d230-4ed9-af74-5e7fde08681e.jpeg align="center") 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 Re[sources.](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Overview.Encryption.html) [**Backup**](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Overview.Encryption.html) * **Backup rete**[**ntion period**: You can choose the number of d](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Overview.Encryption.html)ays to retain the backup you take. For this tutorial, set this value to **1 day.** * **Backup window**: Use the default o*f***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. ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1714973000098/9a8d0e14-9446-4d88-9943-cae826d95caf.jpeg align="center") ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1714973248524/148d88cf-e902-4aec-bab5-7e120b452bd7.jpeg align="center") ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1714973256923/7bde862d-aed1-4cc2-8a5d-3efa7c5ea6de.jpeg align="center") Finally create the Database ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1714973278042/6758d914-e08b-4f1f-9c66-f4cbfc1bfdf2.jpeg align="center") 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. ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1714974410144/3f511d40-5b6b-4e77-b632-870542c17955.jpeg align="center") ##### [Step 2:](http://127.0.0.1:8000/vpc-flow-logs-on-aws/#-Log-Group-Creation) 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](http://dev.mysql.com/downloads/workbench/) install MySQL Workbench. For more information on using MySQL, see the [MySQL Documentation.](http://dev.mysql.com/doc/) [Note:ย  Remembe](http://dev.mysql.com/doc/)r to run MySQL Workbench from the same device from which you created the DB instance. The security group your datab[ase is placed in is conf](http://dev.mysql.com/downloads/workbench/)igured 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. ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1714973860464/074b6bf2-050a-40b6-8c36-df59d0efa910.jpeg align="center") 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. ![You will be prompted to login, sign up, or begin your download.](https://d1.awsstatic.com/getting-started-guides/51-create-mysql-db-steps/download-client-b.c0251d6996db538c832bd2cde9189765ab014116.png align="left") 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. ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1714974183448/021a6972-5e4c-40fb-8014-82157ddf73c4.jpeg align="center") [Step 3:](http://127.0.0.1:8000/vpc-flow-logs-on-aws/#-Flow-Logs_creation) 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 ```bash mysql -h hostname -u admin -p ``` The hostname is the endpoint of the database, the username will be admin and password ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1715047049069/516f987e-4767-44a9-8c63-5f17d0752bc5.jpeg align="center") So the final command will be ```bash 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: ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1715047537244/216c18d6-edc8-4b8a-8486-e5990cde1425.jpeg align="center") To connect via the MySQL client using a clear graphic GUI. Go open the MySQL client downloaded before. Then, hit the "plus sign" ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1714979944693/73fd7680-9783-45d0-a881-81e9b11417f0.jpeg align="center") 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.** ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1714979980262/66689e7c-8784-4e60-82f9-bd3e27fda4ab.jpeg align="center") You will now enter the database and you will be able to add or remove date. ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1715050680197/8b3933ee-d4d4-41d2-b272-e042dd570fbc.jpeg align="center") ##### [Step 4:](http://127.0.0.1:8000/vpc-flow-logs-on-aws/#-Checking-Logs) 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. ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1715049993099/5043baab-d0fb-4b6d-b5c4-a9170b10b67a.jpeg align="center") ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1715050007707/8c5db8bb-bef1-437b-848b-378690a59a86.jpeg align="center") The process of deletion will follow. It will take some minutes be patient. ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1715050141587/c06950f3-fdbc-4146-8163-6b4aad224cb6.jpeg align="center") For the EC2 select the instance to delete then chose "Instance state" option then "Terminate instance". ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1715050402280/e3e2ada7-d84c-435e-b84f-fbeeb84ddcc4.jpeg align="center") ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1715050410402/d28b9b90-3890-4590-92f3-519f17f61293.jpeg align="center") ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1715050418850/9d5eef62-6de4-4fef-950a-4ed3552c4b0b.jpeg align="center") ## ๐Ÿค 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