I. Environmental Configuration
Node information
servername ip port group port mgr_one 10.168.1.227 3306 6606 mgr_two 10.168.1.226 3306 6606 mgr_three 10.168.1.228 3306 6606
group Synchronizes Users and Passwords
User: rpl_lvfk
Password: rpl_lvfk
System: Ubuntu 16.04.3 LTS
Memory: 1G
Installation of mysql-5.7.20
How to install mysql source code, please refer to: mysql-5.7.20 Source Installation
Configuration files
mgr_one:
mgr_two:#base config server_id = 1 port = 3306 #Biglog config, Group Replication is synchronized according to GTID, so GTID needs to be turned on. log_bin = /usr/local/mysql/binarylog/mysql_bin.index binlog-format = ROW gtid-mode = ON enforce-gtid-consistency = ON log-slave-updates = ON master-info-repository = TABLE relay-log-info-repository = TABLE binlog-checksum = NONE expire_logs_days = 7 #group replication config transaction-write-set-extraction = XXHASH64 loose-group_replication_start_on_boot = OFF loose-group_replication_bootstrap_group = OFF loose-group_replication_group_name = 5e1f9814-c91d-11e7-8f9b-000c29ec1057 loose-group_replication_local_address = '10.168.1.227:6606' loose-group_replication_group_seeds = '10.168.1.226:6606,10.168.1.227:6606,10.169.1.228:6606' loose-group_replication_single_primary_mode=true loose-group_replication_enforce_update_everywhere_checks=false
mgr_three:#base config server_id = 2 port = 3306 #Biglog config, Group Replication is synchronized according to GTID, so GTID needs to be turned on. log_bin = /usr/local/mysql/binarylog/mysql_bin.index binlog-format = ROW gtid-mode = ON enforce-gtid-consistency = ON log-slave-updates = ON master-info-repository = TABLE relay-log-info-repository = TABLE binlog-checksum = NONE expire_logs_days = 7 #group replication config transaction-write-set-extraction = XXHASH64 loose-group_replication_start_on_boot = OFF loose-group_replication_bootstrap_group = OFF loose-group_replication_group_name = 5e1f9814-c91d-11e7-8f9b-000c29ec1057 loose-group_replication_local_address = '10.168.1.226:6606' loose-group_replication_group_seeds = '10.168.1.226:6606,10.168.1.227:6606,10.169.1.228:6606' loose-group_replication_single_primary_mode=true loose-group_replication_enforce_update_everywhere_checks=false
#base config server_id = 3 port = 3306 #Biglog config, Group Replication is synchronized according to GTID, so GTID needs to be turned on. log_bin = /usr/local/mysql/binarylog/mysql_bin.index binlog-format = ROW gtid-mode = ON enforce-gtid-consistency = ON log-slave-updates = ON master-info-repository = TABLE relay-log-info-repository = TABLE binlog-checksum = NONE expire_logs_days = 7 #group replication config transaction-write-set-extraction = XXHASH64 loose-group_replication_start_on_boot = OFF loose-group_replication_bootstrap_group = OFF loose-group_replication_group_name = 5e1f9814-c91d-11e7-8f9b-000c29ec1057 loose-group_replication_local_address = '10.168.1.228:6606' loose-group_replication_group_seeds = '10.168.1.226:6606,10.168.1.227:6606,10.169.1.228:6606' loose-group_replication_single_primary_mode=true loose-group_replication_enforce_update_everywhere_checks=false
IV. Installing Group Replication Plug-ins
The following instructions are executed on [mgr_two], [mgr_two], [mgr_three]:
Note: Or configure it directly in the configuration filemysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
plugin-load = group_replication.so
5. Establishing Clusters
5.1. Configure the boot node mgr_one
A. Make the following configuration in [mgr_one]
SET SQL_LOG_BIN=0; CREATE USER rpl_lvfk@'%'; GRANT REPLICATION SLAVE ON *.* TO rpl_lvfk@'%' IDENTIFIED BY 'rpl_lvfk'; FLUSH PRIVILEGES; SET SQL_LOG_BIN=1; CHANGE MASTER TO MASTER_USER='rpl_lvfk', MASTER_PASSWORD='rpl_lvfk' FOR CHANNEL 'group_replication_recovery';
Note: SET SQL_LOG_BIN=0 is used to ensure that user creation is not recorded in the binlog, which can avoid transaction conflict errors when other services join.
[ERROR] Plugin group_replication reported: 'The member contains transactions not present in the group. The member will now exit the group.'B. Start Group REPLICATION
SET GLOBAL group_replication_bootstrap_group=ON; START GROUP_REPLICATION; SET GLOBAL group_replication_bootstrap_group=OFF;
Note: The group_replication_bootstrap_group parameter is set to ON to indicate that all the servers joining the cluster in the future are benchmarked by this server. No settings are required for future additions.
When Group Replication starts successfully, you can view the node information in the following way.
mysql> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ | group_replication_applier | e13d18c2-bf6e-11e7-9a63-000c2951eef3 | mgr_one | 3306 | ONLINE | +---------------------------+--------------------------------------+-------------+-------------+--------------+
Note: The MEMBER_ID here is datadir=/usr/local/mysql/data in the initialization database, so the MEMBER_ID is in/usr/local/mysql/data/auto.cnf.
5.2, mgr_two and mgr_three join Group
Execute the following instructions on [mgr_two] and [mgr_three], respectively
A,Users and passwords for creating group communications: SET SQL_LOG_BIN=0; CREATE USER rpl_lvfk@'%'; GRANT REPLICATION SLAVE ON *.* TO rpl_lvfk@'%' IDENTIFIED BY 'rpl_lvfk'; FLUSH PRIVILEGES; SET SQL_LOG_BIN=1; CHANGE MASTER TO MASTER_USER='rpl_lvfk', MASTER_PASSWORD='rpl_lvfk' FOR CHANNEL 'group_replication_recovery';
B. Start Group Replication
mysql> START GROUP_REPLICATION; Query OK, 0 rows affected (7.40 sec)
C. View Group Information after Startup Completion
mysql> select * from performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ | group_replication_applier | 005e5b4e-c535-11e7-adae-000c2951eef3 | mgr_two | 3306 | ONLINE | | group_replication_applier | 2a7b77a2-c535-11e7-adae-000c2951eef3 | mgr_three | 3306 | ONLINE | | group_replication_applier | e13d18c2-bf6e-11e7-9a63-000c2951eef3 | mgr_one | 3306 | ONLINE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ 3 rows in set (0.00 sec)
Note:
Question 1: Instances that join a group are always in RECOVERING state
Analysis:
By analyzing the error log, it is found that the configured MMBER_HOST is in the form of domain name, but the DNS parsing is not added to the hosts, which makes it impossible to access other servers in the join group.
Solution:
Modify / etc/hosts file and add DNS parsing for three [mgr_one], [mgr_two], [mgr_three]
10.168.1.226 mgr_two 10.168.1.227 mgr_one 10.168.1.228 mgr_three
After that, after stopping the group service, open the group service again
mysql> STOP GROUP_REPLICATION; Query OK, 0 rows affected (1.00 sec) mysql> START GROUP_REPLICATION; Query OK, 0 rows affected (3.10 sec)
6. Judging the Primary Node in Group Replication
mysql> SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'group_replication_primary_member'; +--------------------------------------+ | VARIABLE_VALUE | +--------------------------------------+ | e13d18c2-bf6e-11e7-9a63-000c2951eef3 | +--------------------------------------+ 1 row in set (0.02 sec)
Seven, test
7.1. In single master mode, add data to non-master servers to report direct errors
mysql> insert into user values(null, 'yy', 28); ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement
7.2. By point 6, we can figure out which server the primary node is. We execute the same statement on the primary node, where our primary node is [mgr_one]
mysql> insert into user values(null, 'yy', 28); Query OK, 1 row affected (0.01 sec) mysql> select * from user; +----+------+-----+ | id | name | age | +----+------+-----+ | 8 | yy | 28 | +----+------+-----+ 1 row in set (0.00 sec)
7.3. Execute view statements in [mgr_two], [mgr_three], respectively
[mgr_two] mysql> select * from user; +----+------+-----+ | id | name | age | +----+------+-----+ | 8 | yy | 28 | +----+------+-----+ 1 row in set (0.00 sec) [mgr_three] mysql> select * from user; +----+------+-----+ | id | name | age | +----+------+-----+ | 8 | yy | 28 | +----+------+-----+ 1 row in set (0.00 sec)
As can be seen above, the group replication of single master mode has been successfully constructed. Data is operated on the primary node, other nodes synchronize to new data, and write operations can only be performed on the primary node.
So far, mysql group replication single-primary mode has been built and tested.
For the supplement of 7.1:
MySQL Group Replicaiton does not allow all nodes to write by default. Initially, only one writable node (master) is reserved in the cluster, and the read_only/super_read_only parameters of the remaining nodes are set to ON. When the master fails down, the arbitration component in the group selects a node to set read_only/super_read_only to OFF, so that the node acts as the master node.
Of course, command settings can also be used to change writing to multiple nodes or to configure it directly to multiple master mode.
Execute in [mgr_two]
mysql> show variables like '%read_only%'; +-----------------------+-------+ | 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.01 sec) mysql> set global read_only=OFF; Query OK, 0 rows affected (0.00 sec) mysql> set global super_read_only=OFF; Query OK, 0 rows affected (0.00 sec) mysql> insert into user values(null, 'xx', 27); Query OK, 1 row affected (0.02 sec) mysql> select * from user; +----+------+-----+ | id | name | age | +----+------+-----+ | 8 | yy | 28 | | 9 | xx | 27 | +----+------+-----+ 2 rows in set (0.00 sec)
[mgr_one], [mgr_three] queries
mysql> select * from user; +----+------+-----+ | id | name | age | +----+------+-----+ | 8 | yy | 28 | | 9 | xx | 27 | +----+------+-----+ 2 rows in set (0.00 sec)