MySQL Group Replication Primary NodeIn this blog, I am going to explain the different ways of electing the PRIMARY node in MySQL group replication. Before MySQL 8.0.2, primary election was based on the member’s UUID, with the lowest UUID elected as the new primary in the event of a failover.

From MySQL 8.0.2: We can select the node to be promoted as a PRIMARY using the server weight ( group_replication_member_weight ). This can be achieved during the failure of the current primary node.

From MySQL 8.0.12: We can promote any node as a PRIMARY using the function “group_replication_set_as_primary”. This can be set anytime without any failures of nodes.

Scenario:

 I have installed the 3 node group replication cluster. I am using Percona Server for MySQL 8.0.22.

I am planning to take the current PRIMARY node “172.28.128.14” to maintenance for OS patching. When I bring the current PRIMARY node down, I need to promote the server “172.28.128.15” as a PRIMARY member. Let see how this can be achieved in the following ways.

  • Using server weight (group_replication_member_weight)
  • Using function “group_replication_set_as_primary”

Using server weight (group_replication_member_weight):

This approach is not straightforward. The new node will be promoted as a primary member when the current primary member goes down. Currently, I have the same weight on all my nodes.

I am going to increase the weight on the server “172.28.128.15” so that it will be elected as a PRIMARY member when taking down the “172.28.128.14”.

At “172.28.128.15”,

Note: To set the weight, you don’t need to execute the STOP/START GROUP_REPLICATION.

The weight was increased to 70 on the server “172.28.128.15”. Now, I am going to bring down the current PRIMARY node “172.28.128.14”.

At “172.28.128.14”,

The node was left from the cluster.

At “172.28.128.15”,

You can see that “172.28.128.15” was selected as a new PRIMARY node.

Using function “group_replication_set_as_primary”:

This method is very straightforward and no need to fail the current PRIMARY node to switch the primary member.

Now, we need to execute the function “group_replication_set_as_primary” with the member UUID.

At “172.28.128.15”,

“172.28.128.15” was elected as a PRIMARY node.

Note: You can execute the function “group_replication_set_as_primary_node” on any server of the cluster.

This approach is very useful, and if you want to switch the PRIMARY member to a highly configured server or for any other reason, it will be greatly helpful.

4 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Chandrasekhar Lakkineni

Good Sakthivel

0
Sankar

Its really useful article at this time. Thanks @Sri Sakthivel

0
vinod

after the node comes up which was down , do we need to perform any step to make it as primary ?

0
Mohan

Article is good. My situation is different. In 3 node cluster(1 primaryI(rw), 2 secondary(ro)), my primary is down. Unable to start up due to incosistencies. Can I promote one of ro nodes – make it primary. Then startup cluster with 1 primary 1 secondary ?

0