Database Synchronization on Alibaba Cloud

Overview

In Alibaba Cloud, we can do synchronization for our RDS and ECS. For data disaster recovery, ETL (extract, transform, load), etc, we need to implement replication between RDS MySQL and offline MySQL instances, we will set offline MYSQL instance from ECS.

Prerequisites

1. Alibaba Cloud Access
2. ECS
3. RDS

Implementation

For the ECS Instance, we will use specification type equal to ecs.n4.small, and installed ubuntu 16.04 64 bit. In ECS Instance we need to install MySQL 5.7.

We have public access on ECS, the IP 47.254.95.84.

For RDS, we can choose MySQL 5.6 which is still compatible with version on ECS’s MySQL.

Ensure the resource already set whitelist. Since the best practice the database should be in private access, then we can also set whitelist to 0.0.0.0/0. But for now, let’s set it public access for testing purpose.

We create database and user account from dashboard RDS directly.

For the replication, we will require GTID parameter. It’s metod which ensures a Unique ID in the cluster for each transaction commited on the master database. This method enhances master-slave consistency, failure recovery and fault tolerance capabilities.

In traditional replication based on binary logs, the slave database must inform the master database of the offset from which the incremental synchronization begins, and incorrect offset information causes data omission and inconsistency.

GTID stands for Global Transaction ID. With GTID, other slave database of MySQL automatically finds the correct location for replication on the new master database in case of master-slave switchover, which simplifies the maintenance of clusters under a complicated replication topology and reduces the risk of mis-operation caused by the manually set replication location. Moreover, the replication based on GTID can ignore transactions that have been run, reducing the risk of data inconsistency.

For now, we are going to define installed MySQL on ECS as slave, and RDS MySQL as master.

Check the GTID first for master.

Then slave

On slave, we need to update the config for mysql server on

$ vi /etc/mysql/mysql.conf.d/mysqld.cnf

 server-id              = 1
 log_bin                = /var/log/mysql/mysql-bin.log
 expire_logs_days       = 10
 max_binlog_size   = 100M
 #binlog_do_db           = include_database_name
 #binlog_ignore_db       = include_database_name

 replicate-do-db			= labex

 #GTID
 gtid_mode=on
 enforce_gtid_consistency=on
 binlog_format=row
 log-slave-updates=1

Restart the mysql service first by

$ service mysql restart

Here is slave after restart

To connect the slave to master, we need to configure it by this command

Set MASTER_HOST to your RDS public endpoint, and MASTER_PASSWORD to user password.

Now to start the configuration, run this on SQL

> start slave;

We can check the status of our slave like

> show slave status\G

The database already synchronized and the “Slave_IO_Running” and “Slave_SQL_Running” is set to true/yes.

End Result

Now, we can test our slave to ensure the replication proper or not.

Let’s create table “labex”

Execute it from master first

Now check on slave

Another test, let’s create user inside table of labex

Execute from Master first

Then check on Slave

Reference

Labex.io