In initial Group Replication release, 5.7.17, we included the Single-Primary Mode with automatic membership and fail-over features. The MySQL Server 8.0.2 release has brought further enhancement to Group Replication Single-Primary Mode that will allow users to influence primary member election using integer member weight value.
Introduction
In the Single-Primary mode, before the current MySQL Server 8.0.2 release, the first member of the group, which is sorted in lexicographical order of server_uuid’s, was chosen as the primary member. When the user wanted to prioritise certain members for the primary member election, he had to provide the server_uuid value accordingly.
Example:
Consider a group of five members all having MySQL Server version 5.7.19: M1, M2, M3, M4, M5.
The user wants to prioritise members to become primary member in the following order: M1, M3, M2, M4, M5.
The server_uuid in lexicographical order is as follows:
|
1
2
3
4
5
6
7
8
9
10
11
|
mysql> SELECT MEMBER_ID, MEMBER_HOST, MEMBER_STATE FROM performance_schema.replication_group_members; +--------------------------------------+-------------+--------------+ | MEMBER_ID | MEMBER_HOST | MEMBER_STATE | +--------------------------------------+-------------+--------------+ | 9a0b58b3-6acd-11e7-a040-f48c5048ab0c | M1 | ONLINE | | 9a147108-6acd-11e7-9ebe-f48c5048ab0c | M2 | ONLINE | | 9a1c6c6a-6acd-11e7-9ed1-f48c5048ab0c | M3 | ONLINE | | 9a3ba371-6acd-11e7-9f25-f48c5048ab0c | M4 | ONLINE | | 9a3d0a58-6acd-11e7-9f95-f48c5048ab0c | M5 | ONLINE | +--------------------------------------+-------------+--------------+ 5 rows in set (0.00 sec) |
Now generating these server_uuid in required lexicographical order, will be difficult as compared to integer numbers.
Group Replication Member Weight
With the MySQL Server 8.0.2, a new system variable group_replication_member_weight is introduced, having integer value between 0 and 100, to influence the primary member election in Single-Primary mode. The default value for group_replication_member_weight is kept as 50, so that a user when adding up a new member without consideration, will allow him later to tune new members to a higher or lower value without messing with the existing ones.
Note: The first primary member is still the member which bootstrapped the group irrespective of group_replication_member_weight value.
Example:
Consider a group of five members all having MySQL Server version 8.0.2: M1, M2, M3, M4, M5.
The user wants to prioritise members for primary member in the following order: M1, M3, M2, M4, M5.
The group is bootstrapped from M1, so it will be the first primary member.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
M1> SELECT MEMBER_ID, MEMBER_HOST, MEMBER_ROLE FROM performance_schema.replication_group_members; +--------------------------------------+-------------+-------------+ | MEMBER_ID | MEMBER_HOST | MEMBER_ROLE | +--------------------------------------+-------------+-------------+ | 5a5cc46a-6ad1-11e7-9a14-f48c5048ab0c | M1 | PRIMARY | | 5a5d0f6e-6ad1-11e7-9aee-f48c5048ab0c | M4 | SECONDARY | | 5a618ddf-6ad1-11e7-9a55-f48c5048ab0c | M3 | SECONDARY | | 5a67adc9-6ad1-11e7-9b1f-f48c5048ab0c | M2 | SECONDARY | | 5a6e5078-6ad1-11e7-9bce-f48c5048ab0c | M5 | SECONDARY | +--------------------------------------+-------------+-------------+ 5 rows in set (0.00 sec) M1> SET GLOBAL group_replication_member_weight= 80; Query OK, 0 rows affected (0.00 sec) M2> SET GLOBAL group_replication_member_weight= 50; Query OK, 0 rows affected (0.00 sec) M3> SET GLOBAL group_replication_member_weight= 70; Query OK, 0 rows affected (0.00 sec) M4> SET GLOBAL group_replication_member_weight= 50; Query OK, 0 rows affected (0.00 sec) M5> SET GLOBAL group_replication_member_weight= 40; Query OK, 0 rows affected (0.00 sec) |
Now when M1 fails, then next primary member will be M3 as its has next highest primary member weight of 70.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
M1> STOP GROUP_REPLICATION; Query OK, 0 rows affected (11.88 sec) M1> SELECT MEMBER_ID, MEMBER_HOST, MEMBER_ROLE FROM performance_schema.replication_group_members; +--------------------------------------+-------------+-------------+ | MEMBER_ID | MEMBER_HOST | MEMBER_ROLE | +--------------------------------------+-------------+-------------+ | 5a618ddf-6ad1-11e7-9a55-f48c5048ab0c | M3 | PRIMARY | | 5a5d0f6e-6ad1-11e7-9aee-f48c5048ab0c | M4 | SECONDARY | | 5a67adc9-6ad1-11e7-9b1f-f48c5048ab0c | M2 | SECONDARY | | 5a6e5078-6ad1-11e7-9bce-f48c5048ab0c | M5 | SECONDARY | +--------------------------------------+-------------+-------------+ 4 rows in set (0.00 sec) |
Group with members of equal weight
When two or more members have equal group_replication_member_weight value, member with lower server_uuid will be elected as the primary member.
Example:
In earlier example, M3 was last elected as the primary member. When M3 fails (due to error or STOP GROUP_REPLICATION execution), there will be two candidates for next primary member election: M2 and M4, as both has equal group_replication_member_weight of 50. But M4 is selected as the primary member, as it has higher server_uuid then M2 when compared in lexicographical order.
|
1
2
3
4
5
6
7
8
9
10
11
12
|
M3> STOP GROUP_REPLICATION; Query OK, 0 rows affected (11.88 sec) M2> SELECT MEMBER_ID, MEMBER_HOST, MEMBER_ROLE FROM performance_schema.replication_group_members; +--------------------------------------+-------------+-------------+ | MEMBER_ID | MEMBER_HOST | MEMBER_ROLE | +--------------------------------------+-------------+-------------+ | 5a5d0f6e-6ad1-11e7-9aee-f48c5048ab0c | M4 | PRIMARY | | 5a67adc9-6ad1-11e7-9b1f-f48c5048ab0c | M2 | SECONDARY | | 5a6e5078-6ad1-11e7-9bce-f48c5048ab0c | M5 | SECONDARY | +--------------------------------------+-------------+-------------+ 3 rows in set (0.00 sec) |
Group containing multiple versions
For a group containing members of two different versions, only the members of lowest major server version are candidates for the primary member election.
Example:
Consider a group of five members:
two members having MySQL Server version 5.7 : M1, M2
three members having MySQL Server version 8.0: M3, M4, M5
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
|
M3> SELECT MEMBER_HOST, MEMBER_ROLE, MEMBER_VERSION FROM performance_schema.replication_group_members; +-------------+-------------+----------------+ | MEMBER_HOST | MEMBER_ROLE | MEMBER_VERSION | +-------------+-------------+----------------+ | M1 | PRIMARY | 5.7.18 | | M2 | SECONDARY | 5.7.19 | | M3 | SECONDARY | 8.0.2 | | M4 | SECONDARY | 8.0.2 | | M5 | SECONDARY | 8.0.2 | +-------------+-------------+----------------+ 5 rows in set (0.00 sec) M3> SET GLOBAL group_replication_member_weight= 70; Query OK, 0 rows affected (0.00 sec) M1> STOP GROUP_REPLICATION; Query OK, 0 rows affected (11.88 sec) M3> SELECT MEMBER_HOST, MEMBER_ROLE, MEMBER_VERSION FROM performance_schema.replication_group_members; +-------------+-------------+----------------+ | MEMBER_HOST | MEMBER_ROLE | MEMBER_VERSION | +-------------+-------------+----------------+ | M2 | PRIMARY | 5.7.19 | | M3 | SECONDARY | 8.0.2 | | M4 | SECONDARY | 8.0.2 | | M5 | SECONDARY | 8.0.2 | +-------------+-------------+----------------+ 4 rows in set (0.00 sec) |
The user set group_replication_member_weight to 70 for M3, but it won’t have any effect on the primary member election, as only lower version member versions (5.7) are considered for the election. When M2 also fails, which is the last remaining lower member version present in the group, then only the higher member versions are considered for the primary member election.
Note: Since this feature was introduced in 8.0.2, the group having members of both 8.0.1 and 8.0.2 version can have issues.
Conclusion
We are excited to be able to deliver one of the most requested feature from the MySQL community. Please try Group Replication Member Weight feature and let us know your feedback.
Thanks for using MySQL Group Replication!