介绍
MySQL Group Replication（简称MGR）是MySQL官方于2016年12月推出的一个全新的高可用与高扩展的解决方案。MySQL组复制提供了高可用、高扩展、高可靠的MySQL集群服务。
高一致性，基于原生复制及paxos协议的组复制技术，并以插件的方式提供，提供一致数据安全保证；
高容错性，只要不是大多数节点坏掉就可以继续工作，有自动检测机制，当不同节点产生资源争用冲突时，不会出现错误，按照先到者优先原则进行处理，并且内置了自动化脑裂防护机制；
高扩展性，节点的新增和移除都是自动的，新节点加入后，会自动从其他节点上同步状态，直到新节点和其他节点保持一致，如果某节点被移除了，其他节点自动更新组信息，自动维护新的组信息；
高灵活性，有单主模式和多主模式，单主模式下，会自动选主，所有更新操作都在主上进行；多主模式下，所有server都可以同时处理更新操作。
MGR是MySQL数据库未来发展的一个重要方向。
MGR基础结构要求：
引擎必须为innodb,因为需事务支持在commit时对各节点进行冲突检查
每个表必须有主键，在进行事务冲突检测时需要利用主键值对比
必须开启binlog且为row格式
开启GTID，且主从状态信息存于表中（--master-info-repository=TABLE 、--relay-log-info-repository=TABLE），--log-slave-updates打开
一致性检测设置--transaction-write-set-extraction=XXHASH64
MGR使用限制：
RP和普通复制binlog校验不能共存，需设置--binlog-checksum=none
不支持gap lock(间隙锁)，隔离级别需设置为read_committed
不支持对表进行锁操作（lock /unlock table）,不会发送到其他节点执行 ,影响需要对表进行加锁操作的情况，列入mysqldump全表备份恢复操作
不支持serializable(序列化)隔离级别
DDL语句不支持原子性，不能检测冲突，执行后需自行校验是否一致
不支持外键：多主不支持，单主模式不存在此问题
最多支持9个节点：超过9台server无法加入组
环境
| 序号 | IP地址 | 主机名 | 数据库 | 端口 | server-ID | 操作系统 |
|---|---|---|---|---|---|---|
| 1 | 10.10.100.39 | test1 | mysql5.7.28 | 3306 | 1 | CentOs7.4 |
| 2 | 10.10.100.46 | test2 | mysql5.7.28 | 3306 | 2 | CentOs7.4 |
| 3 | 10.10.100.51 | test3 | mysql5.7.28 | 3306 | 3 | CentOs7.4 |
数据库安装此步省略
在三台数据库服务器上都设置
设置hostname和ip映射
[root@localhost ~]# vim /etc/hosts
10.10.100.39 test1
10.10.100.46 test2
10.10.100.51 test3
创建复制环境
登录数据库，创建一个授权账号
[root@test1 ~]# mysql -uroot -p'Admin123!@#'
mysql> grant replication slave on *.* to slave@'10.10.100.%' identified by 'Admin123!@#';
Query OK, 0 rows affected, 1 warning (0.27 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
服务器test1
配置/etc/my.cnf
[root@test1 ~]# vim /etc/my.cnf
[mysqld]
#Group Replication
server_id=1 #服务ID
gtid_mode=ON #全局事务
enforce_gtid_consistency=ON #强制GTID的一致性
master_info_repository=TABLE #将master.info元数据保存在系统表中
relay_log_info_repository=TABLE #将relay.info元数据保存在系统表中
binlog_checksum=NONE #禁用二进制日志事件校验
log_slave_updates=ON #级联复制
log_bin=binlog #开启二进制日志记录
binlog_format=ROW #以行的格式记录
transaction_write_set_extraction=XXHASH64 #使用哈希算法将其编码为散列
loose-group_replication_group_name='ce9be252-2b71-11e6-b8f4-00212844f856' #加入的组名，组名可以,务必以UUID形式配置自定义
loose-group_replication_start_on_boot=off #不自动启用组复制集群
loose-group_replication_local_address='10.10.100.39:33061' #以本机端口33061接受来自组中成员的传入连接
loose-group_replication_group_seeds='10.10.100.39:33061,10.10.100.46:33062,10.10.100.51:33063' #组中成员访问表
loose-group_replication_bootstrap_group=off #不启用引导组
loose-group_replication_ip_whitelist='10.10.100.39,10.10.100.46,10.10.100.51' #白名单
report-host=10.10.100.39 #后面跟的ip是本机的ip
配置好，保存退出，重启服务
[root@test1 ~]# systemctl restart mysqld
登录数据库，把同步的账号写入数据库，并加入集群组
[root@test1 ~]# mysql -uroot -p'Admin123!@#'
#构建group replication集群
mysql> set SQL_LOG_BIN=0; #停掉日志记录
mysql> grant replication slave on *.* to slave@'10.10.100.%' identified by 'Admin123!@#';
mysql> flush privileges;
mysql> set SQL_LOG_BIN=1; #开启日志记录
mysql> change master to master_user='slave',master_password='Admin123!@#' for channel 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.00 sec)
#查询mysql的插件
mysql> show plugins;
安装group replication插件
mysql> install plugin group_replication soname 'group_replication.so';
Query OK, 0 rows affected (0.47 sec)
手动开启引导
设置group_replication_bootstrap_group为ON是为了标示以后加入集群的服务器以这台服务器为基准，以后加入的就不需要设置
设置group_replication_bootstrap_group 只需要在test1上执行一次，另外两个实例不执行这句
mysql> set global group_replication_bootstrap_group=on;
Query OK, 0 rows affected (0.00 sec)
作为首个节点启动MGR集群
mysql> start group_replication;
ERROR 3092 (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log.
查看mysql的报错日志如下
2019-11-04T03:25:14.660721Z 0 [ERROR] Plugin group_replication reported: '[GCS] Error connecting to the local group communication engine instance.'
2019-11-04T03:25:14.671131Z 0 [ERROR] Plugin group_replication reported: '[GCS] The member was unable to join the group. Local port: 33061'
2019-11-04T03:26:04.641758Z 2 [ERROR] Plugin group_replication reported: 'Timeout on wait for view after joining group'
2019-11-04T03:26:04.641809Z 2 [Note] Plugin group_replication reported: 'Requesting to leave the group despite of not being a member'
2019-11-04T03:26:04.641824Z 2 [ERROR] Plugin group_replication reported: '[GCS] The member is leaving a group without being on one.'
2019-11-04T03:26:04.641946Z 2 [Note] Plugin group_replication reported: 'auto_increment_increment is reset to 1
修改/etc/sysconfig/network-scripts/ifcfg-ens33（ens33为上网用的网卡），设置好本机ip、子网掩码、网关，之后重启network就行
我用的这个IP，是自动获取的，把它修改成静态
[root@test1 ~]# vim /etc/sysconfig/network-scripts/ifcfg-ens33
TYPE="Ethernet"
PROXY_METHOD="none"
BROWSER_ONLY="no"
BOOTPROTO="static"
DEFROUTE="yes"
IPV4_FAILURE_FATAL="no"
IPV6INIT="no"
IPV6_AUTOCONF="yes"
IPV6_DEFROUTE="yes"
IPV6_FAILURE_FATAL="no"
IPV6_ADDR_GEN_MODE="stable-privacy"
NAME="ens33"
UUID="1c606b53-b8ff-4f51-ae28-e1e5ac6cfe73"
DEVICE="ens33"
ONBOOT="yes"
IPADDR=10.10.100.39
NETMASK=255.255.255.0
GATEWAY=10.10.100.1
DNS=8.8.8.8
修改DNS文件/etc/resolv.conf
[root@test3 ~]# vim /etc/resolv.conf
# Generated by NetworkManager
nameserver 8.8.8.8
~
#重启网卡
[root@test1 ~]# service network restart
把selinux关闭
[root@test1 ~]# cat /etc/selinux/config
# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
# enforcing - SELinux security policy is enforced.
# permissive - SELinux prints warnings instead of enforcing.
# disabled - No SELinux policy is loaded.
SELINUX=disabled
# SELINUXTYPE= can take one of three values:
# targeted - Targeted processes are protected,
# minimum - Modification of targeted policy. Only selected processes are protected.
# mls - Multi Level Security protection.
#SELINUXTYPE=targeted
然后重新登录数据库启动，还是报错
mysql> start group_replication;
ERROR 3092 (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log.
报错日志如下
2019-11-04T03:36:01.986964Z 0 [ERROR] Plugin group_replication reported: '[GCS] Error on opening a connection to 10.10.100.51:33063 on local port: 33061.'
2019-11-04T03:36:02.989903Z 0 [ERROR] Plugin group_replication reported: '[GCS] Error on opening a connection to 10.10.100.46:33062 on local port: 33061.'
2019-11-04T03:36:02.990421Z 0 [ERROR] Plugin group_replication reported: '[GCS] Error on opening a connection to 10.10.100.51:33063 on local port: 33061.'
2019-11-04T03:36:02.990435Z 0 [ERROR] Plugin group_replication reported: '[GCS] Error connecting to all peers. Member join failed. Local port: 33061'
2019-11-04T03:36:02.996236Z 0 [Warning] Plugin group_replication reported: 'read failed'
2019-11-04T03:36:02.996824Z 0 [ERROR] Plugin group_replication reported: '[GCS] The member was unable to join the group. Local port: 33061'
把另外两台的服务器也做相同的操作，修改网卡，重启
再重新登录数据库，启动集群
[root@test1 ~]# mysql -uroot -p'Admin123!@#'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.28-log MySQL Community Server (GPL)
Copyright (c) 2000, 2019, 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.
mysql> set global group_replication_bootstrap_group=on;
Query OK, 0 rows affected (0.00 sec)
mysql> start group_replication;
Query OK, 0 rows affected (2.01 sec)
mysql> set global group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)
第一个节点启动成功
查看mgr的状态-- 查询表performance_schema.replication_group_members
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+--------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+--------------+-------------+--------------+
| group_replication_applier | 9ad1ae40-feac-11e9-9f0c-000c293d7962 | 10.10.100.39 | 3306 | ONLINE |
+---------------------------+--------------------------------------+--------------+-------------+--------------+
1 row in set (0.00 sec)
测试服务器39的mysql
mysql> create database test1;
Query OK, 1 row affected (0.00 sec)
mysql> use test1
Database changed
mysql> create table t1 (id int primary key,name varchar(20));
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t1 values (1,'man');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+----+------+
| id | name |
+----+------+
| 1 | man |
+----+------+
1 row in set (0.00 sec)
mysql> show binlog events;
+---------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+-----+----------------+-----------+-------------+---------------------------------------+
| binlog.000001 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.28-log, Binlog ver: 4 |
| binlog.000001 | 123 | Previous_gtids | 1 | 150 | |
| binlog.000001 | 150 | Stop | 1 | 169 | |
+---------------+-----+----------------+-----------+-------------+---------------------------------------+
3 rows in set (0.00 sec)
配置46节点，可以将39的配置文件复制过来，修改一下server-id和loose-group_replication_local_address就可以
[root@test2 ~]# vim /etc/my.cnf
server_id=200
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="ce9be252-2b71-11e6-b8f4-00212844f856"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address="10.10.100.46:33062"
loose-group_replication_group_seeds="10.10.100.39:33061,10.10.100.46:33062,10.10.100.51:33063"
loose-group_replication_ip_whitelist='10.10.100.39,10.10.100.46,10.10.100.51'
loose-group_replication_bootstrap_group=off
report_host=10.10.100.46
report_port=3306
重启服务
[root@test2 ~]# systemctl restart mysqld
用户授权
[root@test2 ~]# mysql -uroot -p'Admin123!@#'
mysql> set SQL_LOG_BIN=0; #停掉日志记录
Query OK, 0 rows affected (0.00 sec)
mysql> grant replication slave on *.* to slave@'10.10.100.%' identified by 'Admin123!@#';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> set SQL_LOG_BIN=1; #开启日志记录
Query OK, 0 rows affected (0.00 sec)
mysql> change master to master_user='slave',master_password='Admin123!@#' for channel 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.00 sec) #构建group replication集群
– 安装group replication插件
mysql> install PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.16 sec)
把实例添回到之前的复制组
mysql> set global group_replication_allow_local_disjoint_gtids_join=ON;
Query OK, 0 rows affected, 1 warning (0.00 sec)
启动
mysql> start group_replication;
Query OK, 0 rows affected (5.71 sec)
在test1上查看复制组状态
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+--------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+--------------+-------------+--------------+
| group_replication_applier | 9ad1ae40-feac-11e9-9f0c-000c293d7962 | 10.10.100.39 | 3306 | ONLINE |
| group_replication_applier | e9cf78da-feaa-11e9-bebd-000c29d555ad | 10.10.100.46 | 3306 | ONLINE |
+---------------------------+--------------------------------------+--------------+-------------+--------------+
2 rows in set (0.01 sec)
在test1创建 一个库，然后在test2上查看，发现已经同步
mysql> create database MGR;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| MGR |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
6 rows in set (0.00 sec)
以同样方法添加test3
[root@test3 ~]# vim /etc/my.cnf
server_id=300
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="ce9be252-2b71-11e6-b8f4-00212844f856"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address="10.10.100.51:33063"
loose-group_replication_group_seeds="10.10.100.39:33061,10.10.100.46:33062,10.10.100.51:33063"
loose-group_replication_ip_whitelist='10.10.100.39,10.10.100.46,10.10.100.51'
loose-group_replication_bootstrap_group=off
report_host=10.10.100.51
report_port=3306
重启数据库
[root@test3 ~]# systemctl restart mysqld
用户授权
[root@test3 ~]# mysql -uroot -p'Admin123!@#'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.28-log MySQL Community Server (GPL)
Copyright (c) 2000, 2019, 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.
mysql>
mysql> set SQL_LOG_BIN=0； #停掉日志记录
mysql> grant replication slave on *.* to slave@'10.10.100.%' identified by 'Admin123!@#';
Query OK, 0 rows affected, 1 warning (0.27 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> set SQL_LOG_BIN=1; #开启日志记录
Query OK, 0 rows affected (0.00 sec)
mysql> change master to master_user='slave',master_password='Admin123!@#' for channel 'group_replication_recovery'; #构建group replication集群
Query OK, 0 rows affected, 2 warnings (0.01 sec)
– 安装group replication插件
mysql> install PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.35 sec)
把实例添回到之前的复制组
mysql> set global group_replication_allow_local_disjoint_gtids_join=ON;
Query OK, 0 rows affected, 1 warning (0.00 sec)
启动
mysql> start group_replication;
Query OK, 0 rows affected, 1 warning (3.19 sec)
在test1上查看复制组状态
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+--------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+--------------+-------------+--------------+
| group_replication_applier | 9ad1ae40-feac-11e9-9f0c-000c293d7962 | 10.10.100.39 | 3306 | ONLINE |
| group_replication_applier | e9cf78da-feaa-11e9-bebd-000c29d555ad | 10.10.100.46 | 3306 | ONLINE |
| group_replication_applier | efa1cd35-feaa-11e9-80cc-000c295640d4 | 10.10.100.51 | 3306 | ONLINE |
+---------------------------+--------------------------------------+--------------+-------------+--------------+
3 rows in set (0.00 sec)
在test3查看数据库，发现库都已经同步
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| MGR |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
6 rows in set (0.04 sec)
到这里单master节点的集群就搭建完毕!
查看集群参数设置列表
mysql> show variables like 'group_replication%';
+----------------------------------------------------+----------------------------------------------------------+
| Variable_name | Value |
+----------------------------------------------------+----------------------------------------------------------+
| group_replication_allow_local_disjoint_gtids_join | OFF |
| group_replication_allow_local_lower_version_join | OFF |
| group_replication_auto_increment_increment | 7 |
| group_replication_bootstrap_group | OFF |
| group_replication_components_stop_timeout | 31536000 |
| group_replication_compression_threshold | 1000000 |
| group_replication_enforce_update_everywhere_checks | OFF #这个参数设置多主模式下各个节点严格一致性检查 |
| group_replication_exit_state_action | READ_ONLY |
| group_replication_flow_control_applier_threshold | 25000 |
| group_replication_flow_control_certifier_threshold | 25000 |
| group_replication_flow_control_mode | QUOTA |
| group_replication_force_members | |
| group_replication_group_name | ce9be252-2b71-11e6-b8f4-00212844f856 |
| group_replication_group_seeds | 10.10.100.39:33061,10.10.100.46:33062,10.10.100.51:33063 |
| group_replication_gtid_assignment_block_size | 1000000 |
| group_replication_ip_whitelist | 10.10.100.39,10.10.100.46,10.10.100.51 |
| group_replication_local_address | 10.10.100.39:33061 |
| group_replication_member_weight | 50 |
| group_replication_poll_spin_loops | 0 |
| group_replication_recovery_complete_at | TRANSACTIONS_APPLIED |
| group_replication_recovery_reconnect_interval | 60 |
| group_replication_recovery_retry_count | 10 |
| group_replication_recovery_ssl_ca | |
| group_replication_recovery_ssl_capath | |
| group_replication_recovery_ssl_cert | |
| group_replication_recovery_ssl_cipher | |
| group_replication_recovery_ssl_crl | |
| group_replication_recovery_ssl_crlpath | |
| group_replication_recovery_ssl_key | |
| group_replication_recovery_ssl_verify_server_cert | OFF |
| group_replication_recovery_use_ssl | OFF |
| group_replication_single_primary_mode | ON 这个开启就是单主模式 |
| group_replication_ssl_mode | DISABLED |
| group_replication_start_on_boot | OFF |
| group_replication_transaction_size_limit | 0 |
| group_replication_unreachable_majority_timeout | 0 |
+----------------------------------------------------+----------------------------------------------------------+
36 rows in set (0.00 sec)
multi-primary模式(多主master模式)：
1、该模式启用需设置两个参数
group_replication_single_primary_mode=0 #这个参数很好理解，就是关闭单master模式
group_replication_enforce_update_everywhere_checks=1 #这个参数设置多主模式下各个节点严格一致性检查
2、 默认启动的都是单master模式，其他节点都设置了read_only、super_read_only这两个参数，需要修改这两个配置
3、 完成上面的配置后就可以执行多点写入了，多点写入会存在冲突检查，这耗损性能挺大的，官方建议采用网络分区功能，在程序端把相同的业务定位到同一节点，尽量减少冲突发生几率。
由单主模式修改为多主模式方法
在test1主节点上操作
先停掉集群
mysql> stop GROUP_REPLICATION;
Query OK, 0 rows affected (9.29 sec)
关闭关闭单master模式
mysql> set global group_replication_single_primary_mode=off;
Query OK, 0 rows affected (0.00 sec)
设置多主模式下各个节点严格一致性检查
mysql> set global group_replication_enforce_update_everywhere_checks=ON;
Query OK, 0 rows affected (0.00 sec)
开启引导
mysql> SET GLOBAL group_replication_bootstrap_group=ON;
启动集群
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected, 1 warning (2.00 sec)
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)
另外两个节点也做同样的操作
mysql> stop GROUP_REPLICATION;
Query OK, 0 rows affected (9.75 sec)
mysql> set global group_replication_allow_local_disjoint_gtids_join=ON; #即使含有组中不存在的事务，也允许当前server加入组)
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> set global group_replication_single_primary_mode=off;
Query OK, 0 rows affected (0.00 sec)
mysql> set global group_replication_enforce_update_everywhere_checks=ON;
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (2.01 sec)
在test1上查看
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+--------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+--------------+-------------+--------------+
| group_replication_applier | 9ad1ae40-feac-11e9-9f0c-000c293d7962 | 10.10.100.39 | 3306 | ONLINE |
| group_replication_applier | e9cf78da-feaa-11e9-bebd-000c29d555ad | 10.10.100.46 | 3306 | ONLINE |
| group_replication_applier | efa1cd35-feaa-11e9-80cc-000c295640d4 | 10.10.100.51 | 3306 | ONLINE |
+---------------------------+--------------------------------------+--------------+-------------+--------------+
3 rows in set (0.00 sec)
mysql> show variables like 'group_replication%';
+----------------------------------------------------+----------------------------------------------------------+
| Variable_name | Value |
+----------------------------------------------------+----------------------------------------------------------+
| group_replication_allow_local_disjoint_gtids_join | OFF |
| group_replication_allow_local_lower_version_join | OFF |
| group_replication_auto_increment_increment | 7 |
| group_replication_bootstrap_group | OFF |
| group_replication_components_stop_timeout | 31536000 |
| group_replication_compression_threshold | 1000000 |
| group_replication_enforce_update_everywhere_checks | ON |
| group_replication_exit_state_action | READ_ONLY |
| group_replication_flow_control_applier_threshold | 25000 |
| group_replication_flow_control_certifier_threshold | 25000 |
| group_replication_flow_control_mode | QUOTA |
| group_replication_force_members | |
| group_replication_group_name | ce9be252-2b71-11e6-b8f4-00212844f856 |
| group_replication_group_seeds | 10.10.100.39:33061,10.10.100.46:33062,10.10.100.51:33063 |
| group_replication_gtid_assignment_block_size | 1000000 |
| group_replication_ip_whitelist | 10.10.100.39,10.10.100.46,10.10.100.51 |
| group_replication_local_address | 10.10.100.39:33061 |
| group_replication_member_weight | 50 |
| group_replication_poll_spin_loops | 0 |
| group_replication_recovery_complete_at | TRANSACTIONS_APPLIED |
| group_replication_recovery_reconnect_interval | 60 |
| group_replication_recovery_retry_count | 10 |
| group_replication_recovery_ssl_ca | |
| group_replication_recovery_ssl_capath | |
| group_replication_recovery_ssl_cert | |
| group_replication_recovery_ssl_cipher | |
| group_replication_recovery_ssl_crl | |
| group_replication_recovery_ssl_crlpath | |
| group_replication_recovery_ssl_key | |
| group_replication_recovery_ssl_verify_server_cert | OFF |
| group_replication_recovery_use_ssl | OFF |
| group_replication_single_primary_mode | OFF |
| group_replication_ssl_mode | DISABLED |
| group_replication_start_on_boot | OFF |
| group_replication_transaction_size_limit | 0 |
| group_replication_unreachable_majority_timeout | 0 |
+----------------------------------------------------+----------------------------------------------------------+
36 rows in set (0.00 sec)
测试在三台同时插入数据，能够同时插入数据，然后三台查询，能够插入，同时查询就可以说明已经正常
单master第一台启动的就是主，单 master只能在主上写入数据。
直接搭建多master的模式
my.cnf配置文件：
# Group Replication
server_id = 100 #服务ID
gtid_mode = ON #全局事务
enforce_gtid_consistency = ON #强制GTID的一致性
master_info_repository = TABLE #将master.info元数据保存在系统表中
relay_log_info_repository = TABLE #将relay.info元数据保存在系统表中
binlog_checksum = NONE #禁用二进制日志事件校验
log_slave_updates = ON #级联复制
log_bin = binlog #开启二进制日志记录
binlog_format= ROW #以行的格式记录
transaction_write_set_extraction = XXHASH64 #使用哈希算法将其编码为散列
loose-group_replication_group_name = 'ce9be252-2b71-11e6-b8f4-00212844f856' #加入的组名
loose-group_replication_start_on_boot = off #不自动启用组复制集群
loose-group_replication_local_address = '10.10.100.39:33061' #以本机端口33061接受来自组中成员的传入连接
loose-group_replication_group_seeds = '10.10.100.39:33061,10.10.100.46:33062,10.10.100.51:33063' #组中成员访问表
loose-group_replication_bootstrap_group = off #不启用引导组
主要添加下面两行
group_replication_single_primary_mode = off #关闭单master模式
group_replication_enforce_update_everywhere_checks = ON #多主一致性检查
异常报错处理
ERROR 3092 (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log.
报错日志如下
2019-11-04T03:36:01.986964Z 0 [ERROR] Plugin group_replication reported: '[GCS] Error on opening a connection to 10.10.100.51:33063 on local port: 33061.'
2019-11-04T03:36:02.989903Z 0 [ERROR] Plugin group_replication reported: '[GCS] Error on opening a connection to 10.10.100.46:33062 on local port: 33061.'
2019-11-04T03:36:02.990421Z 0 [ERROR] Plugin group_replication reported: '[GCS] Error on opening a connection to 10.10.100.51:33063 on local port: 33061.'
2019-11-04T03:36:02.990435Z 0 [ERROR] Plugin group_replication reported: '[GCS] Error connecting to all peers. Member join failed. Local port: 33061'
2019-11-04T03:36:02.996236Z 0 [Warning] Plugin group_replication reported: 'read failed'
2019-11-04T03:36:02.996824Z 0 [ERROR] Plugin group_replication reported: '[GCS] The member was unable to join the group. Local port: 33061'
这个报错，主要是网卡配置的问题，可以修改网卡信息，dns配置，重启网卡既可。如果是自动获取改成静态IP
报错日志如下
ERROR 3096 (HY000): The START GROUP_REPLICATION command failed as there was an error when initializing the group communication layer.
2019-11-04T04:33:40.260764Z 2 [ERROR] Plugin group_replication reported: 'Error calling group communication interfaces while trying to leave the group'
2019-11-04T04:36:10.167237Z 4 [Warning] Plugin group_replication reported: ''group_replication_allow_local_disjoint_gtids_join' is deprecated and will be removed in a future release.'
2019-11-04T04:36:10.167795Z 4 [Note] Plugin group_replication reported: 'Group communication SSL configuration: group_replication_ssl_mode: "DISABLED"'
2019-11-04T04:36:10.167870Z 4 [ERROR] Plugin group_replication reported: '[GCS] There is no local IP address matching the one configured for the local node (10.10.100.39:33061).'
2019-11-04T04:36:10.167878Z 4 [ERROR] Plugin group_replication reported: 'Unable to initialize the group communication engine'
2019-11-04T04:36:10.167882Z 4 [ERROR] Plugin group_replication reported: 'Error on group communication engine initialization'
2019-11-04T04:36:10.167884Z 4 [Note] Plugin group_replication reported: 'Requesting to leave the group despite of not being a member'
2019-11-04T04:36:10.167887Z 4 [ERROR] Plugin group_replication reported: 'Error calling group communication interfaces while trying to leave the group'
这个报错主要是配置文件写错，检查server-id和loose-group_replication_local_address
1648
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
