MySQL5.7.17 Group Replication initial detailed explanation-Mysql Tutorial-php.cn

1, About Group Replication

Group-based Replication is a method used in fault-tolerant systems technology in. Replication-group is composed of multiple servers (nodes) that can communicate with each other.

In the communication layer, Group replication implements a series of mechanisms: such as atomic message delivery and total ordering of messages.

These atomic and abstract mechanisms provide strong support for implementing more advanced database replication solutions.

MySQL Group Replication implements a multi-master, fully updated replication protocol based on these technologies and concepts.

In short, a Replication-group is a group of nodes. Each node can execute transactions independently, and read and write transactions will be coordinated with other nodes in the group before committing.

Therefore, when a transaction is ready to be submitted, it will automatically be atomically broadcast within the group to inform other nodes of what content has been changed/what transactions have been performed.

This atomic broadcast method keeps this transaction in the same order on every node.

This means that each node receives the same transaction log in the same order, so each node replays these transaction logs in the same order, and ultimately the entire group maintains a completely consistent state.

However, there may be resource contention between transactions executed on different nodes. This phenomenon easily occurs in two different concurrent transactions.

Suppose there are two concurrent transactions on different nodes that update the same row of data, then resource contention will occur.

Faced with this situation, Group Replication determines that the transaction submitted first is a valid transaction and will be repeated in the entire group. The transaction submitted later will be directly interrupted, or rolled back, and finally discarded.

Therefore, this is also a shared-nothing replication scheme, and each node saves a complete copy of the data. See the following picture 01.png, which describes the specific workflow and can be compared with other solutions concisely. This replication scheme is, to some extent, similar to the Replication method of the database state machine (DBSM).

2, install mysql5.7.17

Download MYSQL5.7.17 from the official website

Set the /etc/hosts mapping on the three db servers, as follows:

#Installed database server:

192.168.136.130 db1 .136.133 db2

192.168.136.134 db3

Database server address##1201361303317192.168.136.134 (db3)3317gtid

Port	Data directory	Server -id	##192.168.136.130 (db1)
3317	/data/mysql/data		##192.168.136.133 (db2)
##/data/mysql/data	120136133
/data/mysql/data	120136134		##3 , build

Copy:

Configure gtid on 3 my.cnfs:

[mysqld]
gtid_mode=ON
log-slave-updates=ON
enforce-gtid-consistency=ON

Copy after login

Assign accounts to db1, db2, and db3 on 3 mysql instances:

mysql> GRANT  REPLICATION SLAVE ON *.* TO &#39;repl&#39;@&#39;192.168.%&#39; IDENTIFIED BY  &#39;rlpbright_1927@ys&#39;;
Query OK, 0 rows affected, 1 warning  (0.00 sec)
 
mysql>

Copy after login

Build gtid service on db2 and db3:

mysql> change  master to master_user=&#39;repl&#39;, 
master_password=&#39;rlpbright_1927@ys&#39;,  
master_host=&#39;db1&#39;,master_port=3317, master_auto_position=1;
Query OK, 0 rows affected, 2 warnings  (0.02 sec)
 
mysql>  start slave;
Query OK, 0 rows affected (0.04 sec)
 
mysql>
mysql> show  slave status\G
*************************** 1. row  ***************************
               Slave_IO_State: Waiting for  master to send event
                  Master_Host: db1
                  Master_User: repl
                  Master_Port: 3317
                Connect_Retry: 60
              Master_Log_File:  mysql-bin.000002
           Read_Master_Log_Pos: 445
               Relay_Log_File:  mysql-relay-bin.000002
                Relay_Log_Pos: 414
         Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
………………………………..

Copy after login

4，开启Group Replication

有了gtid之后，开启group replication就方便多了。首先需要安装group replication插件

mysql> INSTALL PLUGIN group_replication  SONAME &#39;group_replication.so&#39;;
Query OK, 0 rows affected (0.03 sec)
 
 
mysql> show  plugins;
+----------------------------+----------+--------------------+----------------------+---------+
| Name        | Status   | Type        | Library          | License |
+----------------------------+----------+--------------------+----------------------+---------+
| binlog                 | ACTIVE   | STORAGE ENGINE     | NULL          | GPL     |
…………
|  group_replication          |  ACTIVE   | GROUP REPLICATION  | group_replication.so | GPL     |
+----------------------------+----------+--------------------+----------------------+---------+
45 rows in set  (0.00 sec)

Copy after login

配置参数，db1（master）上：

mysql> set @@global.transaction_write_set_extraction =  XXHASH64
mysql> set @@global.group_replication_start_on_boot = OFF
mysql> set @@global.group_replication_bootstrap_group = OFF
mysql> set @@global.group_replication_group_name = 0c6d3e5f-90e2-11e6-802e-842b2b5909d6
mysql> set @@global.group_replication_local_address = &#39;db1:6606&#39;
mysql> set @@global.group_replication_group_seeds = &#39;db2:6607,db3:6608&#39;

Copy after login

配置参数，db2（slave1）上：

mysql> set @@global.transaction_write_set_extraction =  XXHASH64
mysql> set @@global.group_replication_start_on_boot = OFF
mysql> set @@global.group_replication_bootstrap_group = OFF
mysql> set @@global.group_replication_group_name = 0c6d3e5f-90e2-11e6-802e-842b2b5909d6
mysql> set @@global.group_replication_local_address = &#39;db2:6607&#39;
mysql> set @@global.group_replication_group_seeds = &#39;db111:6606,127.0.0.1:db3&#39;

Copy after login

配置参数，db3（slave2）上：

mysql> set @@global.transaction_write_set_extraction =  XXHASH64
mysql> set @@global.group_replication_start_on_boot = OFF
mysql> set @@global.group_replication_bootstrap_group = OFF
mysql> set @@global.group_replication_group_name = 0c6d3e5f-90e2-11e6-802e-842b2b5909d6
mysql> set @@global.group_replication_local_address = &#39;db3:6608&#39;
mysql> set @@global.group_replication_group_seeds = &#39;db1:6607,db2:6606&#39;

Copy after login

BTY：如果之前没有配置transaction_write_set_extraction=XXHASH64，这里修改之后之前创建的数据库是没有办法执行插入操作的。所有如果想在线完成Group Replication的改造需要保证之前已经设置了transaction_write_set_extraction=XXHASH64。

开始构建集群，在db1（master）上执行：

# 构建集群

Copy after login

CHANGE MASTER TO MASTER_USER=&#39;repl&#39;, MASTER_PASSWORD=&#39;rlpbright_1927@ys&#39;FORCHANNEL&#39;group_replication_recovery&#39;;

Copy after login

#开启group_replication

Copy after login

SETGLOBAL  group_replication_bootstrap_group=ON;
START  GROUP_REPLICATION;
SETGLOBAL  group_replication_bootstrap_group=OFF;

Copy after login

db2、db3上加入

stop slave;
START GROUP_REPLICATION;

Copy after login

在db1上查看集群信息：

mysql>  SELECT * FROM performance_schema.replication_group_members;

Copy after login

The above is the detailed content of MySQL5.7.17 Group Replication initial detailed explanation. For more information, please follow other related articles on the PHP Chinese website!

Related labels：

MySQL5.7.17，Replication，Group

source：php.cn

Previous article：Detailed introduction to the construction and deployment process of MySQL Group Replication[Single-Primary Mode] Next article：Share the solution to solve the problem of forgotten password in Mysql5.7.17 (picture)

Statement of this Website

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn