mysql group replication 单写模式 (single-primary mode) 搭建

一、环境配置
节点信息

servernameipport   group portmgr_one10.168.1.22733066606mgr_two10.168.1.22633066606mgr_three10.168.1.22833066606

group 同步用户和密码
用户：rpl_lvfk
密码：rpl_lvfk


系统：Ubuntu 16.04.3 LTS


内存：1G


二、安装mysql-5.7.20 
如何源码安装mysql，请参考：mysql-5.7.20 源码安装


三、配置文件
mgr_one：

#base configserver_id = 1port = 3306#binglog config,Group Replication是要根据GTID来进行同步的，所以需要开启GTIDlog_bin = /usr/local/mysql/binarylog/mysql_bin.indexbinlog-format = ROWgtid-mode = ONenforce-gtid-consistency = ONlog-slave-updates = ONmaster-info-repository = TABLErelay-log-info-repository = TABLEbinlog-checksum = NONEexpire_logs_days = 7#group replication configtransaction-write-set-extraction = XXHASH64loose-group_replication_start_on_boot = OFFloose-group_replication_bootstrap_group = OFFloose-group_replication_group_name = 5e1f9814-c91d-11e7-8f9b-000c29ec1057loose-group_replication_local_address = '10.168.1.227:6606'loose-group_replication_group_seeds = '10.168.1.226:6606,10.168.1.227:6606,10.169.1.228:6606'loose-group_replication_single_primary_mode=trueloose-group_replication_enforce_update_everywhere_checks=false

mgr_two:

#base configserver_id = 2port = 3306#binglog config,Group Replication是要根据GTID来进行同步的，所以需要开启GTIDlog_bin = /usr/local/mysql/binarylog/mysql_bin.indexbinlog-format = ROWgtid-mode = ONenforce-gtid-consistency = ONlog-slave-updates = ONmaster-info-repository = TABLErelay-log-info-repository = TABLEbinlog-checksum = NONEexpire_logs_days = 7#group replication configtransaction-write-set-extraction = XXHASH64loose-group_replication_start_on_boot = OFFloose-group_replication_bootstrap_group = OFFloose-group_replication_group_name = 5e1f9814-c91d-11e7-8f9b-000c29ec1057loose-group_replication_local_address = '10.168.1.226:6606'loose-group_replication_group_seeds = '10.168.1.226:6606,10.168.1.227:6606,10.169.1.228:6606'loose-group_replication_single_primary_mode=trueloose-group_replication_enforce_update_everywhere_checks=false

mgr_three:

#base configserver_id = 3port = 3306#binglog config,Group Replication是要根据GTID来进行同步的，所以需要开启GTIDlog_bin = /usr/local/mysql/binarylog/mysql_bin.indexbinlog-format = ROWgtid-mode = ONenforce-gtid-consistency = ONlog-slave-updates = ONmaster-info-repository = TABLErelay-log-info-repository = TABLEbinlog-checksum = NONEexpire_logs_days = 7#group replication configtransaction-write-set-extraction = XXHASH64loose-group_replication_start_on_boot = OFFloose-group_replication_bootstrap_group = OFFloose-group_replication_group_name = 5e1f9814-c91d-11e7-8f9b-000c29ec1057loose-group_replication_local_address = '10.168.1.228:6606'loose-group_replication_group_seeds = '10.168.1.226:6606,10.168.1.227:6606,10.169.1.228:6606'loose-group_replication_single_primary_mode=trueloose-group_replication_enforce_update_everywhere_checks=false

四、安装Group Replication插件
[mgr_two]、[mgr_two]、[mgr_three]上分别执行下面指令：

mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';

注：或者在配置文件中直接配置
plugin-load = group_replication.so

五、组建集群
5.1、配置引导节点mgr_one
A、在[mgr_one]中进行如下配置

SET SQL_LOG_BIN=0;CREATE USER rpl_lvfk@'%';GRANT REPLICATION SLAVE ON *.* TO rpl_lvfk@'%' IDENTIFIED BY 'rpl_lvfk';FLUSH PRIVILEGES;SET SQL_LOG_BIN=1;CHANGE MASTER TO MASTER_USER='rpl_lvfk', MASTER_PASSWORD='rpl_lvfk' FOR CHANNEL 'group_replication_recovery';

注：使用SET SQL_LOG_BIN=0;来保证创建用户的操作不记录到binlog中，这么做可以避免其他服务加入时报事务冲突错误。

[ERROR] Plugin group_replication reported: 'The member contains transactions not present in the group. The member will now exit the group.'

B、启动Group REPLICATION

SET GLOBAL group_replication_bootstrap_group=ON;START GROUP_REPLICATION;SET GLOBAL group_replication_bootstrap_group=OFF;

注：group_replication_bootstrap_group参数设置为ON，是为了标示以后加入集群的服务器都已这台服务器为基准。以后加入的就不需要进行设置。

Group Replication启动成功后，可以通过如下方式查看到节点信息了。

mysql> SELECT * FROM performance_schema.replication_group_members;+---------------------------+--------------------------------------+-------------+-------------+--------------+| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |+---------------------------+--------------------------------------+-------------+-------------+--------------+| group_replication_applier | e13d18c2-bf6e-11e7-9a63-000c2951eef3 | mgr_one     |        3306 | ONLINE       |+---------------------------+--------------------------------------+-------------+-------------+--------------+

注: 此处的MEMBER_ID是在初始化数据库的datadir=/usr/local/mysql/data，那么该MEMBER_ID就在/usr/local/mysql/data/auto.cnf

5.2、mgr_two和mgr_three加入Group
在[mgr_two]和[mgr_three]上分别执行下面指令

A、创建组通讯的用户及密码：SET SQL_LOG_BIN=0;CREATE USER rpl_lvfk@'%';GRANT REPLICATION SLAVE ON *.* TO rpl_lvfk@'%' IDENTIFIED BY 'rpl_lvfk';FLUSH PRIVILEGES;SET SQL_LOG_BIN=1;CHANGE MASTER TO MASTER_USER='rpl_lvfk', MASTER_PASSWORD='rpl_lvfk' FOR CHANNEL 'group_replication_recovery';

B、启动Group Replication

mysql> START GROUP_REPLICATION;Query OK, 0 rows affected (7.40 sec)

C、启动完成之后查看Group信息

mysql> select * from performance_schema.replication_group_members;+---------------------------+--------------------------------------+-------------+-------------+--------------+| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |+---------------------------+--------------------------------------+-------------+-------------+--------------+| group_replication_applier | 005e5b4e-c535-11e7-adae-000c2951eef3 | mgr_two     |        3306 | ONLINE       || group_replication_applier | 2a7b77a2-c535-11e7-adae-000c2951eef3 | mgr_three   |        3306 | ONLINE       || group_replication_applier | e13d18c2-bf6e-11e7-9a63-000c2951eef3 | mgr_one     |        3306 | ONLINE       |+---------------------------+--------------------------------------+-------------+-------------+--------------+3 rows in set (0.00 sec)

注：
问题1：加入组的实例一直处于RECOVERING状态
分析：
通过分析错误日志，发现是配置的MEMBER_HOST是域名形式，但未在hosts加入DNS解析，导致无法访问加入组中其他服务器
解决方法：
修改/etc/hosts文件，并针对三个[mgr_one]、[mgr_two]、[mgr_three]加入DNS解析

10.168.1.226    mgr_two10.168.1.227    mgr_one10.168.1.228    mgr_three

之后，停止组服务后，再次开启组服务

mysql> STOP GROUP_REPLICATION;Query OK, 0 rows affected (1.00 sec)mysql> START GROUP_REPLICATION;Query OK, 0 rows affected (3.10 sec)

六、判断组复制中主节点

mysql> SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'group_replication_primary_member';+--------------------------------------+| VARIABLE_VALUE                       |+--------------------------------------+| e13d18c2-bf6e-11e7-9a63-000c2951eef3 |+--------------------------------------+1 row in set (0.02 sec)

七、测试
7.1、在单主模式下，在非主的服务器中新增数据直接报错

mysql> insert into user values(null, 'yy', 28);ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement

7.2、通过第六点可以得出主节点是哪台服务器，我们在主节点上执行同样的语句，这里我们的主节点为[mgr_one]

mysql> insert into user values(null, 'yy', 28);Query OK, 1 row affected (0.01 sec)mysql> select * from user;+----+------+-----+| id | name | age |+----+------+-----+|  8 | yy   |  28 |+----+------+-----+1 row in set (0.00 sec)

7.3、分别在[mgr_two]、[mgr_three]执行查看语句

[mgr_two]mysql> select * from user;+----+------+-----+| id | name | age |+----+------+-----+|  8 | yy   |  28 |+----+------+-----+1 row in set (0.00 sec)[mgr_three]mysql> select * from user;+----+------+-----+| id | name | age |+----+------+-----+|  8 | yy   |  28 |+----+------+-----+1 row in set (0.00 sec)

以上可以看出，单主模式的组复制已经搭建成功，在主节点操作数据，其他节点同步到新数据，并且只能在主节点执行写操作。

至此，mysql group replication 单写模式 (single-primary mode) 搭建并测试完毕

针对7.1的补充一：

MySQL Group Replicaiton默认也不是允许所有节点写入的，初始时集群中只保留一个可写节点(master)，其余节点的read_only/super_read_only参数都设置为ON。当master因故障down掉之后，组中的仲裁组件会选取一个节点将read_only/super_read_only设置成OFF,让这个节点充当master节点。

当然，也可以通过命令设置来改变为多个节点的写入 或者 直接配置为多主模式

在[mgr_two]执行

mysql> show variables like '%read_only%';+-----------------------+-------+| Variable_name         | Value |+-----------------------+-------+| innodb_read_only      | OFF   || read_only             | ON    || super_read_only       | ON    || transaction_read_only | OFF   || tx_read_only          | OFF   |+-----------------------+-------+5 rows in set (0.01 sec)mysql> set global read_only=OFF;Query OK, 0 rows affected (0.00 sec)mysql> set global super_read_only=OFF;Query OK, 0 rows affected (0.00 sec)mysql> insert into user values(null, 'xx', 27);Query OK, 1 row affected (0.02 sec)mysql> select * from user;+----+------+-----+| id | name | age |+----+------+-----+|  8 | yy   |  28 ||  9 | xx   |  27 |+----+------+-----+2 rows in set (0.00 sec)

[mgr_one]、[mgr_three]查询

mysql> select * from user;+----+------+-----+| id | name | age |+----+------+-----+|  8 | yy   |  28 ||  9 | xx   |  27 |+----+------+-----+2 rows in set (0.00 sec)

补充二、如何配置为多主模式，只需在三台服务器上的my.cnf中下面配置

loose-group_replication_single_primary_mode=trueloose-group_replication_enforce_update_everywhere_checks=false

修改为

loose-group_replication_single_primary_mode=falseloose-group_replication_enforce_update_everywhere_checks=true