Cloud SQL is a service of GCP that allows customers to run mySQL or Postgres databases.
For customers who need a managed database to store application data Cloud SQL provides this, and is fully integrated with your nine Managed GKE cluster.
To order a Cloud SQL database please contact email@example.com
Once you have ordered a Cloud SQL database you can find the address and credentials for access on runway. You can use these to configure your application as normal.
Backup and Maintenance Window
Backups are stored in the same region the cluster is in. For example, if the cluster is in Zürich, the backups stay in Zürich.
The backups are also spread across zones (i.e. a, b and c) across europe-west6 (Zürich).
Your Cloud SQL has the following backup schedule:
- Backup window starts at 00:00 UTC every day.
- Maintenance window starts at 02:00 UTC every monday.
- Restores are currently available by contacting firstname.lastname@example.org
- A restore can be provided either by directly restoring your backup over the current version, or by providing the data under a new db name, allowing you to manually restore necessary tables/rows.
- automatic created backups will be stored for 7 days
Access your CloudSQL instance
As all Cloud SQL instances will have only private IPs assigned, you can not connect directly from your local computer to an instance out of the box (only access from the nine Managed GKE cluster is possible). There are a few ways to access your instances externally though:
- Spawn a webapplication in your nine Managed GKE cluster to manage your databases (e.g. phpmyadmin, pgadmin, etc).
- We can create a VPN connection from your location to the google cloud VPC. This allows for direct access to your Cloud SQL instance from your local computer. Please be aware that you will need VPN hardware available.
- Start a proxy pod in your nine Managed GKE cluster and use it with
kubectl port-forwardto access your Cloud SQL instance.
- We can add a public IPv4 to your Cloud SQL instance and also restrict access from certain networks if required. Please contact us if you want this setup on your instance.
Proxy pod example
With kubectl’s local port forwarding feature (which encrypts traffic by default) and a reverse proxy/bouncer deployed in your MGKE cluster you can access your Cloud SQL instance. Here is a sample of a Kubernetes manifest that contains a deployment and a configuration map resource for the reverse proxy which connects to 2 Cloud SQL mysql DBMS. This solution is based on Gobetween, but other TCP reverse proxies can also be used. Please replace the values in the “discovery” section of the configuration. TCP ports within the bind section are just a mere suggestion, just make sure to use port numbers higher than 1024. Container ports should be the same. The private IP addresses of your DB instances can be found on Runway. If you are running a Cloud SQL postgresql instance, then you need to edit the target ports in the “discovery” section.
- Create a file manifest.yaml with the following content
apiVersion: v1 kind: ConfigMap metadata: name: sql-connect-cm data: config.toml: |- [servers.db-prod] bind = "0.0.0.0:9991" protocol = "tcp" [servers.db-prod.discovery] kind = "static" static_list = [ "<cloudsql_private_ip_db1>:3306", ] [servers.db-staging] bind = "0.0.0.0:9992" protocol = "tcp" [servers.db-staging.discovery] kind = "static" static_list = [ "<cloudsql_private_ip_db2>:3306", ] --- apiVersion: apps/v1 kind: Deployment metadata: name: sql-connect labels: app: sql-connect spec: replicas: 1 selector: matchLabels: app: sql-connect template: metadata: labels: app: sql-connect spec: containers: - name: sql-connect ports: - containerPort: 9991 - containerPort: 9992 volumeMounts: - name: config-volume mountPath: /etc/gobetween/conf image: yyyar/gobetween volumes: - name: config-volume configMap: name: sql-connect-cm items: - key: config.toml path: gobetween.toml --- apiVersion: v1 kind: Service metadata: name: access-db-prod spec: selector: app: sql-connect ports: - protocol: TCP port: 3306 targetPort: 9991 --- apiVersion: v1 kind: Service metadata: name: access-db-staging spec: selector: app: sql-connect ports: - protocol: TCP port: 3306 targetPort: 9992
- Apply the manifest
kubectl create ns <namespace> kubectl create -f manifest.yaml -n <namespace>
- Create a local port forward
# prod DB: kubectl port-forward -n <namespace> svc/access-db-prod 3306 & # staging DB: kubectl port-forward -n <namespace> svc/access-db-staging 3307:3306 &
Remember, the port-forwarding is not persistent. It must be recreated if the machine is rebooted.
- Access prod DBMS
mysql -u <user> --host 127.0.0.1 -p
- Access staging DBMS
mysql -u <user> --port 3307 --host 127.0.0.1 -p