mgr在mysql中指是什么字段_MySQL MGR

一、安装插件(先进入数据库安装插件)

INSTALL PLUGIN group_replication SONAME 'group_replication.so';

二、调整MySQL各节点的配置文件并重启MySQL服务(所有节点执行) 带*的需要重点检查

## config master

server-id = 17218228149

log-bin = mysql-bin

master_info_repository = TABLE

binlog_format = ROW

expire_logs_days = 7

sync_binlog = 1

gtid_mode = on

enforce-gtid-consistency = true

binlog_rows_query_log_events = on

*binlog_checksum　　　　　　　　　　　　　　　　　　　　　　　　 =‘none’

## config slave

skip-slave-start

slave-parallel-workers = 8

slave-parallel-type = LOGICAL_CLOCK

*slave_preserve_commit_order = 1

log_slave_updates = 1

*report_host= 192.168.1.149(本机IP)

## config relay log

relay-log = relay-log

relay_log_recovery = ON

sync_relay_log = 0

relay_log_info_repository = TABLE

## config group replication

transaction_write_set_extraction　　　= XXHASH64

*loose-group_replication_group_name 　　　= "412cbc47-74e7-11eb-8283-000c2915e93c" #可以随便填但是要遵守UUID的格式(三台一致)

loose-group_replication_start_on_boot　　　　　　　　= OFF

loose-group_replication_bootstrap_group　　　　　　　　= OFF

*loose-group_replication_local_address　　　　　　　　= "192.168.1.149:33581"

*loose-group_replication_group_seeds　　　= "192.168.1.147:33581,192.168.1.148:33581,192.168.1.149:33581"

*loose-group_replication_ip_whitelist　　　　　　　 = "192.168.1.147,192.168.1.148,192.168.1.149"

三、配置MGR通信账号，并清理MASTER(所有节点执行)

CREATE USER repl@'%' IDENTIFIED BY 'repl';

GRANT REPLICATION SLAVE ON *.* TO repl@'%';

RESET MASTER;

RESET SLAVE ALL;

四、创建MGR依赖的复制环境(所有节点执行)

CHANGE MASTER TO MASTER_USER='repl',

MASTER_PASSWORD='repl'

FOR CHANNEL 'group_replication_recovery';

五、在主节点上启动MGR(在节点192.168.1.147上执行)

SET GLOBAL group_replication_bootstrap_group=ON;

START group_replication;

SET GLOBAL group_replication_bootstrap_group=off;

六、在辅助节点上启动MGR(在节点192.168.1.148和在节点192.168.1.149上执行)

START group_replication;

七、搭建完成后，可以使用下面语句查看状态：

## 查看各节点状态

SELECT *

FROM performance_schema.replication_group_members;

## 查看当前MGR模式(单主还是多主)

SELECT @@group_replication_single_primary_mode;

八、判断节点状态

## 查看当前节点状态

SELECT member_state

FROM performance_schema.replication_group_members

WHERE member_id=@@server_uuid;

## 通过节点是否可写来判断群集是否为主节点

SELECT *

FROM performance_schema.global_variables

WHERE variable_name IN ('read_only', 'super_read_only');

九、MGR成员的五种状态：

ONLINE: The member is in a fully functioning state.

该成员处于完全运作状态。

RECOVERING: The server has joined a group from which it is retrieving data.

服务器已加入从中检索数据的组。

OFFLINE: The group replication plugin is installed but has not been started.

组复制插件已安装，但尚未启动。

ERROR: The member has encountered an error, either during applying transactions or during the recovery phase, and is not participating in the group's transactions.

成员在应用事务或恢复阶段遇到错误，并且未参与组的事务。

UNREACHABLE: The failure detection process suspects that this member cannot be contacted, because the group messages have timed out.

失败检测进程怀疑无法联系此成员，因为组消息已超时。

十、新增MGR节点

目前MySQL不支持自动扩展新节点并将全量数据同步到新节点，因此需要：

1、备份任一节点数据(MySQLDump或Xtrabackup)至新节点并还原

2、在新节点上设置与备份数据对应的Executed_Gtid_Set

3、在新节点上执行下面命令直接启动MGR复制即可。

CHANGE MASTER TO MASTER_USER='repl',

MASTER_PASSWORD='repl'

FOR CHANNEL 'group_replication_recovery';

START GROUP_REPLICATION;