MySQL 5.7组复制实践2-single master模式

博客文章除注明转载外，均为原创。转载请注明出处。
本文链接地址：http://blog.chinaunix.net/uid-31396856-id-5758022.html


前文以及对创建组复制多主multi-master环境进行实践。说到组复制single-master模式，是组复制的默认模式，其实现较多主模式更简单一些


1.前期配置和组复制相关配置见前文，组复制参数配置部分如下：

set global transaction_write_set_extraction = XXHASH64
set global group_replication_start_on_boot = OFF
set global group_replication_bootstrap_group = OFF
set global group_replication_group_name = b6ddfda0-d8bc-4272-a58f-4ea75acbbc79
set global group_replication_local_address = '192.16.0.11:23306'
set global group_replication_group_seeds = '192.16.0.11:23306,192.16.0.12:23306,192.16.0.13:23306'

而不再配置以下两个参数：
set global group_replication_single_primary_mode=FALSE
set global group_replication_enforce_update_everywhere_checks=FALSE

然后启动组复制


在第一个节点上启动组复制：
"root@localhost:mysql3306.sock  [(none)]>SET GLOBAL group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.00 sec)


"root@localhost:mysql3306.sock  [(none)]>START GROUP_REPLICATION;
Query OK, 0 rows affected (1.07 sec)

组复制成员如下：
"root@localhost:mysql3306.sock  [(none)]>SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 8d4ccab2-d4e6-11e6-a83c-080027feaf82 | mgr01       |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
1 row in set (0.00 sec)


然后在加入第二个节点后：
"root@localhost:mysql3306.sock  [(none)]>SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 1a30ac44-d4e7-11e6-9bd3-080027f57e91 | mgr02       |        3306 | ONLINE       |
| group_replication_applier | 8d4ccab2-d4e6-11e6-a83c-080027feaf82 | mgr01       |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
2 rows in set (0.00 sec)

然后在加入第三个节点后：
"root@localhost:mysql3306.sock  [(none)]>
"root@localhost:mysql3306.sock  [(none)]>
"root@localhost:mysql3306.sock  [(none)]>SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 1a30ac44-d4e7-11e6-9bd3-080027f57e91 | mgr02       |        3306 | ONLINE       |
| group_replication_applier | 28a76053-d4e7-11e6-9d54-080027231a47 | mgr03       |        3306 | ONLINE       |
| group_replication_applier | 8d4ccab2-d4e6-11e6-a83c-080027feaf82 | mgr01       |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)

"root@localhost:mysql3306.sock  [(none)]>SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME= 'group_replication_primary_member';
+--------------------------------------+
| VARIABLE_VALUE                       |
+--------------------------------------+
| 8d4ccab2-d4e6-11e6-a83c-080027feaf82 |
+--------------------------------------+
1 row in set (0.00 sec)


MEMBER_ID 判断master是mgr01   

2.组复制单主模式：单写测试
单主模式的组复制的最大特点：只有master节点可以进行ddl和dml写入操作，其它节点都是read-only。
测试如下：
"root@localhost:mysql3306.sock  [(none)]>insert into mgrdb.grtest (id,name) values(5,'single_master');
Query OK, 1 row affected (0.00 sec)


12节点：
"root@localhost:mysql3306.sock  [(none)]>insert into mgrdb.grtest (id,name) values(6,'single_master');
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement


13节点
"root@localhost:mysql3306.sock  [(none)]>insert into mgrdb.grtest (id,name) values(7,'single_master');
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement



"root@localhost:mysql3306.sock  [(none)]>select * from mgrdb.grtest;
+----+---------------+
| id | name          |
+----+---------------+
|  1 | dbwatcher     |
|  2 | dbwatcher     |
|  3 | dbwatcher     |
|  4 | dbwatcher     |
|  5 | single_master |
+----+---------------+
5 rows in set (0.00 sec)

总结:只有master节点成功进行写操作；

3.组父子单主模式切换

当主节点出现问题：比如挂掉，或者网络出现问题等等的时候，其它节点会将failure节点提出组复制环境，重新选出一个主节点。

关闭主节点，或者断开主节点网络：


新的主节点日志：
2017-01-07T18:34:36.733542Z 0 [Note] Plugin group_replication reported: 'getstart group_id c2b4e7e4'
2017-01-07T18:34:37.399474Z 0 [Note] Plugin group_replication reported: 'Unsetting super_read_only.'
2017-01-07T18:34:37.400024Z 7 [Note] Plugin group_replication reported: 'A new primary was elected, enabled conflict detection until the new primary applies all relay logs'

看其它节点日志：
2017-01-07T18:34:37.089014Z 0 [Note] Plugin group_replication reported: 'getstart group_id c2b4e7e4'
2017-01-07T18:34:37.755089Z 0 [Note] Plugin group_replication reported: 'Setting super_read_only.'
2017-01-07T18:34:37.755263Z 7 [Note] Plugin group_replication reported: 'A new primary was elected, enabled conflict detection until the new primary applies all relay logs'

再看组复制环境的状态
"root@localhost:mysql3306.sock  [(none)]>SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 1a30ac44-d4e7-11e6-9bd3-080027f57e91 | mgr02       |        3306 | ONLINE       |
| group_replication_applier | 28a76053-d4e7-11e6-9d54-080027231a47 | mgr03       |        3306 | ONLINE       |
+---------------------------


---The end