1. 首页
  2. 文章中心
  3. Mysql

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

66 阅读 0 评论
我是靠谱客的博主 美满小海豚，最近开发中收集的这篇文章主要介绍mysql group replication 单写模式 (single-primary mode) 搭建，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述


一、环境配置
节点信息 
servername	ip	port	   group 	port
mgr_one		10.168.1.227	3306	6606
mgr_two		10.168.1.226	3306	6606
mgr_three	10.168.1.228	3306	6606

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


系统：Ubuntu 16.04.3 LTS


内存：1G


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


三、配置文件
mgr_one： 
#base config
server_id = 1
port = 3306


#binglog config,Group Replication是要根据GTID来进行同步的，所以需要开启GTID
log_bin = /usr/local/mysql/binarylog/mysql_bin.index
binlog-format = ROW
gtid-mode = ON
enforce-gtid-consistency = ON
log-slave-updates = ON
master-info-repository = TABLE
relay-log-info-repository = TABLE
binlog-checksum = NONE
expire_logs_days = 7


#group replication config
transaction-write-set-extraction = XXHASH64
loose-group_replication_start_on_boot = OFF
loose-group_replication_bootstrap_group = OFF
loose-group_replication_group_name = 5e1f9814-c91d-11e7-8f9b-000c29ec1057
loose-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=true
loose-group_replication_enforce_update_everywhere_checks=false
mgr_two: 
#base config
server_id = 2
port = 3306


#binglog config,Group Replication是要根据GTID来进行同步的，所以需要开启GTID
log_bin = /usr/local/mysql/binarylog/mysql_bin.index
binlog-format = ROW
gtid-mode = ON
enforce-gtid-consistency = ON
log-slave-updates = ON
master-info-repository = TABLE
relay-log-info-repository = TABLE
binlog-checksum = NONE
expire_logs_days = 7


#group replication config
transaction-write-set-extraction = XXHASH64
loose-group_replication_start_on_boot = OFF
loose-group_replication_bootstrap_group = OFF
loose-group_replication_group_name = 5e1f9814-c91d-11e7-8f9b-000c29ec1057
loose-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=true
loose-group_replication_enforce_update_everywhere_checks=false
mgr_three: 
#base config
server_id = 3
port = 3306


#binglog config,Group Replication是要根据GTID来进行同步的，所以需要开启GTID
log_bin = /usr/local/mysql/binarylog/mysql_bin.index
binlog-format = ROW
gtid-mode = ON
enforce-gtid-consistency = ON
log-slave-updates = ON
master-info-repository = TABLE
relay-log-info-repository = TABLE
binlog-checksum = NONE
expire_logs_days = 7


#group replication config
transaction-write-set-extraction = XXHASH64
loose-group_replication_start_on_boot = OFF
loose-group_replication_bootstrap_group = OFF
loose-group_replication_group_name = 5e1f9814-c91d-11e7-8f9b-000c29ec1057
loose-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=true
loose-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_two
10.168.1.227    mgr_one
10.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=true
loose-group_replication_enforce_update_everywhere_checks=false

修改为

loose-group_replication_single_primary_mode=false
loose-group_replication_enforce_update_everywhere_checks=true


最后

以上就是美满小海豚为你收集整理的mysql group replication 单写模式 (single-primary mode) 搭建的全部内容，希望文章能够帮你解决mysql group replication 单写模式 (single-primary mode) 搭建所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。

本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。
点赞(44)

相关文章

Mysql Group Replication 简介及单主模式组复制配置【转】
Mysql Group Replication 简介及单主模式组复制配置【转】
MySQL复制介绍及MGR安装与配置MGR的安装遇到问题
MySQL复制介绍及MGR安装与配置MGR的安装遇到问题
MGR 架构 ~ DBA相关运维管理
MGR 架构 ~ DBA相关运维管理
MySQL Group Replication [Single-Primary Mode]单主模式搭建安装环境
MySQL Group Replication [Single-Primary Mode]单主模式搭建安装环境
mysql group replication 单写模式 (single-primary mode) 搭建
mysql group replication 单写模式 (single-primary mode) 搭建
MYSQL--架构--MGR--部署--03--配置MYSQL–架构–MGR–部署–03–配置1、配置说明2、单主配置3、多主配置
MYSQL--架构--MGR--部署--03--配置MYSQL–架构–MGR–部署–03–配置1、配置说明2、单主配置3、多主配置
Android 6.0 adb remount后没有写权限，提示Read-only file system
Android 6.0 adb remount后没有写权限，提示Read-only file system
带你走进MySQL全新高可用解决方案-MGR​一、初识MGR二、MGR技术演进三、MGR技术特性四、MGR安装体验参考文档
带你走进MySQL全新高可用解决方案-MGR​一、初识MGR二、MGR技术演进三、MGR技术特性四、MGR安装体验参考文档

评论列表共有 0 条评论

立即
投稿
返回
顶部