Skip to main content

Cloud SQL

Cloud SQL is a service of GCP that allows customers to run mySQL or Postgres databases.

Details​

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.

Availability​

To order a Cloud SQL database please contact info@nine.ch

Usage​

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 support@nine.ch
  • 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-forward to 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