GTID creation in Normal MySQL and MySQL Group Replication | Configure async slave from GR cluster without single point of failure ( multi source )

We know well the MySQL Group Replication cluster is functionally working based on the GTID . GTID is mandatory for group replication . Usually, GTID is the combination of source_id ( or server_uuid ) and transaction_id . Do you think the same approach is following in the MySQL Group Replication as well ? Well, this blog mainly going to focus on the below three topics .

  • How the binary log GTID events are being generated in the normal MySQL instance ?
  • How the binary log GTID events are being generated in the MySQL Group Replication cluster ?
  • How to configure the asynchronous replication from Group replication cluster without single point of failure ?

How the binary log GTID events are being generated in normal MySQL instance ?

As I told, the Global Transaction Identifier ( GTID ) will be the combination of server_uuid and transaction_id . [server_uuid]:[transaction_id] . Let’s take the example, I have locally installed a MySQL instance with GTID .

From the above screenshot, we can see the binary log GTID events are represented with the combination of server_uuid and transaction_id .

  • aac1c84b-626e-11ea-bcc8-02bb561f2826 : server_uuid
  • 2980703,2980704,2980705,2980706,2980707 : transaction_id’s

How the binary log GTID events are being generated in MySQL Group Replication cluster ?

Now, Let’s take a look on the MySQL Group Replication servers . I have locally configured a three node setup .

Theoretically, .

At MySQL Group Replication, the GTID is not the combination of server_uuid and transaction_id . Instead of that, it is the combination of group_replication_group_name and transaction_id .

All the servers belongs to the particular group should have the same group_name. The value of group_replication_group_name must be a valid UUID. It can be generated with command uuidgen OR select uuid() ; .

Let’s see the example ,

at node1 ,

at node2,

at node3 ,

from the above screenshots, we can clear the below things .

  • All the three servers in the same group can have different server UUID (server_uuid) .
  • All the three servers in the same group should have the same group name ( group_replication_group_name ).
  • All the three servers in the same group have the GTID events with the combination of [group_replication_group_name] : [transaction_id]

Clear, right ?

How to configure the asynchronous replication from Group replication cluster without single point of failure ?

The requirement is,

  • I have three node cluster sakthi1 , sakthi2 , sakthi3 . I wanted to configure the asynchronous slave ( D1 ) for DR / Reporting purpose .
  • Assume, D1( slave ) is configured as slave under the node sakthi2 ( master ) . If the master node sakthi2 fails and removed from the cluster, it should not affect the D1 replication . The server D1 still need to receive the live updates .
  • D1 should not be single point of failure until the complete cluster fails .

Is that possible ? Yes .

We can configure the MUTI-SOURCE replication with all the three nodes ( sakth1, sakthi2, sakthi3 ) as master from cluster . So, that if one node fails, the data will be keep apply from other nodes .

Replicating the same data from three different nodes will cause any replications errors ( DUPLICATE ENTRY ) ?

NO. Because the replication is happening based on the GTID and, the GTID is same in all nodes in cluster . The slave SQL thread will not execute the executed GTID events again and again .

As per the plan, I have created the replication with 3 channels ( dr1, dr2, dr3 ) .

The final output from my Asynchronous slave is ,

I hope this blog will help someone, who is working on the MySQL Group Replication . Will be come up with my next blog as much as possible .

Thanks !!!

One thought on “GTID creation in Normal MySQL and MySQL Group Replication | Configure async slave from GR cluster without single point of failure ( multi source )

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create your website at WordPress.com
Get started
%d bloggers like this: