How to change Group Replication Mode in MySQL

In this blog, we will see How to change the mode in which a group is running in, either single or multi-primary. For this we will use two functions group_replication_switch_to_multi_primary_mode() & group_replication_switch_to_single_primary_mode(). These functions used to change a group’s mode can be run on any member.

In previous blog we already configured Group Replication with 3 Member Click here to read more will use same group replication configuration to perform mode switch.

Step 1. Changing to Multi-Primary Mode: We will use function group_replication_switch_to_multi_primary_mode() to perform switch to Multi-Primary Mode. Once process is done you will notice that all Group Replication mysql instance will convert to Primary and can accept Read/Write operations.

Changing to Multi-Primary Mode

mysql>
mysql> prompt Member-1 >
PROMPT set to 'Member-1 >'
Member-1 >
Member-1 > select * from performance_schema.replication_group_members ;
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 13c84508-5014-11eb-af41-000c2997dedd | test-machine02 |        3306 | ONLINE       | SECONDARY   | 8.0.22         |
| group_replication_applier | 13ecba9c-444a-11eb-a397-000c29f9d9e6 | test-machine01 |        3306 | ONLINE       | PRIMARY     | 8.0.22         |
| group_replication_applier | 98411332-6aad-11eb-809a-000c29f9d9e6 | test-machine01 |        3307 | ONLINE       | SECONDARY   | 8.0.22         |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)

Member-1 >
Member-1 >
Member-1 >SELECT group_replication_switch_to_multi_primary_mode();
+--------------------------------------------------+
| group_replication_switch_to_multi_primary_mode() |
+--------------------------------------------------+
| Mode switched to multi-primary successfully.     |
+--------------------------------------------------+
1 row in set (1.11 sec)

Member-1 >SELECT event_name, work_completed, work_estimated FROM performance_schema.events_stages_current WHERE event_name LIKE "%stage/group_rpl%";
Empty set (0.01 sec)

Member-1 >select * from performance_schema.replication_group_members ;
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 13c84508-5014-11eb-af41-000c2997dedd | test-machine02 |        3306 | ONLINE       | PRIMARY     | 8.0.22         |
| group_replication_applier | 13ecba9c-444a-11eb-a397-000c29f9d9e6 | test-machine01 |        3306 | ONLINE       | PRIMARY     | 8.0.22         |
| group_replication_applier | 98411332-6aad-11eb-809a-000c29f9d9e6 | test-machine01 |        3307 | ONLINE       | PRIMARY     | 8.0.22         |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)

Member-1 >

Step 2. Changing to Single-Primary Mode: We will use function group_replication_switch_to_single_primary_mode() to perform switch to Single-Primary Mode. Once the process is done you will notice that One Group Replication 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.

Changing to Single-Primary Mode

Member-1 >
Member-1 >SELECT group_replication_switch_to_single_primary_mode();
+---------------------------------------------------+
| group_replication_switch_to_single_primary_mode() |
+---------------------------------------------------+
| Mode switched to single-primary successfully.     |
+---------------------------------------------------+
1 row in set (1.17 sec)

Member-1 >SELECT event_name, work_completed, work_estimated FROM performance_schema.events_stages_current WHERE event_name LIKE "%stage/group_rpl%";
Empty set (0.00 sec)

Member-1 >select * from performance_schema.replication_group_members ;
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 13c84508-5014-11eb-af41-000c2997dedd | test-machine02 |        3306 | ONLINE       | PRIMARY     | 8.0.22         |
| group_replication_applier | 13ecba9c-444a-11eb-a397-000c29f9d9e6 | test-machine01 |        3306 | ONLINE       | SECONDARY   | 8.0.22         |
| group_replication_applier | 98411332-6aad-11eb-809a-000c29f9d9e6 | test-machine01 |        3307 | ONLINE       | SECONDARY   | 8.0.22         |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)

Member-1 >

Reference: Changing a Group’s Mode: https://dev.mysql.com/doc/refman/8.0/en/group-replication-changing-group-mode.html

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.

MYSQL, REPLICATION

DBsGuru