GCP: Cross-Region Read Replica for CloudSQL using Private IP only ( CloudSQL to CloudSQL Replication)

Tanuj Bolisetty
Jul 11, 2019 · 4 min read

Failover instance of CloudSQL( mysql) is only offered in the same region. If the region goes blank , we would loose access to the database. Unfortunately the backups are also tied to the instance and are not accessible when instance is not available.

We wanted to make sure we have a replica in cross region and is in sync with the primary CloudSQL. The below steps would allow us to have a read only copy in other region which is always in sync.

We built some custom building blocks around the standard functionality of replication from external and designed the solution.

This would act as a DR solution.

Steps to setup

Note: If you already have a existing database that you would like to set up a cross region replica , you can work your way from step 5

  1. Create the primary database with private IP only

gcloud beta sql instances create cloudsqlpoc-east4 — database-version=MYSQL_5_7 — activation-policy=always — async — region=us-east4 — no-assign-ip — network=<Network Name> — tier=db-n1-standard-1 — storage-type=SSD — storage-size=10GB — storage-auto-increase — backup — backup-start-time=04:00 — enable-bin-log — maintenance-window-day=SUN — maintenance-window-hour=08 — maintenance-release-channel=preview — database-flags lower_case_table_names=1,slow_query_log=on,log_output=FILE

2. Check if the database is running.

gcloud sql instances list

3. Reset the root password on primary

gcloud sql users set-password root — host=% — instance cloudsqlpoc-east4 — password <xxxxxx>

4. Create databases and load data.

5. Create replication user

Logon to a Jump host in the same project/network/region as of CloudSQL ( cloudsqlpoc-east4 ) and create a user with replication privileges.

mysql -uroot -p -h<IP of cloudsqlpoc-east4>

CREATE USER ‘replicator’@’%’ IDENTIFIED BY ‘<xxxxxxx>’;
GRANT REPLICATION SLAVE ON *.* TO ‘replicator’@’%’;

6. Take mysqldump from cloudsqlpoc-east4

mysqldump — databases < database list> -h < cloudsqlpoc-east4> -u root -p \
— master-data=1 — flush-privileges — hex-blob — skip-triggers \
— order-by-primary — compact — no-autocommit \
— single-transaction — set-gtid-purged=on > dump.sql

Note: If this is a large database , mysqldump may timeout. set the timeout database flags on the instance before taking dump and reset later once the dump is complete.

net_write_timeout=3600

net_read_timeout=3600

The default values are too low and the dump fails. As we did not want to keep these values around we reset them to original once the dump is complete.

Also note, we cannot backup and restore the mysql schema in the instance.

7. Disable the foreign key checks in the dump

Add to beginning of DUMP

SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS;

SET FOREIGN_KEY_CHECKS=0;

Add at the end of DUMP

SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;

8. Upload the dump to a bucket

gsutil cp dump.sql gs://<bucket name/

Note: Make sure not to use -Z flag while uploading to bucket.

9. Setup Portforwarder

As Central-1 cloudsql cannot connect to east-4 , we have introduced a portforwarder in the path. We can achieve portforwarding by any of these ways.

a. Build a VM in the same region as primary

b. Setup IP tables forward rules (or) CloudSQL proxy using private IP (or) a python portforwarder to accept traffic on 3306 and forward it to east-4 cloudsql.

https://github.com/vinodpandey/python-port-forward
https://github.com/vinodpandey/python-port-forward.git

c. Create a firewall rule INGRESS to VM from CloudSQL private peering CIDR range and associate it with a tag. Use the tag for the VM.

10. Source Representation

Create a source representation of the east-4 primary server in the Region central-1 where the replica needs to be created.

gcloud beta sql instances create source-cloudsqlpoc-east4 — region=us-central1 — database-version=MYSQL_5_7 — source-ip-address=<east4 portforwarder IP> — source-port=3306

11. Create the replica

gcloud beta sql instances create cloudsqlpoc-central1 — async — region=us-central1 — network=<Network Name> — no-assign-ip — tier=db-n1-standard-1 — storage-size=10GB — master-instance-name=source-cloudsqlpoc-east4 — master-username=replicator — master-password=<xxxxxx> — master-dump-file-path=gs://<bucket name>/dump.sql

This will create the replica and start the sync.

**********************************************************

When ready to promote the read replica , perform the below steps.

  1. Promote the replica
gcloud sql instances promote-replica [REPLICA_NAME]

2. Enable bin log , backup-start-time ,maintenance-window-day ,maintenance-window-hour , maintenance-release-channel , failover-replica-name ,database-flags as needed.

3. We can create users in the read replica only from gcloud/console. We cannot create users with restricted privileges from mysql prompt when it is in read replica mode. So create application users in the database with required privileges once the database is promoted.

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade