MySQL group replication (multi master mode)
Official website: https://dev.mysql.com/doc/refman/5.7/en/group-replication.html
- 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
- 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.
- 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
Unique server identifier.
Global transaction identifier open.
The server achieves GTID consistency by allowing only statements that can use GTID security records to be executed.
Copy the information repository. The replication application needs to write the source metadata to MySQL slave_ master_ info.
Copy the information repository. The replication application needs to write the replica metadata to MySQL slave_ relay_ log_ Info table.
Binary log checksum off.
A copy update has been recorded.
Binary log is active. The binary log file is named binlog
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 adds the group replication plug-in to the list of plug-ins loaded by the server at startup.
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.
Tell the plug-in to name the group it joins or creates "aaaaaaaa AAAA AAAA aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa.
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.
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.
Specifies the list of hosts allowed to connect to this group.
Instructs the group to automatically select a server to handle the read / write workload. To turn off single master mode, use multi master mode.
Some statements need to be forcibly checked to prevent conflicts
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.
[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
# 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;
# 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;
# 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" ......
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;