Testing MySQL Database Service without VPN

Please note that this is not recommended for any production purpose, don’t expose MySQL traffic to the Internet !

MySQL Database Service is now available, take a look at this nice introduction video:

It is usually a very bad idea to expose a database on a public IP (MySQL or any other database 😉 ). MDS doesn’t allow you to implement such bad security practice.

The best way to use your MySQL instance is to use a VPN as I explained in this article.

However, this can be restrictive for a simple test. In this post I will illustrate how you can setup a MDS instance and test it without having to setup any kind of VPN. Of course I recommend you to use such practice only for testing purpose.

MDS Instance

The first thing to do, is to provision a MySQL Database Service instance. If you never used OCI, you will need to create a VCN and subnets.

VCN

You can use the wizard which is a easy way to set it up. I will show you how to do it manually:

Choose a name and a CIDR block (usually 10.0.0.0/16):

Subnets

We also need to create 2 subnets, the private and the public one:

The private subnet will be on 10.0.1.0/24 range and the public one on 10.0.0.0/24 range:

We should then have something similar to this:

Internet Gateway

We also need an access to the servers in our public subnet to contact The Internet. Let’s create the Internet Gateway:

And we add it to the routing table:

MySQL Instance Provisioning

We are ready to create the MDS instance:

We have only one subnet possibility: the private one !

Click next, define the backup policy and we are done !

We can see it’s IP in the public subnet (10.0.1.0/24):

Compute Instance

As we don’t want to setup a VPN to access our MDS instance, we will use MySQL Router on a compute instance.

We first create a compute instance in the public subnet:

Don’t forget to add a ssh public key and create the instance:

As soon as the instance is provisioned, you will find its public IP:

MySQL Router

We can now connect to our compute instance using ssh:

$ ssh -i ~/.ssh/id_rsa_oci opc@130.61.147.176

Installation

We install MySQL Community’s Yum Repository to install MySQL Router:

[opc@myrouter ~]$ sudo rpm -ivh \ 
      https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
[opc@myrouter ~]# sudo yum -y install mysql-router

Configuration

We need to edit MySQL Router’s configuration file.

In /etc/mysqlrouter/mysqlrouter.conf, we add the following lines:

[routing:primary]
bind_address = 0.0.0.0
bind_port = 3306
destinations = 10.0.1.3:3306
routing_strategy = first-available

[routing:primary_x]
bind_address = 0.0.0.0
bind_port = 33060
destinations = 10.0.1.3:33060
routing_strategy = first-available
protocol = x

And we start it:

[opc@myrouter ~]$ sudo systemctl start mysqlrouter.service

Firewall

We are almost ready ! We still have to configure the local firewall on the compute instance and add a rule on OCI.

On the compute instance, we run these commands:

[opc@myrouter ~]$ sudo firewall-cmd --zone=public --permanent --add-port=3306/tcp
success
[opc@myrouter ~]$ sudo firewall-cmd --zone=public --permanent --add-port=33060/tcp
success
[opc@myrouter ~]$ sudo firewall-cmd --reload
success

On OCI’s interface we add the following rules in the Ingress Rules.

First we add the rule to allow MySQL traffic between our two subnets:

Then we need to accept MySQL Traffic to MySQL Router for our public IP. You can use a service like whatismyip to discover it (add the ip with /32 at the end):

It’s possible to allow connections from the all Internet but that would be very dumb to allow that.

Connecting

We can connect to our MDS instance from the Internet using MySQL Router’s public IP:

And of course we can also use MySQL X protocol to use MDS as a JSON Document Store !

Conclusion

It’s not recommended to publicly expose your MDS instance but for testing purpose, you can use MySQL Router on a Compute Instance in OCI.

For more professional architecture, a VPN is highly recommended and OCI provides many options from OpenVPN to IPSEC (including LibreSwan).