Migrating between Cloud SQL databases

I run a bunch of SQL databases on Cloud SQL v1, and I wanted to move them over to Cloud SQL v2.   I like to automate this sort of thing.  I also like to have the new database essentially mirror the old one, until I’m ready to cut over.

I looked into writing a script that could do that with gcloud.  Turns out,  it is incredibly simple. The sql tools in gcloud can import and export directly to Cloud Storage.

PROJECT=[Project ID]
SRC_INSTANCE=[Name of source Cloud SQL instance to target]
DES_INSTANCE=[Name of destination Cloud SQL instance to target]
BUCKET=gs://[Name of Bucket set aside for temporarily storing mysqldump]
DATABASE=[MySQL Database name]

# Export source SQL to SQL file
gcloud sql instances export $SRC_INSTANCE $BUCKET/sql/export.sql 
--database $DATABASE --project $PROJECT

# Import SQL file to destination SQL
gcloud sql instances import $DES_INSTANCE $BUCKET/sql/export.sql 
--project $PROJECT 

# Delete SQL file and export logs. 
gsutil rm $BUCKET/sql/export.sql*

There you go — three lines of commands.  The only thing you need to do to make the new DB work is make sure all of the database accounts are set up correctly on the new server, otherwise application calls will bomb.

Now keep in mind that your mileage (or kilometerage) may vary.  In this case, I am going between MySQL 5.5 and MySQL 5.6, and I had no issues. If there is a reason that your old DB won’t run in the new target, it will fail.  This script also assumes that you are in the same project with appropriate permissions to all.

There’s a lot more you can do with gcloud to manage your Cloud SQL installation. Make sure to check out the rest of the documentation.