Switching to a new master (or new primary if to use the new naming) in a MySQL 8 InnoDB Cluster is simple with the MySQL Shell console and the function of the cluster variable – setPrimaryInstance.
Why would someone need to do it manually? One of the reasons may be because one of the nodes is on the same physical server and thus suppose a smaller latency.
First, get a cluster object of the cluster by connecting to the cluster API with MySQL Shell:
[root@db-cluster-1 ~]# mysqlsh MySQL Shell 8.0.28 Copyright (c) 2016, 2022, 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 > \connect clusteradmin@db-cluster-1 Creating a session to 'clusteradmin@db-cluster-1' Fetching schema names for autocompletion... Press ^C to stop. Your MySQL connection id is 166928419 (X protocol) Server version: 8.0.28 MySQL Community Server - GPL No default schema selected; type \use <schema> to set one. MySQL db-cluster-1:33060+ ssl JS > var cluster = dba.getCluster()
Second, show the status of the cluster to get the cluster topology and the exact nodes’ names, which will use as an argument of the setPrimaryInstance. Still, in the MySQL Shell Console:
MySQL db-cluster-1:33060+ ssl JS > cluster.status()
{
"clusterName": "mycluster1",
"defaultReplicaSet": {
"name": "default",
"primary": "db-cluster-2:3306",
"ssl": "REQUIRED",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"db-cluster-1:3306": {
"address": "db-cluster-1:3306",
"memberRole": "SECONDARY",
"mode": "R/O",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.28"
},
"db-cluster-2:3306": {
"address": "db-cluster-2:3306",
"memberRole": "PRIMARY",
"mode": "R/W",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.28"
},
"db-cluster-3:3306": {
"address": "db-cluster-3:3306",
"memberRole": "SECONDARY",
"mode": "R/O",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.28"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "db-cluster-2:3306"
}
Alternatively, describe() function may be used, too, for a piece of shorter information.
MySQL db-cluster-1:33060+ ssl JS > cluster.describe()
{
"clusterName": "mycluster1",
"defaultReplicaSet": {
"name": "default",
"topology": [
{
"address": "db-cluster-1:3306",
"label": "db-cluster-1:3306",
"role": "HA"
},
{
"address": "db-cluster-3:3306",
"label": "db-cluster-3:3306",
"role": "HA"
},
{
"address": "db-cluster-2:3306",
"label": "db-cluster-2:3306",
"role": "HA"
}
],
"topologyMode": "Single-Primary"
}
}
The address field of JSON is the instance, which the function to change the master/primary in the MySQL InnoDB Cluster accepts as a string.
Change the master/primary to a new node with:
MySQL db-cluster-1:33060+ ssl JS > cluster.setPrimaryInstance("db-cluster-1:3306");
Setting instance 'db-cluster-1:3306' as the primary instance of cluster 'mycluster1'...
Instance 'db-cluster-2:3306' was switched from PRIMARY to SECONDARY.
Instance 'db-cluster-1:3306' was switched from SECONDARY to PRIMARY.
Instance 'db-cluster-3:3306' remains 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().
The instance 'db-cluster-1:3306' was successfully elected as primary.
So now, the master/primary node is the db-cluster-1 instance. Get a new cluster object to verify it:
MySQL db-cluster-1:33060+ ssl JS > var cluster = dba.getCluster()
MySQL db-cluster-1:33060+ ssl JS > cluster.describe()
{
"clusterName": "mycluster1",
"defaultReplicaSet": {
"name": "default",
"topology": [
{
"address": "db-cluster-1:3306",
"label": "db-cluster-1:3306",
"role": "HA"
},
{
"address": "db-cluster-3:3306",
"label": "db-cluster-3:3306",
"role": "HA"
},
{
"address": "db-cluster-2:3306",
"label": "db-cluster-2:3306",
"role": "HA"
}
],
"topologyMode": "Single-Primary"
}
}
MySQL db-cluster-1:33060+ ssl JS > cluster.status()
{
"clusterName": "mycluster1",
"defaultReplicaSet": {
"name": "default",
"primary": "db-cluster-1:3306",
"ssl": "REQUIRED",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"db-cluster-1:3306": {
"address": "db-cluster-1:3306",
"memberRole": "PRIMARY",
"mode": "R/W",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.28"
},
"db-cluster-2:3306": {
"address": "db-cluster-2:3306",
"memberRole": "SECONDARY",
"mode": "R/O",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.28"
},
"db-cluster-3:3306": {
"address": "db-cluster-3:3306",
"memberRole": "SECONDARY",
"mode": "R/O",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.28"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "db-cluster-1:3306"
}
More on MySQL InnoDB 8 server here – Install and deploy MySQL 8 InnoDB Cluster with 3 nodes under CentOS 8 and MySQL Router for HA
MySQL Router
The MySQL Router immediately reloads with the new topology according to its logs:
2023-02-08 14:33:09 io INFO [7f75aa0a4640] starting 8 io-threads, using backend 'linux_epoll' 2023-02-08 14:33:09 routing INFO [7f75979ff6c0] [routing:mycluster1_ro] started: routing strategy = round-robin-with-fallback 2023-02-08 14:33:09 routing INFO [7f75979ff6c0] [routing:mycluster1_ro] started: listening using /var/lib/mysqlrouter/mysqlro.sock 2023-02-08 14:33:09 routing INFO [7f7596bff6c0] [routing:mycluster1_rw] started: routing strategy = first-available 2023-02-08 14:33:09 routing INFO [7f7596bff6c0] [routing:mycluster1_rw] started: listening using /var/lib/mysqlrouter/mysql.sock 2023-02-08 14:33:09 routing INFO [7f7595dff6c0] [routing:mycluster1_x_ro] started: routing strategy = round-robin-with-fallback 2023-02-08 14:33:09 routing INFO [7f7595dff6c0] [routing:mycluster1_x_ro] started: listening using /var/lib/mysqlrouter/mysqlxro.sock 2023-02-08 14:33:09 routing INFO [7f7594dff6c0] [routing:mycluster1_x_rw] started: routing strategy = first-available 2023-02-08 14:33:09 routing INFO [7f7594dff6c0] [routing:mycluster1_x_rw] started: listening using /var/lib/mysqlrouter/mysqlx.sock 2023-02-08 14:33:09 metadata_cache INFO [7f75a0dfc6c0] Starting Metadata Cache 2023-02-08 14:33:09 metadata_cache INFO [7f75a0dfc6c0] Connections using ssl_mode 'PREFERRED' 2023-02-08 14:33:09 metadata_cache INFO [7f75a49886c0] Starting metadata cache refresh thread 2023-02-08 14:33:09 routing INFO [7f7596bff6c0] Start accepting connections for routing routing:mycluster1_rw listening on 6446 and named socket /var/lib/mysqlrouter/mysql.sock 2023-02-08 14:33:09 routing INFO [7f7595dff6c0] Start accepting connections for routing routing:mycluster1_x_ro listening on 6449 and named socket /var/lib/mysqlrouter/mysqlxro.sock 2023-02-08 14:33:09 routing INFO [7f7594dff6c0] Start accepting connections for routing routing:mycluster1_x_rw listening on 6448 and named socket /var/lib/mysqlrouter/mysqlx.sock 2023-02-08 14:33:09 routing INFO [7f75979ff6c0] Start accepting connections for routing routing:mycluster1_ro listening on 6447 and named socket /var/lib/mysqlrouter/mysqlro.sock 2023-02-08 14:33:09 metadata_cache INFO [7f75a49886c0] Connected with metadata server running on db-cluster-1:3306 2023-02-08 14:33:09 metadata_cache INFO [7f75a49886c0] Potential changes detected in cluster 'mycluster1' after metadata refresh 2023-02-08 14:33:09 metadata_cache INFO [7f75a49886c0] Metadata for cluster 'mycluster1' has 3 member(s), single-primary: (view_id=0) 2023-02-08 14:33:09 metadata_cache INFO [7f75a49886c0] db-cluster-1:3306 / 33060 - mode=RW 2023-02-08 14:33:09 metadata_cache INFO [7f75a49886c0] db-cluster-3:3306 / 33060 - mode=RO 2023-02-08 14:33:09 metadata_cache INFO [7f75a49886c0] db-cluster-2:3306 / 33060 - mode=RO