Connecting MySQL with SSL on AWS Virtual Machine Using Arctype

This article focuses on connecting MySQL server installed on an AWS virtual instance to Arctype via a secure socket layer (SSL). You can also make this connection via a secure shell (SSH) protocol, but it would have to be an interactive session where you are required to be on a computer and typing commands. Connecting via the SSL protocol gives you more options of having a secure non-interactive direct connection where your mobile apps, web apps, or APIs can access your database.

In this article, you will:

• Be introduced to MySQL, AWS, SSL, and Arctype
• Learn how to deploy and configure an EC2 instance (virtual machine) on AWS
• Learn how to install and configure a MySQL server on the EC2 instance
• Learn how to install and configure Arctype on your PC
• Learn how to use default SSL certificate files provided by MySQL
• Learn how to connect to MySQL database on Arctype via SSL

Tools we’ll be using

MySQL is an open-source, fully managed relational database management system. It organizes data into one or more tables of related data types. MySQL requires an operating system to implement its relational database in storage systems, manage network access, manage users, and facilitate database testing.

Amazon Web Service (AWS) is one of the leading cloud service providers. They provide cloud-based computing services like Virtual machines, storage, web hosting, and a host of other cloud-based services.

The AWS free tier EC2 t2 micro instance is suitable for this test project. This tier helps you avoid unnecessary charges from AWS. In addition, the EC2 instance will still have enough storage space to host your MySQL server.

Secure Socket Layer (SSL) is the standard technology for keeping internet connections safe and safeguarding sensitive data transmitted between two systems. It prevents unwarranted access by using encryption algorithms to scramble data in transit, thereby preventing hackers or criminals from accessing it while it is moving.

Arctype is a free, fast, and beautiful SQL client which helps you manage your database and visualize your data. In addition, it has a database graphical user interface (GUI) which allows collaborative workflows for developers and teams.

Prerequisites

We’ll need the following for this project:

• EC2 (Windows/Linux) instance on AWS
• MySQL server
• MySQL client (Arctype)
• SSL certificate (you can use the default SSL cert. provided by MySQL or better still use your own SSL cert.)
• An existing MySQL database(MySQL sample database).

How to connect MySQL with SSL on an AWS EC2 Virtual Machine

Step 1: Deploy and configure the EC2 instance on AWS

Deployment of instances on AWS is quite simple, but you need to make sure you're going for the free tier instance - especially when it's for a test project. You definitely want to avoid unexpected withdrawals from your bank account at the end of the month!

I will be using a 2019 windows server instance for this test. For more on how to deploy an EC2 instance on AWS, visit this link.

Step 2: Configure and add your inbound rules on the security group

During the configuration of the security group, make sure the default inbound rule port for all RDP connections to windows instances is 3389. Select ‘My IP’ for the source in order to be assigned an IP.

Most importantly, you need to add another inbound rule for the MySQL server which will have a default port of 3306. Make sure to select MySQL/Aurora as the type of connection. Also, note that the IP for both inbound rules must be the same. Be sure to also download your key from AWS - you’ll need it for the next step.

Step 3: Connect to the instance remotely

After launching the instance, connect to the instance using the key you downloaded to decrypt the password, then download and run the remote desktop file. Or, better still, open a remote desktop on your PC and insert the required credentials to connect.

Step 4: Download, install and configure the MySQL server on the EC2 instance

With the remote desktop open, download the MySQL installer. The installer comes with several MySQL packages, but for the purpose of this test, you should just go for the server-only option. Remember that the MySQL server should be installed on the EC2 instance, not on your local machine.

Enter a current root password of your choice. This password will be required later on Arctype to enable connection to your MySQL server.

Step 5: Optional: Download the SSL certificate bundle from AWS

If you are connecting to a MySQL RDS instance, free SSL certificates can also be downloaded from AWS. Visit this link to download the AWS SSL certificate. We will be pulling the SSL certificates from the MySQL Server desktop installation folder below.

Step 6: Download and install Arctype

Visit the Arctype web page to download the Arctype SQL client, then navigate to your download folder and run the executable file. After installation, you’ll see a list of databases supported by Arctype. Select MySQL since that is the basis of this test.

Step 7: Connect Arctype to the MySQL server via SSL

Use the steps below to successfully connect to MySQL server from the Arctype GUI:

1. Name your connection
2. Input the IP or public DNS of your AWS instance
3. Add the current root password used when configuring the MySQL server
4. Add a database
5. Add the default MySQL key, cert, and CA cert from the MySQL folder created during the installation of the server. You can also use your own SSL certificate if you own one.
6. Test the connection and save

Step 7: Access your database

Once your connection is successful, you can access your database.

If you followed the above steps correctly, you should now be able to access and visualize your database on Arctype.

Conclusion

You are now familiar with AWS, the MySQL server, SSL, and Arctype, and have seen how they all work together. You also learned how to deploy and configure EC2 instances on AWS. You know how to configure inbound security rules, install and configure a MySQL server, and install and connect Arctype to it.

If you want to take your knowledge further, you can check out additional Arctype documentation or join the Arctype Discord to get help and inspiration from hundreds of SQL experts from around the world.