文章目录
  1. 1. Introduction
  2. 2. About MySQL Group Replication
  3. 3. Architecture of MGR plugin
  4. 4. Resource about Group Replication
  5. 5. Build a MGR cluster with three nodes
    1. 5.1. Requirement
    2. 5.2. 基于 centos7 镜像进行手工部署
      1. 5.2.1. 启动三个 Container
      2. 5.2.2. Software installation
      3. 5.2.3. Configuration & Initialization
      4. 5.2.4. Start MySQL instance
    3. 5.3. 基于我打好的image来提供运行环境
  6. 6. 配置 Group Replication
  7. 7. Summary

Introduction

This is a series post about learning mysql group replication (including deployment ,operations, ha, performance testing, internals).This is the first post and i will share the way to build a MGR cluster in docker enviorement.

About MySQL Group Replication

Group Replication is a technique that can be used to implement fault-tolerant systems. The replication group is a set of servers that interact with each other through message passing. The communication layer provides a set of guarantees such as atomic message and total order message delivery. These are very powerful properties that translate into very useful abstractions that one can resort to build more advanced database replication solutions.

上面的是官方文档对gr的一个简单的解释. 随着 MySQL5.7.17 版本的发布, Group Replication(后面简称 mgr)也随着发布了GA版本, MGR也是目前开源数据库界最热门的话题之一.Oracle官方出品类似 pxc 的数据库高可用解决方案. 个人认为 MGR 将会对现有HA方案产生很大的影响,MGR的前景很明朗,将会是很多场景下的利器.

Architecture of MGR plugin

架构图

Resource about Group Replication

gr 目前已经出了第一个稳定版, 学习gr刻不容缓哈(技术人都是这样, 苦逼的一直学习)
下面列举了一些相关资源

  • MySQL 5.7 官方文档 链接

  • Frederic Descamps 介绍Group Replication的 ppt

  • MySQL High Availability 有很多关于MGR的 文章

  • Galera 和 MGR的对比 链接

Build a MGR cluster with three nodes

为了学习和测试, 需要部署一套真实的MGR运行环境. 接下来我就介绍一种基于容器来快速构建mgr cluster的方法. 方便, 简单, 效率, 让你玩转 MGR.

Requirement

一台安装有docker的可以上网的机器(Windows, mac, Linux).

从docker hub 上下载基础镜像

1.基于 Centos 7 镜像进行手工部署

基于centos 来进行手工部署. 容器,虚拟机 ,实体机三者应该是极其相似的. 既然以容器为例子, 那么就要下载 image

1
docker pull centos

2.基于我打好的image来部署 MGR

打包好的镜像本身就已经部署完成 5.7.17GA, 启动容器的时候, 就会直接运行 mysql 实例

1
docker pull ruiaylin/mysql57-group-replicaiton

由于Group replication的部署需要节点协调, 采用容器的方式可以很快速的进行部署,以供测试
目前还没有实现自动集群部署, 不过后续依赖etcd完全可以自动化进行部署.

进行下面步骤的前提是先将对应的镜像下载完成, 两者原理是一样的只是第二种方式更灵活. 两者都是先进行gr基本环境准备. gr的配置需要单独进行配置.

基于 centos7 镜像进行手工部署

由于第一种部署方式, 做了很多初始化工作, 我们现在进行手工部署的方式.

启动三个 Container

创建:

docker run -it  --name mgr00  -d centos
docker run -it  --name mgr01  -d centos
docker run -it  --name mgr02  -d centos

登录:

docker exec -it mgr00 bash
docker exec -it mgr01 bash
docker exec -it mgr02 bash

Software installation

Install 5.7.17 stable version through yum or you can download the tarball.
This is the rpm installation:

1
2
rpm -ivh http://dev.mysql.com/get/mysql57-community-release-el7-9.noarch.rpm
yum install mysql mysql-server -y

Configuration & Initialization

Configuration

启用 gr 的前提条件如下: 具体可以参考文档 Requirements and Limitations

1
2
3
4
5
6
7
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
binlog_format=ROW

Group replication主要相关参数(都可以动态在线设置):

1
2
3
4
5
6
7
8
transaction-write-set-extraction = XXHASH64
group_replication_start_on_boot = OFF
group_replication_bootstrap_group = OFF
group_replication_group_name = 716483bc-c763-11e6-93c0-0242ac110002
group_replication_local_address = ''
group_replication_group_seeds = ''
group_replication_single_primary_mode=FALSE
group_replication_enforce_update_everywhere_checks=TRUE

下面是一个测试样例 my.cnf :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
[mysql]
prompt=\\u@\\d \\r:\\m:\\s>
default-character-set=utf8mb4
[mysqld]
server_id = 1
log_timestamps=SYSTEM
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
default-storage-engine=INNODB
log_timestamps = SYSTEM
#dir
innodb_data_file_path=ibdata1:10M:autoextend:max:10G
datadir=/var/lib/mysql
slow_query_log_file=/var/lib/mysql/slow.log
log-error=/var/log/mysqld.log
log_bin=/var/lib/mysql/binlog
relay-log=/var/lib/mysql/relaylog
#innodb
innodb_buffer_pool_size = 64M
innodb_log_files_in_group=2
innodb_flush_log_at_trx_commit=1
innodb_io_capacity=100
innodb_read_io_threads=4
innodb_write_io_threads=4
innodb_file_per_table=1
innodb_adaptive_flushing=1
innodb_stats_on_metadata=0
innodb_flush_method=O_DIRECT
innodb_log_buffer_size=10M
transaction-isolation = READ-COMMITTED
#table
table_definition_cache=512
table_open_cache=512
table_open_cache_instances = 2
#thread
thread_stack=262144
thread_cache_size=256
thread_handling=one-thread-per-connection
#gtid&repl
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
#binlog
max_binlog_size=50M
binlog_cache_size=5M
binlog-format=ROW
sync_binlog=1
long_query_time=0.2
slow_query_log=1
skip-slave-start
#coms
query_cache_type=0
query_cache_size=0
local-infile=0
port=3306
max_connections=110
max_user_connections=100
character-set-server=utf8mb4
skip-name-resolve
# GR
plugin_load = "group_replication.so"
transaction_write_set_extraction = XXHASH64
group_replication_start_on_boot = OFF
group_replication_bootstrap_group = OFF
group_replication_group_name = 716483bc-c763-11e6-93c0-0242ac110002
group_replication_local_address = ''
group_replication_group_seeds = ''
group_replication_single_primary_mode=FALSE
group_replication_enforce_update_everywhere_checks=TRUE

初始化

最后一部分 gr 的相关配置参数, 都是可以在线动态修改的:

1
2
3
4
DATADIR="$("mysqld" --verbose --wsrep_provider= --help 2>/dev/null | awk '$1 == "datadir" { print $2; exit }')"
chown -R mysql:mysql "$DATADIR"
chown mysql:mysql /var/log/mysqld.log
mysqld --initialize-insecure --user=mysql

Start MySQL instance

每个容器(虚机,实体机)启动:

mysqld --user=mysql & 

基于我打好的image来提供运行环境

启动三个已经初始化完成的MySQL运行实例:

1
2
3
docker run -it --name mgr0x0 -e MYSQL_ROOT_PASSWORD=root -d ruiaylin/mysql57-group-replication
docker run -it --name mgr0x1 -e MYSQL_ROOT_PASSWORD=root -d ruiaylin/mysql57-group-replication
docker run -it --name mgr0x2 -e MYSQL_ROOT_PASSWORD=root -d ruiaylin/mysql57-group-replication

查看容器:

1
2
3
4
5
➜  ~ docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
23361ae481fa ruiaylin/mysql57-group-replication "/entrypoint.sh " About an hour ago Up About an hour 3306/tcp mgr0x2
2e8a57225332 ruiaylin/mysql57-group-replication "/entrypoint.sh " About an hour ago Up About an hour 3306/tcp mgr0x1
1cb5c7fb1505 ruiaylin/mysql57-group-replication "/entrypoint.sh " About an hour ago Up About an hour 3306/tcp mgr0x0

获取容器的IP(去掉 NetworkSettings.IPAddress 左右大括号的反斜杠 ):

1
2
3
4
5
for x in `  docker ps |grep mgr | awk '{print $1}'`
do
ip=`docker inspect --format='\{\{.NetworkSettings.IPAddress\}\}' $x`
echo $ip $x
done

Output :

1
2
3
172.17.0.7 23361ae481fa
172.17.0.6 2e8a57225332
172.17.0.5 1cb5c7fb1505

将该输出放入到每个容器的 /etc/hosts 里面

你有可能会问为什么 ? 下一篇文章会详细介绍, 敬请期待

配置 Group Replication

配置白名单的网段:

默认值是: 127.0.0.1/32,10.0.0.0/8,172.16.0.0/12,192.168.0.0/16 ; 需要添加自己所在网段的配置.

1
set global group_replication_ip_whitelist="127.0.0.1/32,10.0.0.0/8,172.16.0.0/12,192.168.0.0/16,172.17.0.0/16"

否则会无法访问group, 报错如下:

1
2
3
2016-12-20T05:11:32.220173-00:00 0 [Warning] Plugin group_replication reported: '[GCS] Connection attempt from IP address 172.17.0.5 refused. Address is not in the IP whitelist.'
2016-12-20T05:11:32.220240-00:00 0 [ERROR] Plugin group_replication reported
2016-12-20T05:12:32.203122-00:00 4 [ERROR] Plugin group_replication reported: '[GCS] The member is leaving a group without being on one.'

配置参数:

注意每个节点的 group_replication_local_address 值都是不一样的根据自己的IP来设置

1
2
3
4
5
6
7
8
set global transaction_write_set_extraction = XXHASH64
set global group_replication_start_on_boot = OFF
set global group_replication_bootstrap_group = OFF
set global group_replication_group_name = 716483bc-c763-11e6-93c0-0242ac110002
set global group_replication_local_address = '172.17.0.5:3316'
set global group_replication_group_seeds = '172.17.0.5:3316,172.17.0.6:3316,172.17.0.7:3316'
set global group_replication_single_primary_mode=FALSE
set global group_replication_enforce_update_everywhere_checks=TRUE

创建复制账户(每个节点都要执行)

1
2
3
4
5
6
SET SQL_LOG_BIN=0;
CREATE USER repl@'%';
GRANT REPLICATION SLAVE ON *.* TO repl@'%' IDENTIFIED BY 'repl';
SET SQL_LOG_BIN=1;
# 进行复制调整
CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='repl' FOR CHANNEL 'group_replication_recovery';

启动组复制

第一个节点需要执行:

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

其余节点都应该执行如下:

START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;

检查状态:

1
2
3
4
5
6
7
8
9
    root@(none) 11:08:47>SELECT * FROM performance_schema.replication_group_members;;
+---------------------------+--------------------------------------+--------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+--------------+-------------+--------------+
| group_replication_applier | 2bde3d22-c82b-11e6-9c0b-0242ac110005 | 1cb5c7fb1505 | 3306 | ONLINE |
| group_replication_applier | 666b46de-c82b-11e6-9f57-0242ac110006 | 2e8a57225332 | 3306 | ONLINE |
| group_replication_applier | 6d0bbe81-c82b-11e6-9dbf-0242ac110007 | 23361ae481fa | 3306 | ONLINE |
+---------------------------+--------------------------------------+--------------+-------------+--------------+
3 rows in set (0.00 sec)

至此 , 一个有三个节点的 Multi-Master Group Replication 集群构建完成. 好好去把玩一下把, 希望你喜欢哈哈.

Summary

很久不写东西了, 17年多写写东西. 本着学习分享为本, 以后多写写东西. 这个我 第一篇 写关于MGR的东西, 后续将会持续更新源于管理维护, 监控, 性能测试, 以及 internal 的相关内容.

文章目录
  1. 1. Introduction
  2. 2. About MySQL Group Replication
  3. 3. Architecture of MGR plugin
  4. 4. Resource about Group Replication
  5. 5. Build a MGR cluster with three nodes
    1. 5.1. Requirement
    2. 5.2. 基于 centos7 镜像进行手工部署
      1. 5.2.1. 启动三个 Container
      2. 5.2.2. Software installation
      3. 5.2.3. Configuration & Initialization
      4. 5.2.4. Start MySQL instance
    3. 5.3. 基于我打好的image来提供运行环境
  6. 6. 配置 Group Replication
  7. 7. Summary