MySQL Workbench is a desktop application used to enhance the visibility inside relational database servers like MySQL using a nice graphical user interface. It also provides a query tool to perform SQL queries to the database server. This guide will discuss the following steps to connect to the AWS RDS instance using the MySQL Workbench from a local machine.
- Install MySQL Workbench on a local machine
- Create AWS RDS instance
- Connect to the AWS RDS instance using MySQL Workbench
Install MySQL Workbench on local machine
The first step to connect to the AWS RDS is to install MySQL Workbench on a local machine. Update the local repositories on Ubuntu and then install the MySQL Workbench using the following commands.
ubuntu@ubuntu:~$ sudo apt install mysql-workbench -y
After installing the MySQL Workbench, check its version to verify the installation.
Create AWS RDS instance
To connect to the AWS RDS instance using MySQL Workbench, first launch a new AWS RDS instance with MySQL engine. First, log into the AWS management console and select the region in which you want to deploy an RDS instance. For this article, we will deploy an RDS instance in the us-east-1 (N. Virginia) region.
Search for RDS from the management console.
From the panel on the left side, click on the Databases.
It will display all the database instances deployed there. From the top right corner, click on the Create database button to create an RDS instance.
Select the Standard create as database creation method and MySQL as Engine type.
From the templates, select the Free tier.
Enter the database name and credentials under the Settings section.
Under the Connectivity section, select the VPC you want to deploy the RDS instance in and make the RDS instance publicly available as we will access the RDS from our local machine.
After selecting the VPC, now select the VPC security group and the availability zone.
Leave the rest of the configuration as default and click on the Create database button at the end of the page.
To get the hostname of the RDS instance, go to the databases from the left side panel.
Click on the newly created database, and it will show all the details of the database there.
From the connectivity & security tab, copy the AWS RDS instance endpoint and the port as it will be used to make the connection with the RDS instance from the MySQL Workbench.
After creating the RDS instance, now configure the security group attached to the RDS to allow connection from the internet. A security group is a virtual firewall to allow or block traffic to the RDS instance. By default, the security groups block connections from the internet. From the management console, search for the EC2.
From the panel on the left side, click on the Security Groups under the Network & Security section. It will display all the security groups available in the region.
Click on the security group attached with the MySQL RDS instance, CustomSG, for this demo.
Click on the Edit inbound rules button to add an inbound rule to the security group from the Inbound Rules.
Add a new rule to allow traffic from port 3306 as, by default, the MySQL server runs on port 3306. After adding the rule, click on save rules to save the security group rules.
Connect to AWS RDS using MySQL Workbench
So far, we have installed MySQL Workbench on the local system and launched a MySQL RDS instance on AWS. Now it is time to connect to the MySQL RDS instance using the MySQL Workbench. Open the MySQL Workbench by running the following command.
It will open the MySQL Workbench. Click on the (+) symbol, and it will ask for the database connection parameters.
Enter all the required connection parameters of the AWS MySQL database server and click on the Test Connection button to connect to the database server.
Now it will ask for the password of the RDS instance. Enter the password for the RDS instance and click on the OK button to test the connection.
After entering the password, it will inform whether the connection was successful or not.
Now from the new connection setup, click on the OK button instead of Test Connection to add AWS RDS instance connection settings to the MySQL Workbench.
It will add a MySQL connection on the dashboard of the MySQL Workbench.
To connect to the AWS RDS instance, double click on the AWS RDS connection, and it will start connecting to the RDS instance.
After sometime, it will ask for the password of the AWS RDS instance. Enter the password of the RDS instance and click on the OK button to connect to the instance.
After providing the password, it will connect to the AWS RDS instance and open a nice graphical user interface for the RDS instance on the MySQL Workbench.
In order to create a new schema using MySQL Workbench, click on the new schema button on the menu bar.
It will open a new tab asking for a schema name to be created.
Enter the schema name and click on the Apply button at the bottom right corner of the tab. It will open a new window with the SQL query to create the new schema asking for your confirmation if you want to run the query.
Review the SQL query and click on the Apply button at the bottom right corner of the window to create the schema. Similarly, different SQL queries can also be performed by using a graphical user interface on MySQL Workbench.
Conclusion
MySQL Workbench is a desktop application that provides a nice graphical user interface of the relational database server. It is difficult for beginners to use the command-line interface to perform queries to the database server. MySQL Workbench provides a nice view of the database server and makes it easier to perform SQL queries. This blog describes different steps to connect to the AWS RDS server using the MySQL Workbench.
