1. Group replication model

• Group replication model

MySQL group replication is a new function introduced in MySQL 5.7.17, which realizes high availability mainly from replication. It supports single master model and multi master model (single master model by default).

• Single owner model

A master node is automatically selected from many MySQL nodes in the replication group. Only the master node can write, and other nodes are automatically set to read only. When the master node fails, a new master node will be automatically selected. After the selection is successful, it will be set to writable, and other slave s will point to the new master.

• Multi master model

Any node in the replication group can be written, so there is no concept of master and slave. As long as the number of nodes that fail suddenly is not too many, the multi master model can continue to be available.

2. Group replication principle

The replication group is composed of multiple server members, and each server member in the group can execute transactions independently. However, all read-write (RW) transactions will not be committed until the conflict detection is successful. Read only (RO) transactions do not need to be in conflict detection and can be committed immediately.

In other words, for any RW transaction, the commit operation is not determined by the originating server, but by the group. To be precise, on the originating server, when the transaction is ready to commit, the server broadcasts the write value (the changed row) and the corresponding write set (the unique identifier of the updated row). A global order is then established for the transaction. Ultimately, this means that all server members receive the same set of transactions in the same order. Therefore, all server members apply the same changes in the same order to ensure consistency within the group.

Group replication enables you to create redundant fault-tolerant systems based on the state of the replicated system in a group of servers. Therefore, as long as it is not all or most of the servers fail, even if there are some server failures, the system is still available, at the most, only the performance and scalability are reduced, but it is still available. Server failures are isolated and independent. They are monitored by group member services, which rely on a distributed fault detection system that can signal when any server leaves the group voluntarily or because of an unexpected stop.

In a word, MySQL group replication provides high availability, high flexibility and reliable MySQL services.

3. Realize group replication

Experimental environment:

server1 server2 server3 three virtual machines with mysql installed, and all of the three virtual machines should be written with analysis

vim /etc/hosts
172.25.254.1 server1
172.25.254.2 server2
172.25.254.3 server3

server1:

Experiments before step1 reduction:
Note: if you haven't done other mysql experiments before, you don't need to do this step

systemctl stop mysqld
cd /var/lib/mysql
rm -fr *

Step 2 generate UUID:

uuidgen	#The uuid generated in this step will be used later

step3 edit mysql configuration file:

vim /etc/my.cnf
//Write in:
server_id=1
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
plugin_load_add='group_replication.so'
transaction_write_set_extraction=XXHASH64	#Indicates that the Server must collect write sets for each transaction and encode them as hashes using the XXHASH64 hash algorithm
group_replication_group_name="624dda22-8761-43a3-8abd-b06716fb1694"	#Tell the plug-in that the group you are joining or creating needs to be named, usually write UUID
group_replication_start_on_boot=off	#Indicates that the plug-in does not automatically start group replication when the server starts
group_replication_local_address= "172.25.254.1:33061"	#Tells the plug-in to use the IP address local host and port 33061 to accept incoming connections from other members of the group.
group_replication_group_seeds= "172.25.254.1:33061,172.25.254.2:33061,172.25.254.3:33061"
group_replication_bootstrap_group=off	#Configure whether to automatically boot groups
loose-group_replication_ip_whitelist="127.0.0.1,172.25.254.0/24"	#User white list
loose-group_replication_enforce_update_everywhere_checks=ON	#Enable or disable strict consistency checking for multi master updates in multi master mode
loose-group_replication_single_primary_mode=OFF	#Set the group to automatically select a server to handle read / write work

step4 open the service, filter the password, and initialize the security:

systemctl start mysqld
cat /var/log/mysqld.log | grep password
mysql_secure_installation 

step5 start group replication:

mysql> SET SQL_LOG_BIN=0;	#Disable binary logging
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'Redhat.123'；	#Create user
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';	#Add user rights
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;	#refresh data
Query OK, 0 rows affected (0.00 sec)

mysql> SET SQL_LOG_BIN=1;	#Enable binary logging
Query OK, 0 rows affected (0.00 sec)

mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='Redhat.123' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.04 sec)

mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';	#Install the group replication plug-in (an error is reported because the configuration file has written this parameter)
ERROR 1125 (HY000): Function 'group_replication' already exists
mysql> SHOW PLUGINS;


mysql> SET GLOBAL group_replication_bootstrap_group=ON;	#Open first, wait until group replication is opened
Query OK, 0 rows affected (0.00 sec)

mysql> START GROUP_REPLICATION;	#Open group replication
Query OK, 0 rows affected (2.30 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;	#View the status, and display ONLINE to open successfully

server2:

Experiments before step1 reduction:

Note: if you haven't done other mysql experiments before, you don't need to do this step

systemctl stop mysqld
cd /var/lib/mysql
rm -fr *

step2 edit mysql configuration file:

vim /etc/my.cnf
//Two changes:
server_id=2	#Modify id
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
plugin_load_add='group_replication.so'
transaction_write_set_extraction=XXHASH64	
group_replication_group_name="624dda22-8761-43a3-8abd-b06716fb1694"	
group_replication_start_on_boot=off	
group_replication_local_address= "172.25.254.2:33061"	#Modify native ip
group_replication_group_seeds= "172.25.254.1:33061,172.25.254.2:33061,172.25.254.3:33061"
group_replication_bootstrap_group=off
loose-group_replication_ip_whitelist="127.0.0.1,172.25.254.0/24"	
loose-group_replication_enforce_update_everywhere_checks=ON	
loose-group_replication_single_primary_mode=OFF	

step3 open service, filter password, and initialize security:

systemctl start mysqld
cat /var/log/mysqld.log | grep password
mysql_secure_installation 

step4 configuration group replication:

mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'Redhat.123';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

mysql> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)

mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='Redhat.123' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.11 sec)

mysql> set global group_replication_allow_local_disjoint_gtids_join=on;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected, 1 warning (4.69 sec)

step5 view the status on server1:

mysql> SELECT * FROM performance_schema.replication_group_members;

server3

Experiments before step1 reduction:
Note: if you haven't done other mysql experiments before, you don't need to do this step

systemctl stop mysqld
cd /var/lib/mysql
rm -fr *

step2 edit mysql configuration file:

vim /etc/my.cnf
//Two changes:
server_id=3	#Modify id
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
plugin_load_add='group_replication.so'
transaction_write_set_extraction=XXHASH64	
group_replication_group_name="624dda22-8761-43a3-8abd-b06716fb1694"	
group_replication_start_on_boot=off	
group_replication_local_address= "172.25.254.3:33061"	#Modify native ip
group_replication_group_seeds= "172.25.254.1:33061,172.25.254.2:33061,172.25.254.3:33061"
group_replication_bootstrap_group=off
loose-group_replication_ip_whitelist="127.0.0.1,172.25.254.0/24"	
loose-group_replication_enforce_update_everywhere_checks=ON	
loose-group_replication_single_primary_mode=OFF	

step3 open service, filter password, and initialize security:

systemctl start mysqld
cat /var/log/mysqld.log | grep password
mysql_secure_installation 

step4 configuration group replication:

mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'Redhat.123';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

mysql> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)

mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='Redhat.123' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.11 sec)

mysql> set global group_replication_allow_local_disjoint_gtids_join=on;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected, 1 warning (4.69 sec)

step5 view the status on server1:

mysql> SELECT * FROM performance_schema.replication_group_members;

Test:

step1 creates a new table on server2:

mysql> create database test;
Query OK, 1 row affected (0.01 sec)

mysql> use test;
Database changed
mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL);
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO t1 VALUES (1, 'Luis');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM t1;

Step 2 can see the inserted information in both server1 and server3:

mysql> use test;

Database changed
mysql> SELECT * FROM t1;

step3 inserts information in server1:

mysql> INSERT INTO t1 VALUES (2, 'nigar');
Query OK, 1 row affected (0.02 sec)

mysql> SELECT * FROM t1;

See the latest information in server2 and server3

step4 inserts information in server3:

mysql> INSERT INTO t1 VALUES (3, 'xadi');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM t1;

See the latest information in server1 and server2