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:
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.
We’ll need the following for this project:
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.
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.
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.
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.
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.
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.
Use the steps below to successfully connect to MySQL server from the Arctype GUI:
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.
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.