MySQL group replication (multi master mode)

Official website: https://dev.mysql.com/doc/refman/5.7/en/group-replication.html

brief introduction

MySQL group replication is a MySQL Server plug-in that enables you to create elastic, high availability, fault-tolerant replication topologies. Based on GTID.
Group replication is a technology that can be used to implement fault-tolerant systems. A replication group is a group of servers. Each server has its own complete data copy (no shared replication scheme) and interacts with each other through messaging.

Multi master mode

Multi master mode features: Decentralization

Fault detection

Group replication includes a failure detection mechanism that can find and report which servers are silent and therefore considered to be down. A voting mechanism. When the server finds that a server in the group is disconnected, it will be marked as offline objectively. When the number of votes is enough, that is, multiple servers, it is considered that the failed server is offline, and then the failed server will be offline subjectively and removed from the group.

fault-tolerant

Group replication at present, the maximum number of MySQL servers that can become members of a replication group is 9.
MySQL group replication is based on the implementation of Paxos distributed algorithm to provide distributed coordination between servers. Therefore, it requires most servers to be active to reach a quorum to make a decision. This directly affects the number of faults that the system can tolerate without damaging itself and its overall function.

Server instance configuration

server_id=N
Unique server identifier.
gtid_mode=ON
Global transaction identifier open.
enforce_gtid_consistency=ON
The server achieves GTID consistency by allowing only statements that can use GTID security records to be executed.
master_info_repository=TABLE
Copy the information repository. The replication application needs to write the source metadata to MySQL slave_ master_ info.
relay_log_info_repository=TABLE
Copy the information repository. The replication application needs to write the replica metadata to MySQL slave_ relay_ log_ Info table.
binlog_checksum=NONE
Binary log checksum off.
log_slave_updates=ON
A copy update has been recorded.
log_bin=binlog
Binary log is active. The binary log file is named binlog
binlog_format=ROW
Binary log line format.

Group replication settings

Group replication system variable description official website: https://dev.mysql.com/doc/refman/5.7/en/group-replication-options.html

plugin_load_add='group_replication.so'
Plugin load add adds the group replication plug-in to the list of plug-ins loaded by the server at startup.
transaction_write_set_extraction=XXHASH64
Defines the algorithm used to generate a hash that identifies the writes associated with the transaction. If group replication is used, the hash value is used for distributed conflict detection and processing. On the 64 bit system running group replication, it is recommended to set it to XXHASH64 to avoid unnecessary hash conflict, which will lead to authentication failure and user transaction rollback.
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
Tell the plug-in to name the group it joins or creates "aaaaaaaa AAAA AAAA aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa.
group_replication_start_on_boot=off
off indicates that the plug-in does not automatically start the operation when the server starts. This is important when setting up group replication because it ensures that you can configure the server before manually starting the plug-in. Once members are configured, they can be set to on to automatically start group replication when the server starts.
group_replication_local_address= "172.25.9.1:33061"
Set the network address and port that members use to communicate internally with other members of the group. All group members must resolve the network address configured by.
group_replication_group_seeds= "172.25.9.1:33061,172.25.9.2:33061,172.25.9.3:33061"
Set the host name and port of the group member. The new member is used to establish its connection with the group.
group_replication_bootstrap_group=off
Indicates whether the plug-in boots the group. In this case, even if s1 is the first member of the group, we will set this variable to off in the options file.
group_replication_ip_whitelist="172.25.9.0/24,127.0.0.1/8"
Specifies the list of hosts allowed to connect to this group.
group_replication_single_primary_mode=OFF
Instructs the group to automatically select a server to handle the read / write workload. To turn off single master mode, use multi master mode.
group_replication_enforce_update_everywhere_checks=ON
Some statements need to be forcibly checked to prevent conflicts
group_replication_allow_local_disjoint_gtids_join
Allow a server to join a group, even if the server has local transactions that do not exist in the group. It has been deprecated in version 5.7.21 and is planned to be deleted in a later version.

experiment

host	IP
server1	172.25.9.1
server2	172.25.9.2
server3	172.25.9.3

[note]: if there is no experimental environment in the database, it is recommended to re initialize to ensure data consistency and avoid errors as much as possible.
Initialize data directory:

# Stop the running MySQL first
/etc/init.d/mysqld stop
# Delete data in data directory
rm -fr /usr/local/mysql/data/*
# To initialize the data directory, it is recommended to use the initial configuration file
mysqld --initialize --user=mysql

server1 configuration

# Edit profile
vim /etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
symbolic-links=0


disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
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
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
group_replication_start_on_boot=off
group_replication_local_address= "172.25.9.1:33061"
group_replication_group_seeds= "172.25.9.1:33061,172.25.9.2:33061,172.25.9.3:33061"
group_replication_bootstrap_group=off
group_replication_ip_whitelist="172.25.9.0/24,127.0.0.1/8"
group_replication_single_primary_mode=OFF
group_replication_enforce_update_everywhere_checks=ON

# After the configuration file is modified successfully, start MySQL
/etc/init.d/mysqld start

# Previously, only the data directory initialization was done, but the security initialization was not done. Here, the super user password needs to be changed
mysql -p
alter user root@localhost identified by 'westos';

# Disable binary logging and set this variable OFF to prevent gtids from being assigned to transactions in binary logs.
SET SQL_LOG_BIN=0;
# Create and authorize users for replication
CREATE USER rpl_user@'%' IDENTIFIED BY 'westos';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
FLUSH PRIVILEGES;
# Enable binary logging
SET SQL_LOG_BIN=1;

# Add instance to group
CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='westos' FOR CHANNEL 'group_replication_recovery';
# Group start_ replication_ bootstrap_ Group plug-in (there needs to be a group plug-in to boot the host in the group)
SET GLOBAL group_replication_bootstrap_group=ON;
# Start group
START GROUP_REPLICATION;
# Close the plug-in to avoid the problem of server1. After repairing and rejoining, continue to act as the boot machine, resulting in brain crack
SET GLOBAL group_replication_bootstrap_group=OFF;

# query
SELECT * FROM performance_schema.replication_group_members;

server2 configuration

# First, refer to the above to initialize the data directory

# Modify profile
vim /etc/my.cnf

[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
symbolic-links=0

disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
server_id=2
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="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
group_replication_start_on_boot=off
group_replication_local_address= "172.25.9.2:33061"
group_replication_group_seeds= "172.25.9.1:33061,172.25.9.2:33061,172.25.9.3:33061"
group_replication_bootstrap_group=off
group_replication_ip_whitelist="172.25.9.0/24,127.0.0.1/8"
group_replication_single_primary_mode=OFF
group_replication_enforce_update_everywhere_checks=ON
group_replication_allow_local_disjoint_gtids_join=ON

/etc/init.d/mysql start
mysql -p
alter user root@localhost identified by 'westos';

SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%' IDENTIFIED BY 'westos';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='westos' FOR CHANNEL 'group_replication_recovery';
START GROUP_REPLICATION;

# Query the host status in the group. If the configuration is successful, it should display that server1 and server2 are online
SELECT * FROM performance_schema.replication_group_members;

server3 configuration

# It is no different from configuring server 2. Change the server in the configuration file_ ID and group_replication_local_address
vim /etc/my.cnf
......
server_id=2
......
group_replication_local_address= "172.25.9.3:33061"
......

test

CREATE DATABASE test;
USE test;
CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL);
INSERT INTO t1 VALUES (1, 'Luis');
# Insert data in only one, and the other two can see the data. Indicates that the configuration was successful. This completes the group replication configuration.
select * from test.t1;