Skip to content

Instantly share code, notes, and snippets.

@alastori

alastori/Tutorial - Connecting to a MySQL Database Service DB System with a public IP address using a Network Load Balancer.md

Last active December 10, 2021 22:53
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save alastori/63a9f022ee3e899dff6eb94a97cca1ee to your computer and use it in GitHub Desktop.
Save alastori/63a9f022ee3e899dff6eb94a97cca1ee to your computer and use it in GitHub Desktop.
Download ZIP
How to configure an Oracle Cloud Infrastructure Network Load Balancer with a MySQL Database Service DB System backend. You can use this to create a Public Endpoint to a single read-write MySQL DB System or multiple read-only MySQL DB Systems.
Raw
Tutorial - Connecting to a MySQL Database Service DB System with a public IP address using a Network Load Balancer.md

Tutorial - Connecting to a MySQL Database Service DB System with a public IP address using a Network Load Balancer

This tutorial will teach you how to configure an Oracle Cloud Infrastructure Network Load Balancer with a MySQL Database Service DB System backend. You can use this to create a public endpoint to a single MySQL DB System backend.

Note: It is a high security risk to expose your database to be accessible by hosts from the internet. Consider using the Bastion Service or a VPN. In cases where access via public IP address is the only alternative, ensure you restrict the authorized external IP addresses to the minimum and use in-transit encryption. Check the OCI Networking best practices for more information.

You will be guided through the following steps:

  1. Create a Network Load Balancer to redirect MySQL traffic.
  2. Configure the Load Balancer backend to route traffic to one MySQL DB System.
  3. Configure the Subnet Security Lists to allow traffic to MySQL port.
  4. Connect to MySQL via the public IP.

Assuming you already have created the VCN and MySQL Database Service DB System.

Step 1 - Create a Network Load Balancer using the Console

  1. In the OCI navigation menu, go to Networking, Load Balancers, and select the Compartment to place the new Load Balancer.

  2. Click Create Load Balancer, select Network Load Balancer, click Create Load Balancer.

  3. Give the Load Balancer a name, select Public IP and choose if the IP address will be ephemeral or reserved. If you don't want the IP address to change, create a Reserved Public IP.

  4. Select the same VCN as your MySQL DB System. You can confirm the MySQL VCN in the MySQL DB System details page, Networking. The load balancer subnet must be public and doesn't have to be the same as the MySQL DB System. The traffic is routed by default from the public subnet to the private subnet if you've used the VCN Wizard to create the VCN. Click Next.

  5. Give a listener name, select TCP and specify the MySQL port. If you haven't changed, the default MySQL Classic Protocol port is 3306. You can later optionally create an additional listener to another port like 33060 for the MySQL X-protocol. Click next.

  6. Give a backend set name, don't select any backend for now, and keep the Preserve Source IP option selected. For the Health Check Policy, the protocol is TCP, and you can keep the default values for the remaining configurations. Click Next.

  7. Confirm your configurations and click Create Network Load Balancer.

Step 2 - Configure the Load Balancer backend to route traffic to one MySQL DB System

Note: Do not add more than one MySQL DB System in the load balancer backend. If you write data to multiple backends, you'll have inconsistent data across the DB Systems. If you need public access to more DB Systems, create one load balancer for each of them.

  1. On the Load Balancer details page, click on the Backend Sets and select the existing backend set automatically created in the step before.

  2. On the Backend Set details page, click on Backends and then Add Backends button.

  3. Select IP Addresses as the Backend Type and enter your MySQL DB System private IP endpoint and port. You can find this information in the MySQL DB System details page, Endpoint, Private IP Address. Click Add Backends.

Step 3 - Configure the Subnet Security Lists to allow traffic to MySQL port

  1. In the OCI navigation menu, go to Networking, Virtual Cloud Networks, click on the VCN name.

  2. Click on the Public Subnet name where is your Load Balancer and then click on the Security List name. Add the Ingress Rule:

Stateless: No Source Type: CIDR Source CIDR: Specify your source IP address or a restricted range. Using 0.0.0.0/0 is not recommended for security reasons. IP Protocol: TCP Source Port Range: All Destination Port Range: Your MySQL port (default 3306). Description: MySQL Classic Protocol

  1. If your MySQL DB System is in a different subnet, also add the same Ingress Rule to the subnet's Security List. You can confirm the subnet name in the MySQL DB System details page, Networking, Subnet.

Step 4 - Connect to MySQL via public IP and optionally add the MySQL X-Protocol port

  1. Check your Load balancer public IP address on the Load Balancer details page, Load Balancer Information, IP Address (Public). The Overall Health and Backend Sets Health should be OK (green).

  2. Test the connection to MySQL using the public IP and port configured. Make sure you are using an encrypted connection. One best practice is to add the "REQUIRE SSL" clause when you create or alter the MySQL users that will access the database externally.

  3. Optionally, add another listener and backend to the MySQL X-Protocol port (default 33060). Ensure you have equivalent Listener, Backend, and Security Rules configured to the X-protocol port.

Conclusion

Now you can connect to your MySQL DB System directly from the internet using the Network Load Balancer public IP. Use this method only when other more secure approaches don't meet your requirements. Ensure you restrict external access to the minimum external sources and use in-transit encryption.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment