How do I restore just the databases and not the users from a google cloud sql instance

user_18580170 0
Viewed 31

My company has two projects one is a sandbox for devs and another is production. We refresh our database from the prod to dev; just so we have new fresh data to work with.

It takes us almost 5-6hrs to export and import the whole database, but I found a very hidden gcloud feature that allows me to restore an instance backup from another project i.e. import production db backup instance to dev project with 2 apis.

The Documentation for the 2 Apis

Now this also changes the users of our dev project, and I don't want that although it has reduced time from 5hrs to 20mins but a drawback is that it changes our users password and etc, kinda locks them; the same prod db passwords also don't work.

My question is, is there's a way where I can restore an instance without effecting the users.

google-cloud-storage gcloud
2 Answers

On Cloud SQL, there are 2 levels of management:

  • The infrastructure (instances, backups,...) managed by Google Cloud
  • The database engine (MySQL, Postgres, MS Server) managed by the db engine itself

Now, when you perform a backup, you backup the whole instance. The Cloud SQL service has no idea of the data, the users, the config, (....). When you restore it, the whole backup is taken without any idea of the content.

To answer your question: with backups, you can't do that.

Here the solution is to go to the lower level and not to interact with Cloud SQL APIs (the infrastructure) but directly with the db engine. Export the data in files and import them elsewhere.

A third solution could be to have a script that, after the backup restore (that override your users info), to restore your users' configuration.

Maybe a latest solution, you can rely on IAM for your database authentication, and not on the db engine configuration.

user_113725930

I think what you are trying to do is bad like, Why don't you delete the old instance and clone the one you want in the project?

user_124550050
Related