开发者社区> javenzhen> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

MySQL 5.7 GR Single Primary测试

简介: 测试MySQL5.7的故障转移
+关注继续查看

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)

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
环境篇之 mysql 的授权和远程测试|学习笔记
快速学习环境篇之 mysql 的授权和远程测试
0 0
环境篇之 mysql 的可用性测试|学习笔记
快速学习环境篇之 mysql 的可用性测试
0 0
阿里云分析型数据库MySQL版(AnalyticDB)测试初体验(2)
阿里云分析型数据库MySQL版(AnalyticDB)测试初体验
0 0
阿里云分析型数据库MySQL版(AnalyticDB)测试初体验(1)
阿里云分析型数据库MySQL版(AnalyticDB)测试初体验
0 0
AliSQL 5.6.32 vs MySQL 5.7.15抢鲜测试
AliSQL 5.6.32 vs MySQL 5.7.15抢鲜测试
0 0
软件测试mysql面试题:GUI测试和数据库测试有什么区别?
软件测试mysql面试题:GUI测试和数据库测试有什么区别?
0 0
软件测试mysql面试题:如何测试NULL值?
软件测试mysql面试题:如何测试NULL值?
0 0
软件测试mysql面试题:什么是数据库测试?
软件测试mysql面试题:什么是数据库测试?
0 0
shell脚本使用mysqldump+mysql同步线上库和测试库数据
shell脚本使用mysqldump+mysql同步线上库和测试库数据
0 0
MySQL---决策支持的基本测试标准TPC-DS测试数据的生成及导入
TPC-DS是tpc组织提供的官方决策支持基本测试标准,这个标准的数据对于决策支持的学习和测试很有帮助,怎样生成测试数据及将测试数据导入到MySQL数据库,可以参阅本文。
0 0
+关注
文章
问答
文章排行榜
最热
最新
相关电子书
更多
ICA安全标准组测试认证分享
立即下载
MaxCompute基于BigBench标准的最新测试进展
立即下载
用AI高效测试移动应用
立即下载