全同步复制(Fully synchronous replication)
组复制分单主模式和多主模式
主库执行完一个事务,所有的从库都执行了该事务才返回给客户端。因为需要等待所有从库执行完该事务才能返回,所以全同步复制的性能必然会收到严重的影响。
server1:master
server2 & server3:slave
server1和server2:恢复mysql刚安装好的环境
systemctl stop mysqld
cd /var/lib/mysql
rm -rf * 删掉之前的数据
vim /etc/my.cnf 注释掉之前的设置
server_id=1 所有节点的都不一样
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="85e55fd8-de73-11e9-916d-52540077b399" 复制之前无效的uuid,三个节点都用这个,但三个节点的uuid都和这个不一样
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "172.25.38.1:33061" 本机ip的端口
loose-group_replication_group_seeds= "172.25.38.1:33061,172.25.38.2:33061,172.25.38.3:33061"
loose-group_replication_bootstrap_group=off 插件是否自动引导,这个选项一般都要off掉,只需要由发起组复制的节点开启,并只启动一次,如果是on,下次再启动时,会生成一个同名的组,可能会发生脑裂
loose-group_replication_ip_whitelist="127.0.0.1,172.25.38.0/24" 白名单
loose-group_replication_enforce_update_everywhere_checks=ON
loose-group_replication_single_primary_mode=OFF
systemctl start mysqld
grep password /var/log/mysqld.log
mysql -uroot -p
alter user root@localhost identified by 'Redhat_123'; 安全初始化
server1:主节点设置
SET SQL_LOG_BIN=0; 关闭二进制日志
CREATE USER rpl_user@'%' IDENTIFIED BY 'Redhat_123'; 创建用户
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%'; 用户授权
FLUSH PRIVILEGES; 刷新用户授权表
SET SQL_LOG_BIN=1; 开启二进制日志
CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='Redhat_123' FOR CHANNEL 'group_replication_recovery'; 配置用户
INSTALL PLUGIN group_replication SONAME 'group_replication.so'; 安装插件
show plugins;
SET GLOBAL group_replication_bootstrap_group=ON; 激活全复制,只在server1上做
START GROUP_REPLICATION; 开启组复制
SET GLOBAL group_replication_bootstrap_group=OFF; 关闭全复制
server2 & server3:从节点设置
SET SQL_LOG_BIN=0; 关闭二进制日志
CREATE USER rpl_user@'%' IDENTIFIED BY 'Redhat_123'; 创建用户
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%'; 用户授权
FLUSH PRIVILEGES; 刷新用户授权表
SET SQL_LOG_BIN=1; 开启二进制日志
CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='Redhat_123' FOR CHANNEL 'group_replication_recovery'; 配置用户
INSTALL PLUGIN group_replication SONAME 'group_replication.so'; 安装插件
show plugins;
set global group_replication_allow_local_disjoint_gtids_join=on;server2和server3
START GROUP_REPLICATION; 开启组复制
SET GLOBAL group_replication_bootstrap_group=OFF; 关闭全复制
server1:
SELECT * FROM performance_schema.replication_group_members; 查看组的信息
测试:
server1:
CREATE DATABASE test; 创建库
USE test; 进入库
CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL); 创建表
DESC t1; 查看表
INSERT INTO t1 VALUES (1, 'Luis'); 插入数据
SELECT * FROM t1; 查看数据
server2和server3都开启组复制之后:
server2查看:
server3上面上传数据:
INSERT INTO t1 VALUES (2, 'westos');
SELECT * FROM t1;
server2上面查看数据
结论:在任意一台mysql中插入数据,其他组成员中会立即备份,从而实现了组复制