MySQL AWS RDS Replication to Google Cloud SQL
Here is a simple howto guide for how to replicate from AWS RDS to Google Cloud SQL.
To setup replication on Google Cloud SQL, we need a static IP address but RDS does not have a static IP address. So for that reason, we need the proxy to forward the traffic from master to slave. There are many different kinds of solutions out there which Bill Schneider already wrote in his blog post.
I will go with socat for this test example:
socat: e.g., socat TCP-LISTEN,[port],fork,reuseaddr TCP:[hostname]:[port].
Let's get it on!
So here is the recipe for our cooking session:
Instances:
- AWS RDS (Master)
- EC2 Instance (Proxy)
- Google Cloud SQL (Slave)
Before we can start with the replication we need to make the following changes on the RDS Master instance:
- Enable backup
Backup needs to be enabled for binlog to be enabled or have 1 replication rds instance running.
- Setup a new Parameter Group for your RDS Master.
We need to enable enforce_gtid_consistency & gtid_mode = ON
A reboot is required for this.
Now, lets set up a small VM for forwarding a static IP to an RDS hostname.
Use a static Elastic IP for making sure that the IP will not change during a reboot
Adjust Security Group to make port 3306 wide open. (You can adjust it later to make it more secure)
Once you have the instance running you only need to install socat and run following command:
$ socat -v TCP-LISTEN:3306,fork TCP:rds-master.c3u5ki2hnnys.eu-central-1.rds.amazonaws.com:3306
That's it! nothing more to do here.
Now let us have look on the Google Cloud Replication.
Instead of creating a new instance we need to use the migration wizard of google in order to make replication work.
Only 3 steps are needed for this procedure:
Install the Google Cli SDK on your VM or local machine
Create a Google Storage bucket to upload the mysqldump
Make a mysqldump of the database and upload it to the storage
Then let's run the mysqldump from their master and upload it directly to the Google Storage Bucket:
$ mysqldump \
-h3.122.54.17 -P -urdsmaster -p \
- databases aws-rds-master-db \
- hex-blob - skip-triggers - master-data=1 \
- order-by-primary - compact - no-autocommit \
- default-character-set=utf8mb4 - ignore-table \
- single-transaction - set-gtid-purged=on | gzip | gsutil cp - gs://rdsmaster/hq-master1.sql.gz
You will find more pieces of information about Google Cloud SQL external replication here.
Once this is done we basically only have to go through the Google Cloud SQL Migration process and use the Elastic IP of the EC2 Proxy and the MySQL credentials!