Google cloud sql migrate from first generation to second generation with minimal to no downtime

Question

Is there a way to migrate from first generation cloud sql to second generation with minimal to no downtime?

Dumping my 1st gen DB takes about 30 mintues after I set it to read only (Meaning downtime for me) and I believe import to the 2nd gen will take a long time as well.

What about using a clone while the upgrade is in process and when the upgrade completes copy the difference to the new DB?

I'm using AppEngine, so I was thinking about uploading a new version pointing to the 2nd gen DB while the old version is pointing to the clone. Once the upgrade completes I'll change the version and run a script to copy the missing data.

Could that work? Any better options?

Answer 1

I ended up doing the following:

1. Create the 2nd Gen Instance
2. Create a temporary machine in compute engine
3. Ssh to the temp machine and get the IP of the machine: https://askubuntu.com/questions/95910/command-for-determining-my-public-ip
4. Give access to the IP address on both new and old DB instances
5. Setup user and password in 1st gen instance (Input it in the script below)
6. Upload a new version of your app engine app configured to use the new 2nd gen DB but don't set it to be the default version
7. Edit & run the script below in the temp machine in ssh
8. The script will ask to confirm the read only flag
9. While the script is running the DB will be in READ ONLY mode causing a down time for your application
10. Once the script finishes successfully change the default version of app engine to point to the new version that uses the new 2nd gen DB
11. This gave us 15 minutes of down time

Here is the script:

#!/bin/sh

DB_IP=127.0.0.1
SQL_FILE_NAME=db-backup.sql
GZ_SQL_FILE_NAME=db-backup.sql.gz
BUCKET_NAME=gs://db-bucket-name
GS_FILE_PTH=$BUCKET_NAME/$GZ_SQL_FILE_NAME
INSTANCE_NAME=1st-gen-instance
NEW_INSTANCE_NAME=2nd-gen-instace

gcloud config set project project-name
gcloud sql instances patch $INSTANCE_NAME --database-flags read_only=on
mysqldump --databases dbname -h $DB_IP -u user --password=password \
--hex-blob --skip-triggers --set-gtid-purged=OFF \
--default-character-set=utf8 > $SQL_FILE_NAME
gzip $SQL_FILE_NAME
gsutil cp -r $GZ_SQL_FILE_NAME $GS_FILE_PTH
SERVICE_ACCOUNT_ADDRESS=$(gcloud sql instances describe $NEW_INSTANCE_NAME | sed -n -e 's/^.*serviceAccountEmailAddress: //p')
gsutil acl ch -u $SERVICE_ACCOUNT_ADDRESS:W $BUCKET_NAME
gsutil acl ch -u $SERVICE_ACCOUNT_ADDRESS:R $GS_FILE_PTH
gcloud sql instances import $NEW_INSTANCE_NAME $GS_FILE_PTH
gsutil acl ch -d $SERVICE_ACCOUNT_ADDRESS:W $BUCKET_NAME
gsutil acl ch -d $SERVICE_ACCOUNT_ADDRESS:R $GS_FILE_PTH