本文链接地址: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节点提出组复制环境,重新选出一个主节点。
单主模式的组复制的最大特点:只有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