Since there is a nightly backup of SQL we are wondering of a good way to restore this backup to a different database in the same MySQL server instance. We have prod_xxxx for all our production databases AND we have staging_xxxx for all our staging databases (yes not that good in that they are all on the same mysql instance right now).
Anyways, we would love to restore all tables/constraints/etc and data from prod_incomingdb to staging_incomingdb. Is this possible in cloud SQL?
Since this is over a productive instance I recommend you to perform a backup before start, in order to avoid any data corruption.
To clone a database within the same instance, there is not a direct way to perform the task (this is a missing feature on MySQL).
I followed this path in order to successfully clone a database within same MySQL Cloud SQL instance.
1.- Create a dump of the desired database using the Google Cloud Console (Web UI) by follow these steps
*it is very important to only dump the desired database in format SQL, please not select multiple databases on the dump.
After finish the process, the dump will be available in a Google Cloud Storage Bucket.
2.- Download the dump file to a Compute Engine VM or to any local machine with linux.
3.- please replace the database name (the old one) in the USE clauses.
I used this sed command over my downloaded dump to change the names of the databases
sed -i 's/USE `employees`;/USE `emp2`;/g' employees.sql
*this can take some seconds depending the size of your file.
4.- Upload the updated file to the Cloud storage bucket.
5.- Create a new empty database on your Cloud SQL instance, in this case my target instance is called emp2.
6.- Import the modified dump by following these steps