MySQL 5.7 GR Single Primary测试

本文涉及的产品
云数据库 RDS MySQL,集群版 2核4GB 100GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用版 2核4GB 50GB
简介: 测试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)
相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
2月前
|
关系型数据库 MySQL 测试技术
MySQL性能测试(完整版)
MySQL性能测试(完整版)
127 1
|
1月前
|
存储 关系型数据库 MySQL
Baidu Comate Web在线出题能力测试——MySQL出题能力
Baidu Comate Web在线出题能力测试——MySQL出题能力
21 0
|
1月前
|
SQL 关系型数据库 MySQL
MySQL——case when语句测试
MySQL——case when语句测试
12 0
|
1月前
|
SQL 存储 关系型数据库
MySQL存储过程——Baidu Comate智能代码助手添加20条DML语句——测试索引效果
MySQL存储过程——Baidu Comate智能代码助手添加20条DML语句——测试索引效果
13 0
|
1月前
|
SQL 存储 关系型数据库
MySQL的触发器创建与使用——使用Baidu Comate生成与触发测试完整过程
MySQL的触发器创建与使用——使用Baidu Comate生成与触发测试完整过程
16 0
|
2月前
|
DataWorks NoSQL 关系型数据库
DataWorks操作报错合集之在使用 DataWorks 进行 MongoDB 同步时遇到了连通性测试失败,实例配置和 MongoDB 白名单配置均正确,且同 VPC 下 MySQL 可以成功连接并同步,但 MongoDB 却无法完成同样的操作如何解决
DataWorks是阿里云提供的一站式大数据开发与治理平台,支持数据集成、数据开发、数据服务、数据质量管理、数据安全管理等全流程数据处理。在使用DataWorks过程中,可能会遇到各种操作报错。以下是一些常见的报错情况及其可能的原因和解决方法。
|
2月前
|
存储 关系型数据库 MySQL
RDS for MySQL测试
【4月更文挑战第28天】
|
11天前
|
存储 关系型数据库 MySQL
探索MySQL:关系型数据库的基石
MySQL,作为全球最流行的开源关系型数据库管理系统(RDBMS)之一,广泛应用于各种Web应用、企业级应用和数据仓库中
|
9天前
|
关系型数据库 MySQL 网络安全
Mysql 数据库主从复制
在MySQL主从复制环境中,配置了两台虚拟机:主VM拥有IP1,从VM有IP2。主VM的`my.cnf`设置server-id为1,启用二进制日志;从VM设置server-id为2,开启GTID模式。通过`find`命令查找配置文件,编辑`my.cnf`,在主服务器上创建复制用户,记录二进制日志信息,然后锁定表并备份数据。备份文件通过SCP传输到从服务器,恢复数据并配置复制源,启动复制。检查复制状态确认运行正常。最后解锁表,完成主从同步,新用户在从库中自动更新。
978 6
Mysql 数据库主从复制
|
10天前
|
缓存 运维 关系型数据库
数据库容灾 | MySQL MGR与阿里云PolarDB-X Paxos的深度对比
经过深入的技术剖析与性能对比,PolarDB-X DN凭借其自研的X-Paxos协议和一系列优化设计,在性能、正确性、可用性及资源开销等方面展现出对MySQL MGR的多项优势,但MGR在MySQL生态体系内也占据重要地位,但需要考虑备库宕机抖动、跨机房容灾性能波动、稳定性等各种情况,因此如果想用好MGR,必须配备专业的技术和运维团队的支持。 在面对大规模、高并发、高可用性需求时,PolarDB-X存储引擎以其独特的技术优势和优异的性能表现,相比于MGR在开箱即用的场景下,PolarDB-X基于DN的集中式(标准版)在功能和性能都做到了很好的平衡,成为了极具竞争力的数据库解决方案。

热门文章

最新文章