Replicate MySQL from a VM to Cloud SQL Using Dump file

In this article, we are going to replicate the MySQL from a VM to Cloud SQL using the dump file as in our previous article we have replicated using managed import option.

Let’s get started !!

Before starting the replication, we need to complete the below steps:

1. Configure the external server[master]
2. Create the source representation instance[slave]
3. Setup the Cloud SQL replica

1) Steps to setup the external server

i) Launch the Compute Engine public VM and install the MySQL_5.7 in it.

ii) Navigate to /etc/mysql/mysql.conf.d/mysqld.cnf file in the VM and edit it, add the below content in it. [Enabling gtid and binlog]

server-id                = 322 #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
log_bin                  = /var/log/mysql/mysql-bin.log

iii) Restart the MySQL server

systemctl restart mysql

iv) Create a database and insert some data in it.

mysql -u root -p
create database test;
use test;
CREATE TABLE Persons (PersonID int,LastName varchar(255),FirstName varchar(255),Address varchar(255),City varchar(255));
INSERT INTO Persons(PersonID,LastName,FirstName,Address,City) VALUES(1,'R','Naveen','Chittoor','Tirupati');

v) Create a user for replication

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

vi) Take the database dump backup in the external server.

mysqldump \
    -h [MASTER_IP] -P [MASTER_PORT] -u [USERNAME] -p \
    --databases test\
    --hex-blob  --skip-triggers  --master-data=1  \
    --order-by-primary --no-autocommit \
    --default-character-set=utf8mb4 \
    --single-transaction --set-gtid-purged=on > test.sql

vii) Move the dump file to the GCS bucket

2) Create the source representation instance

Create the source representation instance by running the below command.

gcloud auth login 
 ACCESS_TOKEN="$(gcloud auth print-access-token)" 
 curl --header "Authorization: Bearer ${ACCESS_TOKEN}" \
         --header 'Content-Type: application/json' \
         --data '{
  "name": "naveen-test-latest", 
  "region": "asia-south1",
  "databaseVersion": "MYSQL_5_7",
  "onPremisesConfiguration": {
    "hostPort": "34.93.254.66:3306",
    "username": "user1",
    "password": "user1_password"
    "dumpFilePath": "gs://naveen-test-sample/test.sql"
  }
}' \
         -X POST \
         https://sqladmin.googleapis.com/sql/v1beta4/projects/project_id/instances

Note: In the above command name= source representation instance name, region= source representation instance region, hostport= IP address and port of external server, username= external server replication user name , password= external server replication user password , dumpFilePath= Path of the bucket where the dump file is stored.

3) Create the Cloud SQL replica

Create the Cloud SQL replica by running the below command

gcloud auth login
ACCESS_TOKEN="$(gcloud auth print-access-token)"
curl --header "Authorization: Bearer ${ACCESS_TOKEN}" \
         --header 'Content-Type: application/json' \
         --data '{
        "settings": {
            "tier": "db-custom-2-7680",
            "dataDiskSizeGb": "100",
             "availabilityType": "ZONAL"
        },
        "masterInstanceName": "naveen-test-latest",
        "region": "asia-south1",
        "databaseVersion": "MYSQL_5_7",
        "name": "naveen-test-latest-replica"
       }' \
         -X POST \
         https://sqladmin.googleapis.com/sql/v1beta4/projects/project_id/instance

After the source representation instance and replica created, it will be shown in the SQL console as shown below.

Note: Please don’t use the above IP’s as those are no longer available.

Now navigate to read replica and move the pointer to more info tool-tip and copy the outgoing IP address.

Create a firewall rule and add the outgoing IP address and replica IP address with port allowing to 3306.

Add the virtual machine IP address in the replica authorised networks.

Note:

After the replica is created, we will get a service account created and shown in the console. Add the bucket permissions for the service account as shown below.

gcloud projects add-iam-policy-binding project_id\
  --member serviceAccount:p769170094354-bamq6o@gcp-sa-cloud-sql.iam.gserviceaccount.com \
  --role roles/storage.objectAdmin

Using dump option to setup replication

Login to the external server database and update the replication user permissions as shown below.

GRANT EXECUTE, SELECT, SHOW VIEW, REPLICATION CLIENT, RELOAD ON *.* TO 'user1'@'%';
 flush privileges;

Execute the below command to verify the external sync settings.

gcloud auth login
ACCESS_TOKEN="$(gcloud auth print-access-token)"
curl --header "Authorization: Bearer ${ACCESS_TOKEN}" \
     --header 'Content-Type: application/json' \
     --data '{
         "syncMode": "online",
         "skipVerification": "false"
       }' \
     -X POST \
     https://sqladmin.googleapis.com/sql/v1beta4/projects/project_id/instances/naveen-test-latest-replica/verifyExternalSyncSettings

After executing above command, a response will be appeared as shown below.

Next execute the below command to start the external sync.

gcloud auth login
ACCESS_TOKEN="$(gcloud auth print-access-token)"
curl --header "Authorization: Bearer ${ACCESS_TOKEN}" \
       --header 'Content-Type: application/json' \
       --data '{
           "syncMode": "online",
           "skipVerification": "true"
         }' \
       -X POST \
       https://sqladmin.googleapis.com/sql/v1beta4/projects/project_id/instances/naveen-test-latest-replica/startExternalSync

Once the above command executed, it will show the status pending as the instance is being replicated.

After sometime the replication enabled and in the console it will be shown as below.

Let’s login into the replica database and check for the data whether it is replicated or not.

As the above screenshot shows that the data is being replicated.

Let’s add some data in the external server database and see whether the data is replicating or not.

Now let’s login to the replica database and check whether the data replicated or not.

Once the data is replicated, then we can promote the read replica by clicking on the promote replica option in the console.

Once you click on promote replica, then the pop-up will come as shown below.

Once it is promoted, the replica instance will be shown as the primary instance.