Connect to a private MySQL Database Service Instance via PublicIP
Use MySQL Router to securely connect to your private database via Public IP
Oracle MySQL Database Service is a fully managed database service that lets developers quickly develop and deploy secure, cloud native applications using the world’s most popular open source database. MySQL Database Service is the only MySQL cloud service with an integrated, high-performance analytics engine — HeatWave — that enables customers to run sophisticated analytics directly against their operational MySQL databases, eliminating the need for complex, time-consuming, and expensive data movement and integration with a separate analytics database. Optimized for and exclusively available in Oracle Cloud Infrastructure (OCI), MySQL Database Service is 100% built, managed, and supported by the Oracle Cloud Infrastructure and MySQL engineering teams.
On OCI, for security reasons, you can only obtain a Private IP but sometimes you need to connect to the database on a Public IP. Whether it’s for testing or for just a small period of time, it can be useful to have this possibility.
In this tutorial, you will learn how to install and configure MySQL Router to redirect connections to a MySQL Database Service DB System on Oracle Cloud Infrastructure. You can use this to create a Public Endpoint for MySQL.
Note: for security reasons, it is not recommended to expose your database to be accessible by any host from the internet. For more information, check the OCI Networking best practices.
What are we going to do?
Prerequisites: You will of course need an Oracle Cloud tenancy. Sign up here if you don’t have one! You will get $300 worth of free credits and you’ll forever have the Always Free tier for all you projects!
In this tutorial I won’t go into the details on how to create a MySQL Database Service instance, you can follow this amazing tutorial to get started.
Today, we are going to create Compute Instance in public subnet (with an assigned public IP. On this machine, we will install MySQL Router Package and we will configure it to connect to our DB System on the Private Subnet.
The final architecture will be something that looks pretty much like this:
Step 1: Create a Linux instance on Oracle Cloud and connect via ssh
Note: the shape is irrelevant at this point, you can easily choose the Always Free instance for this tutorial. If you don’t know how to do it check out my article in which I show it step by step.
Step 2: Install MySQL Router package
I am using an Autonomous Linux (CentOS based) image but you can choose other distributions as well (here you can find a guide on how to install MySQL Router on a different OS)
Step 3: Configure MySQL Router to redirect the traffic
We now need to redirect the traffic to MySQL Database Service Instance. In this case, the instance has 10.0.0.3 as IP address. Modify this with your Private IP.
For example, assuming the MySQL endpoint IP is ‘10.0.0.6’, edit and add to the configuration file ‘/etc/mysqlrouter/mysqlrouter.conf’:
sudo nano /etc/mysqlrouter/mysqlrouter.conf
and then at the end add the following code:
MySQL Router now knows where to redirect the traffic. It’s time we start the service and connect!
Step 4: Open firewall ports and start MySQL Router
Autonomous Linux enables by default the firewall, so if we need to open some ports in order to be able to connect to the MySQL Database
Note: if you’re using a different system you may need to modify the script above
Let’s start the service! If all is good, you see a green “Running” text as status like in the image below.
Step 5: Open Security Lists on Oracle Cloud
All that is left is to open the ports on Oracle Cloud. To do this, from the hamburger menu go to Networking > Virtual Cloud Networks > click on the VCN name > click on the Public Subnet name > click on the Security list name > add the 2 Ingress Rules shown below.
Note: It is recommended to be more restrictive about the IP addresses that can reach your instance. Replace the source CIDR `0.0.0.0/0` with more strict ranges.
You should have now something like this
Connect!
From a machine in which you have MySQL Shell installed you can run the following command.
mysqlsh user@MYSQL_ROUTER_MACHINE_PUBLIC_IP
That is it! Now you can test the connection directly to MySQL from your machine.
Next Step
This is just the tip of the iceberg! Join our events to learn how to use MySQL Database Service and discover how HeatWave brings analytics to the next level! HeatWave is a new, in-memory query accelerator for MySQL Database Service available in Oracle Cloud.
Conclusions
In this article, we saw how to use MySQL Router package to connect to a MySQL Database without a PublicIP address. This may be very useful while in many testing cases without the need to build a complex architecture.
Thank you! — I want to thanks my friend and colleague Ali Hassane who deserves all the credits for this tutorial.
To learn about the Always Free cloud resources check out here!
Want more? Join the unofficial Discord community!
Note — Free Tier users may experience changes to services included with their account.
I am Roberto Di Bella, Data Scientist Advocate at Oracle. Helping data scientists, developers and companies be successful on Oracle Cloud.
Feel free to connect on LinkedIn.
