MySQL NDB Cluster Replication Topologies (Part – III)

In this blog, we will discuss about bidirectional replication between two NDB clusters. With this topology, user can do transaction simultaneously from both the clusters on the same database objects.

Let’s create two MySQL NDB Cluster with the following environment, Here, both will be termed as ‘source’ cluster as well as ‘replica’ cluster as we will do replication from both the sides.

• MySQL NDB Cluster version (Latest GA version)
• 1 Management node
• 4 Data nodes
• 2 MySQLDs
• Configuration slots for up to 4 additional API nodes

Replication schema diagram:

                    |-----------S1---------->>----------S3--------------|

                    |                                                               |

     M ----------|                                                               |-------- R
(Cluster 1) |                                                               |   (Cluster 2)
                    |---------S2------------<<--------S4---------------|


M: Source Cluster, R: Replica Cluster
S1, S2 - MySQLD from Source Cluster
S3, S4 - MySQLD from Replica Cluster

S1: Source MySQLD from Cluster 1, from where data will replicate to S3 replica of Cluster 2
S4: Source MySQLD from Cluster 2, from where data will replicate to S2 replica of Cluster 1

S1:: HostName: cluster-test01, Server Id: 61, IP: 100.103.21.66
S2:: HostName: cluster-test02, Server Id: 62, IP: 100.103.21.62
S3:: HostName: cluster-test05, Server Id: 63, IP: 100.103.21.65
S4:: HostName: cluster-test06, Server Id: 64, IP: 100.103.21.75

Step 1: Let’s start the ‘source’ cluster.

And, then start the ‘replica’ cluster.

Now both the clusters are up and running. I am assuming that, user is aware of how to enable configuration variables so that MySQL server can be act as a ‘source’. If not, please refer to my previous blogs on replication. In this case, we have two ‘source’ and should be configured for both the MySQL servers (S1 and S4).

Step 2:

a) Let’s cleanup the old binary files if exist from S1 and S4 MySQL server:

From the source MySQL server (S1), let’s delete all existing binary log files (if any exist), through 'reset master' command. The status of the binary log file can be known through ‘show master status’ command.

mysql> reset master;
mysql> show master status;

Let’s do it for the second source (S4):

b) Login as ‘root’ user in the source MySQL server (S1). Then update the root account so that other host with ‘root’ user can connect to this source MySQL server.

From first Source host (S1): (cluster-test01)

mysql> bin/mysql -u root -P"13010";
mysql> UPDATE mysql.user SET host='%' where user='root' and host='localhost';
mysql> flush privileges;

From the second source host (S4): (cluster-test06)

mysql> bin/mysql -u root -P"13010";
mysql> UPDATE mysql.user SET host='%' where user='root' and host='localhost';
mysql> flush privileges;

c) Let’s start the replication:

Since the user ‘root’ is configured from both the clusters to accept the connection so let’s start the replication from S1 (source) ----> S3 (replica):

From replica (S3): cluster-test05:

mysql> bin/mysql -u root -P"13010"
mysql> CHANGE REPLICATION SOURCE TO SOURCE_HOST='100.103.21.66', SOURCE_USER='root', SOURCE_PORT=13010, IGNORE_SERVER_IDS=(63,64);
mysql> start replica;

From the above queries, we can see an extra parameter to change replication source command i.e. IGNORE_SERVER_IDS. Replica server will ignores all the events that are originating from the server Id: 63 and 64.

Now check the state of the replication:
mysql> show replica status\G

From the above image, we can see that our first replication is started.
Let’s start the replication between S2 ----> S4 MySQL server.

From replica (S2): cluster-test02

mysql> bin/mysql -u root -P"13010"
mysql> CHANGE REPLICATION SOURCE TO SOURCE_HOST='100.103.21.75', SOURCE_USER='root', SOURCE_PORT=13010, IGNORE_SERVER_IDS=(61,62);
mysql> start replica;

Let’s check the status of the second replication:
mysql> show replica status\G

From the above images, we can see that, we have successfully created a bidirectional replication between source and replica cluster.

Step 3:

Now let’s create a database ‘test1’, table ‘t1’ and will do some transactions on it from the first source cluster (S1). We will create table ‘t2’ from the second source (S4) and will do some transaction on it. We will check the schema creation and rows count from both the cluster. Also will do some other operations like delete on the same table from both the cluster.

From the first source MySQL server (S1): cluster-test01

From the second source MySQL server (S4): cluster-test06

Let’s first check whether table ‘t1’ is already created or not and then create a second table ‘t2’.

From the above image we can see that database ‘test1’, table ‘t1’ with ‘30’ rows created at source cluster is now available at replica cluster too. Also we are able to create another table ‘t2’. Let’s insert few rows into table ‘t2’.

So both the tables are now having ‘30’ rows. Let’s check the rows count from the other cluster.

So here also both the tables are having ‘30’ rows in it. So that means replication has been successfully established between two cluster.

Let’s do some transactions on the table ‘t1’. Here, we are deleting 6 rows from the table t1 (rows# 5, 8, 10, 15, 20, 25) from the source’s MySQL server.

Let’s check from the replica’s MySQL server:

From the above image we can see that rows#5, 8, 10, 15, 20, 25 are missing from the table ‘t1’.

Let’s insert those missing rows into the table ‘t1’ from the replica server (S4):

Let’s check the rows (S4):

Let’s check the rows availability from the Source cluster (S1):

From the above image, we can see that the missing rows are available in table ‘t1’ from source cluster too.

So we saw that any operations we do on the table data is seamlessly replicated to other cluster irrespective of where these changes have been made.

This concludes our discussion on third replication topology in MySQL NDB Cluster.