Setting Up an InnoDB Cluster for High Availability and Scalability Using MySQL Shell

‍

As websites and applications grow, using just one MySQL server may not be enough. If your database goes down, your whole app might stop working. That’s why many teams look for ways to keep their database available all the time — even if something breaks. One good solution for this is InnoDB Cluster.

In this blog, we’ll explain how to set up an InnoDB Cluster using MySQL Shell. This tool makes it easier to manage your MySQL setup and helps with things like creating and maintaining clusters.

We’ll also look at how Group Replication works behind the scenes to keep data in sync across multiple servers. If you're new to InnoDB Cluster or just want a clear step-by-step guide, this post will help you get started.

Introduction to InnoDB Cluster

An InnoDB Cluster is a MySQL database cluster that provides high availability and scalability by using Group Replication (GR). It combines MySQL Shell, Group Replication, and MySQL Router to deliver an integrated solution that ensures data integrity, fault tolerance, and scalability.

Let’s break down the components and clarify about Group Replication and how it works with the InnoDB Cluster.

Group Replication:

Group Replication is a MySQL plugin that provides synchronous multi-master replication. It ensures that all nodes in a replication group are consistent and that changes made on one node are committed to all other nodes in the group.

Key features of Group Replication include:

• Synchronous Replication: When a transaction is committed on one node, it is ensured to be replicated to other nodes in the group before the transaction is considered complete.
• Automatic Failover: If a primary node fails, another node in the group is automatically promoted to be the new primary, minimizing downtime.
• Fault Tolerance: Nodes in the group continuously check each other’s availability, allowing the system to recover gracefully from node failures.

‍

‍

InnoDB Cluster:

InnoDB Cluster is a complete solution that integrates Group Replication with MySQL Shell and MySQL Router to form a high-availability MySQL setup.It provides tools for provisioning, management, and monitoring the cluster in a seamless way.

The key components of InnoDB Cluster are:

• MySQL Shell: A command-line tool used to manage the cluster. It helps in provisioning the cluster, adding/removing nodes, and checking the cluster’s health.
• Group Replication (GR): This is the underlying technology that ensures data consistency and availability by replicating data synchronously across all nodes in the cluster.
• MySQL Router: A lightweight, high-performance proxy that provides transparent routing to the cluster nodes. MySQL Router ensures that applications are automatically directed to the correct node for read and write operations, improving load balancing and failover capabilities.

‍

‍

Group Replication = The technology that keeps the data synchronized and provides fault tolerance.

InnoDB Cluster = [Group Replication + MySQL Shell (for management) + MySQL Router (for traffic routing and failover)]. 

‍

Prerequisites for Setting Up an InnoDB Cluster

Before we start, ensure that we meet the following prerequisites

• MySQL 8.0 or Higher: InnoDB Cluster was introduced in MySQL 5.7, but it is recommended to use MySQL 8.0 or higher, as MySQL 5.7 is deprecated.
• MySQL Shell: We must have MySQL Shell installed on the client machine from where we’ll manage the cluster.
• Network Connectivity: The MySQL servers should be able to communicate with each other, and the following ports must be open:

‍

‍

• Cluster Admin User: We will need to create a user with sufficient privileges to manage the cluster. Use the following SQL commands to create the necessary user and grant privileges:

‍

CREATE USER 'mydbops_cluster_admin'@'%' IDENTIFIED BY 'XXXXXXXX';

GRANT CLONE_ADMIN, CONNECTION_ADMIN, CREATE USER, EXECUTE, FILE, GROUP_REPLICATION_ADMIN, 
      PERSIST_RO_VARIABLES_ADMIN, PROCESS, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, 
      REPLICATION_APPLIER, REPLICATION_SLAVE_ADMIN, ROLE_ADMIN, SELECT, SHUTDOWN, 
      SYSTEM_VARIABLES_ADMIN ON *.* TO 'mydbops_cluster_admin'@'%' WITH GRANT OPTION;

GRANT DELETE, INSERT, UPDATE ON mysql.* TO 'mydbops_cluster_admin'@'%' WITH GRANT OPTION;

GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, 
      DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SHOW VIEW, 
      TRIGGER, UPDATE ON mysql_innodb_cluster_metadata.* TO 'mydbops_cluster_admin'@'%' WITH GRANT OPTION;

GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, 
      DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SHOW VIEW, 
      TRIGGER, UPDATE ON mysql_innodb_cluster_metadata_bkp.* TO 'mydbops_cluster_admin'@'%' WITH GRANT OPTION;

GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, 
      DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SHOW VIEW, 
      TRIGGER, UPDATE ON mysql_innodb_cluster_metadata_previous.* TO 'mydbops_cluster_admin'@'%' WITH GRANT OPTION;

‍

• MySQL Server Configuration: Ensure the following variables are set in the MySQL configuration file (my.cnf or my.ini) on each node:

‍

binlog_transaction_dependency_tracking = WRITESET
enforce_gtid_consistency = ON
gtid_mode = ON
server_id = <unique ID>  # Ensure a unique server ID for each node

‍

Flowchart:

Installing and Configuring MySQL for InnoDB Cluster Using MySQL Shell

Our test environment:

‍

Step 1: Install MySQL and MySQL Shell

Install MySQL Server on each of the nodes (at least three servers) if not already installed.

‍

On Ubuntu/Debian:

apt-get update
apt-get install mysql-server

‍

Install MySQL Shell on our local machine:

apt-get install mysql-shell

‍

Step 2: Set Up the Cluster Using MySQL Shell

Now, let’s create the cluster using MySQL Shell. Login to MySQL Shell on the first node:

‍

root@Node_1:/home/ubuntu# mysqlsh
MySQL Shell 8.0.41

Copyright (c) 2016, 2025, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.
MySQL  JS > shell.connect('mydbops_cluster_admin@localhost:3306')
Creating a session to 'mydbops_cluster_admin@localhost:3306'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 27
Server version: 8.0.41-0ubuntu0.22.04.1 (Ubuntu)
No default schema selected; type \use <schema> to set one.
<ClassicSession:mydbops_cluster_admin@localhost:3306>
MySQL  localhost:3306 ssl  JS >

‍

Provision the First Node: The first step in creating the cluster is provisioning a MySQL instance. On the first node, run:

MySQL  localhost:3306 ssl  JS > cluster = dba.createCluster('mydbops_blog')
A new InnoDB Cluster will be created on instance 'Node_1:3306'.

Validating instance configuration at localhost:3306...

This instance reports its own address as Node_1:3306

Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using 'Node_1:3306'. Use the localAddress option to override.

* Checking connectivity and SSL configuration...

Creating InnoDB Cluster 'mydbops_blog' on 'Node_1:3306'...

Adding Seed Instance...
Cluster successfully created. Use Cluster.addInstance() to add MySQL instances.
At least 3 instances are needed for the cluster to be able to withstand up to
one server failure.

<Cluster:mydbops_blog>
MySQL  localhost:3306 ssl  JS > cluster.status()
{
    "clusterName": "mydbops_blog",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "Node_1:3306",
        "ssl": "REQUIRED",
        "status": "OK_NO_TOLERANCE",
        "statusText": "Cluster is NOT tolerant to any failures.",
        "topology": {
            "Node_1:3306": {
                "address": "Node_1:3306",
                "memberRole": "PRIMARY",
                "mode": "R/W",
                "readReplicas": {},
                "replicationLag": "applier_queue_applied",
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.41"
            }
        },
        "topologyMode": "Single-Primary"
    },
    "groupInformationSourceMember": "Node_1:3306"
}
MySQL  localhost:3306 ssl  JS >

‍

Add Nodes to the Cluster: After provisioning the first node, add the other nodes to the cluster:

‍

Add the second node:

The command cluster.addInstance('mydbops_cluster_admin@Node_2:3306') is used to add Node_2 to the InnoDB Cluster. 

The Clone method starts, transferring data from Node_1 to Node_2. The process involves stages like data removal, file copying, and server restart. The clone finishes successfully, and Node_2 is synchronized with Node_1. 

After Node_2 is added, the cluster's status is checked, showing that Node_1 is the primary node (R/W) and Node_2 is the secondary node (R/O). 

‍

MySQL  localhost:3306 ssl  JS > cluster.addInstance('mydbops_cluster_admin@Node_2:3306')

NOTE: The target instance 'Node_2:3306' has not been pre-provisioned (GTID set is empty). The Shell is unable to decide whether incremental state recovery can correctly provision it.
The safest and most convenient way to provision a new instance is through automatic clone provisioning, which will completely overwrite the state of 'Node_2:3306' with a physical snapshot from an existing cluster member. To use this method by default, set the 'recoveryMethod' option to 'clone'.

The incremental state recovery may be safely used if you are sure all updates ever executed in the cluster were done with GTIDs enabled, there are no purged transactions and the new instance contains the same GTID set as the cluster or a subset of it. To use this method by default, set the 'recoveryMethod' option to 'incremental'.


Please select a recovery method [C]lone/[I]ncremental recovery/[A]bort (default Clone): C
Validating instance configuration at Node_2:3306...

This instance reports its own address as Node_2:3306

Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using 'Node_2:3306'. Use the localAddress option to override.

* Checking connectivity and SSL configuration...
A new instance will be added to the InnoDB Cluster. Depending on the amount of
data on the cluster this might take from a few seconds to several hours.

Adding instance to the cluster...

Monitoring recovery process of the new cluster member. Press ^C to stop monitoring and let it continue in background.
Clone based state recovery is now in progress.

NOTE: A server restart is expected to happen as part of the clone process. If the
server does not support the RESTART command or does not come back after a
while, you may need to manually start it back.

* Waiting for clone to finish...
NOTE: Node_2:3306 is being cloned from Node_1:3306
** Stage DROP DATA: Completed
** Clone Transfer
    FILE COPY  ############################################################  100%  Completed
    PAGE COPY  ############################################################  100%  Completed
    REDO COPY  ############################################################  100%  Completed

NOTE: Node_2:3306 is shutting down...

* Waiting for server restart... ready
* Node_2:3306 has restarted, waiting for clone to finish...
** Stage RESTART: Completed
* Clone process has finished: 74.70 MB transferred in about 1 second (~74.70 MB/s)

State recovery already finished for 'Node_2:3306'

The instance 'Node_2:3306' was successfully added to the cluster.

MySQL  localhost:3306 ssl  JS >
MySQL  localhost:3306 ssl  JS > cluster.status()
{
    "clusterName": "mydbops_blog",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "Node_1:3306",
        "ssl": "REQUIRED",
        "status": "OK_NO_TOLERANCE",
        "statusText": "Cluster is NOT tolerant to any failures.",
        "topology": {
            "Node_2:3306": {
                "address": "Node_2:3306",
                "memberRole": "SECONDARY",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLag": "applier_queue_applied",
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.41"
            },
            "Node_1:3306": {
                "address": "Node_1:3306",
                "memberRole": "PRIMARY",
                "mode": "R/W",
                "readReplicas": {},
                "replicationLag": "applier_queue_applied",
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.41"
            }
        },
        "topologyMode": "Single-Primary"
    },
    "groupInformationSourceMember": "Node_1:3306"
}

‍

Add the third node:

MySQL  localhost:3306 ssl  JS > cluster.addInstance('mydbops_cluster_admin@Node_3:3306')

NOTE: The target instance 'Node_3:3306' has not been pre-provisioned (GTID set is empty). The Shell is unable to decide whether incremental state recovery can correctly provision it.
The safest and most convenient way to provision a new instance is through automatic clone provisioning, which will completely overwrite the state of 'Node_3:3306' with a physical snapshot from an existing cluster member. To use this method by default, set the 'recoveryMethod' option to 'clone'.

The incremental state recovery may be safely used if you are sure all updates ever executed in the cluster were done with GTIDs enabled, there are no purged transactions and the new instance contains the same GTID set as the cluster or a subset of it. To use this method by default, set the 'recoveryMethod' option to 'incremental'.


Please select a recovery method [C]lone/[I]ncremental recovery/[A]bort (default Clone): C
Validating instance configuration at Node_3:3306...

This instance reports its own address as Node_3:3306

Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using 'Node_3:3306'. Use the localAddress option to override.

* Checking connectivity and SSL configuration...
A new instance will be added to the InnoDB Cluster. Depending on the amount of
data on the cluster this might take from a few seconds to several hours.

Adding instance to the cluster...

Monitoring recovery process of the new cluster member. Press ^C to stop monitoring and let it continue in background.
Clone based state recovery is now in progress.

NOTE: A server restart is expected to happen as part of the clone process. If the
server does not support the RESTART command or does not come back after a
while, you may need to manually start it back.

* Waiting for clone to finish...
NOTE: Node_3:3306 is being cloned from Node_1:3306
** Stage DROP DATA: Completed
** Clone Transfer
    FILE COPY  ############################################################  100%  Completed
    PAGE COPY  ############################################################  100%  Completed
    REDO COPY  ############################################################  100%  Completed

NOTE: Node_3:3306 is shutting down...

* Waiting for server restart... ready
* Node_3:3306 has restarted, waiting for clone to finish...
** Stage RESTART: Completed
* Clone process has finished: 74.70 MB transferred in about 1 second (~74.70 MB/s)

State recovery already finished for 'Node_3:3306'

The instance 'Node_3:3306' was successfully added to the cluster.

MySQL  localhost:3306 ssl  JS >

MySQL  localhost:3306 ssl  JS > cluster.status()
{
    "clusterName": "mydbops_blog",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "Node_1:3306",
        "ssl": "REQUIRED",
        "status": "OK",
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
        "topology": {
            "Node_2:3306": {
                "address": "Node_2:3306",
                "memberRole": "SECONDARY",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLag": "applier_queue_applied",
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.41"
            },
            "Node_3:3306": {
                "address": "Node_3:3306",
                "memberRole": "SECONDARY",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLag": "applier_queue_applied",
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.41"
            },
            "Node_1:3306": {
                "address": "Node_1:3306",
                "memberRole": "PRIMARY",
                "mode": "R/W",
                "readReplicas": {},
                "replicationLag": "applier_queue_applied",
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.41"
            }
        },
        "topologyMode": "Single-Primary"
    },
    "groupInformationSourceMember": "Node_1:3306"
}

‍

We should see that the cluster is online and all nodes are part of the cluster.

Scaling an InnoDB Cluster for Performance

Once the cluster is running, we might need to scale it to handle increased traffic or provide redundancy. Adding a new node to the cluster is easy with MySQL Shell.

• Provision a New MySQL Server: ‍
  • Install and configure MySQL on the new node, making sure the configuration matches the existing nodes.‍
• ‍Add the New Node to the Cluster:

‍

cluster.addInstance('<user>@<server>:<Port>');

‍

• Monitor the Cluster: ‍
  • Use the cluster.status() command to check the health of the cluster. We can also monitor replication status:

‍

cluster.status();

‍

Monitoring and Managing the InnoDB Cluster

To ensure our InnoDB Cluster runs smoothly, we need to monitor its health regularly. MySQL Shell provides built-in commands to help in monitoring and managing the cluster.

• Check Cluster Status: ‍
  • Use the following command to see the current status of our cluster and all its nodes:

 MySQL  localhost:3306 ssl  JS > cluster.status()
{
    "clusterName": "mydbops_blog",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "Node_1:3306",
        "ssl": "REQUIRED",
        "status": "OK",
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
        "topology": {
            "Node_2:3306": {
                "address": "Node_2:3306",
                "memberRole": "SECONDARY",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLag": "applier_queue_applied",
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.41"
            },
            "Node_3:3306": {
                "address": "Node_3:3306",
                "memberRole": "SECONDARY",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLag": "applier_queue_applied",
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.41"
            },
            "Node_1:3306": {
                "address": "Node_1:3306",
                "memberRole": "PRIMARY",
                "mode": "R/W",
                "readReplicas": {},
                "replicationLag": "applier_queue_applied",
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.41"
            }
        },
        "topologyMode": "Single-Primary"
    },
    "groupInformationSourceMember": "Node_1:3306"
}

‍

• Reboot the cluster:‍
  • If you want to reboot the entire cluster due to unsafe shutdown:
    

MySQL  localhost:3306 ssl  JS > cluster = dba.rebootClusterFromCompleteOutage()
Restoring the Cluster 'mydbops_blog' from complete outage...

Cluster instances: 'Node_2:3306' (OFFLINE), 'Node_3:3306' (OFFLINE), 'Node_1:3306' (OFFLINE)
Waiting for instances to apply pending received transactions...
Validating instance configuration at localhost:3306...

This instance reports its own address as Node_1:3306

Instance configuration is suitable.
* Waiting for seed instance to become ONLINE...
Node_1:3306 was restored.
Validating instance configuration at Node_2:3306...

This instance reports its own address as Node_2:3306

Instance configuration is suitable.
Rejoining instance 'Node_2:3306' to cluster 'mydbops_blog'...

Re-creating recovery account...
NOTE: User 'mysql_innodb_cluster_2'@'%' already existed at instance 'Node_1:3306'. It will be deleted and created again with a new password.

* Waiting for the Cluster to synchronize with the PRIMARY Cluster...
** Transactions replicated  ############################################################  100%

The instance 'Node_2:3306' was successfully rejoined to the cluster.

Validating instance configuration at Node_3:3306...

This instance reports its own address as Node_3:3306

Instance configuration is suitable.
Rejoining instance 'Node_3:3306' to cluster 'mydbops_blog'...

Re-creating recovery account...
NOTE: User 'mysql_innodb_cluster_3'@'%' already existed at instance 'Node_1:3306'. It will be deleted and created again with a new password.

* Waiting for the Cluster to synchronize with the PRIMARY Cluster...
** Transactions replicated  ############################################################  100%

The instance 'Node_3:3306' was successfully rejoined to the cluster.

The Cluster was successfully rebooted.

<Cluster:mydbops_blog>
 MySQL  localhost:3306 ssl  JS >

‍

• Remove a Node: ‍
  • If you need to remove a node from the cluster, use the following command:

MySQL  localhost:3306 ssl  JS > cluster.removeInstance('Node_2:3306');
The instance will be removed from the InnoDB Cluster.

* Waiting for instance 'Node_2:3306' to synchronize with the primary...
** Transactions replicated  ############################################################  100%

* Instance 'Node_2:3306' is attempting to leave the cluster...

The instance 'Node_2:3306' was successfully removed from the cluster.

 MySQL  localhost:3306 ssl  JS > cluster.status();
{
    "clusterName": "mydbops_blog",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "Node_1:3306",
        "ssl": "REQUIRED",
        "status": "OK_NO_TOLERANCE",
        "statusText": "Cluster is NOT tolerant to any failures.",
        "topology": {
            "Node_3:3306": {
                "address": "Node_3:3306",
                "memberRole": "SECONDARY",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLag": "applier_queue_applied",
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.41"
            },
            "Node_1:3306": {
                "address": "Node_1:3306",
                "memberRole": "PRIMARY",
                "mode": "R/W",
                "readReplicas": {},
                "replicationLag": "applier_queue_applied",
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.41"
            }
        },
        "topologyMode": "Single-Primary"
    },
    "groupInformationSourceMember": "Node_1:3306"
}

‍

• Switch to Multi-Primary:‍
  • We can switch from single primary to multi-primary server using the following command. 

MySQL  localhost:3306 ssl  JS > cluster.switchToMultiPrimaryMode()
Switching cluster 'mydbops_blog' to Multi-Primary mode...

Instance 'Node_1:3306' remains PRIMARY.
Instance 'Node_3:3306' was switched from SECONDARY to PRIMARY.
Instance 'Node_2:3306' was switched from SECONDARY to PRIMARY.

The cluster successfully switched to Multi-Primary mode.
 MySQL  localhost:3306 ssl  JS >
 MySQL  localhost:3306 ssl  JS > cluster.status()
{
    "clusterName": "mydbops_blog",
    "defaultReplicaSet": {
        "name": "default",
        "ssl": "REQUIRED",
        "status": "OK",
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
        "topology": {
            "Node_2:3306": {
                "address": "Node_2:3306",
                "memberRole": "PRIMARY",
                "mode": "R/W",
                "readReplicas": {},
                "replicationLag": "applier_queue_applied",
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.41"
            },
            "Node_3:3306": {
                "address": "Node_3:3306",
                "memberRole": "PRIMARY",
                "mode": "R/W",
                "readReplicas": {},
                "replicationLag": "applier_queue_applied",
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.41"
            },
            "Node_1:3306": {
                "address": "Node_1:3306",
                "memberRole": "PRIMARY",
                "mode": "R/W",
                "readReplicas": {},
                "replicationLag": "applier_queue_applied",
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.41"
            }
        },
        "topologyMode": "Multi-Primary"
    },
    "groupInformationSourceMember": "Node_3:3306"
}

‍

Best Practices for Running an InnoDB Cluster in Production

Here are some best practices for running our InnoDB Cluster in production:

• Automate Backups: Use tools like Xtrabackup or MySQL Enterprise Backup to perform regular backups.
• Monitor System Resources: Regularly monitor CPU, RAM, IO, disk usage, and network latency between nodes.
• Use a Load Balancer: Distribute incoming traffic across the cluster nodes to ensure optimal resource utilization. It is highly preferred to right load balancing in the MySQL Router.
• Minimum Three MySQL Nodes: A typical setup involves at least three MySQL nodes (servers), for better failover. A minimum of three nodes are required for Quorum.
• Sufficient Hardware Resources: Ensure all our servers have the same CPU, RAM, IO, Disk type and disk space for optimal performance.

Notable limitation:

• InnoDB Engine: In InnoDB Cluster, only the InnoDB storage engine is supported. This is because Group Replication, the underlying technology for clustering, requires support for transactions, row-level locking, and crash recovery.
• Primary key: In a Group Replication, each table in the database must have a primary key. This is a requirement because Group Replication uses GTIDs (Global Transaction Identifiers) to track and replicate changes. A primary key is essential for ensuring that rows are uniquely identifiable, which helps with data consistency and conflict resolution during replication. 
• SERIALIZABLE Isolation Level: The SERIALIZABLE isolation level is the strictest level of isolation in MySQL and ensures that transactions are executed in a way that prevents phenomena like dirty reads, non-repeatable reads, and phantom reads. But, in a multi-primary group, the SERIALIZABLE isolation level is not supported by default. 
• CASCADING Foreign Key Constraints: In multi-primary mode (where multiple nodes can handle write operations), Group Replication does not support tables with multi-level foreign key dependencies, especially if those tables have CASCADING foreign key constraints defined. 
• Cluster Max Size: In InnoDB Cluster, the maximum number of nodes that can be part of the cluster is limited to 9 nodes.

Setting up and maintaining an InnoDB Cluster using MySQL Shell is a powerful and efficient way to ensure high availability and scalability for our MySQL database infrastructure. MySQL Shell simplifies the process of provisioning, managing, and monitoring the cluster. By following the steps outlined in this guide, we can create a robust, fault-tolerant MySQL environment that scales as our application grows.

Get Expert Help with Your InnoDB Cluster Setup

Mydbops provide specialized InnoDB Cluster Consulting and Support Services to ensure your MySQL database is optimized for performance, reliability, and scalability. Our team of experts is ready to assist you in setting up, configuring, and maintaining your InnoDB Cluster.

Contact us today to learn how we can help you streamline your MySQL setup and ensure your database runs seamlessly.