Migrating MySQL and Amazon RDS to Cloud SQL

There are two ways to get a MySQL database into Cloud SQL:

• Database Migration Service (DMS): copies the data, then replicates changes from the source's binary log until you promote the target. Downtime is the few minutes it takes to stop writes, let replication catch up and repoint the application. Works for self-managed MySQL, Amazon RDS for MySQL, Aurora MySQL and other Cloud SQL instances.
• Dump and import: mysqldump on the source, import into Cloud SQL. Simple, but the application is down for the whole dump and import. Fine for small databases or when a maintenance window is acceptable.

Prepare the source

DMS reads the binary log, so it has to be on, row-based and kept long enough to cover the initial copy.

Self-managed MySQL (/etc/mysql/mysql.conf.d/mysqld.cnf on Debian and Ubuntu, /etc/my.cnf on RHEL-family systems):

[mysqld]
server_id = 1
log_bin = mysql-bin
binlog_format = ROW
binlog_row_image = FULL
binlog_expire_logs_seconds = 604800

Amazon RDS and Aurora MySQL: set binlog_format to ROW in a custom parameter group, make sure automated backups are enabled (RDS only writes binary logs when they are), and set binlog retention, which is unset by default so RDS purges binary logs quickly:

CALL mysql.rds_set_configuration('binlog retention hours', 168);

168 hours (7 days) is the maximum on RDS for MySQL; Aurora MySQL allows up to 2160. Watch storage on the source: binary logs are kept for that long.

Create a migration user on the source with the privileges DMS needs:

CREATE USER 'dms_user'@'%' IDENTIFIED BY 'change-me';
GRANT REPLICATION SLAVE, REPLICATION CLIENT, EXECUTE, SELECT, SHOW VIEW,
      RELOAD, TRIGGER, LOCK TABLES ON *.* TO 'dms_user'@'%';

LOCK TABLES is required on RDS. On MySQL 8.0 and later, the user must not have BACKUP_ADMIN.

Also check:

• Tables use InnoDB. Non-transactional engines such as MyISAM cannot be copied consistently while writes continue.
• Tables have primary keys. Row-based replication of large tables without them is slow.
• The target uses the same MySQL major version or a newer one. Test the application against the newer version first.

Connectivity

The destination Cloud SQL instance has to reach the source. DMS offers four methods:

• IP allowlist: the source is reachable over the internet and its firewall (an RDS security group, for example) allows the Cloud SQL instance's outgoing IP. Use SSL: for RDS, pass the RDS CA bundle (https://truststore.pki.rds.amazonaws.com/global/global-bundle.pem) as the CA certificate.
• Reverse SSH tunnel through a bastion VM, when the source cannot accept inbound connections.
• VPC peering, when the source is reachable from a Google Cloud VPC, for example over HA VPN to AWS.
• Private Service Connect.

For RDS in private subnets, HA VPN between the clouds plus VPC peering is the usual setup and keeps the replication stream off the internet.

Migrate with Database Migration Service

The console walks you through it (Database Migration > Migration jobs > Create migration job). With gcloud, create a source connection profile:

gcloud database-migration connection-profiles create mysql my-rds-source \
  --region=europe-west1 \
  --provider=RDS \
  --host=my-db.abcdefgh1234.eu-central-1.rds.amazonaws.com \
  --port=3306 \
  --username=dms_user --prompt-for-password \
  --ca-certificate="$(< global-bundle.pem)"

Create the destination with gcloud database-migration connection-profiles create cloudsql (it creates the Cloud SQL instance) or in the console. Then create, verify and start a continuous job:

gcloud database-migration migration-jobs create my-migration \
  --region=europe-west1 --type=CONTINUOUS \
  --source=my-rds-source --destination=my-cloudsql-dest \
  --peer-vpc=projects/my-project/global/networks/my-vpc

gcloud database-migration migration-jobs verify my-migration --region=europe-west1
gcloud database-migration migration-jobs start my-migration --region=europe-west1
gcloud database-migration migration-jobs describe my-migration --region=europe-west1

The job goes through a full dump phase and then into change data capture (CDC). From then on the Cloud SQL instance is a read-only replica of the source. Watch the replication delay in the job details; it should trend towards zero and stay there.

Cut over

1. Run the application against the replica read-only for a while: check queries, collations, sql_mode differences and performance.
2. Check that application users and their grants exist on the destination. Recreate them if not.
3. At the window, stop writes on the source: put the application in maintenance mode or stop it.
4. Wait until replication delay is zero.
5. Promote:

   gcloud database-migration migration-jobs promote my-migration --region=europe-west1
   

   The Cloud SQL instance becomes a standalone primary and accepts writes.
6. Point the application at Cloud SQL (private IP, the Cloud SQL Auth Proxy or the Cloud SQL language connectors) and start it.
7. Compare row counts on key tables and run application smoke tests.

Keep the source untouched for the rollback period. After promotion, writes go only to Cloud SQL, so a rollback after real traffic means copying those writes back. Decide beforehand how long that window is.

Migrate with dump and import

For a small database or when downtime is acceptable:

mysqldump -h my-db.abcdefgh1234.eu-central-1.rds.amazonaws.com -u admin -p \
  --single-transaction --routines --triggers --events --hex-blob \
  --set-gtid-purged=OFF app | gzip > app.sql.gz

--single-transaction gives a consistent InnoDB snapshot without locking tables. --set-gtid-purged=OFF avoids SET @@GLOBAL.GTID_PURGED statements that Cloud SQL rejects.

Views, routines and triggers carry a DEFINER. Cloud SQL does not grant SUPER, so an import fails if the definer user does not exist there. Create the same users first, or strip the clause:

zcat app.sql.gz | sed -E 's/DEFINER=`[^`]+`@`[^`]+`//g' | gzip > app-clean.sql.gz

Import through Cloud Storage (the Cloud SQL instance's service account needs read access to the bucket):

gcloud sql databases create app --instance=my-mysql
gcloud storage cp app-clean.sql.gz gs://my-db-imports/
gcloud sql import sql my-mysql gs://my-db-imports/app-clean.sql.gz --database=app

Or import directly through the Cloud SQL Auth Proxy:

cloud-sql-proxy my-project:europe-west1:my-mysql &
zcat app-clean.sql.gz | mysql -h 127.0.0.1 -u root -p app

For large databases, a parallel dump tool such as mydumper and myloader shortens both phases considerably.

After the migration

• Enable automated backups and point-in-time recovery if they are not on yet, and high availability (regional) for production.
• Delete the DMS job and connection profiles, and remove the migration user and firewall openings on the source.
• Reset binlog retention hours on RDS, or delete the RDS instance after taking a final snapshot.

Summary

• Minimal downtime: DMS continuous job, promote at cutover.
• Source needs binlog_format=ROW, binary logs retained, InnoDB tables and a migration user.
• RDS: parameter group, automated backups, mysql.rds_set_configuration('binlog retention hours', 168).
• Connectivity: IP allowlist with SSL, reverse SSH, VPC peering over VPN, or Private Service Connect.
• Offline: mysqldump --single-transaction --set-gtid-purged=OFF, strip DEFINER, gcloud sql import sql.