mysql 5.7+ MGR配置
说明：
此次配置的是异机的三台MGR环境，多实例的方式可能有些差异，建议根据实际情况调整。
主机 hostname 数据库端口 MGR配置端口 linux版本
192.169.100.160 mgr1 3307 33071 centos 6.5
192.169.100.161 mgr2 3307 33071 centos 6.5
192.169.100.162 mgr3 3307 33071 centos 6.5
配置步骤
1. 配置主机名和hosts文件(所有主机)
vi /etc/sysconfig/network NETWORKING=yes HOSTNAME=mgr1-3 vi /etc/hosts 127.0.0.1 localhost localhost.localdomain 192.169.100.160 mgr1 192.169.100.161 mgr2 192.169.100.162 mgr3
2.关闭防火墙和selinx
service iptables stop chkconfig disalbe iptables vi /etc/selinux/config SELINUX=disabled
3. 配置数据库配置文件（数据库安装部分略过）
192.169.100.160 (以下是简单的MGR配置文件)
vi /etc/mymgr.cnf
[mysqld]
user=mysql
datadir=/data/mysql3307/data
basedir=/usr/local/mysql
port=3307
socket=/data/mysql3307/data/mysql.sock
log-error = /data/mysql3307/logs/mysql3306.err
log-bin = /data/mysql3307/logs/binary/mybinlog
server_id=1
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="e2f6a3b1-2e19-4873-a5d6-9ab8455f5ce2" #该名称的配置是三个节点须一致，否者将无法加入其它节点
loose-group_replication_start_on_boot=off
loose-group_replication_local_address="192.169.100.160:33071" #对应自己的ip和端口
loose-group_replication_group_seeds="192.169.100.160:33071,192.169.100.161:33071,192.169.100.162:33071"
loose-group_replication_bootstrap_group=off
loose-group_replication_single_primary_mode=FALSE
loose-group_replication_enforce_update_everywhere_checks= TRUE
relay-log-recovery=1
#skip-grant-tables=1
192.169.100.161 (以下是简单的MGR配置文件)
[mysqld] user=mysql datadir=/data/mysql3307/data basedir=/usr/local/mysql port=3307 socket=/data/mysql3307/data/mysql.sock log-error = /data/mysql3307/logs/mysql3306.err log-bin = /data/mysql3307/logs/binary/mybinlog server_id=2 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 #skip-grant-tables=1 transaction_write_set_extraction=XXHASH64 loose-group_replication_group_name="e2f6a3b1-2e19-4873-a5d6-9ab8455f5ce2" loose-group_replication_start_on_boot=off loose-group_replication_local_address="192.169.100.161:33071" loose-group_replication_group_seeds="192.169.100.160:33071,192.169.100.161:33071,192.169.100.162:33071" loose-group_replication_bootstrap_group=off loose-group_replication_single_primary_mode=FALSE loose-group_replication_enforce_update_everywhere_checks= TRUE relay-log-recovery=1
192.169.100.162 (以下是简单的MGR配置文件)
[mysqld] user=mysql datadir=/data/mysql3307/data basedir=/usr/local/mysql port=3307 socket=/data/mysql3307/data/mysql.sock log-error = /data/mysql3307/logs/mysql3306.err log-bin = /data/mysql3307/logs/binary/mybinlog server_id=3 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 #skip-grant-tables=1 transaction_write_set_extraction=XXHASH64 loose-group_replication_group_name="e2f6a3b1-2e19-4873-a5d6-9ab8455f5ce2" loose-group_replication_start_on_boot=off loose-group_replication_local_address="192.169.100.162:33071" loose-group_replication_group_seeds="192.169.100.160:33071,192.169.100.161:33071,192.169.100.162:33071" loose-group_replication_bootstrap_group=off loose-group_replication_single_primary_mode=FALSE loose-group_replication_enforce_update_everywhere_checks= TRUE relay-log-recovery=1
4. 初始化三台mysql 数据库（数据库安装部分略过）
/usr/local/mysql/bin/mysqld --defaults-file=/etc/mymgr.cnf --basedir=/usr/local/mysql --datadir=/data/mysql3307/data --user=mysql --initialize &
5.启动数据库
/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/mymgr.cnf &
配置密码之后登录：
mysql -uroot -proot -S /data/mysql3307/data/mysql.sock
6. 添加mgr用户用于复制：（所有节点）
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@'192.169.100.%' identified by 'repl'; SET SQL_LOG_BIN=1; flush privileges;
7.192.169.100.160 上执行（开启分布式复制)：
开启分布式复制
change master to master_user='repl',master_password='repl' for channel 'group_replication_recovery';
加载GR插件
install plugin group_replication soname 'group_replication.so';
show plugins;
启动复制程序(主)
set global group_replication_bootstrap_group=ON;
启动单主模式：
set global group_replication_enforce_update_everywhere_checks=OFF;
set global group_replication_single_primary_mode=on;
允许其它节点加入：
set global group_replication_allow_local_disjoint_gtids_join=ON;
设置白名单：
SET GLOBAL group_replication_ip_whitelist="192.169.100.160,192.169.100.161,192.169.100.162";
start group_replication;
set global group_replication_bootstrap_group=OFF;
#检测组是否创建并已加入新成员
select * from performance_schema.replication_group_members;
8.192.169.100.161 和192.169.100.162 上执行：
备库启动 set global group_replication_enforce_update_everywhere_checks=OFF; set global group_replication_single_primary_mode=on; set global group_replication_allow_local_disjoint_gtids_join=ON; SET GLOBAL group_replication_ip_whitelist="192.169.100.160,192.169.100.161,192.169.100.162"; START GROUP_REPLICATION;
9.MGR 环境下设置vip
*/1 * * * * sh /etc/mysqlvip/call_setvip.sh
[root@mysql1 ~]# cat /etc/mysqlvip/call_setvip.sh
#!/bin/sh export LANG=en_US.UTF-8 dir=`dirname $0` ps -ef |grep -w "setvip.sh" CNT=`ps -ef |grep -w "setvip.sh"|grep -v grep|wc -l` echo count: $CNT cd ${curdir} if [ $CNT -eq 0 ] ; then cd ${dir} nohup sh ${dir}/setvip.sh & echo 'start setvip at '`date "+%Y-%m-%d %H:%M"` >> ${dir}/start_setvip.log fi
