MySQL Group Replication: who is the primary master ??

on
Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedIn

As you know, MySQL Group Replication runs by default in single primary mode.

mysql2 mysql> show global variables like 'group_replication_single_primary_mode';
+---------------------------------------+-------+
| Variable_name                         | Value |
+---------------------------------------+-------+
| group_replication_single_primary_mode | ON    |
+---------------------------------------+-------+

But how can we easily find which member of the group is the Primary-Master ?

Of course you could check which one is not in read_only:

mysql2 mysql> select @@read_only;
+-------------+
| @@read_only |
+-------------+
|           1 |
+-------------+

But then you need to perform this on all the nodes one by one until you find the right one.

The primary master is exposed through a status variable: group_replication_primary_member:

mysql2 mysql> show global status like 'group_replication_primary_member';
+----------------------------------+--------------------------------------+
| Variable_name                    | Value                                |
+----------------------------------+--------------------------------------+
| group_replication_primary_member | f7aa830d-0f02-11e7-83ba-08002718d305 |
+----------------------------------+--------------------------------------+

But the value is not that obvious to know which MySQL server it refers to.

Once again, as we are able to know who are the members in the group via Performance_Schema, we can verify this:

mysql2 mysql> select * from performance_schema.replication_group_members\G                                            
*************************** 1. row ***************************
CHANNEL_NAME: group_replication_applier
   MEMBER_ID: 73f48dcc-0f02-11e7-99b8-08002718d305
 MEMBER_HOST: mysql3
 MEMBER_PORT: 3306
MEMBER_STATE: ONLINE
*************************** 2. row ***************************
CHANNEL_NAME: group_replication_applier
   MEMBER_ID: f7aa830d-0f02-11e7-83ba-08002718d305
 MEMBER_HOST: mysql1
 MEMBER_PORT: 3306
MEMBER_STATE: ONLINE
*************************** 3. row ***************************
CHANNEL_NAME: group_replication_applier
   MEMBER_ID: fbe0b0a1-0f02-11e7-a1e5-08002718d305
 MEMBER_HOST: mysql2
 MEMBER_PORT: 3306
MEMBER_STATE: ONLINE

So we can here see that f7aa830d-0f02-11e7-83ba-08002718d305 is in fact mysql1 !

Can we merge all this in one single query ? Of course !

Here is the query you need to use:

mysql2 mysql> SELECT member_host as "primary master"
              FROM performance_schema.global_status         
              JOIN performance_schema.replication_group_members         
              WHERE variable_name = 'group_replication_primary_member'         
                AND member_id=variable_value;
+----------------+
| primary master |
+----------------+
| mysql1         |
+----------------+

Simple and useful tip 😉

Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedIn

7 thoughts on “MySQL Group Replication: who is the primary master ??

  1. Thanks Fred.

    Is there a way to force a primary selection in case of primary does fail ? How does the leader election mechanism work ?

    Best Regards,
    Nicolas

    1. Currently there is no way to force it as all nodes should be equivalent. But we listen to some user stories and we considering the option.

  2. Hi Lefred,

    Thank you for your GR and InnoDB Cluster related blogs.
    Those are very helpful. I will catch up with your blogs.

    >How does the leader election mechanism work ?

    Let me confirm. According to following manual, it mentioned “The next primary is selected by ordering the remaining servers lexicographically (using their UUID) and picking the first member in the list. ”
    https://dev.mysql.com/doc/refman/5.7/en/group-replication-single-primary-mode.html

    Best Regards

    1. Thank you Shunya for you comment. So, when you start the cluster, the member used for bootstrap is used as primary. When new primary needs to be elected, the remaining nodes will agree (consensus) on which one should be the next one. You don’t really have control on the result for the moment, we might come with something in the near future. So currently it’s kind of random (meaning you don’t have real control), the next one in uuid sorted list will be used.

Leave a Reply

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

recent

Last Tweets Last Tweets

Locations of visitors to this page
categories