MySQL 5.7 GroupReplication 高可用架构

最新推荐文章于 2024-09-28 21:52:30 发布
最新推荐文章于 2024-09-28 21:52:30 发布
阅读量431 收藏
点赞数
分类专栏： 学习
原文链接：https://www.cnblogs.com/sallyluo/p/11760304.html
版权

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）33061001b13df29e-90b6-11e8-8d1b-525400fc3993
172.17.0.48（B）330610021fa7b3ca-9475-11e8-a217-5254004e7cfe
172.17.0.2 （C）3306100381d824f1-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;
天码行空的码
关注
专栏目录
MySQL-5.7高可用MHA架构搭建部署
h16678481的博客
06-21 1070
MySQL5.7从主从配置-MHA高可用架构的搭建
mysql5.7镜像；mysql5.7镜像；mysql5.7镜像；mysql5.7镜像
05-30
mysql5.7镜像；mysql5.7镜像；mysql5.7镜像；mysql5.7镜像
mysql5.7高可用架构之MHA
weixin_33775582的博客
10-06 106
一、MHA简介 MHA（Master High Availability）目前在mysql高可用方面比较成熟。是一套优秀的作为 mysql高可用性环境下故障切换和主从提升的高可用软件。在MySQL故障切换过程中，MHA 能做到在0~30秒之内自动完成数据库的故障切换操作，并且在进行故障切换的过程中，MHA 能在最大程度上保证数据的一致性，以达到真正意义上的高可用。 该软件由两部分组成：M...
mysql5.7 高可用mha_mysql5.7高可用架构之MHA
weixin_39617113的博客
01-21 357
一、MHA简介MHA(Master High Availability)目前在mysql高可用方面比较成熟。是一套优秀的作为 mysql高可用性环境下故障切换和主从提升的高可用软件。在MySQL故障切换过程中，MHA 能做到在0~30秒之内自动完成数据库的故障切换操作，并且在进行故障切换的过程中，MHA 能在最大程度上保证数据的一致性，以达到真正意义上的高可用。该软件由两部分组成：MHA Mana...
Mysql—高可用之组复制 (MGR)
最新发布
恭喜你发现了我！！！！
09-28 906
MySQL Group Replication(简称 MGR )是 MySQL 官方于 2016 年 12 月推出的一个全新的高可用与高扩 展的解决方案组复制是 MySQL 5.7.17 版本出现的新特性，它提供了高可用、高扩展、高可靠的 MySQL 集群服务MySQL 组复制分单主模式和多主模式，传统的mysql复制技术仅解决了数据同步的问题， MGR 对属于同一组的服务器自动进行协调。
mysql5.7 组复制 多主_MySQL 5.7组复制实践-多主模式
weixin_31935793的博客
02-09 209
这是官方出品类似 pxc 的数据库高可用解决方案，影响深远。从异步复制，到半同步复制，到组复制，mysql高可用技术越来越强大。随着mgr的逐渐成熟，将会越来越多的应用MySQL的多种场景中。MySQL Group Replication is a MySQL Server plugin that provides distributed state machine replication wit...
mysql5.7主从复制高可用架构---MHA
柠檬精哒博客
08-16 1351
什么是MHA？ MHA(Master High Availability)是现在解决mysql高可用的一个相对成熟的方案 它是由两部分组成,管理端(master manager)和节点端(node manager),管理端可以单独布置在一台机器上,整个mysql主从集群就是它的各个节点, 管理端对所有节点进行监控,当master宕机之后,管理端会根据自己配置文件里的设定,将某一个从节点升为主...
MySQL学习（三）：主从复制Replication实现高可用的架构模式
源码之下，了无秘密
03-10 3049
概述 MySQL主从复制主要用于实现读写分离，所以主从架构一般为一主多从，即主库负责处理所有的写入请求，一个或多个从库负责处理所有的读请求，从而分散读写流量，实现读数据的高可用。除此之外，如果从库很多造成了主库复制负载太大，也可以考虑采用级联复制，即从库既作为主库的从库，也作为下一级从库的主库，从而降低主库的负载。 以上架构都是针对读实现高可用，在主库写方面还是存在单点问题，故也可以基于MySQ...
mysql5.7.31 mips64架构安装包
04-27
通过这个针对 MIPS64 架构优化的 MySQL 5.7.31 安装包，用户可以在龙芯平台上充分利用 MySQL 的强大功能，同时享受到高性能和稳定性。在使用过程中，应密切关注 MySQL 社区的更新和公告，以便获取最新的安全补丁和...
mysql5.7arm64.zip麒麟arm64架构下的deb安装包
09-02
mysql5.7arm64.zip mysql5.7在麒麟arm64架构下的deb安装包 1.安装deb文件 dpkg -i *.deb 安装过程中会要求你设置的mysql的root密码 2.修改/etc/my.cnf datadir = /data01/MPP/mysql/data bind-address=0.0.0.0 skip-...
mysql 5.7 中文文档
02-16
MySQL 5.7中文文档是针对这个特定数据库版本的详细技术参考，包含了丰富的信息，适合开发者、管理员和学习者使用。文档的翻译质量被描述为“还行”，尽管资源可能不易找到，但提供了全面的指南。 该文档涵盖了从...
MySQL 5.7.17 Group Replication搭建
weixin_33725239的博客
06-21 186
基于组复制的强大功能在MySQL 5.7.17之后以插件的形式实现，本文讲述在单机多实例基础上搭建组复制测试环境环境说明：操作系统:CentOS Linux release 7.3.1611 (Core)内核版本:Linux version 3.10.0-514.6.2.el7.x86_64MySQL版本:mysql-5.7.17-linux-gl...
MySQL Group Replication 多机多实例安装配置(Windows环境)
iteye_3489的博客
02-22 537
1、下载mysql5.7.17  下载地址   2、解压zip到指定的目录   本机安装目录为：D:\tools\mysql-5.7.17     3、配置mysql环境变量   PATH=D:\tools\mysql-5.7.17\bin;$PATH  注意：如果之前有安装过mysql，请手动卸载并清除注册表信息和mysql服务链接。 查看注册表信息，WIN+R键入：r...
MySQL复制（五）：组复制概念（Group Replication Concepts）
Vincent的博客
01-10 2403
组复制中的成员可以独立的发起和提交事务（多主模式），当事务准备提交时，需要在组中广播将要修改的数据集，将事务加入全局事务列表，所有组成员必须就全局事务列表中给定事务的顺序达成一致，且事务必须由组服务批准后才能提交。当服务器故障时，组复制虽然可以继续提供数据库服务，但那些连接到故障服务器上的会话必须重新连接，或者failover到其他正常的服务器上，组复制并不提供会话failover的功能，需要其他的解决方案，例如MySQL Router。当单一主库负载过大时，可以通过组复制的多主模式分散写入负载。
MySQL5.7 Group Replication (MGR)
热门推荐
david's Blog
04-09 1万+
MySQL5.7 Group Replication(组复制) 1 介绍 MySQL Group Replication（简称MGR）是MySQL官方于2016年12月推出的一个全新的高可用与高扩展的解决方案。MySQL组复制提供了高可用、高扩展、高可靠的MySQL集群服务。
MySQL中的auto_increment
耿进
08-30 7101
文章目录一、AUTO_INCREMENT1.1 测试二、auto_increment_increment和auto_increment_offset2.1 解释2.2 测试auto_increment_incrementauto_increment_offset当两者同时更改三.组复制中的group_replication_auto_increment_increment1.解释2.操作2.1 配置2.2 插数据 一、AUTO_INCREMENT AUTO_INCREMENT 属性可用于为新行生成唯一标识
mysql高可用之组复制 (MGR)
weixin_68398469的博客
08-24 444
简称MGR )是MySQL官方于2016年12月推出的一个全新的高可用与高扩 展的解决方案组复制是版本出现的新特性，它提供了高可用、高扩展、高可靠的MySQL集群服务MySQL组复制分单主模式和多主模式，传统的mysql复制技术仅解决了数据同步的问题，MGR对属于同一组的服务器自动进行协调。对于要提交的事务，组成员必须就全局事务序列中给定事务的顺序达成一致提交或回滚事务由每个服务器单独完成，但所有服务器都必须做出相同的决定。
mysql group replication(MGR)群组复制相关资料
windyzhou的专栏
05-02 2488
配置的集群成员，通信时会把主机名与ip地址进行对应，最好是在/etc/hosts中设置好，如果没有设置，则会碰到如下错误：2018-05-02T13:04:32.437256Z 10 [Note] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_recovery' executed'. Previous state master_host='mys...
MYSQL--架构--MGR--部署--01--单主模式
zhou920786312的博客
06-01 1306
| 角色 | IP | 主机名 |server_id |mysql版本 | 备注 | | :------- | :------- | :------- |:------- |:------- | | master | 192.168.187.177 | mysql01 | 1 | 8.0.22 | 主服务器,提供读写操作 | | slave | 192.168.187.178 | mysql02 | 2 | 8.0.22 | 从服务器,提供读操作 | | slave
Nginx搭建MYSQL双活高可用架构
- 高可用架构应具备良好的扩展性，以支持系统的平滑升级和扩展。 8. MySQL高可用解决方案 - 常见的MySQL高可用解决方案包括MySQL复制、Galera Cluster、MHA（Master High Availability）、Orchestrator等。 - ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值