MySQL 5.7 GR Single Primary测试

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
云数据库 RDS PostgreSQL,高可用系列 2核4GB
简介: 测试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)
相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
2月前
|
SQL 缓存 关系型数据库
使用温InnoDB缓冲池启动MySQL测试
使用温InnoDB缓冲池启动MySQL测试
52 0
|
2月前
|
SQL 缓存 关系型数据库
MySQL8.4 Enterprise安装Firewall及测试
MySQL8.4 Enterprise安装Firewall及测试
93 0
|
2月前
|
安全 关系型数据库 MySQL
MySQL8使用物理文件恢复MyISAM表测试
MySQL8使用物理文件恢复MyISAM表测试
52 0
|
3月前
|
Oracle 关系型数据库 MySQL
使用崖山YMP 迁移 Oracle/MySQL 至YashanDB 23.2 验证测试
这篇文章是作者尚雷关于使用崖山YMP迁移Oracle/MySQL至YashanDB 23.2的验证测试分享。介绍了YMP的产品信息,包括架构、版本支持等,还详细阐述了外置库部署、YMP部署、访问YMP、数据源管理、任务管理(创建任务、迁移配置、离线迁移、校验初始化、一致性校验)及MySQL迁移的全过程。
|
7月前
|
关系型数据库 MySQL 测试技术
【赵渝强老师】MySQL的基准测试与sysbench
本文介绍了MySQL数据库的基准测试及其重要性,并详细讲解了如何使用sysbench工具进行测试。内容涵盖sysbench的安装、基本使用方法,以及具体测试MySQL数据库的步骤,包括创建测试数据库、准备测试数据、执行测试和清理测试数据。通过这些步骤,可以帮助读者掌握如何有效地评估MySQL数据库的性能。
261 5
|
8月前
|
Java 关系型数据库 MySQL
自动化测试项目实战笔记(一):JDK、Tomcat、MySQL、Jpress环境安装和搭建
这篇文章是关于自动化测试项目实战笔记,涵盖了JDK、Tomcat、MySQL、Jpress环境的安装和搭建过程,以及测试用例和常见问题总结。
187 1
自动化测试项目实战笔记(一):JDK、Tomcat、MySQL、Jpress环境安装和搭建
|
10月前
|
关系型数据库 MySQL 测试技术
使用docker部署MySQL测试环境
使用docker部署MySQL测试环境
156 0
|
1月前
|
负载均衡 算法 关系型数据库
大数据大厂之MySQL数据库课程设计:揭秘MySQL集群架构负载均衡核心算法:从理论到Java代码实战,让你的数据库性能飙升!
本文聚焦 MySQL 集群架构中的负载均衡算法,阐述其重要性。详细介绍轮询、加权轮询、最少连接、加权最少连接、随机、源地址哈希等常用算法,分析各自优缺点及适用场景。并提供 Java 语言代码实现示例,助力直观理解。文章结构清晰,语言通俗易懂,对理解和应用负载均衡算法具有实用价值和参考价值。
大数据大厂之MySQL数据库课程设计:揭秘MySQL集群架构负载均衡核心算法:从理论到Java代码实战,让你的数据库性能飙升!
|
3月前
|
关系型数据库 MySQL Java
【YashanDB知识库】原生mysql驱动配置连接崖山数据库
【YashanDB知识库】原生mysql驱动配置连接崖山数据库
【YashanDB知识库】原生mysql驱动配置连接崖山数据库
|
1月前
|
存储 关系型数据库 MySQL
大数据新视界 --面向数据分析师的大数据大厂之 MySQL 基础秘籍:轻松创建数据库与表,踏入大数据殿堂
本文详细介绍了在 MySQL 中创建数据库和表的方法。包括安装 MySQL、用命令行和图形化工具创建数据库、选择数据库、创建表(含数据类型介绍与选择建议、案例分析、最佳实践与注意事项)以及查看数据库和表的内容。文章专业、严谨且具可操作性,对数据管理有实际帮助。
大数据新视界 --面向数据分析师的大数据大厂之 MySQL 基础秘籍:轻松创建数据库与表,踏入大数据殿堂

推荐镜像

更多