MySQL Group Replication: who is the primary master ??

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 😉

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

9 Comments

  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

    • 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

    • 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.

  3. Hi Lefred,
    Why can’t we just run one query to find out PRIMARY:

    SELECT member_host, member_port, member_state, member_role FROM performance_schema.replication_group_members;

    host port state role
    dotdevrhdba01.dotdev.com 3307 ONLINE PRIMARY
    dotdevrhdba01.dotdev.com 3308 ONLINE SECONDARY
    dotdevrhdba01.dotdev.com 3309 ONLINE SECONDARY

    Thank you.

    • Hi Alec,

      This post was from March 2017. Since then, we improved usability of Group Replication and indeed now it’s much easier 😉

Leave a Reply to lefredCancel Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

As MySQL Community Manager, I am an employee of Oracle and the views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

You can find articles I wrote on Oracle’s blog.