MySQL 5.7.23 组复制部署示例

理论参考官网或其他博文吧，官方参考：Group Replication

组复制配置很简单，本测试配置 Multi-Primary 模式：

 

# vim /etc/my.cnf

[client]
port = 3306
socket = /tmp/mysql.sock

[mysqld]
user=mysql
port = 3306
server_id = 111
socket=/tmp/mysql.sock
basedir =/usr/local/mysql
datadir =/usr/local/mysql/data
log-error =/var/log/mysql/mysqld.log
pid-file =/var/run/mysqld/mysqld.pid
socket =/tmp/mysql.sock  

autocommit = 1
character_set_server=utf8
default-storage-engine=INNODB
transaction_isolation = READ-COMMITTED
event_scheduler = 1
lower_case_table_names=1
explicit_defaults_for_timestamp = 1
skip-external-locking
default-time-zone = '+8:00'
max_allowed_packet = 1G
innodb_buffer_pool_size = 1G

#binlog日志
log-bin=/usr/local/mysql/binlog/mysql-bin
expire_logs_days = 15

#全局事务 & 组复制
binlog_checksum = NONE
binlog_format = row
log_slave_updates = ON

gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
sync_binlog=1
innodb_flush_log_at_trx_commit=1

bind-address = "192.168.1.111" #注意每个实例配置文件的此IP不一样!!!!
report_host = "192.168.1.111"  #注意每个实例配置文件的此IP不一样!!!!
loose-group_replication_local_address= "192.168.1.111:33066"  #注意每个实例配置文件的此IP不一样!!!!
loose-group_replication_group_seeds= "192.168.1.111:33066,192.168.1.112:33066,192.168.1.113:33066"
loose-group_replication_ip_whitelist="127.0.0.1/8,192.168.1.0/24"
loose-group_replication_group_name="11111111-1111-1111-1111-111111111111"
loose-group_replication_start_on_boot=off
loose-group_replication_bootstrap_group=off
loose-group_replication_single_primary_mode=false
loose-group_replication_enforce_update_everywhere_checks=true
transaction_write_set_extraction=XXHASH64

sql_mode=STRICT_TRANS_TABLES

[mysqld_safe]
log-error=/var/log/mysql/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

配置组复制：

#各实例都创建复制用户
SET SQL_LOG_BIN=0;
CREATE USER repl_user@'192.168.1.%' IDENTIFIED BY 'repl_pwd';
GRANT replication slave,replication client ON *.* TO repl_user@'192.168.1.%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;


#各实例 设置复制通道、安装组复制插件
CHANGE MASTER TO MASTER_USER='repl_user',MASTER_PASSWORD='repl_pwd' FOR CHANNEL 'group_replication_recovery';
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
SHOW PLUGINS;


# 192.168.1.111 作为主要启用组复制实例
SET GLOBAL group_replication_bootstrap_group=ON;
START group_replication;
SET GLOBAL group_replication_bootstrap_group=OFF;


# 192.168.1.112 和 192.168.1.113 实例启用组复制
SET GLOBAL group_replication_allow_local_disjoint_gtids_join=ON;
START group_replication;


#每添加一个节点实例后，都会添加到记录中
SELECT * FROM performance_schema.replication_group_members;

 测试，以下SQL每行各个实例中执行，但都会同步到所有实例中

#测试，以下SQL每行在一个实例中执行，但都会同步到所有实例中
create database test;
create table test.tab(id int,name varchar(10),tabdate datetime,PRIMARY KEY (`id`)) ;
insert into test.tab values(1,'aa',CURRENT_TIMESTAMP);

select * from test.tab;