Creating a MySQL DB Instance in Amazon RDS (Relational Database Service)

Dear Readers,

In this article,we will see  Creating a MySQL DB Instance in Amazon RDS (Relational Database Service) .

Implementation steps :

1. Create MySQL DB Instance.
2. Download connect.php file.
3. open connect.php file and replace username, password, hostname and DB name.
4. Create S3 Bucket.
5. Upload connect.php file in S3 bucket.
6. Create Web Server (Specify required commands along with Amazon S3 object’s URL) in userdata.
7. search Public IP/index.php in browser.
8. search Public IP/connect.php in browser (Unable to connect MySQL).
9. Add MYSQL/Aurora and open to web server private IP in your DB Instance VPC security groups.
10. search Public IP/connect.php in browser
11. Able to see successfully connected message by searching public IP/connect.php in browser.
12. First, we need to AWS Console page by using below link.

https://aws.amazon.com/console/

Click on sign in to Console button.

Logging to aws account

Login using username & password and click on sign in.

Enter to AWS Management Console

We can see the AWS Management Console Dashboard.

Go to Services, click on RDS in the Database Module.

Create MySQL DB Instance              

For creating new database to Click on Create Database.

Amazon supports 6 databases

Amazon Aurora not comes under free tier.

If you want to go with free tier select check box (only enable options for RDS Free Usage Tier).

The options comes under free tier.

I have selected Amazon Aurora it is showing one warning message.

Amazon Aurora is not eligible for free tier that’s what we need to choose any of the database expect aurora.

Choose MySQL and click on Next.

Go to Settings,

Specify name for the database instance “ktexperts”(physical),Master Username “ktexperts” (for accessing the database) and provide a master password then click on Next.

Configure Advanced Settings

Select no option in public accessibility.

Database Options

Specify the database Name “ktexperts”.

Click on create database.

Your database “ktexperts” is being created.

Click on All DB instances.

The database has been created successfully.

See the status of the database “Backing Up” means creating backup.

Note

When database created immediately one back will be created default.

Now,we can see the database status is available.

Automated Backup

Automated backups will be created default when database creates.

Verify Backup

Go to snapshots,we can see one snapshot.

Backup in the form of snapshots.

Manual Backup

We need to create backups manually if required.

Go to databases, select DB identifier “ktexperts”  then click on take snapshot in the actions.

Specify snapshot name “ktexperts1” and click on Take Snapshot.

In the snapshots, we can see one snapshot is creating

It will take some time to complete.

Now, the snapshot is available.

Download connect.php file

https://drive.google.com/open?id=1Tw25lMfw5me4gMy5AU_F-5y252S2uc_x

open connect.php file and replace username, password, hostname and DB name.

Note

In the above connect.php file we have to mention your username,password,dbname and hostname.

We coy the hostname from your database.

Go to Amazon RDS,click on DB identifier “ktexperts” to open.

Copy your database Endpoint.

In the php file we have  to replace username, password, hostname and DB name

Note

Host name we have copied earlier.

Go to Services, under the storage module click on S3 service to open.

Create S3 Bucket

Click on create bucket.

 

In Region, Choose required region.

Click on Next.

Click on Next.

Make bucket to public

Unchecked Block all public access.

Go to Manage System permissions and choose Grant Amazon S3 Log Delivery group write access to this bucket then click on Next.

Verify all the details which has given by user then click on create bucket.

We can see the bucket “ktexpertsbucket”.

Upload connect.php file in S3 bucket.

Open the bucket ( click on the bucket name).

Click on upload to add files to your bucket.

Click on Add files.

Choose the required file and click on open.

Click on Next.

Make object public

Go  to Manage Public permissions and choose grant public access to this object.

By default the storage class is standard skip and click on Next.

Verify all the details and click on upload.

We can see the object has been uploaded successfully to the bucket.

Create Web Server

Go to Services, under the compute module click EC2 service to open.

Choose an Amazon Machine Image (AMI)

Select the Operating system of the EC2 instance by choosing any of the Amazon Machine Images (AMI). Select the Microsoft Amazon Linux AMI.

Choose an Instance type

Choose the Type of instance depending on your requirements.

Instance types comprise of varying combinations of CPU, memory, storage, and networking capacity.

select the default option of t2. micro – this instance type is covered within the free tier. Then click on Configure Instance Details.

Configure Instance Details

Configure EC2 instance details as per requirements of your environment and click on Add Storage.

Choose the Type of instance depending on your requirements.

Instance types comprise of varying combinations of CPU, memory, storage, and networking capacity so you can choose the appropriate mix for your applications.

select the default option of t2.micro – this instance type is covered within the free tier. Then click on Configure Instance Details.

Copy the object URL and paste in user data advanced details.

Go to Advanced Details  paste object URL and add required commands in user data.

The Commands are..

#!/bin/bash

sudo su

yum install httpd php php-mysql -y

yum update -y

chkconfig httpd on

service httpd start

cd /var/www/html

echo “ktexperts is a knowledge sharing platform” > index.php

wget https://ktexpertsbucket.s3.ap-south-1.amazonaws.com/connect.php

Add Storage

Here, we can see root volume by default and size of 8GB

Add a new volume if required

Click on Add Tags.

Add Tags

Tags assist in easier identification and classification of the various instances in your AWS environment.

Click on click to add a Name tag to provide name for our server.

Provide the name for the Linux virtual machine for easier understanding and click on Configure Security Groups.

Configure Security Group

A security group allows configuring firewall rules to allow traffic as needed. Only one rule has been added to allow remote connection.

SSH Is enough to connect to our Linux virtual server and click on Review and Launch.

SSH – Secure Shell.

Select HTTP port

Select an existing security group and click on Review and Launch.

Review Instance Launch

Review and confirm the configuration of the instance. Click on the Edit button on each configuration item to make changes and click on Launch.

Choose existing Key Pair and Launch Your Instance

To connect to your virtual machine, you need a key pair. A key pair is used to log into your instance and select existing key pair.

click View Instances to view the instance you have just created and see its status.

We can see web server which was created earlier.

Copy public IP “13.127.223.78” of the Web Server.

Search public IP “13.127.223.78” in browser.

Search Public IP/index.php in browser.

Search public IP “13.127.223.78/index.php” in browser.

Search Public IP/connect.php in browser

Verify the web server is connected to the database

Search public IP “13.127.223.78/connect.php” in browser.

Note

We can see the content “Unable to connect to MySQL” when search public IP in browser.

We need to add private IP of the web server in database Security Groups.

Copy Private IP of the web server.

Add MYSQL/Aurora and open to web server private IP in your DB Instance VPC security groups.

Go to Amazon RDS,click on ktexperts to open.

Go to Security, click on VPC Security groups “rds-launch-wizard-3(sg-061b44207701c72200)”.

Select security group, click on Edit in inbound rules.

Click on Add Rule.

Select MYSQL/Aurora open to webserver private IP “172.31.10.186/32”.

Click on Save.

The port “MYSQL/Aurora” has been added successfully.

Search Public IP/connect.php in browser.

Search public IP “13.127.223.78/connect.php” in browser.

The web server has been connected to MySQL DB Instance successfully.

 

 

Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates

KTEXPERTS is always active on below social media platforms.

Facebook : https://www.facebook.com/ktexperts/
LinkedIn : https://www.linkedin.com/company/ktexperts/
Twitter : https://twitter.com/ktexpertsadmin
YouTube : https://www.youtube.com/c/ktexperts
Instagram : https://www.instagram.com/knowledgesharingplatform