Connecting MySQL to AWS RDS for Data Visualization with QuickSight: A Step-by-Step Tutorial

Introduction:

Hey, fellow data explorers! 🚀 Ever wondered how to seamlessly transition from your local SQL playground to the cloud for dazzling visualizations? Join me in this quick read where I spill the details of my hands-on experience of connecting MySQL workbench to AWS Relational Database Service to create dashboards using AWS Quicksight.

So Let’s light up your data together! 💡🔍

The figure below is the Workflow I have followed and is performed within the free tier limits

So Let's start our tutorial,

Step 1: Sign in to the AWS Management Console.

Step 2: Navigate to Amazon RDS.

Step 3: Launch your AWS RDS Database by clicking on the ‘Create database’ button.

Step 4: Set the database creation method as Standard Create and then choose the Database Engine -> ‘MySQL’

Step 5: Under the templates section select the ‘Free tier’

Step 6: In settings, specify your desired DB instance identifier, then under credential settings type the Master username and Master password.

Don’t misplace those master credentials; losing them is like forgetting the secret handshake to the MySQL Workbench party! 🤫🔑

Step 7: In the Instance configuration set the instance type to:

Step 8: In the storage section set:

as for the demo purpose gp2 storage type is enough.

Step 9: Keep the default selections for the Compute resource, VPC, and the subnet group.

Most important is to enable Public access as we need this setting to connect to our MySQL workbench.

Step 10: Keep the default settings for the Database authentication and Additional configuration and then finally click on Create database.

It’ll take around 4–5 minutes for the database to reach the Active state.

So here is our database

Now we will configure the security group associated with the RDS instance and add an inbound rule. The reason behind the updation of the inbound rule is that it allows incoming network traffic on a specific port (3306: for MySQL), enabling external applications like MySQL Workbench to establish a connection with the RDS database.

Step 11: Click on the link visible under the VPC security groups.

Step 12: Click on the checkbox of the security group -> Inbound Rules and then select :

Step 13: Then select Add rule wherein you select Name as ‘All traffic’, and Anywhere on IPv4 under the Security group.

Step 14: Once you add the Rule, save the rule. Then on the MySQL workbench set up a new connection and provide the necessary fields shown in the figure below.

Give your database a high-five with a connection test — gotta make sure they’re on speaking terms! 🤖✋

Step 15: Time to play database architect — create a schema and add tables 🎨💻 . After setting up your schema and tables, take a backstage pass with AWS CloudWatch to spy on your database’s data moves! 🕵️‍♂️📊

Now we have our data ready let goo dashboarding 🚀📊,

Step 16: On the Console, navigate to AWS QuickSight

Here we opt for the datasets option:

Then select ‘New dataset’ and select the RDS option.

Now fill in the desired Data source name, and the Instance ID automatically detects the database instance inside the RDS irrespective of the Region. Specify your Database name(or Schema name in the MySQL workbench) and finally, specify your RDS instance password and username.

Time to launch those dashboards, but first, a quick connection check — gotta make sure they’re not shy! 🚀🔍

Step 17: Once the connection is validated click on Add data, and then select your preferred table.

Step 18: If you want to join multiple tables, first select the preview button and then add a dataset from any data source, then select anywhere on the 2 red circles (They are join clauses). Then specify the columns where you want to perform the join and the type of join you wish to perform.

After you get your desired filtered data, click on the publish and visualize button on the top-right corner of the console.

Step 19: You’ve landed on the Analysis page — the canvas for your dashboard! 🎨✨ Now, go ahead and craft your visual story. This marks the end of our tutorial journey. Happy dashboarding! 🚀📊

Ta-da! Here’s my demo dashboard below! 📊🎉