如何確定Single-Primary模式下的MGR主節點(文件 ID 2214438.1)

MySQL 5.7

可以透過global status  group_replication_primary_member 確定

root@db20:59:  [mgr]> SELECT VARIABLE_VALUE
    ->          FROM performance_schema.global_status
    ->         WHERE VARIABLE_NAME = 'group_replication_primary_member';
+--------------------------------------+
| VARIABLE_VALUE                       |
+--------------------------------------+
| f8da6826-328a-11e9-8e54-000c29d7ca48 |
+--------------------------------------+
1 row in set (0.00 sec)

可以結合 performance_schema.replication_group_members 表 獲取主機名和埠資訊:

root@db21:05:  [mgr]> SELECT 
    ->     MEMBER_HOST, MEMBER_PORT
    -> FROM
    ->     performance_schema.replication_group_members
    -> WHERE
    ->     MEMBER_ID = (SELECT 
    ->             VARIABLE_VALUE
    ->         FROM
    ->             performance_schema.global_status
    ->         WHERE
    ->             VARIABLE_NAME = 'group_replication_primary_member');
+-------------+-------------+
| MEMBER_HOST | MEMBER_PORT |
+-------------+-------------+
| mgr1        |        3306 |
+-------------+-------------+
1 row in set (0.00 sec)

或者獲取全部成員資訊:

root@db21:05:  [mgr]> SELECT 
    ->     MEMBER_ID,
    ->     MEMBER_HOST,
    ->     MEMBER_PORT,
    ->     MEMBER_STATE,
    ->     IF(global_status.VARIABLE_NAME IS NOT NULL,
    ->         'PRIMARY',
    ->         'SECONDARY') AS MEMBER_ROLE
    -> FROM
    ->     performance_schema.replication_group_members
    ->         LEFT JOIN
    ->     performance_schema.global_status ON global_status.VARIABLE_NAME = 'group_replication_primary_member'
    ->         AND global_status.VARIABLE_VALUE = replication_group_members.MEMBER_ID;
+--------------------------------------+-------------+-------------+--------------+-------------+
| MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE |
+--------------------------------------+-------------+-------------+--------------+-------------+
| f8da6826-328a-11e9-8e54-000c29d7ca48 | mgr1        |        3306 | ONLINE       | PRIMARY     |
| 030930d7-32b2-11e9-8298-000c292aafc5 | mgr2        |        3306 | ONLINE       | SECONDARY   |
| 202bdcd5-32b3-11e9-9f4f-000c29322d33 | mgr3        |        3306 | ONLINE       | SECONDARY   |
+--------------------------------------+-------------+-------------+--------------+-------------+
3 rows in set (0.01 sec)

MySQL 8.0.2 and Later

MySQL 8.0.2開始,  Performance Schema被擴充套件

SELECT MEMBER_HOST, MEMBER_PORT         FROM performance_schema.replication_group_members        WHERE MEMBER_ROLE = 'PRIMARY';

或者獲取全部成員資訊:

mysql> SELECT MEMBER_ID, MEMBER_HOST, MEMBER_PORT, MEMBER_STATE, MEMBER_ROLE, MEMBER_VERSION         FROM performance_schema.replication_group_members;