How to duplicate Google Cloud MySQL database for development purposes?

Question

I'm a bit stuck, I switched recently to Google Cloud MySQL and I would like to clone one of my database (not instance) for an external development environment for freelancers.

The idea is to clone/duplicate existing live database, then scrap sensitive datas (emails, etc...). I know I need to use "gcloud" command line function but I don't really know to do it.

Can someone help me ?

Answer 1

The easiest way to do this would be to restore a backup made on the first instance to a new instance. I recommend you review the Cloud SQL documentation around backups

Example steps:

• Create an on demand backup

gcloud sql backups create --async --instance [SOURCE_INSTANCE_NAME]

• You can see a list of backup ids for the source instance with this:

gcloud sql backups list --instance [SOURCE_INSTANCE_NAME]

• Restore to the new instance. After preparing the new instance (creating, ensuring it has no replicas, etc).

gcloud sql backups restore [BACKUP_ID] --restore-instance=[TARGET_INSTANCE_NAME] \
                                       --backup-instance=[SOURCE_INSTANCE_NAME]

You can also do all of the above through the console.

• Once the restore is complete, you can remove the backup. The easiest way to do this is through the console, but it can be done via the REST API if necessary.

Of course, there isn't a gcloud command to do the data cleanup you describe, you would need to do that yourself, based on your own data and anonymization requirements. Doing good anonymization can be tricky unless you have a very limited amount of sensitive data.

---

If instead you just want to export a single database, then you can use the export and import functionality. This is subject to some limitations, for example, triggers, stored procedures, and possibly views, etc, will need to be manually recreated.

Full instructions for export, but here's a quick summary.

You will need a cloud storage bucket to hold the output, and the service account for the database will need to be a writer on that bucket. Once that is in place:

gcloud sql export sql [INSTANCE_NAME] gs://[BUCKET_NAME]/[DUMP_FILE_NAME] \
                                      --database=[DATABASE_NAME]

You can then either download the file and use it on a local database, or import it into a new instance, as so:

gcloud sql import sql [INSTANCE_NAME] gs://[BUCKET_NAME]/[DUMP_FILE_NAME] \
                                      --database=[DATABASE_NAME]

Obviously, sanitizing the data is still up to you.