docker 环境mgr搭建

mysql版本：mysql:5.7.28

一、MySQL环境准备

1 docker创建三个MySQL环境

docker run -d --name mgr1 --hostname mgr1 \
-v /data2/mysql-1/conf:/etc/mysql:rw \
-v /data2/mysql-1/etc/hosts:/etc/hosts:rw \
-v /data2/mysql-1/mysql:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD='6yhn&UJM' \
-p 43306:3306 \
mysql:5.7.28

docker run -d --name mgr2 --hostname mgr2 \
-v /data2/mysql-2/conf:/etc/mysql:rw \
-v /data2/mysql-2/etc/hosts:/etc/hosts:rw \
-v /data2/mysql-2/mysql:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD='6yhn&UJM' \
-p 53306:3306 \
mysql:5.7.28

docker run -d --name mgr3 --hostname mgr3 \
-v /data2/mysql-3/conf:/etc/mysql:rw \
-v /data2/mysql-3/etc/hosts:/etc/hosts:rw \
-v /data2/mysql-3/mysql:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD='6yhn&UJM' \
-p 63306:3306 \
mysql:5.7.28

2 配置MySQL文件my.cnf

注意每个节点的server_id、loose-group_replication_local_address、loose-group_replication_group_seeds都配置成自己的相应的参数

[client]
port=3306
socket=/var/lib/mysql/mysqld.sock
default-character-set=utf8

[mysql]
no-auto-rehash
default-character-set=utf8

[mysqld]
port=3306
character-set-server=utf8
socket=/var/lib/mysql/mysqld.sock
basedir=/usr/lib/mysql
datadir=/var/lib/mysql
pid-file =/var/run/mysqld/mysqld.pid
log-error=/var/lib/mysql/error.log
bind-address = 0.0.0.0
#explicit_defaults_for_timestamp=true
#lower_case_table_names=1
#back_log=103
max_connections=3000
max_connect_errors=100000
table_open_cache=512
external-locking=FALSE
max_allowed_packet=32M
sort_buffer_size=2M
join_buffer_size=2M
thread_cache_size=51
query_cache_size=32M
#query_cache_limit=4M
transaction_isolation=REPEATABLE-READ
tmp_table_size=96M
max_heap_table_size=96M
log_timestamps=SYSTEM

###***slowqueryparameters
long_query_time=1
slow_query_log = 1
slow_query_log_file=/var/lib/mysql/slow.log

###***binlogparameters
log-bin=mysql-bin
binlog_cache_size=4M
max_binlog_cache_size=4096M
max_binlog_size=1024M
binlog_format=row
expire_logs_days=7

###***relay-logparameters
#relay-log=/data/3307/relay-bin
#relay-log-info-file=/data/3307/relay-log.info
#master-info-repository=table
#relay-log-info-repository=table
#relay-log-recovery=1
gtid_mode = ON
enforce_gtid_consistency = ON
master_info_repository = TABLE
relay_log_info_repository = TABLE
binlog_checksum = NONE
log_slave_updates = ON
transaction_write_set_extraction = XXHASH64
loose-group_replication_group_name = "aaa6a380-7459-42e4-9360-ecddbe058b69"
loose-group_replication_start_on_boot = off
loose-group_replication_local_address = "172.18.42.4:33063"
loose-group_replication_group_seeds ="172.18.42.2:33061,172.18.42.3:33062,172.18.42.4:33063"
loose-group_replication_bootstrap_group = off
#loose-group_replication_single_primary_mode = FALSE
#loose-group_replication_enforce_update_everywhere_checks = TRUE

plugin-load=group_replication.so

#***MyISAMparameters
key_buffer_size=16M
read_buffer_size=1M
read_rnd_buffer_size=16M
bulk_insert_buffer_size=1M
#skip-name-resolve

###***master-slavereplicationparameters
server-id=3
#slave-skip-errors=all

#***Innodbstorageengineparameters
innodb_buffer_pool_size=730M
innodb_data_file_path=ibdata1:10M:autoextend
#innodb_file_io_threads=8
innodb_thread_concurrency=16
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=16M
innodb_log_file_size=512M
innodb_log_files_in_group=2
innodb_max_dirty_pages_pct=75
innodb_buffer_pool_dump_pct=50
innodb_lock_wait_timeout=50
innodb_file_per_table=on

[mysqldump]
quick
max_allowed_packet=32M

[myisamchk]
key_buffer=16M
sort_buffer_size=16M
read_buffer=8M
write_buffer=8M

组复制部分，配置文件介绍：

group_replication变量使用的loose-前缀是指示Server启用时尚未加载复制插件也将继续启动

##指示Server必须为每个事务收集写集合，并使用XXHASH64哈希算法将其编码为散列
transaction_write_set_extraction = XXHASH64

##表示将加入或者创建的复制组命名为01e5fb97-be64-41f7-bafd-3afc7a6ab555
##可自定义(通过cat /proc/sys/kernel/random/uuid)
loose-group_replication_group_name="01e5fb97-be64-41f7-bafd-3afc7a6ab555"

##设置为Server启动时不自动启动组复制
loose-group_replication_start_on_boot=off 

##绑定本地的192.168.29.128及33061端口接受其他组成员的连接，IP地址必须为其他组成员可正常访问
loose-group_replication_local_address="172.18.42.2:33061" 

##本行为告诉服务器当服务器加入组时，应当连接到172.18.42.2:33061,172.18.42.3:33062,172.18.42.4:33063
##这些种子服务器进行配置。本设置可以不是全部的组成员服务地址。
loose-group_replication_group_seeds ="172.18.42.2:33061,172.18.42.3:33062,172.18.42.4:33063"

二、mgr环境配置

1.创建复制环境
在mgr1/mgr2/mgr3上建立复制账号：

set sql_log_bin=0;
grant replication slave,replication client on *.* to repl@'localhost' identified by 'repl';
grant replication slave,replication client on *.* to repl@'127.0.0.1' identified by 'repl';
grant replication slave,replication client on *.* to repl@'172.18.42.%' identified by 'repl';
SET SQL_LOG_BIN=1;
注：如果为三台独立节点，需要将localhost、127.0.0.1和远程主机域都授权用户

 

2.安装group replication插件、开启分布式复制

docker环境中先检查插件所属位置，若没有先将对应的附件copy到对应位置再安装

mysql> show variables like 'plugin_dir';
+---------------+----------------------------------+
| Variable_name | Value |
+---------------+----------------------------------+
| plugin_dir | /usr/lib/mysql/lib/mysql/plugin/ |
+---------------+----------------------------------+
1 row in set (0.01 sec)

安装插件：
在mgr1/mgr2/mgr3上依次安装group replication插件
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';

三、启动mgr集群
开始构建group replication集群，通常操作命令
在mgr1、mgr2、mgr3上依次执行
mysql> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='repl' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.02 sec)

mgr1上建立基本主库master库：
# 设置group_replication_bootstrap_group为ON是为了标示以后加入集群的服务器以这台服务器为基准（只要在mgr1上执行就可以了），以后加入的就不需要设置。
mysql> SET GLOBAL group_replication_bootstrap_group = ON;
mysql> START GROUP_REPLICATION;
mysql> SET GLOBAL group_replication_bootstrap_group = OFF;
mysql> select * from performance_schema.replication_group_members;

mgr2上启动group_replication：
mgr2上mysql命令行上执行启动：
mysql> set global group_replication_allow_local_disjoint_gtids_join=ON;
mysql> start group_replication;
mysql> select * from performance_schema.replication_group_members;

mgr3上启动group_replication：
mgr3命令行上执行：
mysql> set global group_replication_allow_local_disjoint_gtids_join=ON;
mysql> start group_replication;

-- 再去master库mgr1上，查看group_replication成员，会有mgr3的显示，而且已经是ONLINE了
mysql> select * from performance_schema.replication_group_members;

最后查看集群状态，都为ONLINE就表示OK：
mysql> select * from performance_schema.replication_group_members;

7、验证集群功能

在mgr1上建立测试库mgrtest，测试表t1，录入一条数据
mysql> create database mgrtest;
Query OK, 1 row affected (0.00 sec)

mysql> create table mgrtest.test(id int,cn varchar(32));
Query OK, 0 rows affected (0.02 sec)

 

主要问题：

一：这问题我在非docker环境时没有碰到过，但在docker环境里碰到了，在my.cnf里面有一个group_name, 这个名字可以不能设置为每个节点的uuid,比如节点1,2,3这几个节点，group_replication_group_name是需要一致的。之前每次失败都会认认真真拷贝uuid，发现适得其反，改成一致后立马可以了。

2020-06-01T09:31:15.360286-00:00 8 [Note] Plugin group_replication reported: 'Group Replication applier module successfully initialized!'
2020-06-01T09:31:15.360352-00:00 8 [Note] Plugin group_replication reported: 'auto_increment_increment is set to 7'
2020-06-01T09:31:15.360365-00:00 8 [Note] Plugin group_replication reported: 'auto_increment_offset is set to 2'
2020-06-01T09:31:15.361030-00:00 0 [Note] Plugin group_replication reported: 'XCom protocol version: 3'
2020-06-01T09:31:15.361087-00:00 0 [Note] Plugin group_replication reported: 'XCom initialized and ready to accept incoming connections on port 33062'
2020-06-01T09:31:15.365053-00:00 0 [Warning] Plugin group_replication reported: 'read failed'
2020-06-01T09:31:15.390436-00:00 0 [ERROR] Plugin group_replication reported: '[GCS] The member was unable to join the group. Local port: 33062'
2020-06-01T09:32:15.360556-00:00 8 [ERROR] Plugin group_replication reported: 'Timeout on wait for view after joining group'
2020-06-01T09:32:15.360673-00:00 8 [Note] Plugin group_replication reported: 'Requesting to leave the group despite of not being a member'
2020-06-01T09:32:15.360716-00:00 8 [ERROR] Plugin group_replication reported: '[GCS] The member is leaving a group without being on one.'
2020-06-01T09:32:15.361051-00:00 8 [Note] Plugin group_replication reported: 'auto_increment_increment is reset to 1'
2020-06-01T09:32:15.361076-00:00 8 [Note] Plugin group_replication reported: 'auto_increment_offset is reset to 1'
2020-06-01T09:32:15.361671-00:00 13 [Note] Error reading relay log event for channel 'group_replication_applier': slave SQL thread was killed
2020-06-01T09:32:15.361738-00:00 13 [Note] Slave SQL thread for channel 'group_replication_applier' exiting, replication stopped in log 'FIRST' at position 0
2020-06-01T09:32:15.412845-00:00 10 [Note] Plugin group_replication reported: 'The group replication applier thread was killed'