Docker搭建Mysql Group Replication环境

linux版本：oracle linux 7.6 x86_64

目标：在该linux服务器上，安装docker，并用docker创建三个容器实现三节点的mysql mgr复制环境.

一.概念简述：

docker是什么：

docker包括一个命令行程序，一个后台守护进程以及一组远程服务。

它解决了常见的软件问题，并简化了安装，运行，发布和删除软件，这一切能够实现是通过使用了一项Unix技术，称为容器。容器是docker的重要概念。

mysql mgr是什么：

MGR是一个实现了mysql多节点读写的集群方案，

它基于原生复制及Paxos协议的组复制技术，并以插件的方式提供，实现了数据的强一致性，节点切换也变得简单自动，扩展性也很方便。

二.安装docker:

[root@mysqlpri]#yum-config-manager --add-repo http://mirrors.aliyun.com/docker-ce/linux/centos/docker-ce.repo

[root@mysqlpri]#yum makecache fast

[root@mysqlpri]#yum list docker-ce --showduplicates | sort -r

[root@mysqlpri]#yum install -y docker-ce-19.03.8

这个时候会报如下错误：

Error: Package: 3:docker-ce-19.03.8-3.el7.x86_64 (docker-ce-stable)

Requires: container-selinux >= 2:2.74

解决办法：

单独安装container-selinux包，

yum install http://mirror.centos.org/centos/7/extras/x86_64/Packages/container-selinux-2.107-1.el7_6.noarch.rpm

继续安装docker即可顺利完成安装:

[root@mysqlpri yum.repos.d]# yum install -y docker-ce-19.03.6

这里ce表示的community edition，社区版。

配置开机自启动：

[root@mysqlpri ~]# systemctl start docker

[root@mysqlpri ~]# systemctl status docker

[root@mysqlpri ~]# systemctl enable docker

docker其他常用命令：

docker version 查看版本

docker ps 查看当前正在运行的容器

docker ps -a查看所有容器

docker rm container_id/container_name 删除某个容器

docker inspect container_id/container_name查看某个容器的元信息

docker images 查看本地镜像

更多的docker命令在后面用到再提

三.docker搭建MGR环境

创建目录：

[root@mysqlpri ~]# mkdir /docker

[root@mysqlpri ~]# cd /docker/

[root@mysqlpri docker]# pwd

/docker

[root@mysqlpri docker]#

创建一个新的docker网络环境，可以看到下面创建了group1的网络，这个网络的属性为Subnet: 172.18.0.0/16,Gateway: 172.18.0.1等等。

[root@mysqlpri docker]# docker network ls

NETWORK ID NAME DRIVER SCOPE

55202ff3cec7 bridge bridge local

0399c5ca21d4 host host local

ee8794f8062d none null local

[root@mysqlpri docker]# docker network create group1

15c6de2e831ae956c3d24cfb937ff8b63be5dd9cca71bdb0ccb9bee69441e043

[root@mysqlpri docker]# docker network ls

NETWORK ID NAME DRIVER SCOPE

55202ff3cec7 bridge bridge local

15c6de2e831a group1 bridge local

0399c5ca21d4 host host local

ee8794f8062d none null local

[root@mysqlpri docker]# docker inspect group1

[

{

"Name": "group1",

"Id": "15c6de2e831ae956c3d24cfb937ff8b63be5dd9cca71bdb0ccb9bee69441e043",

"Created": "2020-05-12T09:44:38.324204388+08:00",

"Scope": "local",

"Driver": "bridge",

"EnableIPv6": false,

"IPAM": {

"Driver": "default",

"Options": {},

"Config": [

{

"Subnet": "172.18.0.0/16",

"Gateway": "172.18.0.1"

}

]

"Internal": false,

"Attachable": false,

"Ingress": false,

"ConfigFrom": {

"Network": ""

"ConfigOnly": false,

"Containers": {},

"Options": {},

"Labels": {}

}

]

创建一个用于mysql的模板配置文件，可以看到这个文件包含了基本的mysql服务器配置，额外的还包含了GR的一些配置选项。

vi my-template.cnf

[mysqld]

user=mysql

server_id=_SERVER_ID_

gtid_mode=ON

enforce_gtid_consistency=ON

master_info_repository=TABLE

relay_log_info_repository=TABLE

binlog_checksum=NONE

log_slave_updates=ON

log_bin=mysql-bin

relay-log=relay

binlog_format=ROW

log-error=mysqld.err

sync-binlog=1

transaction_write_set_extraction=XXHASH64

loose-group_replication_group_name="aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee"

loose-group_replication_start_on_boot=off

loose-group_replication_local_address= "172.18.0._IP_END_:6606"

loose-group_replication_group_seeds= "172.18.0.2:6606,172.18.0.3:6606,172.18.0.4:6606"

loose-group_replication_ip_whitelist="172.18.0.2,172.18.0.3,172.18.0.4,127.0.0.1"

loose-group_replication_bootstrap_group= off

继续操作，

vi create_container.sh

#!/bin/bash

for node in 1 2 3

export SERVERID=$node

export IPEND=$(($SERVERID+1))

perl -pe 's/_SERVER_ID_/$ENV{SERVERID}/;s/_IP_END_/$ENV{IPEND}/' my-template.cnf > my${node}.cnf

datadir=ddnode${node}

if [ ! -d $datadir ]

then

mkdir $datadir

unset SERVERID

docker run -d --name=node$node --net=group1 --hostname=node$node

-v $PWD/my${node}.cnf:/etc/my.cnf

-v $PWD/data:/data

-v $PWD/$datadir:/var/lib/mysql

-e MYSQL_ROOT_PASSWORD=root

mysql/mysql-server:latest

ip=$(docker inspect --format '{{ .NetworkSettings.Networks.group1.IPAddress}}' node${node})

echo "${node} $ip"

done

跑完脚本后可以docker ps可以看到三个up的容器，并可以看到当前目录/docker下也创建了如下的文件。

[root@mysqlpri docker]# chmod 755 create_container.sh

[root@mysqlpri docker]# ./create_container.sh

[root@mysqlpri docker]# ls -lrt

total 32

-rw-r--r-- 1 root root 730 May 12 09:58 my-template.cnf

drwxr-xr-x 2 root root 32 May 12 10:03 data

-rwxr-xr-x 1 root root 678 May 12 10:09 create_container.sh

-rw-r--r-- 1 root root 713 May 12 10:11 my1.cnf

-rw-r--r-- 1 root root 713 May 12 10:11 my2.cnf

-rw-r--r-- 1 root root 713 May 12 10:11 my3.cnf

drwxr-xr-x 6 27 27 4096 May 12 10:11 ddnode2

drwxr-xr-x 6 27 27 4096 May 12 10:11 ddnode3

drwxr-xr-x 6 27 27 4096 May 12 10:11 ddnode1

[root@mysqlpri docker]# docker ps

CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES

7c552e505d4e mysql/mysql-server:latest "/entrypoint.sh mysq…" 32 minutes ago Up 7 minutes (healthy) 3306/tcp, 33060/tcp node3

b865e78b4164 mysql/mysql-server:latest "/entrypoint.sh mysq…" 32 minutes ago Up 8 minutes (healthy) 3306/tcp, 33060/tcp node2

2d8e17431315 mysql/mysql-server:latest "/entrypoint.sh mysq…" 32 minutes ago Up 8 minutes (healthy) 3306/tcp, 33060/tcp node1

安装GR插件：

[root@mysqlpri docker]# for N in 1 2 3; do docker exec -ti node$N mysql -u root -proot -e "INSTALL PLUGIN group_replication SONAME 'group_replication.so' "; done

mysql: [Warning] Using a password on the command line interface can be insecure.

[root@mysqlpri docker]#

配置GR并启动GR复制：

node1上操作：

docker exec -it node1 mysql -uroot -proot

-e "SET @@GLOBAL.group_replication_bootstrap_group=1;"

-e "SET SQL_LOG_BIN=0;"

-e "create user 'repl'@'%' identified by 'repl';"

-e "GRANT REPLICATION SLAVE ON *.* TO repl@'%';"

-e "ALTER USER 'repl'@'%' IDENTIFIED WITH mysql_native_password; "

-e "alter user 'repl'@'%' identified by 'repl';"

-e "flush privileges;"

-e "SET SQL_LOG_BIN=1;"

-e "change master to master_user='repl' for channel 'group_replication_recovery';"

-e "START GROUP_REPLICATION;"

-e "SET @@GLOBAL.group_replication_bootstrap_group=0;"

-e "SELECT * FROM performance_schema.replication_group_members;"

node2,3上操作：

for N in 2 3

do docker exec -it node$N mysql -uroot -proot

-e "SET SQL_LOG_BIN=0;"

-e "create user 'repl'@'%' identified by 'repl';"

-e "GRANT REPLICATION SLAVE ON *.* TO repl@'%';"

-e "ALTER USER 'repl'@'%' IDENTIFIED WITH mysql_native_password; "

-e "alter user 'repl'@'%' identified by 'repl';"

-e "flush privileges;"

-e "SET SQL_LOG_BIN=1;"

-e "change master to master_user='repl', master_password='repl' for channel 'group_replication_recovery';"

-e "START GROUP_REPLICATION;"

done

最后在三个节点上查看performance_schema视图都可以看到一致的结果：

[root@mysqlpri ddnode2]# docker exec -it node1 mysql -uroot -proot -e "SELECT * FROM performance_schema.replication_group_members;"

+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+

+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+

+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+

安装完毕。