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).
One thought on “Testing MySQL Database Service without VPN”