Configure External Master For CloudSQL — Replicate VM MySQL to CloudSQL

In our previous blog, we have explained that to configure external replica for a CloudSQL instance. Here we are going to see how to configure external master for CloudSQL. Unlike native replication or the method which we explained in our previous blog, setting up external master for CloudSQL is pretty different way. Lets see how we can achive this.

Process Overview:

  • Launch VM and configure MySQL 5.7(it should be 5.5+).
  • Initiate the dump along with the binlog filename and its position.
  • Upload the Backup file to Google Storage.
  • Enable the CloudSQL Admin API.
  • In the CloudSQL console, use migrate data.
  • Select the CloudSQL instance type and its network.
  • Provide the Master server’s IP address and MySQL credentials for replication.

Create the Compute Engine VM:

  • Go to Compute Engine console and create the VM.
  • Give a name and choose the CPU, Memory, OS, and Networking and etc.
  • It should be in Public and assign a static external IP address.
  • MySQL Port should be Open till the Replication complete.
  • Lets wait for Instance creation complete.
  • More details step for creating the VM, please refer the below link.
Note: The communication between the VM and CloudSQL is not Private. Because the CloudSQL is not a part of VPC.

Install MySQL 5.7 on setup the Master:

-- This is command for install mysql for ubuntu 18.04
apt-get update
apt-get install mysql-server

Make the below configuration changes in mysql conf file.

-- This file location is for Ubuntu 18.04
vi /etc/mysql/mysql.conf.d/mysqld.cnf
-- With GTID replication 
(RECOMMENDED)
server-id = 1212 #Server-id should be 2 or above
gtid_mode = ON
enforce_gtid_consistency = ON
log_slave_updates = ON
replicate-ignore-db = mysql
binlog-format = ROW
expire_logs_days = 1
#enable this if you replicating from a slave
#read_only = ON
log_bin = /var/log/mysql/mysql-bin.log

-- if you don't want GTID replication the add this.
(NOT RECOMMENDED)
server-id = 1212 #Server-id should be 2 or above
log_slave_updates = ON
replicate-ignore-db = mysql
binlog-format = ROW
expire_logs_days = 1
#read_only = ON
log_bin = /var/log/mysql/mysql-bin.log

Restart the MySQL to apply the changes.

Create a test database and insert some values:

mysql -u username -p
create database sqladmin;
create table test_tbl (numbers int);
insert into test_tbl values (1),(2),(3),(4),(5);

Create the User for replication:

create user 'rep_user'@'%' identified by 'rep_password';
GRANT REPLICATION SLAVE ON *.* TO 'rep_user'@'%';
flush privileges;

Dump the database:

Run the below command to take the backup. (while restoring you can use native .sql or .gz file).

mysqldump --databases sqladmin -u root -p \
--master-data=1 \
--triggers \
--events \
--flush-privileges \
--hex-blob \
--default-character-set=utf8 > sqladmin.sql

Upload the Backup file to Gcloud storage:

You can assign service account to the VM and start upload the backup file using gsutil.Refer the below link for using gsutil command line interface.

Launch the CloudSQL using Migrate Data:

Note: How this works?
Your backup file contains the Binlog file name and its position and GTID. So once the restoration has been done, CloudSQL will automatically try to establish the replication from the next GTID. If you are not using GTID replication then it’ll start replicating from the binlog position.
  • Go to CloudSQL console.
  • Select the Migrate Data.
  • Click the Begin Migration.
  • In the next window, provide the Master Server IP address, replication user name and password.
  • The next window will ask the name for Name for CloudSQL, Instance type, Storage.
  • Then there is an option for select the mysql dump from the bucket. Click browse and select your Backup file.
  • If you click the Show Advanced Configurations, then you can white list the IP address to access the CoudSQL, Add Database flags and Lables.
  • Now, Click Create and wait for the replication success.
  • We need to create a MySQL user or reset the root password to access this CloudSQL slave. I create a new user called sqladminfor this.
  • Login to the Slave and check the MySQL replication status.
mysql -h CLOUD_SQL_IP -u sqladmin -p
show slave status\G;

Test the Replication:

  • Login to the master and insert few more rows. (from Master VM)
mysql -u root -p
use sqladmin;
insert into test_tbl values (6),(7),(8);
exit
mysql -h CLOUDSQL_IP -u sqladmin -p
use sqladmin;
select * from test_tbl;

Hardening the master:

In the first step, we have allowed mysql port to public. Because we don’t know the slave IP address until its created. Now we have the IP.

Go to FireWall rules and add the slave IP to access it in 3306 port. Once this added remove the public rule(0.0.0.0/0).

Our previous blog about Configuring external replica for CloudSQL:

From our past 2 blogs, we have learned that GTID replication is good while migrating data to CloudSQL. Our Next target manually setup replication between two CloudSQL instances. So stay tune.

Hope you are going to play this, give some claps if it helped.