Connecting to Amazon RDS MySQL Database using MySQL Workbench

In this article, I will explain how to connect Amazon RDS MySQL Database using MySQL Workbench.

What is Amazon RDS(Relational Database Service)?

• Amazon Relational Database Service (Amazon RDS) is a managed SQL database service provided by Amazon Web Services (AWS).
• It supports an array of database engines to store and organize data and helps with database management tasks, such as migration, backup, recovery and patching.
• It is a web service that makes it easier to set up, operate, and scale a relational database in the cloud.
• It provides cost-efficient, resizable capacity for an industry-standard relational database and manages common database administration tasks.

Why do you want a managed relational database service?

• One of the advantages of developers using Amazon RDS instead of managing their own databases is that it reduces or eliminates their administrative responsibilities.
• RDS also manages replication. If developers need high availability in a database, the multi-availability zone option in RDS can be used. With that option, a replica database is kept in sync with the primary database in case of failure.
• With RDS, AWS customers don’t need to install, configure and manage popular relational database systems like Oracle, Microsoft SQL Server, PostgreSQL, MariaDB or MySQL.

Steps to Connect RDS MySQL Database using MySQL Workbench:

Step1: create a DB instance in RDS using Lambda in AWS.

import json
import boto3

def lambda_handler(event, context):

client = boto3.client(‘rds’)
response = client.create_db_instance(
DBName=’TESTDB’,
DBInstanceIdentifier=’test’,
AllocatedStorage=20,
DBInstanceClass=’db.t2.micro’,
Engine=’mysql’,
MasterUsername=’ChanduPriya’,
MasterUserPassword=’password’

)
print(response)
return response

• DB instance is created with the name you specified in the code (“test”), you can go to RDS service and see the instance created.

• Click on the DB instance you have created (test) and copy the Endpoint in Connectivity and security, you need to use it in MySQL connection(Host name).

Step 2: Connecting RDS MySQL Database using MySQL Workbench.

• Open MySQL Workbench and click on (+) near MySQL connections to create new connection for RDS and enter Connection name, Host name (i.e endpoint), Username and then click on OK button as shown.

• You will be redirected to prompt as shown, here you need to provide password of your DB instance and then click on OK button.

• You have successfully made the MySQL connection. You can see the Connection name RDS_DB_INSTANCE in the MySQL Home as shown.

• By clicking on the connection, you can see the Database Name you have created as shown.

• Now you can perform the operations whatever you want in MySQL Workbench.