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

  • 基础信息如下:
IPportserver_idserver_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;

感谢您花费时间阅读这篇文章!祝您阅读、分享愉快!

posted @ 2019-10-29 17:39  Sisiluo  阅读(7288)  评论(0编辑  收藏  举报