How to schedule daily Cloud SQL export to Google Cloud Storage

This will be a quick overview post, perhaps I’ll add more to it when I have time. Basically I was concerned because Google’s Cloud SQL offers backups, but these are tied to the instance and are deleted when the instance is deleted. Therefore, I wanted to add some form of SQL dump to cold storage.

Prerequisites

GCloud CLI (Install and log in)

Quickstart for Debian and Ubuntu | Cloud SDK Documentation | Google Cloud

Enable Cloud SQL Admin API

https://console.developers.google.com/apis/api/sqladmin.googleapis.com/

Steps

Create a Cloud Storage bucket (you can modify to coldline or something else if you need)

gsutil mb -p [your project id] -l asia gs://[any bucket name]

-l is the location, you can leave it as default if you’ve already set your default

I initially tried creating the bucket with the GUI on the Cloud console, but there were some permissions (ownership) issues that I didn’t want to deal with, so it works best if you create from the same (logged-in) command line.

Find your service account email (because each database is created using a new service account — THE BIGGEST GOTCHA EVER)

SA_NAME=$(gcloud sql instances describe [your instance id] --project=[your project id] --format="value(serviceAccountEmailAddress)")

Credit goes to Scott from https://stackoverflow.com/questions/50828098/permissions-for-google-cloud-sql-import-using-service-accounts

Grant write access to the service account

gsutil acl ch -u ${SA_NAME}:W gs://[your bucket name]

This allows the database to run a SQL dump that writes into your bucket.

Create a Cloud Function

Based the script off bllevy’s post https://github.com/googleapis/google-api-nodejs-client/issues/719

index.js

package.json

{
  "name": "export-database",
  "version": "0.0.1",
  "dependencies": {
    "googleapis": "^39.2.0",
    "google-auth-library": "3.1.2"
  }
}

Deploy, then go to the Trigger tab. Copy the URL. (This URL, when accessed with a browser or when it receives a GET request, triggers the function)

Use Cloud Scheduler to schedule a GET request

Google Cloud Platform

Frequency: 0 0 * * * (daily at midnight)

Target: HTTP

URL: [the trigger URL from your Cloud Function]

HTTP method: GET