MySQL 5.7 GR Single Primary测试-阿里云开发者社区

1、查看组复制状态和查找主节点

[root@localhost][(none)]> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 3ac20522-27d6-11e8-8b41-080027049941 | gr2         |        3306 | ONLINE       |
| group_replication_applier | 56ba1eb1-27d6-11e8-8c74-08002750f3b8 | gr3         |        3306 | ONLINE       |
| group_replication_applier | 783e5b4c-27d4-11e8-ba62-080027a663f9 | gr1         |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)

[root@localhost][(none)]> select * from performance_schema.global_status where VARIABLE_NAME='group_replication_primary_member';
+----------------------------------+--------------------------------------+
| VARIABLE_NAME                    | VARIABLE_VALUE                       |
+----------------------------------+--------------------------------------+
| group_replication_primary_member | 783e5b4c-27d4-11e8-ba62-080027a663f9 |
+----------------------------------+--------------------------------------+
1 row in set (0.00 sec)

可以通过上面的SQL查找出成员gr1是RW节点，即主节点。

2、单主写入测试
gr1节点：在主节点上创建数据库和表，并且插入数据

[root@localhost][performance_schema]> create database test;
Query OK, 1 row affected (0.02 sec)

[root@localhost][performance_schema]> create table test.t1 (id int not null);
Query OK, 0 rows affected (0.05 sec)

[root@localhost][performance_schema]> insert into test.t1 values(1);
ERROR 3098 (HY000): The table does not comply with the requirements by an external plugin.

[root@localhost][performance_schema]> alter table test.t1 add primary key (id);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

[root@localhost][performance_schema]> insert into test.t1 values(1);
Query OK, 1 row affected (0.01 sec)

组复制数据库中的表需要有主键。

gr2节点：

[root@localhost][test]> select * from t1;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

[root@localhost][test]> insert into test.t1 values(2);
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement

其他节点也即从节点是无法插入数据，与同步复制的主从一致

3、测试主节点宕机
官方文档：Once a new primary is elected, it is automatically set to read-write and the other secondaries remain as secondaries, and as such, read-only.

一旦选择了新的主节点后，它将自动设置为读写，其他辅助节点仍然为辅助节点，因此也是只读。
gr1：关闭主节点

[root@localhost][(none)]> shutdown;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    78
Current database: *** NONE ***

Query OK, 0 rows affected (0.01 sec)

gr2：查看主节点转移到组中哪个成员server

[root@localhost][(none)]> select * from performance_schema.global_status where VARIABLE_NAME='group_replication_primary_member';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    171
Current database: *** NONE ***

+----------------------------------+--------------------------------------+
| VARIABLE_NAME                    | VARIABLE_VALUE                       |
+----------------------------------+--------------------------------------+
| group_replication_primary_member | 3ac20522-27d6-11e8-8b41-080027049941 |
+----------------------------------+--------------------------------------+
1 row in set (0.02 sec)

[root@localhost][(none)]> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 3ac20522-27d6-11e8-8b41-080027049941 | gr2         |        3306 | ONLINE       |
| group_replication_applier | 56ba1eb1-27d6-11e8-8c74-08002750f3b8 | gr3         |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
2 rows in set (0.00 sec)

[root@localhost][(none)]> insert into test.t1 values(2);
Query OK, 1 row affected (0.02 sec)

gr3：

[root@localhost][(none)]> select * from test.t1;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    102
Current database: *** NONE ***

+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.00 sec)

gr1：启动之后，可以直接加入到组中

[root@gr1 ~]# mysqld --defaults-file=/etc/my.cnf &
[1] 3522

[root@gr1 ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.21-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

[root@localhost][(none)]> start group_replication;
Query OK, 0 rows affected (2.53 sec)

[root@localhost][(none)]> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 3ac20522-27d6-11e8-8b41-080027049941 | gr2         |        3306 | ONLINE       |
| group_replication_applier | 56ba1eb1-27d6-11e8-8c74-08002750f3b8 | gr3         |        3306 | ONLINE       |
| group_replication_applier | 783e5b4c-27d4-11e8-ba62-080027a663f9 | gr1         |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)

[root@localhost][(none)]> select * from performance_schema.global_status where VARIABLE_NAME='group_replication_primary_member';
+----------------------------------+--------------------------------------+
| VARIABLE_NAME                    | VARIABLE_VALUE                       |
+----------------------------------+--------------------------------------+
| group_replication_primary_member | 3ac20522-27d6-11e8-8b41-080027049941 |
+----------------------------------+--------------------------------------+
1 row in set (0.00 sec)

[root@localhost][(none)]> select * from test.t1;
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.00 sec)

[root@localhost][(none)]> insert into test.t1 values(3);
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement

官方文档：Upon primary member failure, an automatic primary election mechanism chooses the next primary member. The method of choosing the next primary depends on the server's server_uuid variable and the group_replication_member_weight variable.
主服务器故障时，自动选主机制选择下一个主服务器。通过server_uuid和group_replication_member_weight参数选择组成员来作为下一个主服务器。

4、group_replication_member_weight：5.7.20支持的参数，默认值50
官方文档：A percentage weight that can be assigned to members to influence the chance of the member being elected as primary in the event of failover, for example when the existing primary leaves a single-primary group. Assign numeric weights to members to ensure that specific members are elected, for example during scheduled maintenance of the primary or to ensure certain hardware is prioritized in the event of failover.
一个百分比权重，可以分配给成员，以影响在故障转移中被选为主要成员的成员的机会，例如，在计划维护期间或在故障转移时确保特定主机的优先级。当现有的主节点failover的时候，可以为成员分配权重，以确保选出制定的成员为主节点。

gr3:是我们的目标主节点server

[root@localhost][(none)]> set global group_replication_member_weight=100;
Query OK, 0 rows affected (0.00 sec)

gr2:关闭节点

[root@localhost][(none)]> shutdown;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    177
Current database: *** NONE ***

Query OK, 0 rows affected (0.00 sec)

gr3:检查主节点是否正常转移

[root@localhost][(none)]> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 56ba1eb1-27d6-11e8-8c74-08002750f3b8 | gr3         |        3306 | ONLINE       |
| group_replication_applier | 783e5b4c-27d4-11e8-ba62-080027a663f9 | gr1         |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
2 rows in set (0.00 sec)

[root@localhost][(none)]> select * from performance_schema.global_status where VARIABLE_NAME='group_replication_primary_member';
+----------------------------------+--------------------------------------+
| VARIABLE_NAME                    | VARIABLE_VALUE                       |
+----------------------------------+--------------------------------------+
| group_replication_primary_member | 56ba1eb1-27d6-11e8-8c74-08002750f3b8 |
+----------------------------------+--------------------------------------+

[root@localhost][(none)]> insert into test.t1 values (4);
Query OK, 1 row affected (0.01 sec)

gr2:

[root@localhost][(none)]> start group_replication;
Query OK, 0 rows affected (2.84 sec)

[root@localhost][(none)]> select * from test.t1;
+----+
| id |
+----+
|  1 |
|  2 |
|  4 |
+----+
3 rows in set (0.00 sec)