在前一种模式Single-Primary中,无论集群中有多少个节点,只有一个节点允许写入,其它节点都是只读的,这个允许写入的节点被称为主节点,只有当这个主节点出现问题从集群中被踢出,才会在剩余的节点中选举出另外一个节点成为新的主节点,并且将该节点置为可写模式。
而在后一种模式Multi-Primary中,所有的节点都是主节点,都可以同时被读写,看上去这似乎更好,但是因为多主的复杂性,在功能上如果设置了多主模式,则会有一些使用的限制,比如不支持Foreign Keys with Cascading Constraints。
在多主模式下,集群中的节点退出集群,也不再会出现重新选举的动作,因为本来所有的节点都是Primary节点。
下面设置一下Multi-Primary模式,当前的环境是Single-primary,只有qht131是可读写的,其它的是只读的。
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-000c29e8e89c | qht134 | 3306 | ONLINE | | group_replication_applier | bb0dea82-58ed-11e8-94e5-000c29e8e90b | qht132 | 3306 | ONLINE | | group_replication_applier | bb0dea82-58ed-11e8-94e5-000c29e8e91b | qht133 | 3306 | ONLINE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ 4 rows in set (0.00 sec)
1.最简单的办法就是将当前的所有节点按照顺序都停掉,关闭组复制设置好参数后再重新打开数据库。
也可以将所有的slave节点都关闭掉,primary节点通过更改全局变量而不需要重启数据库。
由于我的测试环境,只接关闭所有的节点,先关闭salve节点最后关闭primary节点。
qht132,qht133,qht134先关闭组复制
mysql> stop GROUP_REPLICATION;
qht131,最后关闭qht131组复制
mysql> stop GROUP_REPLICATION;
# ## Group Replication configuration multi-primary mode ## loose-group_replication_single_primary_mode=off loose-group_replication_enforce_update_everywhere_checks=ON
-group_replication_single_primary_mode=ON,表示启动了Single-Primary模式,那么修改为OFF就意味着要启动Multi-Primary模式。
-group_replication_enforce_update_everywhere_checks该参数设置为ON,则禁用了在多主模式下一些可能产生未知数据冲突的操作
整个组复制的参数如下:
# # Group Replication configuration # transaction_write_set_extraction = XXHASH64 loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" loose-group_replication_start_on_boot=off loose-group_replication_local_address= "172.17.61.131:33060" loose-group_replication_group_seeds= "172.17.61.131:33060,172.17.61.132:33060,172.17.61.133:33060,172.17.61.134:33060" loose-group_replication_bootstrap_group= off # ## Group Replication configuration multi-primary mode ## loose-group_replication_single_primary_mode=off loose-group_replication_enforce_update_everywhere_checks=ON
3.用新参数重启所有节点:
[root@qht131 ~]# service mysql restart Shutting down MySQL.. [ OK ] Starting MySQL.... [ OK ]
4.开启其中一个节点,比如qht131:
以这个节点为基准,开启组复制 :
mysql> SET GLOBAL group_replication_bootstrap_group=ON; Query OK, 0 rows affected (0.04 sec) mysql> START GROUP_REPLICATION; Query OK, 0 rows affected (2.25 sec) mysql> SET GLOBAL group_replication_bootstrap_group=OFF; Query OK, 0 rows affected (0.00 sec)
5.其它的节后加入组:
qht132,qht133,qht134:
mysql> START GROUP_REPLICATION;. Query OK, 0 rows affected (3.13 sec)
所有的节后加进来后,检查组成员状态:
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-000c29e8e89c | qht134 | 3306 | ONLINE | | group_replication_applier | bb0dea82-58ed-11e8-94e5-000c29e8e90b | qht132 | 3306 | ONLINE | | group_replication_applier | bb0dea82-58ed-11e8-94e5-000c29e8e91b | qht133 | 3306 | ONLINE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ 4 rows in set (0.00 sec)
所有的节点read_only都应该是关闭的:
mysql> select @@read_only; +-------------+ | @@read_only | +-------------+ | 0 | +-------------+ 1 row in set (0.00 sec)
6.多主复制的测试:
qht131:
mysql> select * from l5m.test_mgr; +----+ | c1 | +----+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | +----+ 6 rows in set (0.18 sec) mysql> insert into l5m.test_mgr values(7); Query OK, 1 row affected (0.31 sec)
qht132:
mysql> select * from l5m.test_mgr; +----+ | c1 | +----+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | +----+ 7 rows in set (0.03 sec) mysql> insert into l5m.test_mgr values(8); Query OK, 1 row affected (0.02 sec)
qht131:
mysql> select * from l5m.test_mgr; +----+ | c1 | +----+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | | 8 | +----+ 8 rows in set (0.00 sec)
两个节点同时写数据没有问题。
参考:http://www.dbform.com/html/2017/3659.html