如何确定Single-Primary模式下的MGR主节点

如何确定Single-Primary模式下的MGR主节点(文档 ID 2214438.1)

MySQL 5.7

可以通过global status group_replication_primary_member确定

1
2
3
4
5
6
7
8
9
mysql> SELECT VARIABLE_VALUE
FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'group_replication_primary_member';
+--------------------------------------+
| VARIABLE_VALUE |
+--------------------------------------+
| 9d7f8c28-c02c-11e6-9829-08002715584a |
+--------------------------------------+
1 row in set (0.00 sec)

如果是Multi-Primary默认则结果为空

可以结合performance_schema.replication_group_members表 获取主机名和端口信息:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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 |
+-------------+-------------+
| ol7 | 3306 |
+-------------+-------------+
1 row in set (0.00 sec)

或者获取全部成员信息:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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 |
+--------------------------------------+-------------+-------------+--------------+-------------+
| 9d7f8c28-c02c-11e6-9829-08002715584a | ol7 | 3306 | ONLINE | PRIMARY |
| f2bbb11d-c0c4-11e6-98ec-08002715584a | ol7 | 3308 | ONLINE | SECONDARY |
| f5bb7d78-c02c-11e6-9c56-08002715584a | ol7 | 3307 | ONLINE | SECONDARY |
+--------------------------------------+-------------+-------------+--------------+-------------+
3 rows in set (0.00 sec)

MySQL 8.0.2 and Later

MySQL 8.0.2开始, Performance Schema被扩展

1
2
3
4
5
6
7
8
9
mysql> SELECT MEMBER_HOST, MEMBER_PORT
FROM performance_schema.replication_group_members
WHERE MEMBER_ROLE = 'PRIMARY';
+-------------+-------------+
| MEMBER_HOST | MEMBER_PORT |
+-------------+-------------+
| ol7 | 3306 |
+-------------+-------------+
1 row in set (0.00 sec)

或者获取全部成员信息:
1
2
3
4
5
6
7
8
9
10
mysql> SELECT MEMBER_ID, MEMBER_HOST, MEMBER_PORT, MEMBER_STATE, MEMBER_ROLE, MEMBER_VERSION
FROM performance_schema.replication_group_members;
+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| 33565eab-731f-11e7-8e94-08002715584a | ol7 | 3307 | ONLINE | SECONDARY | 8.0.2 |
| 86034b0a-731f-11e7-9f33-08002715584a | ol7 | 3308 | ONLINE | SECONDARY | 8.0.2 |
| a45d3804-731e-11e7-9003-08002715584a | ol7 | 3306 | ONLINE | PRIMARY | 8.0.2 |
+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)

References
https://dev.mysql.com/doc/refman/en/group-replication-find-primary.html
https://dev.mysql.com/doc/refman/en/group-replication-options.html#sysvar_group_replication_single_primary_mode
https://dev.mysql.com/doc/refman/en/replication-group-members-table.html
http://mysqlhighavailability.com/group-replication-extending-group-replication-performance_schema-tables/
http://lefred.be/content/mysql-group-replication-who-is-the-primary-master-updated/

Powered by Hexo and Hexo-theme-hiker

Copyright © 2013 - 2022 Fan() All Rights Reserved.

访客数 : | 访问量 :