MySQL 5.7 GR Single Primary测试

最新推荐文章于 2024-09-13 13:44:06 发布

煊琰

最新推荐文章于 2024-09-13 13:44:06 发布

阅读量211

点赞数

本文介绍如何通过SQL查询检查MySQL组复制状态，确定主节点，并进行单主写入测试，验证从节点的数据同步情况。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

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

原文链接