MYSQL Group Replication搭建过程

#设置hostname和ip映射

10.20.102.72 gr1
10.20.102.73 gr2
10.20.102.154 gr3
10.20.102.155 gr4

#为以上各台机安装mysql 5.7.17,详细见： 点击打开链接 ，注意先设置hostname

#创建Group Replication相关用户权限,登陆各个mysql执行以下语句：

SET SQL_LOG_BIN=0;
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%' IDENTIFIED BY 'rpl_pass';
GRANT ALL PRIVILEGES ON *.* TO dbmon@'%' IDENTIFIED BY 'dbmon';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_pass' FOR CHANNEL 'group_replication_recovery';

#在各mysql的my.cnf中配置Group Replication,

[mysqld]
# server configuration
datadir=/var/mysql/data    
basedir=/usr/local/mysql
port=3306                
socket=/usr/local/mysql/mysql.sock  
server_id=1                       #server-id别忘记改，1-4
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
innodb_buffer_pool_instances=4
innodb_buffer_pool_size=1G
innodb_flush_log_at_trx_commit=2
sync_binlog=0
#for parallel apply binlog
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=4
slave_preserve_commit_order=on
#for group replication
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="5d3d3349-f65f-11e6-894f-005056982cb6"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "gr1:5701" #改成本机的hostname就可以了
loose-group_replication_group_seeds= "gr1:5701,gr2:5701,gr3:5701,gr4:5701"
loose-group_replication_bootstrap_group= off
loose-group_replication_single_primary_mode=false

重启mysql

service mysqld restart

#初始化Group Replication#在各mysql中执行：

INSTALL PLUGIN group_replication SONAME 'group_replication.so';

#在gr1的mysql执行:

root@localhost [(none)] 03:30:29>>>SET GLOBAL group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.00 sec)

root@localhost [(none)] 03:30:38>>>START GROUP_REPLICATION;
Query OK, 0 rows affected (1.01 sec)

root@localhost [(none)] 03:30:39>>>SET GLOBAL group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)

root@localhost [(none)] 03:30:55>>>SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME          | MEMBER_ID                   | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | d2f23694-f65e-11e6-afb7-005056982cb6 | gr1      |      3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
1 row in set (0.00 sec)

#在gr2-4中mysql执行：

mysql> set global group_replication_allow_local_disjoint_gtids_join=ON;
Query OK, 0 rows affected (0.00 sec)
 
mysql> start group_replication;
Query OK, 0 rows affected (1.99 sec)
 
-- 再去gr1上，查看group_replication成员:
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | cf047a2a-f65e-11e6-b220-005056983d0c | gr4         |        3306 | ONLINE       |
| group_replication_applier | d0cc51f5-f65e-11e6-a6cf-005056986fa1 | gr2         |        3306 | ONLINE       |
| group_replication_applier | d2f23694-f65e-11e6-afb7-005056982cb6 | gr1         |        3306 | ONLINE       |
| group_replication_applier | d485d712-f65e-11e6-97f9-005056985179 | gr3         |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
4 rows in set (0.00 sec)

#验证集群复制功能,在任意一台机中执行：

mysql> create database db1;
Query OK, 1 row affected (0.00 sec)
mysql> create table db1.t1(id int,cn varchar(32));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t1 select 1,'a';
Query OK, 1 row affected (0.01 sec)
在另外一台机上查看数据：
mysql> select * from db1.t1;
+----+------+
| id | cn  |
+----+------+
|  1| a    |
+----+------+
1 row in set (0.00 sec)