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 ?
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:
gcloud sql backups create --async --instance [SOURCE_INSTANCE_NAME]
gcloud sql backups list --instance [SOURCE_INSTANCE_NAME]
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.
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.