How to setup mysql database on AWS RDS ( Relational Database Service )

In this Article we will learn how to setup a mysql database in AWS RDS
and How to connect that database from phpmyadmin of our EC2 instance or local Machine. All of this is free tier eligible.

Prerequisites
Create a AWS Account .

Create a DB Instance on Amazon RDS

We will use Amazon RDS to create a MySQL DB Instance with db.t2.micro DB instance class, 20 GB of storage and this is free tier eligible.

The Amazon RDS Free Tier is available to you for 12 months.

• 750 hrs per month of Amazon RDS in a Single-AZ db.t2.micro Instance.
• 20 GB per month of General Purpose Storage (SSD).
• 20 GB per month for automated backup storage and any user-initiated DB Snapshots.

Steps to create a DB Instance

1. Login with your AWS Account and go to console.

2. Search for RDS in Services and go to RDS .

3. In the top right corner of the Amazon RDS console, select the Region in which you want to create the DB instance . (optional)

4. Click on Create Database in Create Database Section on Dashboard.

5. Choose Standard Create to configure db Instance .

6. Choose MYSQL in Engine options and Free Tier in Templates.

7. Configure a DB instance name and credentials settings.

8. Disable Storage auto scaling in storage Section .

9. Change you Additional connectivity configurations . Public Accessible to yes to access the Database from local machine.

10 . Set Database Authentication to Password Authentication

11. Now Coming towards Additional Configuration

a ) Enter a Initial Database name

b) Enable Automatic Backups and Backup retention period to 1 or 2 days and Disable Monitoring.

d) Enable All Log exports.

e) Enable auto minor version upgrade and Delete Protection also . This will not allow anyone to delete the Db instance Directly .

12. Click on create Database

Here , you are done with setting a MYSQL database in Amazon RDS (Free Tier Eligible ) .

How to Access mysql Database from phpmyadmin in local Machine or EC2 Instance .

To access the db instance from your local machine you have to allow the public accessibility option to yes in step 9. And also the Security group rules in your Db instance should look like this .

Now open phpmyadmin config.inc.php in your machine or EC2 instance to connect with your database server .

Open /etc/phpmyadmin/config.inc.php to add DB Instance to phpmyadmin

$ sudo vi /etc/phpmyadmin/config.inc.php

Add the below Line at the end of config.inc.php

$i++;
$cfg['Servers'][$i]['host']          = 'ENDPOINT_HERE';
$cfg['Servers'][$i]['port']          = '3306';
$cfg['Servers'][$i]['socket']        = '';
$cfg['Servers'][$i]['connect_type']  = 'tcp';
$cfg['Servers'][$i]['extension']     = 'mysql';
$cfg['Servers'][$i]['compress']      = FALSE;
$cfg['Servers'][$i]['auth_type']     = 'config';
$cfg['Servers'][$i]['user']          = 'root'
$cfg['Servers'][$i]['password']      = 'PASSWORD_HERE';

Replace “ENDPOINT_HERE” and “PASSWORD_HERE” with your credentials.

For Endpoint : Go To databases on RDS. Click on your db instance and you will find your DB Endpoints in Connectivity & security.

For Password : Use your db password that you have used in Step 7 ( Configure a DB instance name and credentials settings. ) while creating a Database Instance .

Note : You can use your credentials with any software that connects to your database instance . i.e. DataGrip , MySQL Workbench ,etc .

References

1. https://aws.amazon.com/getting-started/hands-on/create-mysql-db/
2. https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_MySQL.html