How to change Group Replication Mode in MySQL

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.

 369 Total Views,  4 Views Today

How useful was this post?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

Spread the Knowledge!

Leave a Reply

Your email address will not be published. Required fields are marked *

11 − three =

Follow us on Social Media
Open chat
1
Contact Us:
Hi,

Greetings of the day! How can we help you?

Thanks.
Team DBsGuru
Direct Call:+91-9310167776
Email: info@dbsguru.com

Share Learn Grow!


Click on the below technical group to join us to share expertise in PostgreSQL and MySQL on WhatsApp group. The purpose of this group is only help to the DBA community.

PostgreSQL Technical Group.

MySQL technical Group.

NOTE: Excuse us for spammer/promoter i.e don't join the group for spam, will be kicked off without warnings.

Thanks.
Team DBsGuru.

Share Learn Grow!

Welcome to DBsGuru! We wish you a very healthy day, hope and pray things to go in a good way for all of humanity. Stay safe!

We encourage technology experts to contribute share technical knowledge in form of writing technical articles/blogs, SQL commands for daily usage (basic to a high level), Carrier guidance on any technology, and become an author.

We have a ready platform for you with no profit no loss (as of now, in the future you may also earn revenue) if you are ready to contribute to writing articles, click on the registration link and the article will be published as an individual contributor on your name.

Click here for registration

Thanks,
Team DBsGuru
We Commit We Deliver