MySQL 组复制Group Replication安装

1.安装环境说明 ：

机器名        IP                        角色

qht131    172.17.61.131        primary   

qht132    172.17.61.132        secdnode1

qht133    172.17.61.133        secdnode2

用三台机器组成mysql group replication (MGR),其中qht131为primary，其它两台为从库。

2.准备好各自的mysql，这里就不做说明了，参考之前的博客。

3.修改各自的配置文件:

下面是qht131的配置文件：

[root@qht131 mysql]# cat /etc/my.cnf
[client]
port = 3306
socket = /u01/mysql/mysql.sock


[mysql]
no-auto-rehash


[mysqld]
socket = /u01/mysql/mysql.sock
character_set_server= utf8
init_connect= 'SET NAMES utf8'
basedir= /usr/local/mysql
datadir= /u01/mysql
socket = /u01/mysql/mysql.sock
log-error= /u01/log/mysql/mysql_3306.err
pid-file= /u01/mysql/mysqld.pid
lower_case_table_names = 1
sql_mode= STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
secure-file-priv = /u01/backup
server-id=10000
log_bin = /u01/mysql/mysql_bin
#skip-grant-tables
#innodb_flush_log_at_trx_commit=1
#sync_binlog=1
#expire_logs_days=10
#max_binlog_size=1073741824
#autocommit=off
#long_query_time=15
#slow_query_log=on

log_slave_updates = ON
relay_log_info_repository = TABLE
master_info_repository = TABLE
transaction_write_set_extraction = XXHASH64 #server必须为每个事务收集写集合，并使用XXHASH64哈希算法将其编码为散列 
binlog_format = ROW
binlog_checksum = NONE
enforce_gtid_consistency = ON
gtid_mode = ON
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"#告知插件加入或创建组命名，UUID  
loose-group_replication_start_on_boot=off #server启动时不自启组复制
loose-group_replication_local_address= "172.17.61.131:33060" #告诉插件使用IP地址，端口33060用于接收组中其他成员转入连接
loose-group_replication_group_seeds= "172.17.61.131:33060,172.17.61.132:33060,172.17.61.133:33060"#启动组server，种子server，加入组应该连接这些的ip和端口；其他server要加入组得由组成员同意 
loose-group_replication_bootstrap_group= off #插件是否自动引导组，此选项在任何时候只能在一个server上使用，通过是首次引导组时。如果多次引导组，就可能会造成人为的脑裂情况 。

[mysqld_safe]
open-files-limit = 8192
log-error= /u01/log/mysql/mysql_3306.err

qht132和qht133的配置除了server-id以及loose-group-replication_local_address不同，其它的都是一样，另外要确保三个库的server-uuid是不同的，可以查看/u01/mysql/auto.cnf查看server-uuid。

设置group_replication_bootstrap_group为ON是为了标示以后加入集群的服务器以这台服务器为基准，以后加入的就不需要设置。

4.重启三台数据库：

[root@qht131 mysql]# service mysql restart
Shutting down MySQL..                                      [  OK  ]
Starting MySQL..                                           [  OK  ]

5.配置MGR的第一个节点：

第一步：创建所需要的用户以及权限

第二步：用change master配置此server在下次需要从其它成员恢复时，使用group_replication_recovery复制通道给定的凭证。

第三步：安装组复制插件

第四步：启动复制组（只需要在主复制点启动一次就可以）

mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.02 sec)

mysql> create user mgruser@'%' identified by 'mgruser';
Query OK, 0 rows affected (0.06 sec)

mysql>  grant replication slave,replication client on *.* to mgruser@'%';
Query OK, 0 rows affected (0.00 sec)

mysql>  create user mgruser@'127.0.0.1' identified by 'mgruser';
Query OK, 0 rows affected (0.00 sec)

mysql>  grant replication slave,replication client on *.* to mgruser@'127.0.0.1';
Query OK, 0 rows affected (0.00 sec)

mysql>  create user mgruser@'localhost' identified by 'mgruser';
Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave,replication client on *.* to mgruser@'localhost';
Query OK, 0 rows affected (0.01 sec)

mysql> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)

mysql> change master to
    -> master_user='mgruser',
    -> master_password='mgruser'
    -> for channel 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.21 sec)

mysql>  install plugin group_replication soname 'group_replication.so';
Query OK, 0 rows affected (0.12 sec)

mysql> set global group_replication_bootstrap_group=on;
Query OK, 0 rows affected (0.01 sec)

mysql> start group_replication;
Query OK, 0 rows affected (2.22 sec)

mysql> set global group_replication_bootstrap_group=off;
Query OK, 0 rows affected (0.00 sec)

6.配置第二，第三个节点：

第二个结点和第一个结点唯一的不同在于它不在要自己去建一个群了、它只要加入第一个结点建的群就可以了。

逻辑上第二个结点与第三、第四、第五 ... 等等结点有着一样的逻辑角色、就也是说它们都不是群主；所以它们的配置方式和第二个结点是一样的。

　　以下是我配置第二个结点时的过程

mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

mysql>  create user mgruser@'%' identified by 'mgruser';
Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave,replication client on *.* to mgruser@'%';
Query OK, 0 rows affected (0.00 sec)

mysql>  create user mgruser@'127.0.0.1' identified by 'mgruser';
Query OK, 0 rows affected (0.00 sec)

mysql>  grant replication slave,replication client on *.* to mgruser@'127.0.0.1';
Query OK, 0 rows affected (0.00 sec)

mysql> create user mgruser@'localhost' identified by 'mgruser';
Query OK, 0 rows affected (0.01 sec)

mysql>  grant replication slave,replication client on *.* to mgruser@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)

mysql> change master to
    ->  master_user='mgruser',
    -> master_password='mgruser'
    -> for channel 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.06 sec)

mysql> install plugin group_replication soname 'group_replication.so';
Query OK, 0 rows affected (0.13 sec)

mysql> start group_replication;
Query OK, 0 rows affected (6.65 sec)

7.验证各个mgr节点是否正常：

mysql> select * from performance_schema.replication_group_members ;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | bb0dea82-58ed-11e8-94e5-000c29e8e89b | qht131      |        3306 | ONLINE       |
| group_replication_applier | bb0dea82-58ed-11e8-94e5-000c29e8e90b | qht132      |        3306 | ONLINE       |
| group_replication_applier | bb0dea82-58ed-11e8-94e5-000c29e8e91b | qht133      |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)这

这样就配置好了一个主节点，两个备用节点的mgr，主节点为可读写的，备用节点是只读模式：

有以下方法查看哪台是主节点：

mysql>  SELECT IF((SELECT @@server_uuid) = (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME= 'group_replication_primary_member'), 1, 0) as is_primary_node;
+-----------------+
| is_primary_node |
+-----------------+
|               1 |
+-----------------+
1 row in set (0.03 sec)

也可以查看是否为只读状态来看是主节点还是备用节点

mysql> select @@read_only;
+-------------+
| @@read_only |
+-------------+
|           0 |
+-------------+
1 row in set (0.00 sec)

测试一下是否能主从同步：

qht131:

mysql> create database l5m;
Query OK, 1 row affected (0.04 sec)

mysql> create table l5m.test_mgr (c1 int);
Query OK, 0 rows affected (0.18 sec)

qht132:

mysql> desc l5m.test_mgr;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c1    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.02 sec)

qht133:

mysql> desc l5m.test_mgr;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c1    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.12 sec)

同步没有问题，自此一个节点可读写的mgr安装完毕。

【注意】 前面的用户密码修改和创建用户操作必须设置binlog不记录，执行后再打开，否则会引起START GROUP_REPLICATION执行报错:

报错信息如下:

ERROR 3092 (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log.
1
mysql后台报错信息:

2016-12-15T07:51:28.317816Z 0 [ERROR] Plugin group_replication reported: 'This member has more executed transactions than those present in the group. Local transactions: f16f7f74-c283-11e6-ae37-fa163ee40410:1 > Group transactions: 3c992270-c282-11e6-93bf-fa163ee40410:1,
aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-5'
2016-12-15T07:51:28.317878Z 0 [ERROR] Plugin group_replication reported: 'The member contains transactions not present in the group. The member will now exit the group.'
2016-12-15T07:51:28.317887Z 0 [Note] Plugin group_replication reported: 'To force this member into the group you can use the group_replication_allow_local_disjoint_gtids_join option'
2016-12-15T07:51:28.317999Z 14 [Note] Plugin group_replication reported: 'Going to wait for view modification'
2016-12-15T07:51:28.318429Z 0 [Note] Plugin group_replication reported: 'getstart group_id 4317e324'
2016-12-15T07:51:32.437462Z 0 [Note] Plugin group_replication reported: 'state 4330 action xa_terminate'
2016-12-15T07:51:32.437897Z 0 [Note] Plugin group_replication reported: 'new state x_start'
2016-12-15T07:51:32.437913Z 0 [Note] Plugin group_replication reported: 'state 4257 action xa_exit'
2016-12-15T07:51:32.437981Z 0 [Note] Plugin group_replication reported: 'Exiting xcom thread'
2016-12-15T07:51:32.437993Z 0 [Note] Plugin group_replication reported: 'new state x_start'
2016-12-15T07:51:37.472364Z 14 [Note] Plugin group_replication reported: 'auto_increment_increment is reset to 1'
2016-12-15T07:51:37.472474Z 14 [Note] Plugin group_replication reported: 'auto_increment_offset is reset to 1'
2016-12-15T07:51:37.472943Z 19 [Note] Error reading relay log event for channel 'group_replication_applier': slave SQL thread was killed
2016-12-15T07:51:37.485851Z 16 [Note] Plugin group_replication reported: 'The group replication applier thread was killed'

解决方案是：根据提示打开group_replication_allow_local_disjoint_gtids_join选项，mysql命令行执行:

mysql> set global group_replication_allow_local_disjoint_gtids_join=ON;
然后再执行:
mysql> start group_replication;
Query OK, 0 rows affected (7.89 sec)

参考：

https://blog.csdn.net/d6619309/article/details/53691352

https://www.cnblogs.com/JiangLe/p/6727281.html