mysql的组复制

组复制

作用：实现了对数据库更方便的管理，在组中，对所有的节点都可以进行写操作，并同步到所有的结点上，而主从复制，却只能在主master上去对数据库进行写操作，不能对所有的slave节点进行写操作，从节点只能进行读操作，如果在从节点上对数据库进行写操作，将会导致主从节点数据库上的数据不同步。

安装5.7版本mysql
mysql5.7安装及主从复制
在server1，2，3中同时安装mysql，作为一个组。

安装完成之后用 grep password /var/log/mysql.log过滤数据库的初始密码。
配置mysql的配置文件，三台主机上server_id不同，本机的ip不同，其他配配置均相同。

cd /var/log/mysql
rm -fr *
vim /etc/my.cnf
server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON # gtid方式打开
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="205be85c-9c7c-11e8-b7ae-525400f04ef2"  # 格式为UUID的格式
loose-group_replication_start_on_boot=off
loose-group_replication_local_address="172.25.11.5:24901"
loose-group_replication_group_seeds="172.25.11.5:24901,172.25.11.6:24901,172.25.11.7:24901" # 组内成员的ip和端口号
loose-group_replication_bootstrap_group= off
loose-group_replication_single_primary_mode=off
loose-group_replication_enforce_update_everywhere_checks=on 
loose-group_replication_ip_whitelist="172.25.11.0/24,127.0.0.1/8"  #白名单

server1：

/etc/init.d/mysql start
mysql>alter  user root@identified by 'WDd+0910' # 设置文密码认证，否则无法进行任何操作
mysql>SET SQL_LOG_BIN=0;
mysql>GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%' IDENTIFIED BY 'WDd+0910';
mysql>reset master # 刷新master，否则数据不同步，可能会出错

mysql>FLUSH PRIVILEGES;
mysql> SET SQL_LOG_BIN=1;
mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='WDd+0910' FOR CHANNEL 'group_replication_recovery';
mysql>INSTALL PLUGIN group_replication SONAME 'group_replication.so'; #安装插件
mysql>SHOW PLUGINS;
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;
SELECT * FROM performance_schema.replication_group_members;

状态为ONLINE表示为成功

创建数据库，建立表，插入数据。

mysql> CREATE DATABASE test;
mysql> use test
mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL);
mysql> INSERT INTO t1 VALUES (1, 'Luis');
mysql>SELECT * FROM test.t1 

server2上

SET SQL_LOG_BIN=0;
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%' IDENTIFIED BY 'WDd+0910';
reset master;
SET SQL_LOG_BIN=1;
CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='WDd+0910'  FOR CHANNEL 'group_replication_recovery';
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
START GROUP_REPLICATION;
SELECT * FROM performance_schema.replication_group_members; #状态为ONLINE
+---------------------------+--------------------------------------+-------------+-------------+---------------+
|    CHANNEL_NAME           |          MEMBER_ID                   | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+---------------+
| group_replication_applier | 395409e1-6dfa-11e6-970b-00212844f856 |    myhost   |    24801    |     ONLINE    |
| group_replication_applier | ac39f1e6-6dfa-11e6-a69d-00212844f856 |    myhost   |    24802    |     ONLINE    |
+---------------------------+--------------------------------------+-------------+-------------+---------------+
SHOW DATABASES LIKE 'test';
SELECT * FROM test.t1;
SHOW BINLOG EVENTS;

在server3上

SET SQL_LOG_BIN=0;
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%' IDENTIFIED BY 'WDd+0910';
reset master;
SET SQL_LOG_BIN=1;
CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='WDd+0910'  FOR CHANNEL 'group_replication_recovery';
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
START GROUP_REPLICATION;
SELECT * FROM performance_schema.replication_group_members; #状态为ONLINE
SHOW DATABASES LIKE 'test';
SELECT * FROM test.t1;
SHOW BINLOG EVENTS;

测试：在server1，2，3上分别插入数据，查看其他两个是否同步


插入数据同步。