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都配置成自己的相应的参数
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
[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
组复制部分,配置文件介绍:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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'