什么是Group Replication
基于组的复制（Group-based Replication）是一种被使用在容错系统中的技术。Replication-group（复制组）是由能够相互通信的多个服务器（节点）组成的。
安装配置Group Replication
|
servername |
ip |
port |
group port |
|
mysql01 |
10.39.3.76 |
3306 |
33061 |
|
mysql02 |
10.39.3.70 |
3306 |
33061 |
|
mysql03 |
10.39.3.69 |
3306 |
33061 |
一、关闭防火墙（mysql01-03）
service iptables stop chkconfig --level 2345 iptables off
禁止selinx
echo -e "#SELINUX=enforcing\n#SELINUXTYPE=targeted\nSELINUX=disabled\nSETLOCALDEFS=0" > /etc/selinux/config
删除默认lanmp（可选步骤）（mysql01-03）
rm -rf /etc/my.cnf
rm -rf /etc/init.d/mysql*
rm -rf /etc/init.d/php*
rm -rf /etc/init.d/http*
rm -rf /etc/php.ini
yum -y remove httpd
yum -y remove http
yum -y remove php
yum -y remove mysql
yum -y remove mysqld
yum -y remove mariadb
安装组件（mysql01-03）
yum -y install readline* gcc* xmlto kernel-devel yum* screen vim* psmisc wget lrzsz pcre-devel libtidy libtidy-devel iptraf sysstat libevent libevent-devel perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker tar rrdtool ntp* dos2unix setup* net-snmp-utils net-snmp-devel ruby ruby-devel php-snmp net-snmp-perl net-snmp net-snmp-libs wget elinks make autoconf213 m4 gcc gcc-c++ automake autoconf fontconfig fontconfig-devel libjpeg libjpeg* libjpeg-devel libpng libpng-devel freetype freetype* freetype-devel libxml2 libxml2-devel libdhash libdhash-devel libxslt* zlib zlib-devel glibc glibc-devel glib2 glib2-devel bzip2 bzip2* libc-client libc-client-devel libicu-devel ncurses ncurses-devel xmlrpc-c xmlrpc-c-devel curl curl-devel e2fsprogs e2fsprogs-devel krb5-devel libidn libidn-devel openssl openssl-devel openldap openldap-devel openldap-clients openldap-servers gd gd-devel pam* gettext gettext-devel keyutils compat* mpfr cpp libgomp libstdc++-devel ppl cloog-ppl keyutils-libs-devel libcom_err-devel libsepol-devel libXpm* php-gd ncurses* libtool* patch unzip libcom_err-devel libsepol-devel gtk* libselinux-devel libtiff libtiff* php-common policycoreutils telnet t1lib* nasm*
修改主机（mysql01-03）
vim /etc/hosts
10.39.3.76 mysql01
10.39.3.70 mysql02
10.39.3.69 mysql03
设置时间同步（mysql01-03）
crontab -e */5 * * * * /usr/sbin/ntpdate pool.ntp.org >/dev/null 2>&1 /etc/init.d/crond restart
cmake安装（mysql01-03）
wget https://cmake.org/files/v3.9/cmake-3.9.1.tar.gz tar zxvf cmake-3.9.1.tar.gz
cd cmake-3.9.1 ./configure make && make install
cmake -version
----------------------
显示如下成功 cmake version 3.9.1
bison安装（mysql01-03）
wget http://ftp.gnu.org/gnu/bison/bison-3.0.tar.gz
tar zxvf bison-3.0.tar.gz
cd bison-3.0
./configure
make && make install bison --version
-------------------- 显示如下成功 bison (GNU Bison) 3.0
openssl安装（mysql01-03）
wget https://www.openssl.org/source/openssl-1.0.2h.tar.gz
yum install -y zlib
tar zxf openssl-1.0.2h.tar.gz
cd openssl-1.0.2h
./config shared zlib
make
make install mv /usr/bin/openssl /usr/bin/openssl.bak
mv /usr/include/openssl /usr/include/openssl.bak
ln -s /usr/local/ssl/bin/openssl /usr/bin/openssl
ln -s /usr/local/ssl/include/openssl /usr/include/openssl
echo “/usr/local/ssl/lib” >> /etc/ld.so.conf
ldconfig -v openssl version -a
-------------------
显示如下成功 OpenSSL 1.0.2h 3 May 2016
创建用户和所属组（mysql01-03）
groupadd mysql
useradd -M -s/sbin/nologin -g mysql -r mysql
下载boots（mysql01-03）
mysql 5.7 依赖于boost_1_59_0或更高版本, 查看系统是否自带boost, 如果有就卸载了
下载boost_1_59_0.tar.gz
放在/usr/local/boost里面
安装mysql（mysql01-03）
mkdir -p /data/mysql/data
tar zxvf mysql-5.7.21.tar.gz
cd mysql-5.7.21
cmake . -DCMAKE_INSTALL_PREFIX=/data/mysql -DMYSQL_DATADIR=/data/mysql/data -DMYSQL_UNIX_ADDR=/data/mysql/mysql.sock -DSYSCONFDIR=/etc -DMYSQL_USER=mysql -DDEFAULT_CHARSET=utf8mb4 -DDEFAULT_COLLATION=utf8mb4_general_ci -DENABLED_LOCAL_INFILE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_MEMORY_STORAGE_ENGINE=1 -DMYSQL_TCP_PORT=3306 -DWITH_BOOST=/usr/local/boost/ -DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 -DWITH_PARTITION_STORAGE_ENGINE=1 -DWITH_FEDERATED_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITH_EMBEDDED_SERVER=1
make && make install
建立mysql启动（mysql01-03）
cp /data/mysql/support-files/mysql.server /etc/init.d/mysqld
chmod +x /etc/init.d/mysqld
chkconfig --add mysqld
chkconfig mysqld on
修改data和base（mysql01-03）
vim /etc/init.d/mysqld
basedir=/data/mysql
datadir=/data/mysql/data
修改my.cnf（dbnode1）
[client]
port = 3306
socket = /data/mysql/mysql.sock
default-character-set = utf8mb4
[mysqld]
port = 3306
socket = /data/mysql/mysql.sock
character-set-server = utf8mb4
basedir = /data/mysql
datadir = /data/mysql/data
pid-file = /data/mysql/mysql.pid
user = mysql
server-id = 1
bind-address = 0.0.0.0
####binlog binlog配置，Group Replication是要根据GTID来进行同步的，所以需要开启GTID
gtid_mode=ON
enforce-gtid-consistency = ON
master-info-repository = TABLE
relay-log-info-repository = TABLE
log-bin=binlog
expire-logs-days=10
binlog-checksum = NONE
log-slave-updates = ON
binlog_format=ROW ###日志存放
log_error = /data/mysql/data/mysql-error.log
slow_query_log = 1
slow_query_log_file = /data/mysql/data/mysql-slow.log
log_queries_not_using_indexes=1 ### innodb设置
default_storage_engine = InnoDB
innodb_file_per_table = 1
innodb_buffer_pool_size=24G
innodb_log_buffer_size=8M ###加入group replication设置
transaction-isolation=READ-COMMITTED
###同步算法
transaction_write_set_extraction=XXHASH64
###集群的uuid
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
###是否随着服务启动集群
loose-group_replication_start_on_boot=off #为了避免每次启动自动引导具有相同名称的第二个组,所以设置为OFF ##集群本机端口，和服务端口不同 loose-group_replication_local_address= "10.39.3.76:33061"
##集群包含的所有节点
loose-group_replication_group_seeds= "10.39.3.76:33061,10.39.3.70:33061,10.39.3.69:33061,10.39.3.68:33061,10.39.3.67:33061"
#设置白名单
loose-group_replication_ip_whitelist='10.39.3.76/24,10.4.39.3.70/24,10.39.3.69/24,10.39.3.68/24,10.39.3.67/24,10.39.3.0/16,127.0.0.1/8' #设置允许访问的网段 ##是否设置为主节点，当创建集群时其他加入的节点都以该节点为目标加入集群 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
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
初始化数据（mysql01-05）
mysqld --initialize-insecure --user=mysql --basedir=/data/mysql --datadir=/data/mysql/data
增加权限（mysql01-05）
chown -R mysql.mysql /data/mysql
修改密码（mysql01-05）
mysqladmin -u root password 'thAd6Zelma7_gropE3936polLUtioN' -p
进入mysql创建授权用户（dbnode1）###创建授权用户不写入bin_log
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';
###安装mysql group replication:
INSTALL PLUGIN group_replication SONAME 'group_replication.so'; mysql> SHOW PLUGINS;
| Name |Status | Type | Library | License |
| binlog | ACTIVE | STORAGE ENGINE | NULL GPL
group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL| ###启动mysql group replication 第一台server启动需要设置，重启也一样，
SET GLOBAL group_replication_bootstrap_group=ON;
###启动
START GROUP_REPLICATION;
###关闭
SET GLOBAL group_replication_bootstrap_group=OFF;
###查看group在线机器
SELECT * FROM performance_schema.replication_group_members;
mysql> SELECT * FROM performance_schema.replication_group_members; | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
group_replication_applier | 02d70597-9f73-11e7-8a51-000c29578bd4 | mysql-01 | 3306 | ONLINE |
1 row in set (0.00 sec)
修改my.cnf（dbnode2）
[client]
port = 3306
socket = /data/mysql/mysql.sock
default-character-set = utf8mb4
[mysqld]
#base config 基础配置信息
port = 3306
socket = /data/mysql/mysql.sock
character-set-server = utf8mb4
basedir = /data/mysql
datadir = /data/mysql/data
pid-file = /data/mysql/mysql.pid
user = mysql
server-id = 2
bind-address = 0.0.0.0 ####binlog binlog配置，Group Replication是要根据GTID来进行同步的，所以需要开启GTID
gtid_mode=ON
enforce-gtid-consistency = ON
master-info-repository = TABLE
relay-log-info-repository = TABLE
log-bin=binlog
expire-logs-days=10
binlog-checksum = NONE
log-slave-updates = ON
binlog_format=ROW ###日志存放
log_error = /data/mysql/data/mysql-error.log
slow_query_log = 1
slow_query_log_file = /data/mysql/data/mysql-slow.log
log_queries_not_using_indexes=1 ### innodb设置
default_storage_engine = InnoDB
innodb_file_per_table = 1
innodb_buffer_pool_size=24G
innodb_log_buffer_size=8M ###加入group replication设置
transaction-isolation=READ-COMMITTED
###同步算法
transaction_write_set_extraction=XXHASH64
###集群的uuid
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
###是否随着服务启动集群
loose-group_replication_start_on_boot=off #为了避免每次启动自动引导具有相同名称的第二个组,所以设置为OFF
##集群本机端口，和服务端口不同
loose-group_replication_local_address= "10.39.3.70:33061"
##集群包含的所有节点 loose-group_replication_group_seeds= "10.39.3.76:33061,10.39.3.70:33061,10.39.3.69:33061,10.39.3.68:33061,10.39.3.67:33061"
#设置白名单
loose-group_replication_ip_whitelist='10.39.3.76/24,10.4.39.3.70/24,10.39.3.69/24,10.39.3.68/24,10.39.3.67/24,10.39.3.0/16,127.0.0.1/8' #设置允许访问的网段
##是否设置为主节点，当创建集群时其他加入的节点都以该节点为目标加入集群
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
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
进入mysql创建授权用户（dbnode2）###创建授权用户不写入bin_log
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';
###安装mysql group replication:
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
mysql> SHOW PLUGINS;
| Name |Status | Type | Library | License |
| binlog | ACTIVE | STORAGE ENGINE | NULL GPL
group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL|
###开启兼容模式
set global group_replication_allow_local_disjoint_gtids_join=ON;
###启动
START GROUP_REPLICATION;
###查看group在线机器
mysql> SELECT * FROM performance_schema.replication_group_members; | CHANNEL_NAME | MEMBER_ID |MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
group_replication_applier | 02d70597-9f73-11e7-8a51-000c29578bd4 | mysql-01 | 3306 | ONLINE |
group_replication_applier | b8a02335-a1bb-11e7-b39b-000c29c4cbb3 | mysql-02 | 3306 | ONLINE |
1 row in set (0.00 sec)
修改my.cnf（dbnode3）
[client]
port = 3306
socket = /data/mysql/mysql.sock
default-character-set = utf8mb4
[mysqld]
#base config 基础配置信息
port = 3306
socket = /data/mysql/mysql.sock
character-set-server = utf8mb4
basedir = /data/mysql
datadir = /data/mysql/data
pid-file = /data/mysql/mysql.pid
user = mysql
server-id = 3
bind-address = 0.0.0.0 ####binlog binlog配置，Group Replication是要根据GTID来进行同步的，所以需要开启GTID
gtid_mode=ON
enforce-gtid-consistency = ON
master-info-repository = TABLE
relay-log-info-repository = TABLE
log-bin=binlog
expire-logs-days=10
binlog-checksum = NONE
log-slave-updates = ON
binlog_format=ROW ###日志存放
log_error = /data/mysql/data/mysql-error.log
slow_query_log = 1
slow_query_log_file = /data/mysql/data/mysql-slow.log
log_queries_not_using_indexes=1 ### innodb设置
default_storage_engine = InnoDB
innodb_file_per_table = 1
innodb_buffer_pool_size=24G
innodb_log_buffer_size=8M ###加入group replication设置
transaction-isolation=READ-COMMITTED
###同步算法
transaction_write_set_extraction=XXHASH64
###集群的uuid
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
###是否随着服务启动集群
loose-group_replication_start_on_boot=off #为了避免每次启动自动引导具有相同名称的第二个组,所以设置为OFF
##集群本机端口，和服务端口不同
loose-group_replication_local_address= "10.39.3.69:33061"
##集群包含的所有节点
loose-group_replication_group_seeds= "10.39.3.76:33061,10.39.3.70:33061,10.39.3.69:33061,10.39.3.68:33061,10.39.3.67:33061"
#设置白名单
loose-group_replication_ip_whitelist='10.39.3.76/24,10.4.39.3.70/24,10.39.3.69/24,10.39.3.68/24,10.39.3.67/24,10.39.3.0/16,127.0.0.1/8' #设置允许访问的网段
##是否设置为主节点，当创建集群时其他加入的节点都以该节点为目标加入集群
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
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
进入mysql创建授权用户（dbnode3）###创建授权用户不写入bin_log
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';
###安装mysql group replication:
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
mysql> SHOW PLUGINS;
| Name |Status | Type | Library | License |
| binlog | ACTIVE | STORAGE ENGINE | NULL GPL
group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL|
###开启兼容模式
set global group_replication_allow_local_disjoint_gtids_join=ON;
###启动
START GROUP_REPLICATION;
###查看group在线机器
mysql> SELECT * FROM performance_schema.replication_group_members;
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
group_replication_applier | 02d70597-9f73-11e7-8a51-000c29578bd4 | mysql-01 | 3306 | ONLINE |
group_replication_applier | b8a02335-a1bb-11e7-b39b-000c29c4cbb3 | mysql-02 | 3306 | ONLINE |
group_replication_applier | dc19223f-a1bb-11e7-85ce-000c29826f1f | mysql-03 | 3306 | ONLINE |
1 row in set (0.00 sec)
修改my.cnf（dbnode4）
[client]
port = 3306
socket = /data/mysql/mysql.sock
default-character-set = utf8mb4
[mysqld]
#base config 基础配置信息
port = 3306
socket = /data/mysql/mysql.sock
character-set-server = utf8mb4
basedir = /data/mysql
datadir = /data/mysql/data
pid-file = /data/mysql/mysql.pid
user = mysql
server-id = 4
bind-address = 0.0.0.0 ####binlog binlog配置，Group Replication是要根据GTID来进行同步的，所以需要开启GTID
gtid_mode=ON
enforce-gtid-consistency = ON
master-info-repository = TABLE
relay-log-info-repository = TABLE
log-bin=binlog
binlog-checksum = NONE
log-slave-updates = ON
binlog_format=ROW ###日志存放
log_error = /data/mysql/data/mysql-error.log
slow_query_log = 1
slow_query_log_file = /data/mysql/data/mysql-slow.log
log_queries_not_using_indexes=1 ### innodb设置
default_storage_engine = InnoDB
innodb_file_per_table = 1
innodb_buffer_pool_size=24G
innodb_log_buffer_size=8M ###加入group replication设置
transaction-isolation=READ-COMMITTED
###同步算法
transaction_write_set_extraction=XXHASH64
###集群的uuid
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
###是否随着服务启动集群
loose-group_replication_start_on_boot=off #为了避免每次启动自动引导具有相同名称的第二个组,所以设置为OFF
##集群本机端口，和服务端口不同
loose-group_replication_local_address= "10.39.3.68:33061"
##集群包含的所有节点
loose-group_replication_group_seeds= "10.39.3.76:33061,10.39.3.70:33061,10.39.3.69:33061,10.39.3.68:33061,10.39.3.67:33061"
#设置白名单
loose-group_replication_ip_whitelist='10.39.3.76/24,10.4.39.3.70/24,10.39.3.69/24,10.39.3.68/24,10.39.3.67/24,10.39.3.0/16,127.0.0.1/8' #设置允许访问的网段
##是否设置为主节点，当创建集群时其他加入的节点都以该节点为目标加入集群
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
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
进入mysql创建授权用户（dbnode4）###创建授权用户不写入bin_log
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';
###安装mysql group replication:
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
mysql> SHOW PLUGINS;
| Name |Status | Type | Library | License |
| binlog | ACTIVE | STORAGE ENGINE | NULL GPL
group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL|
###开启兼容模式
set global group_replication_allow_local_disjoint_gtids_join=ON;
###启动
START GROUP_REPLICATION;
###查看group在线机器
mysql> SELECT * FROM performance_schema.replication_group_members;
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
group_replication_applier | 02d70597-9f73-11e7-8a51-000c29578bd4 | mysql-01 | 3306 | ONLINE |
group_replication_applier | b8a02335-a1bb-11e7-b39b-000c29c4cbb3 | mysql-02 | 3306 | ONLINE |
group_replication_applier | dc19223f-a1bb-11e7-85ce-000c29826f1f | mysql-03 | 3306 | ONLINE |
group_replication_applier | dc19223f-a1bb-11e7-85ce-000c29826f2f | mysql-04 | 3306 | ONLINE |
1 row in set (0.00 sec)
修改my.cnf（dbnode5）
[client]
port = 3306
socket = /data/mysql/mysql.sock
default-character-set = utf8mb4
[mysqld]
#base config 基础配置信息
port = 3306
socket = /data/mysql/mysql.sock
character-set-server = utf8mb4
basedir = /data/mysql
datadir = /data/mysql/data
pid-file = /data/mysql/mysql.pid
user = mysql
server-id = 5
bind-address = 0.0.0.0 ####binlog binlog配置，Group Replication是要根据GTID来进行同步的，所以需要开启GTID
gtid_mode=ON
enforce-gtid-consistency = ON
master-info-repository = TABLE
relay-log-info-repository = TABLE
log-bin=binlog
binlog-checksum = NONE
log-slave-updates = ON
binlog_format=ROW ###日志存放
log_error = /data/mysql/data/mysql-error.log
slow_query_log = 1
slow_query_log_file = /data/mysql/data/mysql-slow.log
log_queries_not_using_indexes=1 ### innodb设置
default_storage_engine = InnoDB
innodb_file_per_table = 1
innodb_buffer_pool_size=24G
innodb_log_buffer_size=8M ###加入group replication设置
transaction-isolation=READ-COMMITTED
###同步算法
transaction_write_set_extraction=XXHASH64
###集群的uuid
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
###是否随着服务启动集群
loose-group_replication_start_on_boot=off #为了避免每次启动自动引导具有相同名称的第二个组,所以设置为OFF
##集群本机端口，和服务端口不同
loose-group_replication_local_address= "10.39.3.67:33061"
##集群包含的所有节点
loose-group_replication_group_seeds= "10.39.3.76:33061,10.39.3.70:33061,10.39.3.69:33061,10.39.3.68:33061,10.39.3.67:33061"
#设置白名单
loose-group_replication_ip_whitelist='10.39.3.76/24,10.4.39.3.70/24,10.39.3.69/24,10.39.3.68/24,10.39.3.67/24,10.39.3.0/16,127.0.0.1/8' #设置允许访问的网段
##是否设置为主节点，当创建集群时其他加入的节点都以该节点为目标加入集群
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
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
进入mysql创建授权用户（dbnode5）###创建授权用户不写入bin_log
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';
###安装mysql group replication:
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
mysql> SHOW PLUGINS;
| Name |Status | Type | Library | License |
| binlog | ACTIVE | STORAGE ENGINE | NULL GPL
group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL|
###开启兼容模式
set global group_replication_allow_local_disjoint_gtids_join=ON;
###启动
START GROUP_REPLICATION;
###查看group在线机器
mysql> SELECT * FROM performance_schema.replication_group_members;
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
group_replication_applier | 02d70597-9f73-11e7-8a51-000c29578bd4 | mysql-01 | 3306 | ONLINE |
group_replication_applier | b8a02335-a1bb-11e7-b39b-000c29c4cbb3 | mysql-02 | 3306 | ONLINE |
group_replication_applier | dc19223f-a1bb-11e7-85ce-000c29826f1f | mysql-03 | 3306 | ONLINE |
group_replication_applier | dc19223f-a1bb-11e7-85ce-000c29826f2f | mysql-04 | 3306 | ONLINE |
group_replication_applier | dc19223f-a1bb-11e7-85ce-000c29826f3f | mysql-05 | 3306 | ONLINE |
1 row in set (0.00 sec)
创建测试数据库：
CREATE DATABASE test; use test CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL); INSERT INTO t1 VALUES (1, 'Luis');
查看binlog事件
SHOW BINLOG EVENTS;
###01显示
+---------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+-----+----------------+-----------+-------------+---------------------------------------+
| binlog.000001 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.17-log, Binlog ver: 4 |
| binlog.000001 | 123 | Previous_gtids | 1 | 154 | |
| binlog.000001 | 154 | Stop | 1 | 177 | |
+---------------+-----+----------------+-----------+-------------+---------------------------------------+
02-03显示
+---------------+-----+----------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+-----+----------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------+
| binlog.000001 | 4 | Format_desc | 2 | 123 | Server ver: 5.7.17-log, Binlog ver: 4 |
| binlog.000001 | 123 | Previous_gtids | 2 | 154 | |
| binlog.000001 | 154 | Anonymous_Gtid | 2 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000001 | 219 | Query | 2 | 414 | ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*805BF2CF337DE1FE7EAEF974FDDB98F24168A43F' |
| binlog.000001 | 414 | Stop | 2 | 437 | |
+---------------+-----+----------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------
#####设置expire_logs_days自动过期清理binlog
show variables like 'expire_logs_days';
flush logs;