基于组复制的强大功能在MySQL 5.7.17之后以插件的形式实现，本文讲述在单机多实例基础上搭建组复制测试环境

环境说明：

操作系统:

CentOS Linux release 7.3.1611 (Core)

内核版本:

Linux version 3.10.0-514.6.2.el7.x86_64

MySQL版本:

mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz

依赖包安装:

yum-yinstallgccgcc-c++libaio-develboost-develautoconfautomakezlib-devellibxml2-develncurses-devellibgcrypt-devellibtool-developenssl-develbison-develunzipnumactl-devel

同时需要关闭selinux及防火墙

setenforce0systemctlstopfirewalldsystemctldisabledfirewalld

MySQL5.7.17安装及配置

下载解压至安装目录

wgethttp://mirrors.sohu.com/mysql/MySQL-5.7/mysql-5.7.17-linux-glibc2.5-x86_64.tartarxfmysql-5.7.17-linux-glibc2.5-x86_64.tarmvmysql-5.7.17-linux-glibc2.5-x86_64/usr/local/mysql5.7.17

添加环境变量

echo'PATH=/usr/local/mysql5.7.17/bin:$PATH'>>/etc/profilesource/etc/profile

手动创建mysql用户

useradd-s/sbin/nologin-d/dev/nullmysql

添加本机主机名解析

echo"127.0.0.1$HOSTNAME">>/etc/host

MySQL多实例:(三个实例)

我们将实例建3306、3307、3308三个实例

创建数据文件目录

mkdir-p/usr/local/mysql5.7.17/data/330{6,7,8}

创建配置文件目录

mkdir-p/usr/local/mysql5.7.17/conf

创建配置文件

3306实例的配置文件

cat /usr/local/mysql5.7.17/conf/3306.cnf

[mysqld]basedir=/usr/local/mysql5.7.17datadir=/usr/local/mysql5.7.17/data/3306port=3306socket=/usr/local/mysql5.7.17/data/3306/mysqld.socklog-error=/usr/local/mysql5.7.17/data/3306/mysqld.loguser=mysqlserver_id=3306gtid_mode=ONenforce_gtid_consistency=ONmaster_info_repository=TABLErelay_log_info_repository=TABLEbinlog_checksum=NONElog_slave_updates=ONlog_bin=binlogbinlog_format=ROW#组复制基本配置transaction_write_set_extraction=XXHASH64loose-group_replication_group_name="01e5fb97-be64-41f7-bafd-3afc7a6ab555"loose-group_replication_start_on_boot=offloose-group_replication_local_address="127.0.0.1:13306"loose-group_replication_group_seeds="127.0.0.1:13306,127.0.0.1:13307,127.0.0.1:13308"loose-group_replication_bootstrap_group=off#loose-group_replication_ip_whitelist=”10.30.0.0/16,10.31.0..0/16,10.27.0.0/16″

说明:

group_replication变量使用的loose-前缀是指示Server启用时尚未加载复制插件也将继续启动

transaction_write_set_extraction = XXHASH64指示Server必须为每个事务收集写集合，并使用XXHASH64哈希算法将其编码为散列

loose-group_replication_group_name="01e5fb97-be64-41f7-bafd-3afc7a6ab555"表示将加入或者创建的复制组命名为01e5fb97-be64-41f7-bafd-3afc7a6ab555,可自定义(通过cat /proc/sys/kernel/random/uuid)

loose-group_replication_start_on_boot=off 设置为Server启动时不自动启动组复制

loose-group_replication_local_address="127.0.0.1:13306" 绑定本地的127.0.0.1及13306端口接受其他组成员的连接，IP地址必须为其他组成员可正常访问

loose-group_replication_group_seeds="127.0.0.1:13306,127.0.0.1:13307,127.0.0.1:13308"本行为告诉服务器当服务器加入组时，应当连接到127.0.0.1:13306,127.0.0.1:13307,127.0.0.1:13308这些种子服务器进行配置。本设置可以不是全部的组成员服务地址

loose-group_replication_bootstrap_group = off 配置是否自动引导组

loose-group_replication_ip_whitelist=”10.30.0.0/16,10.31.0..0/16,10.27.0.0/16″默认情况下只允许127.0.0.1连接到复制组，如果是其他IP则需要配置。

3307实例的配置文件

cat /usr/local/mysql5.7.17/conf/3307.cnf

[mysqld]basedir=/usr/local/mysql5.7.17datadir=/usr/local/mysql5.7.17/data/3307port=3307socket=/usr/local/mysql5.7.17/data/3307/mysqld.socklog-error=/usr/local/mysql5.7.17/data/3307/mysqld.loguser=mysqlserver_id=3307gtid_mode=ONenforce_gtid_consistency=ONmaster_info_repository=TABLErelay_log_info_repository=TABLEbinlog_checksum=NONElog_slave_updates=ONlog_bin=binlogbinlog_format=ROWtransaction_write_set_extraction=XXHASH64loose-group_replication_group_name="01e5fb97-be64-41f7-bafd-3afc7a6ab555"loose-group_replication_start_on_boot=offloose-group_replication_local_address="127.0.0.1:13307"loose-group_replication_group_seeds="127.0.0.1:13306,127.0.0.1:13307,127.0.0.1:13308"loose-group_replication_bootstrap_group=offloose-group_replication_ip_whitelist=”10.30.0.0/16,10.31.0..0/16,10.27.0.0/16″

3308实例配置文件

cat /usr/local/mysql5.7.17/conf/3308.cnf

[mysqld]basedir=/usr/local/mysql5.7.17datadir=/usr/local/mysql5.7.17/data/3308port=3308socket=/usr/local/mysql5.7.17/data/3308/mysqld.socklog-error=/usr/local/mysql5.7.17/data/3308/mysqld.loguser=mysqlserver_id=3308gtid_mode=ONenforce_gtid_consistency=ONmaster_info_repository=TABLErelay_log_info_repository=TABLEbinlog_checksum=NONElog_slave_updates=ONlog_bin=binlogbinlog_format=ROWtransaction_write_set_extraction=XXHASH64loose-group_replication_group_name="01e5fb97-be64-41f7-bafd-3afc7a6ab555"loose-group_replication_start_on_boot=offloose-group_replication_local_address="127.0.0.1:13308"loose-group_replication_group_seeds="127.0.0.1:13306,127.0.0.1:13307,127.0.0.1:13308"loose-group_replication_bootstrap_group=offloose-group_replication_ip_whitelist=”10.30.0.0/16,10.31.0..0/16,10.27.0.0/16″

初始化数据库：

/usr/local/mysql5.7.17/bin/mysqld--initialize-insecure--basedir=/usr/local/mysql5.7.17--datadir=/usr/local/mysql5.7.17/data/3306/usr/local/mysql5.7.17/bin/mysqld--initialize-insecure--basedir=/usr/local/mysql5.7.17--datadir=/usr/local/mysql5.7.17/data/3307/usr/local/mysql5.7.17/bin/mysqld--initialize-insecure--basedir=/usr/local/mysql5.7.17--datadir=/usr/local/mysql5.7.17/data/3308chown-Rmysql.mysql/usr/local/mysql5.7.17/data/*

启动数据库:

/usr/local/mysql5.7.17/bin/mysqld--defaults-file=/usr/local/mysql5.7.17/conf/3306.cnf&/usr/local/mysql5.7.17/bin/mysqld--defaults-file=/usr/local/mysql5.7.17/conf/3307.cnf&/usr/local/mysql5.7.17/bin/mysqld--defaults-file=/usr/local/mysql5.7.17/conf/3308.cnf&

3306实例:

mysql-S/usr/local/mysql5.7.17/data/3306/mysqld.sock#创建复制用户及密码setsql_log_bin=0;grantreplicationslave,replicationclienton*.*to'repluser'@'%'identifiedby'replpass';flushprivileges;setsql_log_bin=1;#设置复制用户名及密码changemastertomaster_user='repluser',master_password='replpass'forchannel'group_replication_recovery';#安装组复制插件installplugingroup_replicationsoname'group_replication.so';#检查插件是否安装成功showplugins;#启动组复制setglobalgroup_replication_bootstrap_group=ON;STARTgroup_replication;setglobalgroup_replication_bootstrap_group=OFF;exit;

3307实例：

mysql-S/usr/local/mysql5.7.17/data/3307/mysqld.socksetsql_log_bin=0;grantreplicationslave,replicationclienton*.*to'repluser'@'%'identifiedby'replpass';flushprivileges;setsql_log_bin=1;changemastertomaster_user='repluser',master_password='replpass'forchannel'group_replication_recovery';installplugingroup_replicationsoname'group_replication.so';showplugins;#启动组复制STARTgroup_replication;exit;

3308实例:

mysql-S/usr/local/mysql5.7.17/data/3308/mysqld.socksetsql_log_bin=0;grantreplicationslave,replicationclienton*.*to'repluser'@'%'identifiedby'replpass';flushprivileges;setsql_log_bin=1;changemastertomaster_user='repluser',master_password='replpass'forchannel'group_replication_recovery';installplugingroup_replicationsoname'group_replication.so';showplugins;#启动组复制STARTgroup_replication;exit

注意:3306、3307、3308启动组复制的不同

set global group_replication_bootstrap_group=ON;

START group_replication;

set global group_replication_bootstrap_group=OFF;

查看组复制结果:

mysql>select*fromperformance_schema.replication_group_members;+---------------------------+--------------------------------------+-------------+-------------+--------------+|CHANNEL_NAME|MEMBER_ID|MEMBER_HOST|MEMBER_PORT|MEMBER_STATE|+---------------------------+--------------------------------------+-------------+-------------+--------------+|group_replication_applier|51f188a1-5626-11e7-a2f3-000c29335f88|haproxy206|3306|ONLINE||group_replication_applier|5b45cd3f-5626-11e7-a435-000c29335f88|haproxy206|3307|ONLINE||group_replication_applier|6374c473-5626-11e7-a5aa-000c29335f88|haproxy206|3308|ONLINE|+---------------------------+--------------------------------------+-------------+-------------+--------------+

可以看到MEMBER_STATE都是ONLINE，组复制配置成功

组复制测试：

mysql-S/usr/local/mysql5.7.17/data/3306/mysqld.sockmysql>createdatabasetest;mysql>createtabletb1(idintnotnull);mysql>insertintotb1values(1);Thetabledoesnotcomplywiththerequirementsbyanexternalplugin.#报错，那是因为group_replication中的表必须有主键保证唯一性，否则失败。mysql>droptabletb1;mysql>createtabletb1(idintnotnullprimarykeyauto_increment,usernamevarchar(300));mysql>insertintotb1values(1,'guest');

在其他实例测试:

mysql-S/usr/local/mysql5.7.17/data/3307/mysqld.sockmysql>select*fromtest.tb1;+----+----------+|id|username|+----+----------+|1|guest|+----+----------+mysql-S/usr/local/mysql5.7.17/data/3308/mysqld.sockmysql>select*fromtest.tb1;+----+----------+|id|username|+----+----------+|1|guest|+----+----------+mysql>insertintotest.tb1values(2,'admin');TheMySQLserverisrunningwiththe--super-read-onlyoptionsoitcannotexecutethisstatement

在单主模式下，只有主机才允许写入，其他都为只读模式；

在单主模式下寻找主实例

#查找主实例mysql>selectvariable_valuefromperformance_schema.global_statuswherevariable_name='group_replication_primary_member';+--------------------------------------+|variable_value|+--------------------------------------+|51f188a1-5626-11e7-a2f3-000c29335f88|+--------------------------------------+#获取实例信息mysql>select*fromperformance_schema.replication_group_members;+---------------------------+--------------------------------------+-------------+-------------+--------------+|CHANNEL_NAME|MEMBER_ID|MEMBER_HOST|MEMBER_PORT|MEMBER_STATE|+---------------------------+--------------------------------------+-------------+-------------+--------------+|group_replication_applier|51f188a1-5626-11e7-a2f3-000c29335f88|haproxy206|3306|ONLINE||group_replication_applier|5b45cd3f-5626-11e7-a435-000c29335f88|haproxy206|3307|ONLINE||group_replication_applier|6374c473-5626-11e7-a5aa-000c29335f88|haproxy206|3308|ONLINE|+---------------------------+--------------------------------------+-------------+-------------+--------------+

则可以知道haproxy206的3306端口为主实例

mysql>showvariableslike'%group_replication%';+----------------------------------------------------+-------------------------------------------------+|Variable_name|Value|+----------------------------------------------------+-------------------------------------------------+|group_replication_allow_local_disjoint_gtids_join|OFF||group_replication_allow_local_lower_version_join|OFF||group_replication_auto_increment_increment|7||group_replication_bootstrap_group|OFF||group_replication_components_stop_timeout|31536000||group_replication_compression_threshold|1000000||group_replication_enforce_update_everywhere_checks|OFF||group_replication_flow_control_applier_threshold|25000||group_replication_flow_control_certifier_threshold|25000||group_replication_flow_control_mode|QUOTA||group_replication_force_members|||group_replication_group_name|01e5fb97-be64-41f7-bafd-3afc7a6ab555||group_replication_group_seeds|127.0.0.1:13306,127.0.0.1:13307,127.0.0.1:13308||group_replication_gtid_assignment_block_size|1000000||group_replication_ip_whitelist|AUTOMATIC||group_replication_local_address|127.0.0.1:13306||group_replication_poll_spin_loops|0||group_replication_recovery_complete_at|TRANSACTIONS_APPLIED||group_replication_recovery_reconnect_interval|60||group_replication_recovery_retry_count|10||group_replication_recovery_ssl_ca|||group_replication_recovery_ssl_capath|||group_replication_recovery_ssl_cert|||group_replication_recovery_ssl_cipher|||group_replication_recovery_ssl_crl|||group_replication_recovery_ssl_crlpath|||group_replication_recovery_ssl_key|||group_replication_recovery_ssl_verify_server_cert|OFF||group_replication_recovery_use_ssl|OFF||group_replication_single_primary_mode|ON||group_replication_ssl_mode|DISABLED||group_replication_start_on_boot|OFF|+----------------------------------------------------+-------------------------------------------------+

注意上面的

group_replication_enforce_update_everywhere_checks|OFFgroup_replication_single_primary_mode|ON

单主模式变更为多主模式

3306实例：mysql-S/usr/local/mysql5.7.17/data/3306/mysqld.sockSTOPgroup_replication;setglobalgroup_replication_single_primary_mode=OFF;setglobalgroup_replication_enforce_update_everywhere_checks=ON;setglobalgroup_replication_bootstrap_group=ON;STARTgroup_replication;setglobalgroup_replication_bootstrap_group=OFF;3307实例：mysql-S/usr/local/mysql5.7.17/data/3307/mysqld.sockSTOPgroup_replication;setglobalgroup_replication_single_primary_mode=OFF;setglobalgroup_replication_enforce_update_everywhere_checks=ON;STARTgroup_replication;3308实例:mysql-S/usr/local/mysql5.7.17/data/3308/mysqld.sockSTOPgroup_replication;setglobalgroup_replication_single_primary_mode=OFF;setglobalgroup_replication_enforce_update_everywhere_checks=ON;STARTgroup_replication;

经过以上配置后你就可以在每个实例上都进行增删改

增加节点

我们已三个成员的组复制分别为3306、3307、3308现在我们增加3309实例

mkdir -p /usr/local/mysql5.7.17/data/3309

cat /usr/local/mysql5.7.17/conf/3309.cnf

[mysqld]basedir=/usr/local/mysql5.7.17datadir=/usr/local/mysql5.7.17/data/3309port=3309socket=/usr/local/mysql5.7.17/data/3309/mysqld.socklog-error=/usr/local/mysql5.7.17/data/3309/mysqld.loguser=mysqlserver_id=3309gtid_mode=ONenforce_gtid_consistency=ONmaster_info_repository=TABLErelay_log_info_repository=TABLEbinlog_checksum=NONElog_slave_updates=ONlog_bin=binlogbinlog_format=ROWtransaction_write_set_extraction=XXHASH64loose-group_replication_group_name="01e5fb97-be64-41f7-bafd-3afc7a6ab555"loose-group_replication_start_on_boot=offloose-group_replication_local_address="127.0.0.1:13309"loose-group_replication_group_seeds="127.0.0.1:13306,127.0.0.1:13307,127.0.0.1:13308"loose-group_replication_bootstrap_group=off#loose-group_replication_ip_whitelist=”10.30.0.0/16,10.31.0..0/16,10.27.0.0/16″

初始化数据库并启动数据库

/usr/local/mysql5.7.17/bin/mysqld--initialize-insecure--basedir=/usr/local/mysql5.7.17--datadir=/usr/local/mysql5.7.17/data/3309chown-Rmysql.mysql/usr/local/mysql5.7.17/data/3309/usr/local/mysql5.7.17/bin/mysqld--defaults-file=/usr/local/mysql5.7.17/conf/3309.cnf&

配置

mysql-S/usr/local/mysql5.7.17/data/3309/mysqld.socksetsql_log_bin=0;grantreplicationslave,replicationclienton*.*to'repluser'@'%'identifiedby'replpass';flushprivileges;setsql_log_bin=1;changemastertomaster_user='repluser',master_password='replpass'forchannel'group_replication_recovery';installplugingroup_replicationsoname'group_replication.so';showplugins;STARTgroup_replication;

查看组成员

mysql>select*fromperformance_schema.replication_group_members;+---------------------------+--------------------------------------+-------------+-------------+--------------+|CHANNEL_NAME|MEMBER_ID|MEMBER_HOST|MEMBER_PORT|MEMBER_STATE|+---------------------------+--------------------------------------+-------------+-------------+--------------+|group_replication_applier|51f188a1-5626-11e7-a2f3-000c29335f88|haproxy206|3306|ONLINE||group_replication_applier|5b45cd3f-5626-11e7-a435-000c29335f88|haproxy206|3307|ONLINE||group_replication_applier|6374c473-5626-11e7-a5aa-000c29335f88|haproxy206|3308|ONLINE||group_replication_applier|b19bea84-5629-11e7-8b05-000c29335f88|haproxy206|3309|ONLINE|+---------------------------+--------------------------------------+-------------+-------------+--------------+

如果数据量大的话，3309的状态可能为recovering

问题处理:

2017-06-21T15:06:01.854288+08:00 0 [Warning] Plugin group_replication reported: ‘[GCS] Connection attempt from IP address 192.168.0.2 refused. Address is not in the IP whitelist.’

原因：group_replication_ip_whitelist默认值为127.0.0.1/8，设置为需要的配置

处理：set globalgroup_replication_ip_whitelist='192.168.0.0/24' (多个网段使用逗号隔开)

Slave I/O for channel 'group_replication_recovery': Fatal error: Invalid (empty) username when attempting to connect to the master server. Connection attempt terminated. Error_code: 1593

原因：没有配置同步账号跟密码，使用的是空密码进行同步。 需要为复制通道group_replication_recovery 设置同步信息,一定要注意同步通道group_replication_recovery

处理：change master to master_user='repluser',master_password='replpass' for channel 'group_replication_recovery';