December 1 2017

mgr单主模式

mgr单主模式
1.在单主模式下,组复制具有自动选主功能,每次只有一个 server成员接受更新。
2.在多主模式下,所有的 server 成员都可以同时接受更新.

我们已经安装过mgr多主了 详细的步骤可以看:http://www.ucjmh.com/2017/12/12/mysql-mgr搭建/

接下来我们把mgr的多主模式转成单主的进行操作。

loose-group_replication_single_primary_mode = off #关闭单主模式的参数
loose-group_replication_enforce_update_everywhere_checks = on #开启多主模式的参数

loose-group_replication_start_on_boot= on #自动启动组复制
group_replication_allow_local_disjoint_gtids_join= on#自动加入到复制组

所以我们只要把两个参数改了就可以了

loose-group_replication_single_primary_mode = on #关闭单主模式的参数
loose-group_replication_enforce_update_everywhere_checks = false #开启多主模式的参数

Db1上建立基本主库master库:
# 设置group_replication_bootstrap_group为ON是为了标示以后加入集群的服务器以这台服务器为基准,以后加入的就不需要设置。

SET GLOBAL group_replication_bootstrap_group = ON;
START GROUP_REPLICATION;
select * from performance_schema.replication_group_members;

Db2上启动group_replication:

set global group_replication_allow_local_disjoint_gtids_join=ON;
start group_replication;
select * from performance_schema.replication_group_members;

3306 [RO] sys 这个时候就自动的变成了read_only的数据库

Db3上启动group_replication:

set global group_replication_allow_local_disjoint_gtids_join=ON;
start group_replication;
select * from performance_schema.replication_group_members;

最后查看集群状态,都为ONLINE就表示OK:
select * from performance_schema.replication_group_members;

dbadmin@(none) 02:51:36>select * from performance_schema.replication_group_members;
+—————————+————————————–+————–+————-+————–+————-+—————-+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+—————————+————————————–+————–+————-+————–+————-+—————-+
| group_replication_applier | 0d1a2f17-c2ce-11e7-a306-0050568f6233 | 10.18.19.233 | 3306 | ONLINE | SECONDARY | 8.0.3 |
| group_replication_applier | 0d1a2f17-c2ce-11e7-a306-0050568f6234 | 10.18.19.234 | 3306 | ONLINE | SECONDARY | 8.0.3 |
| group_replication_applier | 0d1a2f17-c2ce-11e7-a306-0050568f6a55 | 10.18.19.55 | 3306 | ONLINE | PRIMARY | 8.0.3 |
+—————————+————————————–+————–+————-+————–+————-+—————-+
3 rows in set (0.00 sec)

这个时候我们搞点事情 我们直接把主给停掉 模拟错误 看一下别的alert.log

2017-12-13T06:52:52.987528Z 0 [Note] Plugin group_replication reported: ‘[GCS] getstart group_id 66ab9bc4’
2017-12-13T06:52:52.987671Z 0 [Note] Plugin group_replication reported: ‘[GCS] Re-using server node 0 host 10.18.19.234’
2017-12-13T06:52:52.987698Z 0 [Note] Plugin group_replication reported: ‘[GCS] Re-using server node 1 host 10.18.19.233’
2017-12-13T06:52:52.987715Z 0 [Note] Plugin group_replication reported: ‘[GCS] Installed site start={66ab9bc4 423 0} boot_key={66ab9bc4 412 0} node 0’
2017-12-13T06:52:54.682354Z 0 [Note] Plugin group_replication reported: ‘A new primary was elected, enabled conflict detection until the new primary applies all relay logs’
2017-12-13T06:52:54.682522Z 0 [Note] Plugin group_replication reported: ‘Setting super_read_only.’
2017-12-13T06:52:54.682575Z 10 [Note] Plugin group_replication reported: ‘A new primary was elected, enabled conflict detection until the new primary applies all relay logs’
2017-12-13T06:52:55.990525Z 0 [Note] Plugin group_replication reported: ‘[GCS] Failure reading from fd=47 n=0’

可以看到SECONDARY的两个节点从0开始 然后进行选举出来一个新的主节点
dbadmin@(none) 02:51:38>dbadmin@(none) 02:51:38>select * from performance_schema.replication_group_members;
+—————————+————————————–+————–+————-+————–+————-+—————-+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+—————————+————————————–+————–+————-+————–+————-+—————-+
| group_replication_applier | 0d1a2f17-c2ce-11e7-a306-0050568f6233 | 10.18.19.233 | 3306 | ONLINE | PRIMARY | 8.0.3 |
| group_replication_applier | 0d1a2f17-c2ce-11e7-a306-0050568f6234 | 10.18.19.234 | 3306 | ONLINE | SECONDARY | 8.0.3 |
+—————————+————————————–+————–+————-+————–+————-+—————-+
2 rows in set (0.00 sec)

在我们把55起来 添加进去之前 我们先在主节点上添加一个数据库 看会不会在55加入进去之后 同步binlog

然后我们把55起来 执行一样的加入操作:
set global group_replication_allow_local_disjoint_gtids_join=ON;
start group_replication;

看一下当前的状态
dbadmin@(none) 02:56:29>select * from performance_schema.replication_group_members;
+—————————+————————————–+————–+————-+————–+————-+—————-+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+—————————+————————————–+————–+————-+————–+————-+—————-+
| group_replication_applier | 0d1a2f17-c2ce-11e7-a306-0050568f6233 | 10.18.19.233 | 3306 | ONLINE | PRIMARY | 8.0.3 |
| group_replication_applier | 0d1a2f17-c2ce-11e7-a306-0050568f6234 | 10.18.19.234 | 3306 | ONLINE | SECONDARY | 8.0.3 |
| group_replication_applier | 0d1a2f17-c2ce-11e7-a306-0050568f6a55 | 10.18.19.55 | 3306 | RECOVERING | SECONDARY | 8.0.3 |
+—————————+————————————–+————–+————-+————–+————-+—————-+
3 rows in set (0.00 sec)

虽然已经进去了,但是我们看一下alert.log 可以看到数据库报错了

2017-12-13T06:57:47.747854Z 20 [ERROR] Plugin group_replication reported: ‘There was an error when connecting to the donor server. Please check that group_replication_recovery channel credentials and all MEMBER_HOST column values of performance_schema.replication_group_members table are correct and DNS resolvable.’
2017-12-13T06:57:47.747874Z 20 [ERROR] Plugin group_replication reported: ‘For details please check performance_schema.replication_connection_status table and error log messages of Slave I/O for channel group_replication_recovery.’

然后我们看一下select * from performance_schema.replication_connection_status;

Fatal error: Invalid (empty) username when attempting to connect to the master server. Connection attempt terminated.
这个错误是说明这个没有指向过主节点 我们以后在建mgr的时候 一定要在所有的节点上指向

change master to master_user=’slave’,master_password=’slave’ for channel ‘group_replication_recovery’;

重新指向一下 然后再起动 就没有问题了

dbadmin@(none) 02:57:47>dbadmin@(none) 02:57:47>select * from performance_schema.replication_group_members;
+—————————+————————————–+————–+————-+————–+————-+—————-+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+—————————+————————————–+————–+————-+————–+————-+—————-+
| group_replication_applier | 0d1a2f17-c2ce-11e7-a306-0050568f6233 | 10.18.19.233 | 3306 | ONLINE | PRIMARY | 8.0.3 |
| group_replication_applier | 0d1a2f17-c2ce-11e7-a306-0050568f6234 | 10.18.19.234 | 3306 | ONLINE | SECONDARY | 8.0.3 |
| group_replication_applier | 0d1a2f17-c2ce-11e7-a306-0050568f6a55 | 10.18.19.55 | 3306 | ONLINE | SECONDARY | 8.0.3 |
+—————————+————————————–+————–+————-+————–+————-+—————-+
3 rows in set (0.00 sec)

然后我们在55上看一下
$dbs
=========================================
PORT STATUS DATABASE
3306 [RO] a sys



Copyright 2019. All rights reserved.

Posted 2017年12月1日 by ucjmh in category "mysql

Leave a Reply

Your email address will not be published. Required fields are marked *