MySQL Group Replication – How to Elect the New Primary Node

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.