http://mysqlhighavailability.com/gr/doc/technical.html
http://blog.itpub.net/29510932/viewspace-2055679/
MySQL Group Replication
对测试版（on labs）的Group Replication的第一印象：这个MySQL插件让多主结构的MySQL集群能够进行全更新（update everywhere）。
它糅合了分布式系统（比如组通信）和RDBMS中replication的技术和概念。
通过这个插件，一组MySQL服务器组成了一个完美的分布式、强一致性的集群，集群内的MySQL服务器共同合作来保持集群的一致性.
什么是Group Replication
基于组的复制（Group-based Replication）是一种被使用在容错系统中的技术。Replication-group（复制组）是由能够相互通信的多个服务器（节点）组成的。
在通信层，Group replication实现了一系列的机制：比如原子消息（atomic message delivery）和全序化消息（total ordering of messages）。
这些原子化，抽象化的机制，为实现更先进的数据库复制方案提供了强有力的支持。
MySQL Group Replication正是基于这些技术和概念，实现了一种多主全更新的复制协议。
简而言之，一个Replication-group就是一组节点，每个节点都可以独立执行事务，而读写事务则会在于group内的其他节点进行协调之后再commit。
因此，当一个事务准备提交时，会自动在group内进行原子性的广播，告知其他节点变更了什么内容/执行了什么事务。
这种原子广播的方式，使得这个事务在每一个节点上都保持着同样顺序。
这意味着每一个节点都以同样的顺序，接收到了同样的事务日志，所以每一个节点以同样的顺序重演了这些事务日志，最终整个group保持了完全一致的状态。
然而，不同的节点上执行的事务之间有可能存在资源争用。这种现象容易出现在两个不同的并发事务上。
假设在不同的节点上有两个并发事务，更新了同一行数据，那么就会发生资源争用。
面对这种情况，Group Replication判定先提交的事务为有效事务，会在整个group里面重演，后提交的事务会直接中断，或者回滚，最后丢弃掉。
因此，这也是一个无共享的复制方案，每一个节点都保存了完整的数据副本。图1也描述了具体的工作流程，能够简洁的和其他方案进行对比。
这个复制方案，在某种程度上，和数据库状态机（DBSM）的Replication方法比较类似。
Group Replication简单构架图：
早期Group Replication数据同步引擎采用的是第三方的corosync,最新的版本中已经需要corosync了，完全集成到mysql中了。
安装配置Group Replication
节点信息
| servername | ip | port | group port |
|---|---|---|---|
| server01 | 127.0.0.1 | 3306 | 6606 |
| server02 | 127.0.0.1 | 3307 | 6607 |
| server03 | 127.0.0.1 | 3308 | 6608 |
系统环境：
系统：CentOS release 6.3 (Final)
内存：32G
mysql5.7安装
下载mysql至/usr/local/下：
http://downloads.mysql.com/snapshots/pb/mysql-group-replication-0.9.0-labs/mysql-5.7.15-labs-gr090-linux-glibc2.5-x86_64.tar.gz
解压安装包
[root@mser02 local]# tar -zxvf mysql-5.7.15-labs-gr090-linux-glibc2.5-x86_64.tar.gz
[root@mser02 local]# mv mysql-5.7.15-labs-gr090-linux-glibc2.5-x86_64 mysql5.7
[root@mser02 local]# cd mysql5.7/初始化数据
[server01]
./bin/mysqld --no-defaults --user=mysql --initialize --explicit_defaults_for_timestamp --basedir=/usr/local/mysql5.7/ --datadir=/usr/local/mysql5.7/data01[server02]
./bin/mysqld --no-defaults --user=mysql --initialize --explicit_defaults_for_timestamp --basedir=/usr/local/mysql5.7/ --datadir=/usr/local/mysql5.7/data02[server03]
./bin/mysqld --no-defaults --user=mysql --initialize --explicit_defaults_for_timestamp --basedir=/usr/local/mysql5.7/ --datadir=/usr/local/mysql5.7/data03编辑配置文件
[my1.cnf]
[mysqld]
#base config 基础配置信息
server-id = 1
basedir=/usr/local/mysql5.7/
datadir=/usr/local/mysql5.7/data01
user=mysql
explicit_defaults_for_timestamp
socket=mysql1.sock
port = 3306
#binlog binlog配置，Group Replication是要根据GTID来进行同步的，所以需要开启GTID
log-bin=mysql-bin
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
#group replication
transaction-write-set-extraction = XXHASH64
group_replication_start_on_boot = OFF
group_replication_bootstrap_group = OFF
group_replication_group_name = 0c6d3e5f-90e2-11e6-802e-842b2b5909d6
group_replication_local_address = '127.0.0.1:6606'
group_replication_group_seeds = '127.0.0.1:6607,127.0.0.1:6608'
参数说明：
group_replication_start_on_boot 是否随mysql启动Group Replication
group_replication_bootstrap_group 是否是Group Replication的引导节点，初次搭建集群的时候需要有一个节点设置为ON来启动Group Replication
group_replication_group_name的值必须是uuid格式，uuid获取方法：
mysql> select uuid();
+--------------------------------------+
| uuid() |
+--------------------------------------+
| 94318aa8-91c2-11e6-a1cf-842b2b5909d6 |
+--------------------------------------+
1 row in set (0.00 sec)group_replication_local_address 组中本机链接的信息格式为ipaddress:port
group_replication_group_seeds 组中其他几点的信息格式为ipaddress1:port1,ipaddress2:port2
其他实例配置文件
[my2.cnf]
[mysqld]
#base config
server-id = 2
basedir=/usr/local/mysql5.7/
datadir=/usr/local/mysql5.7/data02
user=mysql
explicit_defaults_for_timestamp
socket=mysql2.sock
port = 3307
#binlog
log-bin=mysql-bin
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
#group replication
transaction-write-set-extraction = XXHASH64
group_replication_start_on_boot = OFF
group_replication_bootstrap_group = OFF
group_replication_group_name = 0c6d3e5f-90e2-11e6-802e-842b2b5909d6
group_replication_local_address = '127.0.0.1:6607'
group_replication_group_seeds = '127.0.0.1:6606,127.0.0.1:6608'[my3.cnf]
[mysqld]
#base config
server-id = 3
basedir=/usr/local/mysql5.7/
datadir=/usr/local/mysql5.7/data03
user=mysql
explicit_defaults_for_timestamp
socket=mysql3.sock
port = 3308
#binlog
log-bin=mysql-bin
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
#group replication
transaction-write-set-extraction = XXHASH64
group_replication_start_on_boot = OFF
group_replication_bootstrap_group = OFF
group_replication_group_name = 0c6d3e5f-90e2-11e6-802e-842b2b5909d6
group_replication_local_address = '127.0.0.1:6608'
group_replication_group_seeds = '127.0.0.1:6606,127.0.0.1:6607'启动mysql实例
[server01]
./bin/mysqld --defaults-file=/usr/local/mysql5.7/my1.cnf[server02]
./bin/mysqld --defaults-file=/usr/local/mysql5.7/my2.cnf[server03]
./bin/mysqld --defaults-file=/usr/local/mysql5.7/my3.cnf启动后注意修改一下初始的root密码：
./bin/mysql -uroot -p -P3306 -h127.0.0.1 password "new_pwd"安装Group Replication插件
INSTALL PLUGIN group_replication SONAME 'group_replication.so';mysql> show plugins;
+----------------------------+----------+--------------------+----------------------+---------+
| Name | Status | Type | Library | License |
+----------------------------+----------+--------------------+----------------------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
（…………）
| group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL |
+----------------------------+----------+--------------------+----------------------+---------+
注：或者在配置文件中直接配置
plugin-load = group_replication.so组建集群
配置引导节点
首先在[server01]中进行如下配置：
SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%' IDENTIFIED BY 'rpl_pass';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_pass' FOR CHANNEL 'group_replication_recovery';注：使用SET SQL_LOG_BIN=0;来保证创建用户的操作不记录到binlog中，这么做可以避免其他服务加入时报事务冲突错误。
2016-10-13T07:59:37.025099Z 0 [ERROR] Plugin group_replication reported: 'The member contains transactions not present in the group. The member will now exit the group.'启动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 | a876d35e-9110-11e6-a365-842b2b5909d6 | mser01 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
1 rows in set (0.00 sec)其他节点加入Group
在[server02]、[server03]上执行如下操作：
SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%' IDENTIFIED BY 'rpl_pass';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_pass' FOR CHANNEL 'group_replication_recovery';启动Group Rplication
START GROUP_REPLICATION;启动完成之后在看Group信息：
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 0aab37dc-911c-11e6-ab03-842b2b5909d6 | mser01 | 3308 | ONLINE |
| group_replication_applier | a876d35e-9110-11e6-a365-842b2b5909d6 | mser01 | 3306 | ONLINE |
| group_replication_applier | b34df071-911a-11e6-9796-842b2b5909d6 | mser01 | 3307 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)可以查询replication协议的其他数据。比如队列中有多少个事务，以及检测到的资源争用：
mysql> select * from performance_schema.replication_group_member_stats\G
*************************** 1. row ***************************
CHANNEL_NAME: group_replication_applier
VIEW_ID: 14763420898745772:29
MEMBER_ID: a876d35e-9110-11e6-a365-842b2b5909d6
COUNT_TRANSACTIONS_IN_QUEUE: 0
COUNT_TRANSACTIONS_CHECKED: 0
COUNT_CONFLICTS_DETECTED: 0
COUNT_TRANSACTIONS_ROWS_VALIDATING: 0
TRANSACTIONS_COMMITTED_ALL_MEMBERS: 0aab37dc-911c-11e6-ab03-842b2b5909d6:1,
0c6d3e5f-90e2-11e6-802e-842b2b5909d6:1-27,
a876d35e-9110-11e6-a365-842b2b5909d6:1-3,
b34df071-911a-11e6-9796-842b2b5909d6:1
LAST_CONFLICT_FREE_TRANSACTION:
1 row in set (0.00 sec)写入测试
这样一个初步的集群就搭建完成了，可以进行基本操作测试了：
在[server01]上执行如下语句：
mysql> create database bobo;
Query OK, 1 row affected (0.00 sec)
mysql> use bobo;
Database changed
mysql> create table test (id int primary key,name varchar(20));
Query OK, 0 rows affected (0.23 sec)
mysql> insert into test (id,name) values (1,'aaaa');
Query OK, 1 row affected (0.06 sec)
mysql> insert into test (id,name) values (2,'bbbb');
Query OK, 1 row affected (0.05 sec)
mysql> insert into test (id,name) values (3,'cccc');
Query OK, 1 row affected (0.03 sec)
mysql> select * from test;
+----+------+
| id | name |
+----+------+
| 1 | aaaa |
| 2 | bbbb |
| 3 | cccc |
+----+------+
3 rows in set (0.00 sec)在[server02][server03]上查询：
[server02]
mysql> use bobo;
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 test;
+----+------+
| id | name |
+----+------+
| 1 | aaaa |
| 2 | bbbb |
| 3 | cccc |
+----+------+
3 rows in set (0.00 sec)[server03]
mysql> use bobo;
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 test;
+----+------+
| id | name |
+----+------+
| 1 | aaaa |
| 2 | bbbb |
| 3 | cccc |
+----+------+
3 rows in set (0.00 sec)再在[server02]上执行如下语句：
mysql> insert into test (id,name) values (4,'dddd');
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement
mysql> show variables like '%read_only%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| innodb_read_only | OFF |
| read_only | ON |
| super_read_only | ON |
| tx_read_only | OFF |
+------------------+-------+
4 rows in set (0.00 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 test (id,name) values (4,'dddd');
Query OK, 1 row affected (0.04 sec)注：在新加入Group的节点中，默认会开启read_only和super_read_only，要最这个节点进行修改前，要关闭这两个选项。
在[server01]、[server03]上查询结果：
mysql> select * from test;
+----+------+
| id | name |
+----+------+
| 1 | aaaa |
| 2 | bbbb |
| 3 | cccc |
| 4 | dddd |
+----+------+
4 rows in set (0.00 sec)再在[server03]上执行如下语句：
mysql> update test set name='ddaa' where id=4;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from test;
+----+------+
| id | name |
+----+------+
| 1 | aaaa |
| 2 | bbbb |
| 3 | cccc |
| 4 | ddaa |
+----+------+
4 rows in set (0.00 sec)在[server01]、[server02]上查询结果：
mysql> select * from test;
+----+------+
| id | name |
+----+------+
| 1 | aaaa |
| 2 | bbbb |
| 3 | cccc |
| 4 | ddaa |
+----+------+
4 rows in set (0.00 sec)从以上测试可以看出，集群中没有节点中都可以进行写入，并且可以同步至集群中的其他节点。这也说明Group Replication可以拓展写操作。
补充：
MySQL Group Replicaiton默认也不是允许所有节点写入的，初始时集群中只保留一个可写节点(master)，其余节点的read_only/super_read_only参数都设置为ON。当master因故障down掉之后，组中的仲裁组件会选取一个节点将read_only/super_read_only设置成OFF,让这个节点充当master节点。
正好OneProxy中可以根据后端节点“read_only”的值来区分master和slave(http://www.onexsoft.com/zh/oneproxy-auto-readonly.html),那么Group Replication可以和OneProxy结合来实现更灵活的读写分离负载。
扫一扫
1283
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
