Changing InnoDB Cluster Topology in MySQL

By default, an InnoDB Cluster runs in single-primary mode, where the cluster has one primary server that accepts read and writes queries (R/W), and all of the remaining instances in the cluster accept only read queries (R/O). When you configure a cluster to run in multi-primary mode, all of the instances in the cluster are primaries, which means that they accept both read and write queries (R/W).

You can change the mode (sometimes described as the topology) in which a cluster is running in between single-primary and multi-primary using the following operations:

Cluster.switchToMultiPrimaryMode(), which switches the cluster to multi-primary mode. All instances become primaries.
Cluster.switchToSinglePrimaryMode([instance]), which switches the cluster to single-primary mode. If an instance is specified, it becomes the primary and all the other instances become secondary. If an instance is not specified, the new primary is the instance with the highest member weight (and the lowest UUID in case of a tie on member weight).

In the previous blog, we already created InnoDB Cluster with 3 MySQL instances Click here to read more. We will use the same InnoDB Cluster to change cluster mode or topology.

Step 1. Validate InnoDB Cluster: Login to mysqlsh and validate all mysql instance in InnoDB Cluster are online and accessible.

[root@test-machine01 ~]#
[root@test-machine01 ~]# mysqlsh root@localhost:3306
MySQL Shell 8.0.23

Copyright (c) 2016, 2021, 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.
Creating a session to 'root@localhost:3306'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 3410
Server version: 8.0.22 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
 MySQL  localhost:3306 ssl  JS >
 MySQL  localhost:3306 ssl  JS > var cluster=dba.getCluster();
MySQL  localhost:3306 ssl  JS >
 MySQL  localhost:3306 ssl  JS > cluster.status();
{
    "clusterName": "innodbclustertest",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "test-machine01:3306",
        "ssl": "REQUIRED",
        "status": "OK",
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
        "topology": {
            "test-machine01:3306": {
                "address": "test-machine01:3306",
                "mode": "R/W",
                "readReplicas": {},
                "replicationLag": null,
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.22"
            },
            "test-machine01:3307": {
                "address": "test-machine01:3307",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLag": null,
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.22"
            },
            "test-machine02:3306": {
                "address": "test-machine02:3306",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLag": null,
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.22"
            }
        },
        "topologyMode": "Single-Primary"
    },
    "groupInformationSourceMember": "test-machine01:3306"
}
 MySQL  localhost:3306 ssl  JS >

Step 2. Changing to Multi-Primary Mode: We will use function switchToMultiPrimaryMode() to perform the switch to Multi-Primary Mode. Once the process is done you will notice that all InnoDB Cluster mysql instances will convert to Primary and can accept Read/Write operations.

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

Instance 'test-machine02:3306' was switched from SECONDARY to PRIMARY.
Instance 'test-machine01:3306' remains PRIMARY.
Instance 'test-machine01:3307' was switched from SECONDARY to PRIMARY.

The cluster successfully switched to Multi-Primary mode.
 MySQL  localhost:3306 ssl  JS >
 MySQL  localhost:3306 ssl  JS >

 MySQL  localhost:3306 ssl  JS >
 MySQL  localhost:3306 ssl  JS > cluster.status();
{
    "clusterName": "innodbclustertest",
    "defaultReplicaSet": {
        "name": "default",
        "ssl": "REQUIRED",
        "status": "OK",
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
        "topology": {
            "test-machine01:3306": {
                "address": "test-machine01:3306",
                "mode": "R/W",
                "readReplicas": {},
                "replicationLag": null,
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.22"
            },
            "test-machine01:3307": {
                "address": "test-machine01:3307",
                "mode": "R/W",
                "readReplicas": {},
                "replicationLag": null,
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.22"
            },
            "test-machine02:3306": {
                "address": "test-machine02:3306",
                "mode": "R/W",
                "readReplicas": {},
                "replicationLag": null,
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.22"
            }
        },
        "topologyMode": "Multi-Primary"
    },
    "groupInformationSourceMember": "test-machine01:3306"
}
 MySQL  localhost:3306 ssl  JS >

Step 3. Changing to Single-Primary Mode: We will use function switchToSinglePrimaryMode() to perform the switch to Single-Primary Mode. Once the process is done you will notice that the One InnoDB Cluster mysql instance will convert to Primary and can accept Read/Write operation and the remaining two mysql instances will change to SECONDARY with read-only mode.

MySQL  localhost:3306 ssl  JS >
MySQL  localhost:3306 ssl  JS > cluster.switchToSinglePrimaryMode();
Switching cluster 'innodbclustertest' to Single-Primary mode...

Instance 'test-machine02:3306' remains PRIMARY.
Instance 'test-machine01:3306' was switched from PRIMARY to SECONDARY.
Instance 'test-machine01:3307' was switched from PRIMARY to SECONDARY.

WARNING: The cluster internal session is not the primary member anymore. For cluster management operations please obtain a fresh cluster handle using dba.getCluster().

WARNING: Existing connections that expected a R/W connection must be disconnected, i.e. instances that became SECONDARY.

The cluster successfully switched to Single-Primary mode.
 MySQL  localhost:3306 ssl  JS >
 MySQL  localhost:3306 ssl  JS >


 MySQL  localhost:3306 ssl  JS >
 MySQL  localhost:3306 ssl  JS >  cluster.status();
{
    "clusterName": "innodbclustertest",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "test-machine02:3306",
        "ssl": "REQUIRED",
        "status": "OK",
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
        "topology": {
            "test-machine01:3306": {
                "address": "test-machine01:3306",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLag": null,
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.22"
            },
            "test-machine01:3307": {
                "address": "test-machine01:3307",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLag": null,
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.22"
            },
            "test-machine02:3306": {
                "address": "test-machine02:3306",
                "mode": "R/W",
                "readReplicas": {},
                "replicationLag": null,
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.22"
            }
        },
        "topologyMode": "Single-Primary"
    },
    "groupInformationSourceMember": "test-machine01:3306",
    "metadataServer": "test-machine02:3306"
}

References: Working with InnoDB Cluster: https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-innodb-cluster-working-with-cluster.html
Part I: Adopting a Group Replication Deployment into InnoDB Cluster: https://dbsguru.com/adopting-a-group-replication-deployment-into-innodb-cluster/
Part II: How to Setup MySql InnoDB Cluster: https://dbsguru.com/how-to-setup-mysql-innodb-cluster/

This document is only for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.

Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the latest update. Click here to know more about our pursuit.

INNODB CLUSTER, MYSQL, REPLICATION

Changing InnoDB Cluster Topology in MySQL

Related Articles