InnoDB Cluster Built-in Functions: A Practical Guide for MySQL Group Replication Management

InnoDB Cluster is Oracle's official tool for managing MySQL group replication, making deployment, operation, and development much simpler.

Test environment : three nodes on the same host – Node A (192.168.2.210:3601, primary), Node B (192.168.2.210:3602, secondary), Node C (192.168.2.210:3603, secondary).

1. get_cluster() – Retrieves the cluster object for subsequent management.

MySQL  debian-ytt1:3601 ssl  Py > c1 = dba.get_cluster();
MySQL  debian-ytt1:3601 ssl  Py > c1

2. describe() – Returns a concise JSON view of the cluster’s current topology, node IPs, and roles.

MySQL  debian-ytt1:3601 ssl  Py > c1.describe()
{
    "clusterName": "ytt_mgr",
    "defaultReplicaSet": {
        "name": "default",
        "topology": [
            {"address": "127.0.0.1:3601", "label": "127.0.0.1:3601", "role": "HA"},
            {"address": "127.0.0.1:3602", "label": "127.0.0.1:3602", "role": "HA"},
            {"address": "127.0.0.1:3603", "label": "127.0.0.1:3603", "role": "HA"}
        ],
        "topologyMode": "Single-Primary"
    }
}

3. list_routers() – Shows the current MySQL Router configuration, including read/write ports and version.

MySQL  debian-ytt1:3601 ssl  Py > c1.list_routers()
{
    "clusterName": "ytt_mgr",
    "routers": {
        "debian-ytt1::system": {
            "hostname": "debian-ytt1",
            "lastCheckIn": "2020-07-15 11:43:18",
            "roPort": 6447,
            "roXPort": 64470,
            "rwPort": 6446,
            "rwXPort": 64460,
            "version": "8.0.21"
        }
    }
}

4. set_option() – Modifies global cluster parameters, e.g., changing the cluster name.

It can also change the consistency level from eventual to strong consistency.

5. options() – Retrieves all current cluster parameters for verification.

Verification can also be done via MySQL Router, checking read/write node consistency settings.

6. set_instance_option() – Sets options for a single instance (e.g., assigning readable labels to each node).

Viewing the label key confirms the change.

7. set_primary_instance() – Promotes a secondary node to primary (e.g., promoting Node B and demoting Node A).

MySQL  debian-ytt1:3601 ssl  Py > c1.status().get("defaultReplicaSet").get("topology")
{
    "node_a": {"address": "127.0.0.1:3601", "mode": "R/O", "role": "HA", "status": "ONLINE", "version": "8.0.21"},
    "node_b": {"address": "127.0.0.1:3602", "mode": "R/W", "role": "HA", "status": "ONLINE", "version": "8.0.21"},
    "node_c": {"address": "127.0.0.1:3603", "mode": "R/O", "role": "HA", "status": "ONLINE", "version": "8.0.21"}
}

Router now directs write traffic to the new primary (port 3602).

root@debian-ytt1:/home/ytt# mysql -uroot -proot -P6446 -hdebian-ytt1 -e "select @@port"
+--------+
| @@port |
+--------+
|   3602 |
+--------+

8. switch_to_multi_primary_mode() – Converts the cluster to multi‑primary mode, making all nodes primary.

MySQL  debian-ytt1:3601 ssl  Py > c1.switch_to_multi_primary_mode()
Switching cluster 'ytt_mgr_sandbox' to Multi-Primary mode...
Instance '127.0.0.1:3601' was switched from SECONDARY to PRIMARY.
Instance '127.0.0.1:3602' remains PRIMARY.
Instance '127.0.0.1:3603' was switched from SECONDARY to PRIMARY.
The cluster successfully switched to Multi-Primary mode.

9. switch_to_single_primary_mode() – Reverts the cluster back to single‑primary mode.

10. status() – Provides detailed runtime information; the "extended" parameter (0‑3) controls the depth of output.

0 – No detailed info (default).

1 – Basic metadata and protocol version.

2 – Transaction replay thread details.

3 – Full per‑node details.

Example of c1.status({"extended":2}) shows node B’s replication lag, replayed GTID position, etc.

11. dissolve() – Destroys the cluster, removing all metadata, group‑replication configs, and logs while leaving user data untouched.

MySQL debian-ytt1:3601 ssl  Py > c1.dissolve()
Are you sure you want to dissolve the cluster? [y/N]: y
Instance '127.0.0.1:3602' is attempting to leave the cluster...
Instance '127.0.0.1:3603' is attempting to leave the cluster...
Instance '127.0.0.1:3601' is attempting to leave the cluster...
The cluster was successfully dissolved.
Replication was disabled but user data was left intact.

Conclusion – MySQL InnoDB Cluster provides a powerful, easy‑to‑use suite for managing MySQL group replication, and mastering its built‑in functions greatly simplifies everyday administration tasks.