How To Setup Multi Data Centre MySQL Clustering

How many times have we come across a requirement of mysql cluster spread across different data centres , and each data centre offering its own failover strategy , and failover between data centre to data centre upon failure ? We have also come across such requirements however we implemented such environments with a combination of mysql asynchronous replication and clustering and always thought if mysql would do something in-house to take care of asynchronous replication as well and we found MySQL InnoDB clusterSet .

Why Should you choose MySQL InnoDB Cluster

1. Extended solution of InnoDB Cluster to manage asynchronous replication between data centres
2. DR setup using mysql between multiple data centres
3. Single Primary read-write setup and multiple replica setup
4. MySQL to orchestrator the asynchronous replication between data centres
5. Availability is important than consistency
6. Controlled or manual (emergency) failover is acceptable by the application

so a regular multi DB setup using mysql would be something as below:

Why you should not choose InnoDB ClusterSet ?

1. ClusterSet should be chosen only if availability is more important than consistency
2. InnoDB ClusterSet doesn’t failover to replica Cluster its a manual process to force a failover to replica cluster
3. A ClusterSet cannot have 5.7 and 8.0 InnoDB Clusters.
4. InnoDB Cluster wouldn’t manage the existing manually configured asynchronous replication
5. Only Single read-write primary cluster and all the replica clusters are for read only purposes.
6. multi-primary is not supported because consistency is not guaranteed
7. One InnoDB Cluster can be part of only one InnoDB ClusterSet
8. InnoDB ClusterSet only supports asynchronous replication, and cannot use semisynchronous replication.

NOTE : If Consistency is important then Deploy Innodb Cluster with one node in each data centre but be aware to compromise on significant latency in write performance

MySQL ClusterSet Architecture Overview

On a very high level ClusterSet is a group of Individual InnoDB Clusters offered by MySQL which manages asynchronous replication between the clusters by itself. The only biggest challenge here is if the primary cluster/Data centre goes down completely we have to manually trigger a failover or script to failover to different replica set without worrying about data consistency. All the routers would be bootstrapped to respective primary nodes so application

Benefits here using InnoDB ClusterSet

1. Can be easily configured using MySQLShell
2. Migration from Existing InnoDB Cluster to ClusterSet is possible
3. Asynchronous replication is managed by InnoDB Cluster itself
4. Adding Data centres and nodes to Cluster is easy using mysqlshell using Clone plugin
5. One setting up first node in any data centre takes time as it has to clone data over the network however it uses local primary node to clone the data for the replica nodes within the same DC
6. In an ideal situation you should have 2 sets of routers one for RW traffic and one for RO Traffic so we can make use of replica InnoDB clusters for RO traffic.

In this blog we just wanted to give an introduction of MySQL InnoDB clusterSet and our initial requirement analysis for choosing this technology for an implementation. We would be sharing the setup procedure in the series of next blog in coming days along with how do we force failover ? How do we check cluster status and how does the overall metadata looks like ? etc. Meanwhile feel free to go through mysql documentation if you want to further learn about the technology and if you would like to discuss on whether use case is a suitable one or if you have any HA requirements in MySQL please contact us or send a email to support@genexdbs.com.