MySQL 5.7 MGR原理及部署
MySQL 5.7 MGR
1、背景
主从复制，一主多从，主库提供读写功能，从库提供只读功能。当一个事务在master 提交成功时，会把binlog文件同步到从库服务器上落地为relay log给slave端执行，这个过程主库是不考虑从库是否有接收到binlog文件，有可能出现这种情况，当主库commit一个事务后，数据库发生宕机，刚好它的binlog还没来得及传送到slave端，这个时候选任何一个slave端都会丢失这个事务，造成数据不一致情况。 为了避免出现主从数据不一致的情况，MySQL引入了半同步复制，添加多了一个从库反馈机制，即半同步复制。这个有两种方式设置：
- 主库执行完事务后，同步binlog给从库，从库ack反馈接收到binlog，主库提交commit，反馈给客户端，释放会话；
- 主库执行完事务后，主库提交commit ，同步binlog给从库，从库ack反馈接收到binlog，反馈给客户端，释放会话；
但是，虽然满足了一主多从，读写分析，数据一致，但是，依旧有两个弊端：
- 写操作只能在master上；
- 如果master宕机，需要人为选择新主并重新给其他的slave端执行change master；
为了解决一系列问题，官方推出了MySQL Group Replication，从group replication发布以后，就有3种方法来实现MySQL的高可用集群：
- 异步复制
- 半同步复制
- group replication
2、Group Replication原理
MySQL Group Replication有两种模式，单主模式single-primary mode和多主模式multi-primary mode，在同一个group内，不允许两种模式同时存在，并且若要切换到不同模式，必须修改配置后重新启动集群。
1、单主模式
在单主模式下，只有一个节点可以读写，其他节点提供只读服务。单主模式下，该参数 _ 必须被设置为 FALSE ，当主节点宕掉，自动会根据服务器的server_uuid变量和group_replication_member_weight变量值，选择下一个slave谁作为主节点，group_replication_member_weight的值最高的成员被选为新的主节点，该参数默认为50，建议可以在节点上设置不同值；在group_replication_member_weight值相同的情况下，group根据数据字典中 server_uuid排序，排序在最前的被选择为主节点。
- 单主模式中发现当前的主服务器，该值VARIABLE_VALUE为实例节点的server_uuid：
select * from performance_schema.global_status WHERE VARIABLE_NAME like '%group_replication%';
2、多主模式
在mysql多主模式下,在组复制中通过Group Replication Protocol协议及Paxos协议，形成的整体高可用解决方案 同时增加了certify的概念，负责检查事务是否允许提交，是否与其它事务存在冲突,Group Replication是由多个节点共同组成一个数据库集群，每个节点都可以单独执行事务，但是read-write（rw）的操作只有在组内验证后才可以commit，Read-only (RO)事务是不需要验证可以立即执行，当一个事务在一个节点上提交之前，会在组内自动进行原子性的广播，告知其他节点变更了什么内容/执行了什么事务，然后为该事物建立一个全局的排序，最终，这意味着所有的服务器都以相同的顺序接收相同的事务集。因此，所有服务器都按照相同的顺序应用相同的变更集，因此它们在组中保持一致。 在多主模式下，该组的所有成员都设置为读写模式，在多主模式下，不支持SERIALIZABLE事务隔离级别，且不能完全支持级联外键约束。 
3、配置要求和限制
- inndb存储引擎；
- 每个表需要定义显式主键；
- 隔离级别：官网建议READ COMMITTED级别，不支持SERIALIZABLE隔离级别；
- 不建议使用级联外键；
- IPv4网络；
- auto_increment_increment和auto_increment_offset的配置；
- log-bin = ROW；
- log_slave_updates = ON；
- 开启GTID；
- 安装引擎：group_replication.so；
4、搭建Mysql Group Replication
- 基础信息如下:
| IP | port | server_id | server_uuid |
|---|---|---|---|
| 172.17.0.37（A） | 3306 | 1001 | b13df29e-90b6-11e8-8d1b-525400fc3993 |
| 172.17.0.48（B） | 3306 | 1002 | 1fa7b3ca-9475-11e8-a217-5254004e7cfe |
| 172.17.0.2 （C） | 3306 | 1003 | 81d824f1-90ba-11e8-a83d-52540043d75a |
- my.cnf配置文件:
#172.17.0.37（A）:
[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
character_set_server=utf8
init_connect='SET NAMES utf8'
lower_case_table_names = 1
# Disabling symbolic-links is rmended to prevent assorted security risks
symbolic-links=0
log-error=/usr/local/mysql/logs/mysqld.log
pid-file=/usr/local/mysql/pids/mysqld.pid
# Group Replication
server_id = 1001
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
transaction_write_set_extraction ='XXHASH64'
loose-group_replication_group_name = 'b13df29e-90b6-11e8-8d1b-525400fc3996'
loose-group_replication_start_on_boot = off
loose-group_replication_local_address = '172.17.0.37:10061'
loose-group_replication_group_seeds ='172.17.0.37:10061,172.17.0.2:10063,172.17.0.48:10062'
loose-group_replication_bootstrap_group = off
group_replication_ip_whitelist = '172.17.0.37,172.17.0.2,172.17.0.48'
report_host='172.17.0.37'
log_timestamps='system'
plugin-dir=/usr/local/mysql/plugin
--------------------------------------------------------------------------------
172.17.0.48（B）:
[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
character_set_server=utf8
init_connect='SET NAMES utf8'
lower_case_table_names = 1
# Disabling symbolic-links is rmended to prevent assorted security risks
symbolic-links=0
log-error=/usr/local/mysql/logs/mysqld.log
pid-file=/usr/local/mysql/pids/mysqld.pid
# Group Replication
server_id = 1002
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
transaction_write_set_extraction ='XXHASH64'
loose-group_replication_group_name = 'b13df29e-90b6-11e8-8d1b-525400fc3996'
loose-group_replication_start_on_boot = off
loose-group_replication_local_address = '172.17.0.48:10062'
loose-group_replication_group_seeds ='172.17.0.37:10061,172.17.0.2:10063,172.17.0.48:10062'
loose-group_replication_bootstrap_group = off
group_replication_ip_whitelist = '172.17.0.37,172.17.0.2,172.17.0.48'
log_timestamps='system'
report_host='172.17.0.48'
plugin-dir=/usr/local/mysql/plugin
--------------------------------------------------------------------------------
172.17.0.2 （C）:
[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
character_set_server=utf8
init_connect='SET NAMES utf8'
lower_case_table_names = 1
# Disabling symbolic-links is rmended to prevent assorted security risks
symbolic-links=0
log-error=/usr/local/mysql/logs/mysqld.log
pid-file=/usr/local/mysql/pids/mysqld.pid
# Group Replication
server_id = 1003
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
transaction_write_set_extraction ='XXHASH64'
loose-group_replication_group_name = 'b13df29e-90b6-11e8-8d1b-525400fc3996'
loose-group_replication_start_on_boot = off
loose-group_replication_local_address = '172.17.0.2:10063'
loose-group_replication_group_seeds ='172.17.0.37:10061,172.17.0.2:10063,172.17.0.48:10062'
loose-group_replication_bootstrap_group = off
group_replication_ip_whitelist = '172.17.0.37,172.17.0.2,172.17.0.48'
log_timestamps='system'
report_host='172.17.0.2'
plugin-dir=/usr/local/mysql/plugin
- 建立复制账号:
A、B、C：
grant replication slave on *.* to rpl_user@'%' identified by 'rpl_pass';
- 安装引擎:
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
show plugins;
1、单主模式（set global groupreplicationsingleprimarymode =ON）:
- 配置Group:
172.17.0.37：
mysql> set global group_replication_single_primary_mode=on;
Query OK, 0 rows affected (0.00 sec)
mysql> SET GLOBAL group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.00 sec)
mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_pass' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.07 sec)
mysql> start group_replication;
Query OK, 0 rows affected, 1 warning (2.08 sec)
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)
172.17.0.48和172.17.0.2：
mysql> set global group_replication_single_primary_mode=on;
Query OK, 0 rows affected (0.00 sec)
mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_pass' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.05 sec)
mysql> start group_replication;
Query OK, 0 rows affected (5.81 sec)
172.17.0.37：
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 1fa7b3ca-9475-11e8-a217-5254004e7cfe | 172.17.0.48 | 3306 | ONLINE |
| group_replication_applier | 81d824f1-90ba-11e8-a83d-52540043d75a | 172.17.0.2 | 3306 | ONLINE |
| group_replication_applier | b13df29e-90b6-11e8-8d1b-525400fc3993 | 172.17.0.37 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)
mysql> show variables like '%read_on%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_read_only | OFF |
| read_only | OFF |
| super_read_only | OFF |
| transaction_read_only | OFF |
| tx_read_only | OFF |
+-----------------------+-------+
5 rows in set (0.00 sec)
mysql> show variables like '%server_uuid%';
+---------------+--------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------+
| server_uuid | b13df29e-90b6-11e8-8d1b-525400fc3993 |
+---------------+--------------------------------------+
1 row in set (0.00 sec)
mysql> select * from performance_schema.global_status WHERE VARIABLE_NAME like '%group_replication%';
+----------------------------------+--------------------------------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+----------------------------------+--------------------------------------+
| group_replication_primary_member | b13df29e-90b6-11e8-8d1b-525400fc3993 |
+----------------------------------+--------------------------------------+
1 row in set (0.01 sec)
mysql> show variables like '%group_replication_member_weight%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| group_replication_member_weight | 80 |
+---------------------------------+-------+
1 row in set (0.00 sec)
172.17.0.48：
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 1fa7b3ca-9475-11e8-a217-5254004e7cfe | 172.17.0.48 | 3306 | ONLINE |
| group_replication_applier | 81d824f1-90ba-11e8-a83d-52540043d75a | 172.17.0.2 | 3306 | ONLINE |
| group_replication_applier | b13df29e-90b6-11e8-8d1b-525400fc3993 | 172.17.0.37 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)
mysql> show variables like '%read_on%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_read_only | OFF |
| read_only | ON |
| super_read_only | ON |
| transaction_read_only | OFF |
| tx_read_only | OFF |
+-----------------------+-------+
5 rows in set (0.00 sec)
mysql> select * from performance_schema.global_status WHERE VARIABLE_NAME like '%group_replication%';
+----------------------------------+--------------------------------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+----------------------------------+--------------------------------------+
| group_replication_primary_member | b13df29e-90b6-11e8-8d1b-525400fc3993 |
+----------------------------------+--------------------------------------+
1 row in set (0.01 sec)
mysql> show variables like '%server_uuid%';
+---------------+--------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------+
| server_uuid | 1fa7b3ca-9475-11e8-a217-5254004e7cfe |
+---------------+--------------------------------------+
1 row in set (0.00 sec)
mysql> show variables like '%group_replication_member_weight%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| group_replication_member_weight | 50 |
+---------------------------------+-------+
1 row in set (0.00 sec)
172.17.0.2：
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 1fa7b3ca-9475-11e8-a217-5254004e7cfe | 172.17.0.48 | 3306 | ONLINE |
| group_replication_applier | 81d824f1-90ba-11e8-a83d-52540043d75a | 172.17.0.2 | 3306 | ONLINE |
| group_replication_applier | b13df29e-90b6-11e8-8d1b-525400fc3993 | 172.17.0.37 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)
mysql> show variables like '%read_on%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_read_only | OFF |
| read_only | ON |
| super_read_only | ON |
| transaction_read_only | OFF |
| tx_read_only | OFF |
+-----------------------+-------+
5 rows in set (0.00 sec)
mysql> select * from performance_schema.global_status WHERE VARIABLE_NAME like '%group_replication%';
+----------------------------------+--------------------------------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+----------------------------------+--------------------------------------+
| group_replication_primary_member | b13df29e-90b6-11e8-8d1b-525400fc3993 |
+----------------------------------+--------------------------------------+
1 row in set (0.00 sec)
mysql> show variables like '%server_uuid%';
+---------------+--------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------+
| server_uuid | 81d824f1-90ba-11e8-a83d-52540043d75a |
+---------------+--------------------------------------+
1 row in set (0.00 sec)
mysql> show variables like '%group_replication_member_weight%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| group_replication_member_weight | 100 |
+---------------------------------+-------+
1 row in set (0.01 sec)
单主故障处理
（1）非主数据库挂掉
172.17.0.48：
[root@VM_0_48_centos ~]# service mysqld stop
Shutting down MySQL............. SUCCESS!
172.17.0.37/172.17.0.2：
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 81d824f1-90ba-11e8-a83d-52540043d75a | 172.17.0.2 | 3306 | ONLINE |
| group_replication_applier | b13df29e-90b6-11e8-8d1b-525400fc3993 | 172.17.0.37 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
2 rows in set (0.00 sec)
mysql> select * from performance_schema.global_status WHERE VARIABLE_NAME like '%group_replication%';
+----------------------------------+--------------------------------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+----------------------------------+--------------------------------------+
| group_replication_primary_member | b13df29e-90b6-11e8-8d1b-525400fc3993 |
+----------------------------------+--------------------------------------+
172.17.0.48：
mysql> use sissi_01;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+--------------------+
| Tables_in_sissi_01 |
+--------------------+
| tab |
+--------------------+
1 row in set (0.00 sec)
mysql> select * from tab;
+----+------+
| id | name |
+----+------+
| 1 | NULL |
| 2 | NULL |
| 7 | NULL |
| 8 | NULL |
| 9 | NULL |
| 10 | NULL |
| 13 | aa |
| 20 | aa |
| 27 | aa |
| 41 | bb |
| 42 | cc |
| 47 | ddd |
| 54 | ee |
| 61 | ff |
| 68 | gg |
| 75 | hh |
| 76 | hh |
| 77 | hh |
| 78 | ii |
| 79 | ggw |
| 80 | cccc |
| 81 | coco |
| 84 | kk |
| 90 | kk2 |
| 96 | kk4 |
+----+------+
25 rows in set (0.00 sec)
mysql> insert into tab select 97,'tencent';
Query OK, 1 row affected (0.03 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> select * from sissi_01.tab;
+----+---------+
| id | name |
+----+---------+
| 1 | NULL |
| 2 | NULL |
| 7 | NULL |
| 8 | NULL |
| 9 | NULL |
| 10 | NULL |
| 13 | aa |
| 20 | aa |
| 27 | aa |
| 41 | bb |
| 42 | cc |
| 47 | ddd |
| 54 | ee |
| 61 | ff |
| 68 | gg |
| 75 | hh |
| 76 | hh |
| 77 | hh |
| 78 | ii |
| 79 | ggw |
| 80 | cccc |
| 81 | coco |
| 84 | kk |
| 90 | kk2 |
| 96 | kk4 |
| 97 | tencent |
+----+---------+
26 rows in set (0.00 sec)
172.17.0.2：
mysql> select * from tab;
ERROR 1046 (3D000): No database selected
mysql> select * from sissi_01.tab;
+----+---------+
| id | name |
+----+---------+
| 1 | NULL |
| 2 | NULL |
| 7 | NULL |
| 8 | NULL |
| 9 | NULL |
| 10 | NULL |
| 13 | aa |
| 20 | aa |
| 27 | aa |
| 41 | bb |
| 42 | cc |
| 47 | ddd |
| 54 | ee |
| 61 | ff |
| 68 | gg |
| 75 | hh |
| 76 | hh |
| 77 | hh |
| 78 | ii |
| 79 | ggw |
| 80 | cccc |
| 81 | coco |
| 84 | kk |
| 90 | kk2 |
| 96 | kk4 |
| 97 | tencent |
+----+---------+
26 rows in set (0.00 sec)
172.17.0.48：
[root@VM_0_48_centos ~]# service mysqld start
Starting MySQL. SUCCESS!
mysql> select * from performance_schema.replication_group_members;
+---------------------------+-----------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+-----------+-------------+-------------+--------------+
| group_replication_applier | | | NULL | OFFLINE |
+---------------------------+-----------+-------------+-------------+--------------+
1 row in set (0.00 sec)
mysql> set global group_replication_single_primary_mode=on;
Query OK, 0 rows affected (0.00 sec)
mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_pass' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.11 sec)
mysql> start group_replication;
Query OK, 0 rows affected (3.27 sec)
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 1fa7b3ca-9475-11e8-a217-5254004e7cfe | 172.17.0.48 | 3306 | ONLINE |
| group_replication_applier | 81d824f1-90ba-11e8-a83d-52540043d75a | 172.17.0.2 | 3306 | ONLINE |
| group_replication_applier | b13df29e-90b6-11e8-8d1b-525400fc3993 | 172.17.0.37 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)
mysql> select * from sissi_01.tab;
+----+---------+
| id | name |
+----+---------+
| 1 | NULL |
| 2 | NULL |
| 7 | NULL |
| 8 | NULL |
| 9 | NULL |
| 10 | NULL |
| 13 | aa |
| 20 | aa |
| 27 | aa |
| 41 | bb |
| 42 | cc |
| 47 | ddd |
| 54 | ee |
| 61 | ff |
| 68 | gg |
| 75 | hh |
| 76 | hh |
| 77 | hh |
| 78 | ii |
| 79 | ggw |
| 80 | cccc |
| 81 | coco |
| 84 | kk |
| 90 | kk2 |
| 96 | kk4 |
| 97 | tencent |
+----+---------+
26 rows in set (0.00 sec)（2）主数据库挂掉
172.17.0.37：
[root@VM_0_37_centos ~]# service mysqld stop
Shutting down MySQL............ SUCCESS!
172.17.0.2：
mysql> show variables like '%group_replication_member_weight%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| group_replication_member_weight | 100 |
+---------------------------------+-------+
1 row in set (0.01 sec)
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 1fa7b3ca-9475-11e8-a217-5254004e7cfe | 172.17.0.48 | 3306 | ONLINE |
| group_replication_applier | 81d824f1-90ba-11e8-a83d-52540043d75a | 172.17.0.2 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
2 rows in set (0.00 sec)
mysql> show variables like '%read_on%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_read_only | OFF |
| read_only | OFF |
| super_read_only | OFF |
| transaction_read_only | OFF |
| tx_read_only | OFF |
+-----------------------+-------+
5 rows in set (0.01 sec)
mysql> select * from performance_schema.global_status WHERE VARIABLE_NAME like '%group_replication%';
+----------------------------------+--------------------------------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+----------------------------------+--------------------------------------+
| group_replication_primary_member | 81d824f1-90ba-11e8-a83d-52540043d75a |
+----------------------------------+--------------------------------------+
1 row in set (0.00 sec)
mysql> show variables like '%server_uuid%';
+---------------+--------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------+
| server_uuid | 81d824f1-90ba-11e8-a83d-52540043d75a |
+---------------+--------------------------------------+
1 row in set (0.00 sec)
mysql> insert into sissi_01.tab select 98 ,'sng001';
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> select * from sissi_01.tab;
+----+---------+
| id | name |
+----+---------+
| 1 | NULL |
| 2 | NULL |
| 7 | NULL |
| 8 | NULL |
| 9 | NULL |
| 10 | NULL |
| 13 | aa |
| 20 | aa |
| 27 | aa |
| 41 | bb |
| 42 | cc |
| 47 | ddd |
| 54 | ee |
| 61 | ff |
| 68 | gg |
| 75 | hh |
| 76 | hh |
| 77 | hh |
| 78 | ii |
| 79 | ggw |
| 80 | cccc |
| 81 | coco |
| 84 | kk |
| 90 | kk2 |
| 96 | kk4 |
| 97 | tencent |
| 98 | sng001 |
+----+---------+
27 rows in set (0.00 sec)
172.17.0.48：
mysql> show variables like '%group_replication_member_weight%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| group_replication_member_weight | 50 |
+---------------------------------+-------+
1 row in set (0.01 sec)
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 1fa7b3ca-9475-11e8-a217-5254004e7cfe | 172.17.0.48 | 3306 | ONLINE |
| group_replication_applier | 81d824f1-90ba-11e8-a83d-52540043d75a | 172.17.0.2 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
2 rows in set (0.00 sec)
mysql> show variables like '%read_on%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_read_only | OFF |
| read_only | ON |
| super_read_only | ON |
| transaction_read_only | OFF |
| tx_read_only | OFF |
+-----------------------+-------+
5 rows in set (0.00 sec)
mysql> select * from performance_schema.global_status WHERE VARIABLE_NAME like '%group_replication%';
+----------------------------------+--------------------------------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+----------------------------------+--------------------------------------+
| group_replication_primary_member | 81d824f1-90ba-11e8-a83d-52540043d75a |
+----------------------------------+--------------------------------------+
1 row in set (0.00 sec)
mysql> show variables like '%server_uuid%';
+---------------+--------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------+
| server_uuid | 1fa7b3ca-9475-11e8-a217-5254004e7cfe |
+---------------+--------------------------------------+
1 row in set (0.00 sec)
mysql> select * from sissi_01.tab;
+----+---------+
| id | name |
+----+---------+
| 1 | NULL |
| 2 | NULL |
| 7 | NULL |
| 8 | NULL |
| 9 | NULL |
| 10 | NULL |
| 13 | aa |
| 20 | aa |
| 27 | aa |
| 41 | bb |
| 42 | cc |
| 47 | ddd |
| 54 | ee |
| 61 | ff |
| 68 | gg |
| 75 | hh |
| 76 | hh |
| 77 | hh |
| 78 | ii |
| 79 | ggw |
| 80 | cccc |
| 81 | coco |
| 84 | kk |
| 90 | kk2 |
| 96 | kk4 |
| 97 | tencent |
| 98 | sng001 |
+----+---------+
27 rows in set (0.00 sec)
172.17.0.37：
[root@VM_0_37_centos ~]# service mysqld start
Starting MySQL. SUCCESS!
mysql> select * from performance_schema.replication_group_members;
+---------------------------+-----------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+-----------+-------------+-------------+--------------+
| group_replication_applier | | | NULL | OFFLINE |
+---------------------------+-----------+-------------+-------------+--------------+
1 row in set (0.00 sec)
mysql> set global group_replication_single_primary_mode=on;
Query OK, 0 rows affected (0.00 sec)
mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_pass' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.07 sec)
mysql> start group_replication;
Query OK, 0 rows affected (2.93 sec)
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 1fa7b3ca-9475-11e8-a217-5254004e7cfe | 172.17.0.48 | 3306 | ONLINE |
| group_replication_applier | 81d824f1-90ba-11e8-a83d-52540043d75a | 172.17.0.2 | 3306 | ONLINE |
| group_replication_applier | b13df29e-90b6-11e8-8d1b-525400fc3993 | 172.17.0.37 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)
mysql> select * from sissi_01.tab;
+----+---------+
| id | name |
+----+---------+
| 1 | NULL |
| 2 | NULL |
| 7 | NULL |
| 8 | NULL |
| 9 | NULL |
| 10 | NULL |
| 13 | aa |
| 20 | aa |
| 27 | aa |
| 41 | bb |
| 42 | cc |
| 47 | ddd |
| 54 | ee |
| 61 | ff |
| 68 | gg |
| 75 | hh |
| 76 | hh |
| 77 | hh |
| 78 | ii |
| 79 | ggw |
| 80 | cccc |
| 81 | coco |
| 84 | kk |
| 90 | kk2 |
| 96 | kk4 |
| 97 | tencent |
| 98 | sng001 |
+----+---------+
27 rows in set (0.00 sec)
mysql> select * from performance_schema.global_status WHERE VARIABLE_NAME like '%group_replication%';
+----------------------------------+--------------------------------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+----------------------------------+--------------------------------------+
| group_replication_primary_member | 81d824f1-90ba-11e8-a83d-52540043d75a |
+----------------------------------+--------------------------------------+
1 row in set (0.00 sec)
#### 2、多主模式（set global group_replication_single_primary_mode =OFF）:
172.17.0.37/2/48：
mysql> set global group_replication_single_primary_mode =OFF;
Query OK, 0 rows affected (0.00 sec)
172.17.0.37：
mysql> set global group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.00 sec)
mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_pass' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.06 sec)
mysql> start group_replication;
Query OK, 0 rows affected (2.09 sec)
mysql> set global group_replication_bootstrap_group=Off;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 1fa7b3ca-9475-11e8-a217-5254004e7cfe | 172.17.0.48 | 3306 | ONLINE |
| group_replication_applier | 81d824f1-90ba-11e8-a83d-52540043d75a | 172.17.0.2 | 3306 | ONLINE |
| group_replication_applier | b13df29e-90b6-11e8-8d1b-525400fc3993 | 172.17.0.37 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)
mysql> select * from performance_schema.global_status WHERE VARIABLE_NAME like '%group_replication%';
+----------------------------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+----------------------------------+----------------+
| group_replication_primary_member | |
+----------------------------------+----------------+
1 row in set (0.00 sec)
mysql> show variables like '%read_only%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_read_only | OFF |
| read_only | OFF |
| super_read_only | OFF |
| transaction_read_only | OFF |
| tx_read_only | OFF |
+-----------------------+-------+
5 rows in set (0.00 sec)
mysql> select * from sissi_01.tab;
+----+---------+
| id | name |
+----+---------+
| 1 | NULL |
| 2 | NULL |
| 7 | NULL |
| 8 | NULL |
| 9 | NULL |
| 10 | NULL |
| 13 | aa |
| 20 | aa |
| 27 | aa |
| 41 | bb |
| 42 | cc |
| 47 | ddd |
| 54 | ee |
| 61 | ff |
| 68 | gg |
| 75 | hh |
| 76 | hh |
| 77 | hh |
| 78 | ii |
| 79 | ggw |
| 80 | cccc |
| 81 | coco |
| 84 | kk |
| 90 | kk2 |
| 96 | kk4 |
| 97 | tencent |
| 98 | sng001 |
| 99 | sng002 |
+----+---------+
28 rows in set (0.00 sec)
172.17.0.2：
mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_pass' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.04 sec)
mysql> start group_replication;
Query OK, 0 rows affected (3.26 sec)
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 1fa7b3ca-9475-11e8-a217-5254004e7cfe | 172.17.0.48 | 3306 | ONLINE |
| group_replication_applier | 81d824f1-90ba-11e8-a83d-52540043d75a | 172.17.0.2 | 3306 | ONLINE |
| group_replication_applier | b13df29e-90b6-11e8-8d1b-525400fc3993 | 172.17.0.37 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)
mysql> select * from performance_schema.global_status WHERE VARIABLE_NAME like '%group_replication%';
+----------------------------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+----------------------------------+----------------+
| group_replication_primary_member | |
+----------------------------------+----------------+
1 row in set (0.00 sec)
mysql> show variables like '%read_only%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_read_only | OFF |
| read_only | OFF |
| super_read_only | OFF |
| transaction_read_only | OFF |
| tx_read_only | OFF |
+-----------------------+-------+
5 rows in set (0.00 sec)
mysql> insert into sissi_01.tab select 98 ,'sng002';
ERROR 1062 (23000): Duplicate entry '98' for key 'PRIMARY'
mysql> insert into sissi_01.tab select 99 ,'sng002';
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> select * from sissi_01.tab;
+----+---------+
| id | name |
+----+---------+
| 1 | NULL |
| 2 | NULL |
| 7 | NULL |
| 8 | NULL |
| 9 | NULL |
| 10 | NULL |
| 13 | aa |
| 20 | aa |
| 27 | aa |
| 41 | bb |
| 42 | cc |
| 47 | ddd |
| 54 | ee |
| 61 | ff |
| 68 | gg |
| 75 | hh |
| 76 | hh |
| 77 | hh |
| 78 | ii |
| 79 | ggw |
| 80 | cccc |
| 81 | coco |
| 84 | kk |
| 90 | kk2 |
| 96 | kk4 |
| 97 | tencent |
| 98 | sng001 |
| 99 | sng002 |
+----+---------+
28 rows in set (0.00 sec)
172.17.0.48:
mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_pass' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.05 sec)
mysql> start group_replication;
Query OK, 0 rows affected (5.80 sec)
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 1fa7b3ca-9475-11e8-a217-5254004e7cfe | 172.17.0.48 | 3306 | ONLINE |
| group_replication_applier | 81d824f1-90ba-11e8-a83d-52540043d75a | 172.17.0.2 | 3306 | ONLINE |
| group_replication_applier | b13df29e-90b6-11e8-8d1b-525400fc3993 | 172.17.0.37 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)
mysql> select * from performance_schema.global_status WHERE VARIABLE_NAME like '%group_replication%';
+----------------------------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+----------------------------------+----------------+
| group_replication_primary_member | |
+----------------------------------+----------------+
1 row in set (0.00 sec)
mysql> show variables like '%read_only%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_read_only | OFF |
| read_only | OFF |
| super_read_only | OFF |
| transaction_read_only | OFF |
| tx_read_only | OFF |
+-----------------------+-------+
5 rows in set (0.00 sec)
mysql> select * from sissi_01.tab;
+----+---------+
| id | name |
+----+---------+
| 1 | NULL |
| 2 | NULL |
| 7 | NULL |
| 8 | NULL |
| 9 | NULL |
| 10 | NULL |
| 13 | aa |
| 20 | aa |
| 27 | aa |
| 41 | bb |
| 42 | cc |
| 47 | ddd |
| 54 | ee |
| 61 | ff |
| 68 | gg |
| 75 | hh |
| 76 | hh |
| 77 | hh |
| 78 | ii |
| 79 | ggw |
| 80 | cccc |
| 81 | coco |
| 84 | kk |
| 90 | kk2 |
| 96 | kk4 |
| 97 | tencent |
| 98 | sng001 |
| 99 | sng002 |
+----+---------+
28 rows in set (0.00 sec)多主故障处理
172.17.0.48：
[root@VM_0_48_centos ~]# service mysqld stop
Shutting down MySQL............ SUCCESS!
mysql> insert into sissi_01.tab select 99 ,'sng002';
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
172.17.0.2：
mysql> select * from sissi_01.tab;
+----+---------+
| id | name |
+----+---------+
| 1 | NULL |
| 2 | NULL |
| 7 | NULL |
| 8 | NULL |
| 9 | NULL |
| 10 | NULL |
| 13 | aa |
| 20 | aa |
| 27 | aa |
| 41 | bb |
| 42 | cc |
| 47 | ddd |
| 54 | ee |
| 61 | ff |
| 68 | gg |
| 75 | hh |
| 76 | hh |
| 77 | hh |
| 78 | ii |
| 79 | ggw |
| 80 | cccc |
| 81 | coco |
| 84 | kk |
| 90 | kk2 |
| 96 | kk4 |
| 97 | tencent |
| 98 | sng001 |
| 99 | sng002 |
+----+---------+
28 rows in set (0.00 sec)
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 81d824f1-90ba-11e8-a83d-52540043d75a | 172.17.0.2 | 3306 | ONLINE |
| group_replication_applier | b13df29e-90b6-11e8-8d1b-525400fc3993 | 172.17.0.37 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
2 rows in set (0.00 sec)
172.17.0.37：
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 81d824f1-90ba-11e8-a83d-52540043d75a | 172.17.0.2 | 3306 | ONLINE |
| group_replication_applier | b13df29e-90b6-11e8-8d1b-525400fc3993 | 172.17.0.37 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
2 rows in set (0.00 sec)
mysql> insert into sissi_01.tab select 100 ,'sng003';
Query OK, 1 row affected (0.03 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> select * from sissi_01.tab;
+-----+---------+
| id | name |
+-----+---------+
| 80 | cccc |
| 81 | coco |
| 84 | kk |
| 90 | kk2 |
| 96 | kk4 |
| 97 | tencent |
| 98 | sng001 |
| 99 | sng002 |
| 100 | sng003 |
+-----+---------+
29 rows in set (0.00 sec)
172.17.0.48：
[root@VM_0_48_centos ~]# service mysqld start
Starting MySQL. SUCCESS!
mysql> set global group_replication_single_primary_mode =OFF;
Query OK, 0 rows affected (0.00 sec)
mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_pass' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.06 sec)
mysql> start group_replication;
Query OK, 0 rows affected (2.72 sec)
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 1fa7b3ca-9475-11e8-a217-5254004e7cfe | 172.17.0.48 | 3306 | ONLINE |
| group_replication_applier | 81d824f1-90ba-11e8-a83d-52540043d75a | 172.17.0.2 | 3306 | ONLINE |
| group_replication_applier | b13df29e-90b6-11e8-8d1b-525400fc3993 | 172.17.0.37 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)
mysql> select * from sissi_01.tab;
+-----+---------+
| id | name |
+-----+---------+
| 80 | cccc |
| 81 | coco |
| 84 | kk |
| 90 | kk2 |
| 96 | kk4 |
| 97 | tencent |
| 98 | sng001 |
| 99 | sng002 |
| 100 | sng003 |
+-----+---------+
29 rows in set (0.00 sec)补充：模拟一个节点和两个节点挂恢复是一样的：
set global group_replication_single_primary_mode =OFF;
CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_pass' FOR CHANNEL 'group_replication_recovery';
start group_replication;感谢您花费时间阅读这篇文章！祝您阅读、分享愉快！
