InnoDB ClusterSet Deployment With MySQLRouter

This blog post will cover the basic setup of the InnoDB ClusterSet environment, which provides disaster tolerance for InnoDB Cluster deployments by associating a primary InnoDB Cluster with one or more replicas in alternate locations/different data centers. InnoDB ClusterSet automatically manages replication from the primary cluster to the replica clusters via a specific ClusterSet Async replication channel. If the primary cluster becomes inaccessible due to a loss of network connectivity or a data center issue, you can make a replica cluster active in its place.

Now, let’s see in detail how exactly we can configure the topology.

We have used the sandbox environment available via MySQLShell utility for this setup.

Environment

Let’s set up the first cluster (“cluster1”)

•  Deploying the sandboxes.

•  Then, we need to perform some pre-checks before initiating the cluster.

Once all the instances are prepared, we can plan to create the cluster with the seed node. The “createcluster” command will perform all the hidden steps of initializing group replication, and later on, the other nodes join the group with distributed recovery/clone plugin.

InnoDB cluster is built on top of group replication which provides (automatic membership management, fault tolerance, and automatic failover). It provides us with an easy interface to deploy/manage the complex topologies with DR support.

• We will bootstrap the cluster with an initial node(“localhost:3308″).

Output:

• Here, we have successfully bootstrapped the first node. Next, the other nodes will join the cluster using the CLONE Plugin.

Output:

Output:

• At this stage, our first cluster is ready with all three nodes.

Output:

Let’s now proceed with the second cluster (“cluster2”) setup

• Deploying the sandboxes via MySqlShell.

•  Similarly, perform some pre-checks as we did for “cluster1” nodes.

•  Next, we will create the ClusterSet topology by triggering the sync on the node (127.0.0.1:3311) by existing cluster1 nodes. Node (127.0.0.1:3311) will be the Primary node for cluster2, and the rest of other nodes will join this node by Clone/Incremental process.

1) First, connect to “cluster1” node.

2) Here, “cluster1” join the ClusterSet topology,

Output:

3) Verifying the status.

Output:

 4) Now, Node (“127.0.0.1:3311″) will sync with the existing “cluster1” with Async process.

Output:

5) Next, the other nodes join the “cluster2” with the clone process.

6) Finally, checking the status of our clusterset environment.

Output:

Here, the ClusterSet topology is ready now with all six nodes.

In the next phase, we will bootstrap MySQLRouter with our newly created ClusterSet environment:

• First, we will generate a dedicated user for MySQLRouter monitoring/management.

Output:

• Bootstrap the router with the user (“router_usr) and router name (“Router1”).

We are using –-force here because without –-force mysqlrouter won’t recognize the clusterset. This will reconfigure the existing clusterset.

Here, we will see some useful information that later on is required to connect to a database or manage the services.

• Finally, start the mysqlrouter service:

Validating the connection route

•  Connect to the router port “6446” and create some demo table/data:

•  Connect to the router port “6447” for reading purposes. Here, the connection will be, by default, balanced among the number of nodes of the Primary Cluster(cluster1).

So, by default, all the connections will route to the default “Primary” Cluster, which, in our case, is “Clustrer1”; however, we can change the primary component based on the requirement.

Changing ClusterSet topology

Output:

• Changing the Primary cluster from “cluster1” to “cluster2:

Output:

• If we see the output again, we can observe that  “clusterRole:PRIMARY” is shifted to “cluster2”.

Output: