CNDBA社区
一、环境准备
实验环境：centos 7
数据库版本：5.7.22
虚拟机数量：3台
实验的IP地址：10.xx.xxx.211（主） 10.xx.xxx.209（从） 10.xx.xxx.102（从）
MGR模式：单主模式
二、数据库的安装
安装mysql此处省略，可参考地址：
https://www.cndba.cn/laonanhai/article/2946
三、数据库配置文件my.cnf中加入group replication参数
10.200.22.211服务器配置
# 基础参数
server_id = 1
log_bin = binlog
log_slave_updates = on
relay_log = relay-log
# 开启GTID功能
gtid_mode = on
enforce_gtid_consistency = on
# 设置row格式的binlog
binlog_format = ROW
# 禁用binlog_checksum
binlog_checksum = NONE
# 使用系统表来存储slave的信息
master_info_repository = TABLE
relay_log_info_repository = TABLE
# 开启并行复制
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 16
slave_preserve_commit_order = ON
# 开启主键信息采集（XXHASH64或者MURMUR32）
transaction_write_set_extraction = XXHASH64
# group replication插件的基本参数设置
# 设置组的名字（这个名字可以通过select uuid();来生成）
loose-group_replication_group_name = 'f1582d8f-bbb9-11e8-a020-080027b2b4d5'
# 设置成员的本地地址(IP+独立的TCP端口)
loose-group_replication_local_address = '10.xx.xxx.211:33061'
# 设置种子成员的地址
loose-group_replication_group_seeds = '10.xx.xxx.211:33061,10.xx.xxx.209:33061,10.xx.xxx.102:33061'
# 是否随服务器启动而自动启动组复制
loose-group_replication_start_on_boot = off
# 设置单主模式
loose-group_replication_single_primary_mode = on
loose-group_replication_enforce_update_everywhere_checks = off
# 设置引导组成员的组（用于第一次搭建MGR跟重新搭建MGR的时候使用）
loose-group_replication_bootstrap_group = off
10.xx.xxx.209服务器配置
# 基础参数
server_id = 2
log_bin = binlog
log_slave_updates = on
relay_log = relay-log
# 开启GTID功能
gtid_mode = on
enforce_gtid_consistency = on
# 设置row格式的binlog
binlog_format = ROW
# 禁用binlog_checksum
binlog_checksum = NONE
# 使用系统表来存储slave的信息
master_info_repository = TABLE
relay_log_info_repository = TABLE
# 开启并行复制
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 16
slave_preserve_commit_order = ON
# 开启主键信息采集（XXHASH64或者MURMUR32）
transaction_write_set_extraction = XXHASH64
# group replication插件的基本参数设置
# 设置组的名字（这个名字可以通过select uuid();来生成）
loose-group_replication_group_name = 'f1582d8f-bbb9-11e8-a020-080027b2b4d5'
# 设置成员的本地地址(IP+独立的TCP端口)
loose-group_replication_local_address = '10.xx.xxx.209:33061'
# 设置种子成员的地址
loose-group_replication_group_seeds = '10.xx.xxx.211:33061,10.xx.xxx.209:33061,10.xx.xxx.102:33061'
# 是否随服务器启动而自动启动组复制
loose-group_replication_start_on_boot = off
# 设置单主模式
loose-group_replication_single_primary_mode = on
loose-group_replication_enforce_update_everywhere_checks = off
# 设置引导组成员的组（用于第一次搭建MGR跟重新搭建MGR的时候使用）
loose-group_replication_bootstrap_group = off
10.xx.xxx.102服务器配置
# 基础参数
server_id = 3
log_bin = binlog
log_slave_updates = on
relay_log = relay-log
# 开启GTID功能
gtid_mode = on
enforce_gtid_consistency = on
# 设置row格式的binlog
binlog_format = ROW
# 禁用binlog_checksum
binlog_checksum = NONE
# 使用系统表来存储slave的信息
master_info_repository = TABLE
relay_log_info_repository = TABLE
# 开启并行复制
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 16
slave_preserve_commit_order = ON
# 开启主键信息采集（XXHASH64或者MURMUR32）
transaction_write_set_extraction = XXHASH64
# group replication插件的基本参数设置
# 设置组的名字（这个名字可以通过select uuid();来生成）
loose-group_replication_group_name = 'f1582d8f-bbb9-11e8-a020-080027b2b4d5'
# 设置成员的本地地址(IP+独立的TCP端口)
loose-group_replication_local_address = '10.xx.xxx.102:33061'
# 设置种子成员的地址
loose-group_replication_group_seeds = '10.xx.xxx.211:33061,10.xx.xxx.209:33061,10.xx.xxx.102:33061'
# 是否随服务器启动而自动启动组复制
loose-group_replication_start_on_boot = off
# 设置单主模式
loose-group_replication_single_primary_mode = on
loose-group_replication_enforce_update_everywhere_checks = off
# 设置引导组成员的组（用于第一次搭建MGR跟重新搭建MGR的时候使用）
loose-group_replication_bootstrap_group = off
四、配置/etc/hosts
三台服务器hosts配置（都一样）
vi /etc/hosts
加入以下行，配置如下：
10.xx.xxx.211 master-211
10.xx.xxx.209 slave-209
10.xx.xxx.102 slave-102
五、配置group replication
添加主节点10.xx.xxx.211服务器
[root@localhost ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or /g.
Your MySQL connection id is 3
Server version: 5.7.22-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.
mysql> set sql_log_bin = off;
Query OK, 0 rows affected (0.00 sec)
mysql> grant replication slave,replication client on *.* to rpl_user@'%' identified by 'xxxxxx';
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 = on;
Query OK, 0 rows affected (0.00 sec)
mysql> change master to master_user='rpl_user',master_password='xxxxxx' for channel 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.03 sec)
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.08 sec)
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.14 sec)
mysql> SET GLOBAL group_replication_bootstrap_group = off;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | cefdd8c5-9ea7-11e8-93d9-080027b2b4d5 | master-211 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
1 row in set (0.00 sec)
# 创建测试数据
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.03 sec)
mysql> create database test;
Query OK, 1 row affected (0.04 sec)
mysql> use test
Database changed
mysql> create table t1 (id int primary key,name varchar(20));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t1 values (1,'aa');
Query OK, 1 row affected (0.01 sec)
mysql> select * from t1;
+----+------+
| id | name |
+----+------+
| 1 | aa |
+----+------+
1 row in set (0.00 sec)
mysql> show binlog events;
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000003 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.22-log, Binlog ver: 4 |
| mysql-bin.000003 | 123 | Previous_gtids | 1 | 154 | |
| mysql-bin.000003 | 154 | Stop | 1 | 177 | |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
3 rows in set (0.00 sec)
添加从节点10.xx.xxx.209服务器
[root@localhost ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or /g.
Your MySQL connection id is 3
Server version: 5.7.22-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.
mysql> set sql_log_bin = off;
Query OK, 0 rows affected (0.00 sec)
mysql> grant replication slave,replication client on *.* to rpl_user@'%' identified by 'xxxxxx';
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 = on;
Query OK, 0 rows affected (0.00 sec)
mysql> change master to master_user='rpl_user',master_password='xxxxxx' for channel 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.03 sec)
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.08 sec)
mysql> set global group_replication_allow_local_disjoint_gtids_join=ON;
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (2.14 sec)
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | a1437797-bbb8-11e8-b631-080027710809 | slave-209 | 3306 | ONLINE |
| group_replication_applier | cefdd8c5-9ea7-11e8-93d9-080027b2b4d5 | master-211 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
2 rows in set (0.00 sec)
# 验证
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.04 sec)
mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from t1;
+----+------+
| id | name |
+----+------+
| 1 | aa |
+----+------+
1 row in set (0.00 sec)
添加从节点10.xx.xxx.102服务器
[root@slave-102 ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or /g.
Your MySQL connection id is 3
Server version: 5.7.22-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.
mysql> set sql_log_bin = off;
Query OK, 0 rows affected (0.00 sec)
mysql> grant replication slave,replication client on *.* to rpl_user@'%' identified by 'xxxxxx';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> set sql_log_bin = on;
Query OK, 0 rows affected (0.00 sec)
mysql> change master to master_user='rpl_user',master_password='xxxxxx' for channel 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.03 sec)
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.08 sec)
mysql> set global group_replication_allow_local_disjoint_gtids_join=ON;
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (3.29 sec)
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 4b01c5c8-bbb8-11e8-9483-080027a613c3 | slave-102 | 3306 | ONLINE |
| group_replication_applier | a1437797-bbb8-11e8-b631-080027710809 | slave-209 | 3306 | ONLINE |
| group_replication_applier | cefdd8c5-9ea7-11e8-93d9-080027b2b4d5 | master-211 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)
# 验证
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.05 sec)
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from t1;
+----+------+
| id | name |
+----+------+
| 1 | aa |
+----+------+
1 row in set (0.00 sec)
六、故障模拟
10.xx.xxx.209上查看主节点
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 4b01c5c8-bbb8-11e8-9483-080027a613c3 | slave-102 | 3306 | ONLINE |
| group_replication_applier | a1437797-bbb8-11e8-b631-080027710809 | slave-209 | 3306 | ONLINE |
| group_replication_applier | cefdd8c5-9ea7-11e8-93d9-080027b2b4d5 | master-211 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)
mysql> show global status like 'group_replication_primary_member';
+----------------------------------+--------------------------------------+
| Variable_name | Value |
+----------------------------------+--------------------------------------+
| group_replication_primary_member | cefdd8c5-9ea7-11e8-93d9-080027b2b4d5 |
+----------------------------------+--------------------------------------+
1 row in set (0.00 sec)
10.xx.xxx.102上查看主节点
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 4b01c5c8-bbb8-11e8-9483-080027a613c3 | slave-102 | 3306 | ONLINE |
| group_replication_applier | a1437797-bbb8-11e8-b631-080027710809 | slave-209 | 3306 | ONLINE |
| group_replication_applier | cefdd8c5-9ea7-11e8-93d9-080027b2b4d5 | master-211 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)
mysql> show global status like 'group_replication_primary_member';
+----------------------------------+--------------------------------------+
| Variable_name | Value |
+----------------------------------+--------------------------------------+
| group_replication_primary_member | cefdd8c5-9ea7-11e8-93d9-080027b2b4d5 |
+----------------------------------+--------------------------------------+
1 row in set (0.00 sec)
10.xx.xxx.211上关闭mysql服务（主节点）
[root@master-211 ~]# service mysqld stop
Shutting down MySQL............ SUCCESS!
10.xx.xxx.209上重新查看主节点
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 4b01c5c8-bbb8-11e8-9483-080027a613c3 | slave-102 | 3306 | ONLINE |
| group_replication_applier | a1437797-bbb8-11e8-b631-080027710809 | slave-209 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
2 rows in set (0.00 sec)
mysql> show global status like 'group_replication_primary_member';
+----------------------------------+--------------------------------------+
| Variable_name | Value |
+----------------------------------+--------------------------------------+
| group_replication_primary_member | 4b01c5c8-bbb8-11e8-9483-080027a613c3 |
+----------------------------------+--------------------------------------+
1 row in set (0.00 sec)
10.xx.xxx.102上重新查看主节点
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 4b01c5c8-bbb8-11e8-9483-080027a613c3 | slave-102 | 3306 | ONLINE |
| group_replication_applier | a1437797-bbb8-11e8-b631-080027710809 | slave-209 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
2 rows in set (0.00 sec)
mysql> show global status like 'group_replication_primary_member';
+----------------------------------+--------------------------------------+
| Variable_name | Value |
+----------------------------------+--------------------------------------+
| group_replication_primary_member | 4b01c5c8-bbb8-11e8-9483-080027a613c3 |
+----------------------------------+--------------------------------------+
1 row in set (0.00 sec)
从上面可以看出，在主节点出现故障之后，10.xx.xxx.102服务器自动切换成了新的主节点。
新主节点（10.xx.xxx.102）上创建t2表
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> use test
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1 |
+----------------+
1 row in set (0.00 sec)
mysql> create table t2(id int primary key,name varchar(20));
Query OK, 0 rows affected (0.07 sec)
从节点（10.xx.xxx.209）验证
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> use test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1 |
| t2 |
+----------------+
2 rows in set (0.00 sec)
老的主节点（10.xx.xxx.211）启动mysql服务，并重新加入group
[root@master-211 ~]# service mysqld start
Starting MySQL.. SUCCESS!
[root@master-211 ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or /g.
Your MySQL connection id is 3
Server version: 5.7.22-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.
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+-----------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+-----------+-------------+-------------+--------------+
| group_replication_applier | | | NULL | OFFLINE |
+---------------------------+-----------+-------------+-------------+--------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+-----------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+-----------+-------------+-------------+--------------+
| group_replication_applier | | | NULL | OFFLINE |
+---------------------------+-----------+-------------+-------------+--------------+
1 row in set (0.00 sec)
mysql> change master to master_user='rpl_user',master_password='xxxxxx' for channel 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.05 sec)
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (3.24 sec)
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 4b01c5c8-bbb8-11e8-9483-080027a613c3 | slave-102 | 3306 | ONLINE |
| group_replication_applier | a1437797-bbb8-11e8-b631-080027710809 | slave-209 | 3306 | ONLINE |
| group_replication_applier | cefdd8c5-9ea7-11e8-93d9-080027b2b4d5 | master-211 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)
mysql> show global status like 'group_replication_primary_member';
+----------------------------------+--------------------------------------+
| Variable_name | Value |
+----------------------------------+--------------------------------------+
| group_replication_primary_member | 4b01c5c8-bbb8-11e8-9483-080027a613c3 |
+----------------------------------+--------------------------------------+
1 row in set (0.00 sec)
老的主节点（10.xx.xxx.211）验证数据
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1 |
| t2 |
+----------------+
2 rows in set (0.00 sec)
至此，group replication单主模式就搭建完毕，故障模拟也成功了。
版权声明：本文为博主原创文章，未经博主允许不得转载。
nerver give up
