MySQL InnoDB Cluster Tutorial 3 ( Switching cluster mode and primary member )

So far, I have written two tutorial blogs about MySQL InnoDB Cluster . Those blogs describe about the InnoDB Cluster configuration and how to integrate InnoDB Cluster with the MySQL router . You can get them through the below links .

• MySQL InnoDB Cluster Tutorial 1 ( Group Replication + MySQL Shell )
• MySQL InnoDB Cluster Tutorial 2 ( Integrating with MySQL router )

In this blog I am going to explain the following two things ,

• How to switch the cluster to ( single | multi ) primary mode without downtime ?
• How to make the specific node as the Primary member without downtime ?

I have already configured the three node InnoDB Cluster with single primary mode topology .

MySQL 192.168.33.11:3306 ssl JS > cluster.getName();
first_InnoDB_cluster

MySQL 192.168.33.11:3306 ssl JS > \sql
Switching to SQL mode… Commands end with ;

MySQL 192.168.33.11:3306 ssl SQL > select channel_name,member_host,member_state,member_role,member_version from performance_schema.replication_group_members\G
* 1. row *
channel_name: group_replication_applier
member_host: sakthilabs11
member_state: ONLINE
member_role: PRIMARY
member_version: 8.0.18
* 2. row *
channel_name: group_replication_applier
member_host: sakthilabs12
member_state: ONLINE
member_role: SECONDARY
member_version: 8.0.18
* 3. row *
channel_name: group_replication_applier
member_host: sakthilabs13
member_state: ONLINE
member_role: SECONDARY
member_version: 8.0.18
3 rows in set (0.0070 sec)

Now, this is the time for the experiment .

1. How to switch the cluster to ( single | multi ) primary mode without downtime ?

Right now my cluster topology is single primary mode .

        }
    }, 
    "topologyMode": "Single-Primary"
}, 
"groupInformationSourceMember": "sakthilabs11:3306"

to convert to Multi primary mode ..

MySQL 192.168.33.11:3306 ssl sakthi JS > cluster.switchToMultiPrimaryMode();
Switching cluster ‘first_InnoDB_cluster’ to Multi-Primary mode…

Instance ‘sakthilabs11:3306’ remains PRIMARY.
Instance ‘sakthilabs12:3306’ was switched from SECONDARY to PRIMARY.
Instance ‘sakthilabs13:3306’ was switched from SECONDARY to PRIMARY.

The cluster successfully switched to Multi-Primary mode.

At MySQL Group Replication, you need to turn the variable group_replication_single_primary_mode to OFF, to switch to Multi primary topology . It will not allow when the cluster is active . So, it is difficult to do without MySQL Shell .

to convert to single primary mode again ,

MySQL 192.168.33.11:3306 ssl sakthi JS > cluster.switchToSinglePrimaryMode();
Switching cluster ‘first_InnoDB_cluster’ to Single-Primary mode…

Instance ‘sakthilabs11:3306’ remains PRIMARY.
Instance ‘sakthilabs12:3306’ was switched from PRIMARY to SECONDARY.
Instance ‘sakthilabs13:3306’ was switched from PRIMARY to SECONDARY.

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.

Perfect !!

2. How to make the specific node as the Primary member without downtime ?

Right now , sakthilabs12 is the primary member of my cluster .

“primary”: “sakthilabs12:3306”,
“ssl”: “REQUIRED”,
“status”: “OK”,

Because of some mainteneance task, I wanted to switch the primary member to sakthilabs11 without any downtime .

MySQL 192.168.33.11:3306 ssl sakthi JS > cluster.setPrimaryInstance(‘sakthilabs11:3306’)
Setting instance ‘sakthilabs11:3306’ as the primary instance of cluster ‘first_InnoDB_cluster’…

Instance ‘sakthilabs11:3306’ was switched from SECONDARY to PRIMARY.
Instance ‘sakthilabs12:3306’ was switched from PRIMARY to SECONDARY.
Instance ‘sakthilabs13:3306’ remains SECONDARY.

The instance ‘sakthilabs11:3306’ was successfully elected as primary.

“primary”: “sakthilabs11:3306”,

“ssl”: “REQUIRED”,

“status”: “OK”,

Perfect ,

At group replication you need to perform the below task to switch the primary node without any downtime .

mysql> select @@hostname;
+---------------+
| @@hostname    |
+---------------+
| sakthilabs13  |
+---------------+
1 row in set (0.00 sec)

mysql> show global variables like 'server_uuid';
+---------------+--------------------------------------+
| Variable_name | Value                                | 
+---------------+--------------------------------------+
| server_uuid   | f374a06e-28a5-11ea-a6c6-080027bc6e8c |
+---------------+--------------------------------------+
1 row in set (0.00 sec)

mysql> select group_replication_set_as_primary('f374a06e-28a5-11ea-a6c6-080027bc6e8c');
+--------------------------------------------------------------------------+
| group_replication_set_as_primary('f374a06e-28a5-11ea-a6c6-080027bc6e8c') |
+--------------------------------------------------------------------------+
| Primary server switched to: f374a06e-28a5-11ea-a6c6-080027bc6e8c |
+--------------------------------------------------------------------------+
1 row in set (0.08 sec)

mysql> select member_host,member_role from performance_schema.replication_group_members;
+---------------+-------------+
| member_host   | member_role |
+---------------+-------------+
| sakthilabs11  | SECONDARY   |
| sakthilabs12  | SECONDARY   |
| sakthilabs13  | PRIMARY     |
+---------------+-------------+
3 rows in set (0.02 sec)

I hope this blog will help someone who has started to learn the MySQL InnoDB cluster . I’ll update my next blog soon.

Thanks !!