基于组复制的强大功能在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 -y install gcc gcc-c++ libaio-devel boost-devel autoconf automake zlib-devel libxml2-devel ncurses-devel libgcrypt-devel libtool-devel openssl-devel bison-devel unzip numactl-devel
同时需要关闭selinux及防火墙
setenforce 0 systemctl stop firewalld systemctl disabled firewalld
MySQL5.7.17安装及配置
下载解压至安装目录
wget http://mirrors.sohu.com/mysql/MySQL-5.7/mysql-5.7.17-linux-glibc2.5-x86_64.tar tar xf mysql-5.7.17-linux-glibc2.5-x86_64.tar mv mysql-5.7.17-linux-glibc2.5-x86_64 /usr/local/mysql5.7.17
添加环境变量
echo 'PATH=/usr/local/mysql5.7.17/bin:$PATH'>>/etc/profile source /etc/profile
手动创建mysql用户
useradd -s /sbin/nologin -d /dev/null mysql
添加本机主机名解析
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.17 datadir=/usr/local/mysql5.7.17/data/3306 port = 3306 socket =/usr/local/mysql5.7.17/data/3306/mysqld.sock log-error=/usr/local/mysql5.7.17/data/3306/mysqld.log user = mysql server_id = 3306 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="01e5fb97-be64-41f7-bafd-3afc7a6ab555" loose-group_replication_start_on_boot=off loose-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.17 datadir=/usr/local/mysql5.7.17/data/3307 port = 3307 socket =/usr/local/mysql5.7.17/data/3307/mysqld.sock log-error=/usr/local/mysql5.7.17/data/3307/mysqld.log user = mysql server_id = 3307 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="01e5fb97-be64-41f7-bafd-3afc7a6ab555" loose-group_replication_start_on_boot=off loose-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 = off loose-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.17 datadir=/usr/local/mysql5.7.17/data/3308 port = 3308 socket =/usr/local/mysql5.7.17/data/3308/mysqld.sock log-error=/usr/local/mysql5.7.17/data/3308/mysqld.log user = mysql server_id = 3308 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="01e5fb97-be64-41f7-bafd-3afc7a6ab555" loose-group_replication_start_on_boot=off loose-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 = 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/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/3308 chown -R mysql.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 #创建复制用户及密码 set sql_log_bin=0; grant replication slave,replication client on *.* to 'repluser'@'%' identified by 'replpass'; flush privileges; set sql_log_bin=1; #设置复制用户名及密码 change master to master_user='repluser',master_password='replpass' for channel 'group_replication_recovery'; #安装组复制插件 install plugin group_replication soname 'group_replication.so'; #检查插件是否安装成功 show plugins; #启动组复制 set global group_replication_bootstrap_group=ON; START group_replication; set global group_replication_bootstrap_group=OFF; exit;
3307实例：
mysql -S /usr/local/mysql5.7.17/data/3307/mysqld.sock set sql_log_bin=0; grant replication slave,replication client on *.* to 'repluser'@'%' identified by 'replpass'; flush privileges; set sql_log_bin=1; change master to master_user='repluser',master_password='replpass' for channel 'group_replication_recovery'; install plugin group_replication soname 'group_replication.so'; show plugins; #启动组复制 START group_replication; exit;
3308实例:
mysql -S /usr/local/mysql5.7.17/data/3308/mysqld.sock set sql_log_bin=0; grant replication slave,replication client on *.* to 'repluser'@'%' identified by 'replpass'; flush privileges; set sql_log_bin=1; change master to master_user='repluser',master_password='replpass' for channel 'group_replication_recovery'; install plugin group_replication soname 'group_replication.so'; show plugins; #启动组复制 START group_replication; exit
注意:3306、3307、3308启动组复制的不同
| 实例名 | 启动组复制命令 |
| 3306 | set global group_replication_bootstrap_group=ON; START group_replication; set global group_replication_bootstrap_group=OFF; |
| 3307 | START group_replication; |
| 3308 | START group_replication; |
查看组复制结果:
mysql> select * from performance_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.sock mysql > create database test; mysql > create table tb1 (id int not null); mysql > insert into tb1 values(1); The table does not comply with the requirements by an external plugin. #报错，那是因为group_replication中的表必须有主键保证唯一性，否则失败。 mysql > drop table tb1; mysql > create table tb1 (id int not null primary key auto_increment,username varchar(300)); mysql > insert into tb1 values(1,'guest');
在其他实例测试:
mysql -S /usr/local/mysql5.7.17/data/3307/mysqld.sock mysql> select * from test.tb1; +----+----------+ | id | username | +----+----------+ | 1 | guest | +----+----------+ mysql -S /usr/local/mysql5.7.17/data/3308/mysqld.sock mysql> select * from test.tb1; +----+----------+ | id | username | +----+----------+ | 1 | guest | +----+----------+ mysql> insert into test.tb1 values(2,'admin'); The MySQL server is running with the --super-read-only option so it cannot execute this statement
在单主模式下，只有主机才允许写入，其他都为只读模式；
在单主模式下寻找主实例
#查找主实例 mysql> select variable_value from performance_schema.global_status where variable_name ='group_replication_primary_member'; +--------------------------------------+ | variable_value | +--------------------------------------+ | 51f188a1-5626-11e7-a2f3-000c29335f88 | +--------------------------------------+ #获取实例信息 mysql> select * from performance_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> show variables like '%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 | OFF group_replication_single_primary_mode | ON
单主模式变更为多主模式
3306实例： mysql -S /usr/local/mysql5.7.17/data/3306/mysqld.sock STOP group_replication; set global group_replication_single_primary_mode=OFF; set global group_replication_enforce_update_everywhere_checks= ON; set global group_replication_bootstrap_group=ON; START group_replication; set global group_replication_bootstrap_group=OFF; 3307实例： mysql -S /usr/local/mysql5.7.17/data/3307/mysqld.sock STOP group_replication; set global group_replication_single_primary_mode=OFF; set global group_replication_enforce_update_everywhere_checks= ON; START group_replication; 3308实例: mysql -S /usr/local/mysql5.7.17/data/3308/mysqld.sock STOP group_replication; set global group_replication_single_primary_mode=OFF; set global group_replication_enforce_update_everywhere_checks= ON; START group_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.17 datadir=/usr/local/mysql5.7.17/data/3309 port = 3309 socket =/usr/local/mysql5.7.17/data/3309/mysqld.sock log-error=/usr/local/mysql5.7.17/data/3309/mysqld.log user = mysql server_id = 3309 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="01e5fb97-be64-41f7-bafd-3afc7a6ab555" loose-group_replication_start_on_boot=off loose-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/3309 chown -R mysql.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.sock set sql_log_bin=0; grant replication slave,replication client on *.* to 'repluser'@'%' identified by 'replpass'; flush privileges; set sql_log_bin=1; change master to master_user='repluser',master_password='replpass' for channel 'group_replication_recovery'; install plugin group_replication soname 'group_replication.so'; show plugins; START group_replication;
查看组成员
mysql> select * from performance_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 global group_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';