centos7搭建mysql多主组复制(MGR)

配置主机名与ip映射

192.168.1.130    mgr1
192.168.1.210    mgr2
192.168.1.211     mgr3

三台上面的配置文件  my.cnf

##mgr1的my.cnf配置文件
[mysqld]
user=mysql
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links = 0
#-------Public setup---------#
max_connect_errors = 20000
max_connections = 2000
wait_timeout = 3600
interactive_timeout = 3600
net_read_timeout = 3600
net_write_timeout = 3600
#-----Replilication Framework--MGR----------#
server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
log_bin=binlog
binlog_format=ROW
log_slave_updates=ON
binlog_checksum=NONE
log_bin_trust_function_creators=1;
relay-log-recovery=1
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="ce9be252-2b71-11e6-b8f4-00212844f856"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address="192.168.1.130:33061"
loose-group_replication_group_seeds="192.168.1.130:33061,192.168.1.210:33061,192.168.1.211:33061"
loose-group_replication_ip_whitelist='192.168.1.130,192.168.1.210,192.168.1.211'
loose-group_replication_bootstrap_group=off
loose-group_replication_single_primary_mode=true
loose-group_replication_enforce_update_everywhere_checks=false

#
##----------innoDB---------#
##----------Mysql Log-----------#
slow_query_log=on
slow-query-log-file=/var/log/mysql/slow-query.log
long_query_time=1
max_binlog_cache_size = 128M
max_binlog_size = 500M
binlog_cache_size = 64k
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid


##mgr2的my.cnf配置文件
[mysqld]
user=mysql
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links = 0
#skip-grant-tables
# #
#-------Public setup---------#
max_connect_errors = 20000
max_connections = 2000
wait_timeout = 3600
interactive_timeout = 3600
net_read_timeout = 3600
net_write_timeout = 3600
# # #-----Replilication Framework--MGR----------#
server_id=2
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
log_bin=binlog
binlog_format=ROW
log_slave_updates=ON
binlog_checksum=NONE
log_bin_trust_function_creators=1;
read-only=off
relay-log-recovery=1
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="ce9be252-2b71-11e6-b8f4-00212844f856"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address="192.168.1.210:33061"
loose-group_replication_group_seeds=
"192.168.1.130:33061,192.168.1.210:33061,192.168.1.211:33061"
loose-group_replication_ip_whitelist='xxx.130,xxxxx.210,xxxxxx.211'
loose-group_replication_bootstrap_group=off
loose-group_replication_single_primary_mode=true
loose-group_replication_enforce_update_everywhere_checks=false
#
#
##----------innoDB---------#
#####----------Mysql Log-----------#
slow_query_log=on
slow-query-log-file=/var/log/mysql/slow-query.log
long_query_time=1
max_binlog_cache_size = 128M
max_binlog_size = 500M
binlog_cache_size = 64k
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid


####mgr3的my.cnf配置文件
[mysqld]
user=mysql
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links = 0
#skip-grant-tables
#
#-------Public setup---------#
max_connect_errors = 20000
max_connections = 2000
wait_timeout = 3600
interactive_timeout = 3600
net_read_timeout = 3600
net_write_timeout = 3600
### #-----Replilication Framework--MGR----------#
##
server_id=3
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
log_bin=binlog
binlog_format=ROW
log_slave_updates=ON
binlog_checksum=NONE
log_bin_trust_function_creators=1;
read-only=off
relay-log-recovery=1
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="ce9be252-2b71-11e6-b8f4-00212844f856"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "xxxxxx.211:33061"
loose-group_replication_group_seeds= "xxxx.211:33061,xxxxxx.210:33061,xxxxx.211:33061"
loose-group_replication_ip_whitelist='xxxx.130,xxxxx.210,xxxxxx.211'
loose-group_replication_bootstrap_group=off
loose-group_replication_single_primary_mode=true
loose-group_replication_enforce_update_everywhere_checks=false
##-------------------------------------------------------------###########
#
###----------innoDB---------#
#
####----------Mysql Log-----------#
slow_query_log=on
slow-query-log-file=/var/log/mysql/slow-query.log
long_query_time=1
max_binlog_cache_size = 128M
max_binlog_size = 500M
binlog_cache_size = 64k
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

在mgr1 mgr2 mgr3 三台上面的都执行的操作

开启三台mysql用户之间的授权 确认telnet可以相互通 记得开启防火墙端口或关闭防火墙
mysql -uroot -p password
SET SQL_LOG_BIN=0;
grant all privileges on *.* to 'root'@'192.168.1.130' identified by 'mysqlpassowrd';
grant all privileges on *.* to 'root'@'192.168.1.210' identified by '2bgvRXeQH#fpqfcW';
grant all privileges on *.* to 'root'@'192.168.1.211' identified by '2bgvRXeQH#fpqfcW';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;

###指定组复制渠道
CHANGE MASTER TO MASTER_USER='root', MASTER_PASSWORD='password' FOR CHANNEL 'group_replication_recovery';  
###安装插件
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
show plugins;

mgr1上建立基本主库master库：

# 设置group_replication_bootstrap_group为ON是为了标示以后加入集群的服务器以这台服务器为基准，以后加入的就不需要设置。  

mysql> SET GLOBAL group_replication_bootstrap_group = ON;    

mysql>  START GROUP_REPLICATION;    

查看集群状态
mysql> select * from performance_schema.replication_group_members; 

mgr2上启动group_replication： 

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：

mysql> set global group_replication_allow_local_disjoint_gtids_join=ON;  

mysql> start group_replication;  

   

-- 再去master库mgr1上，查看group_replication成员，三行显示 而且已经是ONLINE了  

mysql> select * from performance_schema.replication_group_members;

   搭建成功的标记

报错记录

报错解决
ERROR 3094 (HY000): The START GROUP_REPLICATION command failed as the applier module failed to start.、

解决办法:

reset slave;
set global group_replication_allow_local_disjoint_gtids_join=ON;
start group_replication;

报错 数据不同步的处理办法
[ERROR] Plugin group_replication reported: '[GCS] Error pushing message into group communication engine
[ERROR] Slave SQL for channel 'group_replication_recovery': Error 'Can't drop database 'morey_mgr1'; database doesn't exist' on query. Default database: 'morey_mgr1'. Query: 'drop database morey_mgr1', Error_code: 1008
[Warning] Slave: Can't drop database 'morey_mgr1'; database doesn't exist Error_code: 1008
[ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'binlog.000003' position 1290.

解决办法

reset master;   //删除所有binlog日志
reset slave;
set global group_replication_allow_local_disjoint_gtids_join=ON;
start group_replication;
select * from performance_schema.replication_group_members;