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.