Migrating from 1st gen google cloud sql to second generation

Question

I'm unable to migrate my 1st gen to second by way of export/import.

When I try to import a .sql file backup I get this error:

ERROR 1227 (42000) at line 28: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

I learned that I need to exclude the mysql database. After doing so I'm presented with this error:

ERROR 1419 (HY000) at line 148: You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

It's worth noting that my database DOES have triggers. Is this not allowed on the 2nd generation google cloud storage? How can I restore my data to this new instance?

Answer 1

(1) If the target 2nd Gen CloudSQL DB already has databases, you want to drop them before importing or reimporting the 1st Gen CloudSQL databases (useful if you're repeatedly testing this). The next 2 statements create a file with all DBs to be dropped and then drops them.

mysql -u tisadmin -p -h IP_ADDRESS_2nd_Gen_CloudSQL_DB -e "show databases" | grep -v Database | grep -v mysql| grep -v information_schema| grep -v performance_schema |gawk '{print "drop database " $1 "; select sleep(0.1);"}' > dbsToBeDropped.sql

mysql -u tisadmin -p -h IP_ADDRESS_2nd_Gen_CloudSQL_DB < dbsToBeDropped.sql

(2) Update the CloudSQL 2nd Generation Engine to have privileges you had in 1st Gen

gcloud auth login gcloud config set project [project-id] gcloud sql instances patch [2ndGenCloudSQLInstance] --database-flags log_bin_trust_function_creators=ON

(3) Export from 1st Gen CloudSQL Instance with flags

mysqldump -u tisadmin -p -h IP_ADDRESS_1st_Gen_CloudSQL_DB --hex-blob --skip-triggers --default-character-set=utf8 --databases "Channel_MASTER" "sequence" "dmm_old" "BusinessDataStandards" > dev-backup-20170307a.sql

(4) Import into 2nd Gen CloudSQL

mysql -u tisadmin -p -h IP_ADDRESS_2nd_Gen_CloudSQL_DB < dev-backup-20170307a.sql

References: https://cloud.google.com/sql/docs/mysql/import-export/creating-mysqldump-csv#mysqldump

DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled