GCP: Cross-Region Read Replica for CloudSQL using Private IP only ( CloudSQL to CloudSQL Replication)
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.
Replicating from an external server | Cloud SQL for MySQL | Google Cloud
Whether your business is early in its journey or well on its way to digital transformation, Google Cloud's solutions…
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
- 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.
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
Add at the end of DUMP
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.
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.
- 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.