Hybrid Cloud : Replicating data to CloudSQL MySQL from an external MySQL server

This is a step by step guide to replicate data from a self-managed/managed MySQL to CloudSQL MySQL.

Cloud SQL for MySQL is a fully-managed database service that helps you set up, maintain, manage, and administer your MySQL relational databases on Google Cloud Platform. With Cloud SQL for MySQL, you can spend less time on your database operations and more time on your applications. CloudSQL MySQL offers some great features and differentiators which are getting a lot of attention and making CloudSQL has the first choice for MySQL enthusiasts.

GCP Database Migration Service makes it easy to migrate databases from on-premises, Compute Engine, and other clouds to Cloud SQL with minimal downtime.

Alternatively, if you need more control while migrating to CloudSQL you can create CloudSQL MySQL as a native replica from an external server and replicate data. Replication between your self-managed/managed mysql server and the Cloud SQL replica continues indefinitely.

Use cases for external server configuration

External server configuration helps you achieve the following goals:

1. Migrate your data from your self-managed MySQL server to Google Cloud with minimum downtime.
2. Use native tools like mydumper/myloader to exponentially increase the export and import performance.
3. Retain colocation and control of your server while off-loading the administration of the replicas to Cloud SQL.

External replication configuration includes the following instances:

• The MySQL server that you manage, also called the source database server.
• The Cloud SQL replica.
• The source representation instance. The source representation instance is a Cloud SQL instance that represents the source database server to the Cloud SQL replica. It is visible in the Google Cloud console and appears the same as a regular Cloud SQL instance, but it contains no data, requires no configuration or maintenance, and does not affect billing.

Overview:

1. Create a GCE instance , install and configure MySQL 8(with GTID)
2. Create a dummy database & table and insert few records
3. Prerequisites on source DB — Enable binary logging and log retention , create replication user & grant privileges
4. Set up a source representation instance
5. Set up a Cloud SQL replica & add users to the Cloud SQL replica
6. Get the Cloud SQL replica’s outgoing IP address and Allow incoming connections on the external server
7. Promote CloudSQL Replica
8. Perform a custom dump and import
9. Demote the Cloud SQL instance
10. Start replication on the Cloud SQL instance
11. Replication validation

Lets Start !

1. Create a GCE instance , install and configure MySQL 8(with GTID)

Login into the GCE instance:

sudo apt-get install wget
wget https://dev.mysql.com/get/mysql-apt-config_0.8.22-1_all.deb
sudo apt install ./mysql-apt-config_0.8.22–1_all.deb
sudo apt update
sudo apt install mysql-server
sudo service mysql status

Add the following lines in /etc/mysql/mysql.cnf

[mysqld]

pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
log-error       = /var/log/mysql/error.log


bind-address=**.**.**.**
log_bin=/var/log/mysql/mysql-bin.log
server_id=11111
gtid_mode=ON
enforce_gtid_consistency=ON

Restart mysql using below commands:

sudo service mysql status
sudo service mysql stop
/usr/sbin/mysqld - defaults-file=/etc/mysql/mysql.cnf

Please make sure the mysql user has R/W permissions on /var/run/mysqld , /var/lib/mysql , /var/log/mysql. For any errors , please check and fix /var/log/mysql/error.log

Validate GTID config on source MySQL :

2. Create a dummy database & table and insert few records

Create database sample;
Use sample;

CREATE TABLE employees ( 
emp_no      INT             NOT NULL,  -- UNSIGNED AUTO_INCREMENT??
birth_date  DATE            NOT NULL,
first_name  VARCHAR(14)     NOT NULL,
last_name   VARCHAR(16)     NOT NULL,
gender      ENUM ('M','F')  NOT NULL,  -- Enumeration of either 'M' or 'F'  
hire_date   DATE            NOT NULL,
PRIMARY KEY (emp_no)                   -- Index built automatically on primary-key column
);



insert into employees values (1,now(),'aaaa','bbbb','M',now());
insert into employees values (2,now(),'aaaa','bbbb','M',now());
insert into employees values (3,now(),'aaaa','bbbb','M',now());
insert into employees values (4,now(),'aaaa','bbbb','M',now());
insert into employees values (5,now(),'aaaa','bbbb','M',now());
insert into employees values (6,now(),'aaaa','bbbb','M',now());

3. Prerequisites on source DB — Enable binary logging and log retention , create replication user & grant privileges

Enable binary logging and log retention

Create replication user & grant privileges on the source DB:

CREATE USER 'replicationUser'@'%' IDENTIFIED BY '******';
GRANT SELECT, SHOW VIEW ON *.* TO 'replicationUser'@'%';
FLUSH PRIVILEGES;
GRANT REPLICATION SLAVE, EXECUTE ON *.*
TO 'replicationUser'@'%';
grant FLUSH_TABLES ON *.* TO 'replicationUser'@'%';
grant process ON *.* TO 'replicationUser'@'%';

4. Set up a source representation instance

vi Source.json


    {
      "name": "clousql-custom",
      "region": "us-central1",
      "databaseVersion": "MYSQL_8_0",
      "onPremisesConfiguration": {
        "hostPort": "*.*.*.*:3306",
        "username": "replicationUser",
        "password": "****"
      }
    }

 gcloud auth login
    ACCESS_TOKEN="$(gcloud auth print-access-token)"
    curl --header "Authorization: Bearer ${ACCESS_TOKEN}" \
         --header 'Content-Type: application/json' \
         --data @/home/source.json \
         -X POST \
        https://sqladmin.googleapis.com/sql/v1beta4/projects/<project-name>/instances

5. Set up a Cloud SQL replica & add users to the Cloud SQL replica

vi replica.json

 {
         "settings": {
             "tier": "db-custom-4-15360",
             "dataDiskSizeGb": "50",
             "ipConfiguration": {
                 "privateNetwork": "projects/<project-name>/global/networks/<netwrorkname>"
              },
              "availabilityType": "ZONAL"
         },
         "masterInstanceName": "cloudsql-custom",
         "region": "us-central1",
         "databaseVersion": "MYSQL_8_0",
         "name": "cloudsql-custom-replica"
     }

 gcloud auth login
      ACCESS_TOKEN="$(gcloud auth print-access-token)"
      curl --header "Authorization: Bearer ${ACCESS_TOKEN}" \
           --header 'Content-Type: application/json' \
           --data @/home/replica.json \
           -X POST \ https://sqladmin.googleapis.com/sql/v1beta4/projects/<project-name>/instances

To ensure your instances were set up correctly, go to the Cloud SQL Instances page.

You should see your source representation instance and the Cloud SQL replica, in a listing similar to the following:

Add users to the CloudSQL Replica

6. Get the Cloud SQL replica’s outgoing IP address and allow incoming connections on the external server

Outgoing IP address to be whitelisted = 34.31.123.60

The Cloud SQL replica needs to connect to the external server for replication to succeed. You must configure the network firewall for your external server to accept connections from the Cloud SQL replica’s outgoing IP address if the following conditions apply:

• The external server is behind a firewall or some other network restriction.
• Your Cloud SQL replica is using a public IP.

7. Promote CloudSQL Replica

Because Cloud SQL replica instances are read-only, in order to perform a custom import, you need to promote the Cloud SQL replica to a standalone instance. Once the initial data import is complete, you demote the instance back to a replica.

8. Perform a custom dump from source and import it into cloudSQL Replica

mysqldump     --host=<source-hostip>     --port=3306     --user=replicationUser  
  --password=*****    --databases sample      --hex-blob     --no-autocommit 
    --default-character-set=utf8mb4     --single-transaction   
  --set-gtid-purged=on     --add-drop-table > sample_dump.sql

Import in the promoted CloudSQL Replica

mysql -h <cloudsql replicaip> -u root -p  < sample_dump.sql

In case the database size is big , then you can use mydumper and myloader to tune the export and import process and documented here.

Write down the GTID or binlog information of the data dump. You need this information to configure the replication with the Cloud SQL stored procedures.

  example:
SET @@GLOBAL.GTID_PURGED='32eb1e6a-17b6-11ea-a39e-06d94ac3ec98:1-33496';

Remove the following lines in the dump file that require super privileges. Since Cloud SQL users don’t have super privileges, these lines cause the import to fail.

For GTID-based replication: Remove the SET GTID_PURGED statement along with the session variable setting statement in the dump. For example:

   ...
   SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
   SET @@SESSION.SQL_LOG_BIN= 0;
   ...
   SET @@GLOBAL.GTID_PURGED='32eb1e6a-17b6-11ea-a39e-06d94ac3ec98:1-33496';
   ...
   SET @@SESSION.SQL_LOG_BIN=@MYSQLDUMP_TEMP_LOG_BIN;

9. Demote the Cloud SQL instance

vi demotemaster.json

{
        "demoteMasterContext": {
          "masterInstanceName": clousql-custom,
          "skipReplicationSetup": true
          }
     }

  gcloud auth login
     ACCESS_TOKEN="$(gcloud auth print-access-token)"
     curl --header "Authorization: Bearer ${ACCESS_TOKEN}" \
       --header 'Content-Type: application/json' \
       --data @/home/demotemaster.json \
       -X POST \     https://sqladmin.googleapis.com/sql/v1beta4/projects/<project-name>/instances/cloudsql-custom-replica/demoteMaster

10. Start replication on the Cloud SQL instance

Log on to the replica instance.

Use the mysql.resetMaster stored procedure to reset replication settings.

 mysql> call mysql.resetMaster();

Configure the replication

    mysql> call mysql.skipTransactionWithGtid('32eb1e6a-17b6-11ea-a39e-06d94ac3ec98:1-33496');
    mysql> call mysql.setupExternalSourceAutoPosition('<externalmysqlip>', 3306, \
    'USERNAME', 'PASSWORD', \
    /* master_auto_position= */ 1,false, false); \
   mysql> call mysql.startReplication();
   mysql> show slave status\G

Once you start replication from the external server, you need to monitor replication and then complete your migration. To learn more, see Monitoring replication.

11. Replication Validation

Insert records on the source DB:

Validate on CloudSQL Replica:

In the next blog ,we promote the CloudSQL as master and demote the self managed MySQL as Replica.

Till next time , Happy Learning !