How To Configure MySQL Replication Between CloudSQL To CloudSQL

From my past 2 posts, I have explained how to configure replication between CloudSQL to VM and VM to CloudSQL. Now we have implemented one more solution in CloudSQL is Configure replication between CloudSQL to another CloudSQL. Its almost similar to setting up the external Master. Lets see how to do this.

Where this will help you?

  • Migrating from one project to different projects.
  • Migrate the existing CloudSQL to Another Region.
  • There is no straightforward ways shrink the MySQL volume(Some worst cases the auto increment added more space). So we can replicate it to another CloudSQL with less downtime.
  • Setup replication between Multiple Accounts.
  • Have an uptodate Replica for Test purpose.

Setting up the master:

  • Just go and launch the CloudSQL with 5.7
  • Create the sample database and table.
create database sqladmin;
CREATE TABLE `new` (
`name` varchar(20) DEFAULT NULL,
`branch` varchar(20) DEFAULT NULL,
`mis` int(11) NOT NULL,
PRIMARY KEY (`mis`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  • Create an user for replication.
create user 'rep_user'@'%' identified by 'rep_password';
GRANT REPLICATION SLAVE ON *.* TO 'rep_user'@'%';
flush privileges;
  • Then we need to allow the Replica to access the Mater CloudSQL, but we don’t have the IP address of the replica. So just temporarily allow all the traffic in the authorized network of Master. Once the replication established just add the Replica IP then remove the 0.0.0.0/0.
NOTE UPDATE: 
Once you click create then there is it’ll show OUTGOING IP, just whitelist that IP in Master’s network authorized network.

Take the dump and upload it to Storage Bucket

For taking this dump, we must need a VM. Because the default Export option is not include the binlog file, position or GTID. Without these things, we can setup the replication.

  • Go and launch a VM, if you already have the use it.
  • Take the dump using the below command.
mysqldump -h CLOUDSQL_MASTER_IP  --databases sqladmin -u bhuvi -p \
--master-data=1 \
--routines \
--triggers \
--events \
--flush-privileges \
--hex-blob \
--default-character-set=utf8 > sqladmin.sql
  • Upload it to Storage Bucket. You can any CLI tools or any other possible tools. Click the below link to use gsutil

Create the replica:

  • Go to CloudSQL and click Migrate Data.
  • Then provide the Mater CloudSQL details.
  • Then select the Instance type for the Replica and the disk size.
  • From the Import SQL dump from Google Cloud Storage select the Dump file (Its supports .sql and .sql.gz
  • Then click create and wait for the replication complete.

Check the replication

Right now there is no users in the Replica, so Just reset the root password or create a new user.

  • Allow your network to access the Slave.
  • Login to the Slave and do show slave status\G.
  • Add a new row in the master and check the data in slave.
-- in Master
insert into sqladmin.new values ('sqladmin','bglr',4);
-- In slave
select * from sqladmin.new;
  • Replicated :)

We have covered from recent blogs,

Feel free to give your comments below and give some claps as well :)