Retrieving Automated Google Cloud SQL Backups

Question

My team has a requirement that we be able to retrieve a backup of our database (hosted on Google Cloud SQL) and restore that database to a locally hosted instance of MySQL.

I know that Google Cloud SQL has the ability to schedule backups, but these don't appear to be available to download anywhere.

I also know that we are able to "export" our database to Google Cloud Storage, but we'd like to be able to schedule the "export".

The end goal here is to execute the following steps in some sort of an admin script:

1. Automatically backup our database that is hosted on Google Cloud SQL.
2. Download the backup to a local (not cloud) server.
3. Restore backup to a local instance of MySQL.

Any ideas?

Answer 1

gcloud sdk commands now provide import/export functionality:

gcloud sql export sql <DATABASE_INSTANCE> \
    gs://<CLOUD_STORAGE_BUCKET>/cloudsql/export.sql.gz \
    --database <DATABASE_NAME>

This export can be downloaded using gsutil. It can also be imported using mysqlimport

Answer 2

That's the problem I've encountered and my solution was:

1. Go to IAM Service Accounts Management
2. Create a new Service account (I called it sql-backuper), download access key for it in JSON
3. Grant Viewer, Storage Object Creator roles to it on main IAM page (currently GCloud doesn't have a separate read-only role for SQL)
4. Set it up on the machine that will do backups: gcloud auth activate-service-account [email protected] --key-file /home/backuper/gcloud-service-account.json (gcloud auth documentation)
5. Create a new bucket at GCloud Storage Browser
6. Now on your backup machine you can run: gcloud sql instances export [sql-instance-name] gs://[bucket-name]/[file-name].gz --database [your-db-name] (gcloud sql documentation) and gsutil cp gs://[bucket-name]/[file-name].gz [local-file-name].gz (gsutil cp documentation)
7. You've got a local DB copy which you can now use as you want