Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?

Tutorial - Using MySQL Router with MySQL Database Service

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 to MySQL.

Note: for security reasons, it is not recommended to expose your database to be accessible by any host from the internet. Check the OCI Networking best practices for more information.

You will be guided through the following steps:

  1. Install MySQL Router in your OCI Compute (it will act as a proxy to the database)
  2. Add a Security Rule to open MySQL Router R/W port to your App Server

Assuming you already have an OCI Compute based on Oracle Linux 7 and a MySQL Database Service DB System.

Step 1 - Install and Configure MySQL Router in the OCI Compute instance

  1. SSH into the OCI Compute where MySQL Router will be installed

  2. To install MySQL Router, run:

sudo yum -y install https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
sudo yum -y install mysql-router
  1. Configure MySQL Router to redirect the traffic. For example, assuming the MySQL endpoint IP is 10.0.0.6, edit and add to the configuration file /etc/mysqlrouter/mysqlrouter.conf:
[routing:redirect_classic]
bind_address = 0.0.0.0:3306
destinations = 10.0.0.6:3306
routing_strategy=first-available
 
[routing:redirect_xprotocol]
bind_address = 0.0.0.0:33060
destinations = 10.0.0.6:33060
protocol = x
routing_strategy=first-available
  1. Start MySQL Router and check if the service is active (running):
$ sudo systemctl start mysqlrouter.service
$ sudo systemctl status mysqlrouter.service
  1. Automatically start MySQL Router when the Compute instance reboots
$ sudo systemctl enable mysqlrouter.service
  1. Add the firewalld rules. Run:
$ sudo firewall-cmd --permanent --add-port=3306/tcp
$ sudo firewall-cmd --permanent --add-port=33060/tcp
$ sudo firewall-cmd --reload
$ sudo firewall-cmd --list-all

Step 2 - Configure the Public Subnet Security Lists to allow traffic to ports 3306 and 33060

In the OCI web console, access 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:

Stateless: No Source: 0.0.0.0/0 IP Protocol: TCP Source Port Range: All Destination Port Range: 3306 Description: MySQL Classic Protocol

Stateless: No Source: 0.0.0.0/0 IP Protocol: TCP Source Port Range: All Destination Port Range: 33060 Description: MySQL X-Protocol

Note: It is recommended to be more restrictive about with the IP addresses that can reach your instance. Replace the source CIDR 0.0.0.0/0 with more restrict ranges.

Conclusion

That is it. Now you can test the connection directly to MySQL from your machine.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.