Create Read-Only Replica From AWS RDS To GCP Cloud SQL (+ Query With BigQuery!) [MySQL 8.0]

I will post story about create read-only replica from AWS RDS to GCP Cloud SQL.

When we create read-only database to Cloud SQL, we can join data with BigQuery.

AWS RDS to Cloud SQL

The steps are as follows:

1. Change parameter(GTID) values of AWS RDS Master / Slave
2. Dump AWS RDS Master’s data
3. Upload dump data to Google Cloud Storage
4. Migrate data using Google Cloud SQL’s migration tool
5. Add external data source to BigQuery

First, we should change AWS RDS Master / Slave’s parameter value.

Click RDS -> Configuration -> Parameter Group, and change enforce_gtid_consistency, gtid-mode values to ON.

Change GTID Parameter

If you have Read-Only Replica in your AWS RDS, you should change both of them.

Please reboot your database. (If bin-log is disabled, turn it on!)

Second, dump AWS RDS database.

We will dump Master Database using mysqldump.

mysqldump \     
--host=YOUR_HOST \     
--port=YOUR_PORT \     
--user=YOUR_USER \     
-p \     
--databases DATABASE_NAME \     
--hex-blob \     
--no-autocommit \     
--default-character-set=utf8mb4 \     
--single-transaction \     
--set-gtid-purged=on \     
-v \   
--compress \     
| gzip > sample.gz

Dump file will be compressed and saved to your directory.

Upload dumped .gz file to Google Cloud Storage.

When upload is done,

GCP Data Migration

Google Cloud Platform -> Cloud SQL -> 데이터 마이그레이션을 클릭합니다.

Type your database information(AWS RDS Master).

You can create replica-only user to MySQL.

CREATE USER '[USERNAME]'@'%' IDENTIFIED BY '[PASSWORD]';
GRANT REPLICATION SLAVE, EXECUTE, SELECT, SHOW VIEW ON *.* TO '[USERNAME]'@'[HOST]';
FLUSH PRIVILEGES;

And select your uploaded dump file.

Click create button to create read-only replica.

If read-only replica is available, you can modify connection and user information.

AWS RDS -> Cloud SQL Read-Only Replica creation is complete.

Let’s add external database to BigQuery.

Click Google BigQuery tab.

Click resources -> Add data-> External data source.

Type read-only database’s information.

The Cloud SQL instance ID can be found in the Cloud SQL Connection -> instance connection name.

If you use this connection, you can query AWS RDS’s data by using Google BigQuery.

Thanks for reading! Have a nice day :D