mysql组复制集群拓扑：
环境：
centos6.5
mysql5.7.19
一、组复制搭建：
配置hosts文件
再三台服务器上分别启动一个mysql实例，共三个。
参考配置文件如下：
server_id=10203306
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=binlog
binlog_format=ROW
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "192.168.10.20:33306"
loose-group_replication_group_seeds= "192.168.10.10:33306,192.168.10.20:33306,192.168.10.30:33306"
loose-group_replication_bootstrap_group= off
loose-group_replication_single_primary_mode=FALSE
loose-group_replication_enforce_update_everywhere_checks= TRUE
#loose-group_replication_single_primary_mode=true # 单主模式
#loose-group_replication_enforce_update_everywhere_checks=false # 单主模式
登陆mysql进行组复制配置：
第一个节点：
SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%' IDENTIFIED BY 'rpl_pass';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_pass' FOR CHANNEL 'group_replication_recovery';
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
启动组复制：
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;
第二个节点：
SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%' IDENTIFIED BY 'rpl_pass';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_pass' FOR CHANNEL 'group_replication_recovery';
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
START GROUP_REPLICATION;
第三个节点：
SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%' IDENTIFIED BY 'rpl_pass';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_pass' FOR CHANNEL 'group_replication_recovery';
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
START GROUP_REPLICATION;
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
START GROUP_REPLICATION;
配置完成查看集群成员：
SELECT * FROM performance_schema.replication_group_members;
二、mysqlrouter配置：
mysqlrouter安装：
官网下载通用二进制包解包，自定义下目录和创建配置文件 即可启动使用
mysqlrouter配置文件参考：
[DEFAULT]
logging_folder = /usr/local/mysqlrouter/logs
[logger]
level = INFO
[routing:failover]
bind_address = 0.0.0.0
bind_port = 7001
max_connections = 1024
mode = read-write # 高可用
destinations = 192.168.10.10:3306,192.168.10.20:3306 # 第一个socket不可用，则会转移到第二个socket
[routing:balancing]
bind_address = 0.0.0.0
bind_port = 7002
max_connections = 1024
mode = read-only # 轮询读
destinations = 192.168.10.20:3306, 192.168.10.30:3306 # 对这组socket进行 轮询读
启动mysqlrouter：
/usr/local/mysqlrouter/bin/mysqlrouter -c /usr/local/mysqlrouter/conf/mysqlroute.conf
三、keepalived配置：
keepalived参考配置文件：
global_defs {
lvs_id proxy_HA
}
vrrp_script check_proxy {
script "/etc/keepalived/check.sh" #检查脚本
interval 1
weight 2
}
# Virtual interface
# The priority specifies the order in which the assigned interface to take over in a failover
vrrp_instance VI_01 {
state BACKUP # 主备多设置伟BACKUP
nopreempt # 非抢占模式
interface em1
virtual_router_id 93
priority 100
authentication {
auth_type PASS
auth_pass mcDBhapwd
}
virtual_ipaddress {
192.168.10.2 dev em1 # vip
}
track_script {
check_proxy
}
notify_master /etc/keepalived/dd/notify_master.py
notify_fault /etc/keepalived/dd/notify_fault.py
notify_stop /etc/keepalived/dd/notify_stop.py
}
参考check脚本：
#!/bin/bash
pass='xxxxx'
PROXYSQL_CMD="/usr/local/mysql/bin/mysql -uxx -p${pass} -P7001 -h 192.168.10.2 "
declare rs=0
$PROXYSQL_CMD -Ns -e "select 1"
if [ $? -eq 0 ];then
echo OK,rs=$?
exit 14
else
declare -i num=0
while [ $num -lt 4 ];do
$PROXYSQL_CMD -Ns -e "select 1"
if [ $? -gt 0 ];then
((rs++))
fi
((num++));
done
if [ $rs -ge 3 ];then
service keepalived stop
fi
fi