未加星标

MySQLGroupReplication初测

字体大小 | |
[数据库(mysql) 所属分类 数据库(mysql) | 发布者 店小二04 | 时间 2016 | 作者 红领巾 ] 0人收藏点击收藏
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方法比较类似。
MySQLGroupReplication初测
Group Replication简单构架图:
MySQLGroupReplication初测

早期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
解压安装包
[[email protected] local]# tar -zxvf mysql-5.7.15-labs-gr090-linux-glibc2.5-x86_64.tar.gz
[[email protected] local]# mv mysql-5.7.15-labs-gr090-linux-glibc2.5-x86_64 mysql5.7
[[email protected] 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结合来实现更灵活的读写分离负载。

本文数据库(mysql)相关术语:navicat for mysql mysql workbench mysql数据库 mysql 存储过程 mysql安装图解 mysql教程 mysql 管理工具

分页:12
转载请注明
本文标题:MySQLGroupReplication初测
本站链接:http://www.codesec.net/view/483625.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mysql) | 评论(0) | 阅读(42)