如何确定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;