MySQL5.7.17在单机多实例基础上如何搭建组复制测试环境
下文我给大家简单讲讲关于MySQL 5.7.17在单机多实例基础上如何搭建组复制测试环境，大家之前了解过相关类似主题内容吗？感兴趣的话就一起来看看这篇文章吧，相信看完MySQL 5.7.17在单机多实例基础上如何搭建组复制测试环境对大家多少有点帮助吧。
创新互联主营扎赉特网站建设的网络公司,主营网站建设方案,成都app软件开发,扎赉特h5成都微信小程序搭建,扎赉特网站营销推广欢迎扎赉特等地区企业咨询
环境说明：
操作系统:
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';
大家觉得MySQL 5.7.17在单机多实例基础上如何搭建组复制测试环境这篇文章怎么样，是否有所收获。如果想要了解更多相关，可以继续关注我们的行业资讯板块。
本文名称：MySQL5.7.17在单机多实例基础上如何搭建组复制测试环境
本文地址：http://ybzwz.com/article/gpoosi.html
