1

Currently we are running multiple MySQL servers in an off-site datacenter, these servers receive connections from multiple webservers, as well as connections from 20 to 30 users from their homes, with local installed apps.

The database servers are running in their own private network and this network has a single external IP address, with a single firewall. All servers are running on the same MySQL port, and by port-mapping the firewall routes the correct port to the right server, a connection to external-ip:2001 goes to database-server 1, connecting to external-ip:2002 makes a connection to database-server 2.

Now we want to migrate to the Google Cloud Platform. I have been playing with different settings, but I cant find a way to set it up like we have now. I can see firewall settings for each individual SQL-instance, but that is not the way I would like to go. With 8 different servers, some with multiple replica's, it would soon become a nightmare to manage all firewall rules. As mentioned, we have over 30 users, and not all have fully static IP addresses.

What would be the best way to configure this setup?

1
  • I am not aware of the details of your configuration, but in order to obtain the most similar configuration in Google Cloud Platform, you may use your MySQL servers inside Compute Engine instances.
    – Javier A
    Jun 30 at 9:36
0

Cloud SQL is a managed DB service and accessing it may be different from how you usually do on an on-prem server. There are two ways for you to access your Cloud SQL instances. If you're connecting from external application, either:

  1. Authorize each client IP address per instance.
  2. Use Cloud SQL Proxy.

Take note that there's nothing here that mentions configuring a Firewall. The first option doesn't sound good because you have many users and their IP addresses aren't totally static. These problems are solved by using Cloud SQL proxy. By using the second approach, you'll have to configure the proxy on the user side in order to connect to the instance.

The Cloud SQL Proxy works by having a local client, called the proxy, running in the local environment. Your application communicates with the proxy with the standard database protocol used by your database. The proxy uses a secure tunnel to communicate with its companion process running on the server.

While the proxy can listen on any port, it only creates outgoing connections to your Cloud SQL instance on port 3307. If you have an outbound firewall policy, make sure it allows connections to port 3307 on your Cloud SQL instance's IP.


To understand how to use the proxy, refer to this doc: https://cloud.google.com/sql/docs/mysql/connect-admin-proxy

1
  • Unfortunatly is is not possible for me to use the Cloud SQL Proxy. I cant be installing this on all client machines. Currently I am leaning towards using ProxySQL.
    – Nhz
    Feb 15 at 10:15
0

There is a way in which you can get this desired architecture.

I will list out the steps to be able to do it.

Basically what you want is port forwarding from one IP, which is publicly accessible, to multiple IPs, which are not publicly available, through which you can connect to the Cloud SQL backend server.

To do that -

Create two Cloud SQL instances of MySQL type and configure them to use Private IP addresses only. To know how to configure a private IP please follow this document.

Create one Compute Engine instance with a Ubuntu 16.04 LTS image and enable IP forwarding while creating it. You can follow this document to know how to do this.

SSH into the Compute Engine instance

gcloud compute ssh FIRST_COMPUTE_ENGINE_INSTANCE_NAME

Run the following command to edit the /etc/sysctl.conf file.

sudo nano /etc/sysctl.conf

Now uncomment the line to enable IP forwarding

#uncomment the below line
net.ipv4.ip_forward=1

Now run the following commands, which will set the ip_forward to 1 if not set.

sudo su
echo 1 >| /proc/sys/net/ipv4/ip_forward
exit

Now we have to configure the IP tables. Run the following commands

sudo iptables -F

The above command will flush or delete all the IP rules which are currently existing.

sudo iptables -F -t nat

The above command will flush or delete all the IP rules of type Network Address Translation(NAT).

 sudo iptables -t nat -A PREROUTING -p tcp -d INTERNAL_IP_OF_COMPUTE_ENGINE --d port 4479 -j DNAT --to-destination PRIVATE_IP_OF_FIRST_SQL_INSTANCE:3306

 sudo iptables -t nat -A PREROUTING -p tcp -d INTERNAL_IP_OF_COMPUTE_ENGINE --d port 4480 -j DNAT --to-destination PRIVATE_IP_OF_SECOND_SQL_INSTANCE:3306

The above command routes the request from Compute Engine to the Cloud SQL instance. As I have two SQL instances I have to run the command twice with different ports i.e. 4479 and 4480. You can run the above command as many times according to the number of your Cloud SQL instances we want to connect specifying different ports. Please note that we have to add firewall rules to allow connections on these port numbers in our Virtual Private Network(VPC). The 3306 port is in accordance with the MySQL database as the Cloud SQL instance is MySQL type.

sudo iptables -t nat  -A POSTROUTING -j MASQUERADE

The above command will cover up/mask the IPs which are trying to connect to the database.

sudo iptables -L -t nat

The above command will list the iptables of type NAT.

The above steps will configure to connect to multiple Cloud SQL instances through a single IP, which will be accessible to the public. Now to test if it actually works you can create a test-instance in Compute Engine and connect to the database.

For more details you can follow this article.

Alternatively, you may explore this document to know about protocol forwarding in a Compute Engine instance which may help your use case.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.