Configure MySQL Group replication

The previous article briefly describes the workflow, prerequisites, and constraints of MySQL group replication, and compares the features of the traditional asynchronous replication set semi-synchronous replication. This article describes the specific configuration process for MySQL group replication.

I. Introduction to the Environment

OS version: CentOS Linux 7.2 64bit

MySQL version: mysql-5.7.19-linux-glibc2.12-x86_64

3 server Hosts file (where VM2 is the first host to replicate as a MySQL group, usually the first boot is master)

# cat/etc/hosts

192.168.115.5 VM1

192.168.115.6 vm2

192.168.115.7 vm3

Second, configuration vm2 my.cnf file

Server-id=1155lower_case_table_names = 1skip-name-resolveinnodb_file_per_table=1gtid_mode = ONenforce_gtid_ Consistency = Onslave_parallel_workers=4master_verify_checksum = 1slave_sql_verify_checksum = 1log-slave-updates= Truesql_mode=no_engine_substitution,strict_trans_tablesslave-parallel-type=logical_ Clockslave-preserve-commit-order=onmaster_info_repository = Tablerelay_log_info_repository = TABLEbinlog_checksum = nonetransaction_write_set_extraction = Xxhash64loose-group_replication_group_name = ' Aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa ' Loose-group_replication_start_on_boot = Offloose-group_replication_local_ Address = ' vm2:3306 ' loose-group_replication_group_seeds = ' vm1:3306,vm2:3306,vm3:3306 ' Loose-group_replication_ Bootstrap_group = Off

The specific meanings of each configuration file can be found in the MySQL manual (MySQL installation deployment is omitted in this article, but also refer to the MySQL manual):

Https://dev.mysql.com/doc/refman/5.7/en/group-replication-configuring-instances.html

Third, create a copy account and permissions configuration on VM2

mysql> set sql_log_bin=0; query ok, 0 rows affected  (0.01 sec) mysql> create user [email  protected] '% '  identified by  ' 123456 '; query ok, 0 rows affected  (0.00 sec) mysql> grant replication  Slave on *.* to [email protected] '% '; query ok, 0 rows affected  (0.00 sec) mysql> flush privileges; query ok, 0 rows affected  (0.00 sec) mysql>  set sql_log_bin=1; query ok, 0 rows affected  (0.00 sec) mysql>  change master  to master_user= ' Repl ', master_password= ' 123456 '  for channel  ' Group_replication_ Recovery '; query ok, 0 rows affected, 2 warnings  (0.08 sec) Mysql> install  PLUGIN group_replication SONAME  ' group_replication.so '; query ok, 0 rows affected  (0.53 SEC) 

Iv. Start the group replication process on VM2

mysql> set global group_replication_bootstrap_group=on; query ok, 0 rows affected  (0.01 sec) mysql> start group_replication; query ok, 0 rows affected  (2.66 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;+---------------------------+------------------------------------ --+-------------+-------------+--------------+| channel_name               | MEMBER_ID                              | member_host | member_port | member_state |+----------------------- ----+--------------------------------------+-------------+-------------+--------------+| group_replication_applier |  33025907-6d2d-11e7-8ce6-000c29e07281 | vm2         |         3306 | ONLINE        |+---------------------------+--------------------------------------+-------------+-------------+-------- ------+1 row in set  (0.00 SEC)

V. Configure VM3 in the same way as VM2, note that server-id in the my.cnf file needs to be modified

mysql> set sql_log_bin=0; query ok, 0 rows affected  (0.00 sec) mysql> create user [email  protected] '% '  identified by  ' 123456 '; query ok, 0 rows affected  (0.01 sec) mysql> grant replication  Slave on *.* to [email protected] '% '; 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= ' Repl ', master_password= ' 123456 '  for channel  ' group_replication_recovery '; query ok, 0 rows affected, 2 warnings  (0.05 sec) mysql>   install plugin group_replication soname  ' group_replication.so ';query ok, 0 rows affected  (0.38 sec) mysql>  set global  Group_replication_allow_local_disjoint_gtids_join=on; query ok, 0 rows affected  (0.00 sec) mysql> start group_replication; query ok, 0 rows affected  (7.89 sec) mysql> select * from  performance_schema.replication_group_members;+---------------------------+------------------------------------ --+-------------+-------------+--------------+| channel_name               | MEMBER_ID                              | member_host | member_port | member_state |+----------------------- ----+--------------------------------------+-------------+-------------+--------------+| group_replication_applier | 33025907-6d2d-11e7-8ce6-000c29e07281 | vm2          |        3306  | online       | |  group_replication_applier | 35678d62-6d2d-11e7-80a7-000c2909332c | vm3          |        3306 |  online       | |  group_replication_applier | 8b643791-6d30-11e7-986a-000c29d53b31 | vm1          |        3306 |  online       |+---------------------------+-------------------------------- ------+-------------+-------------+--------------+3 rows in set  (0.00 sec)

It is important to note that the VM3 and vm1 that are added to the group later are required to configure the Group_replication_allow_local_disjoint_gtids_join parameter to ON, According to the document description, the meaning of this parameter is to allow join group replication if the transaction on the current database does not exist in group replication.

650) this.width=650; "Src=" https://s1.51cto.com/wyfs02/M01/A7/05/wKioL1nfNV6x5TqkAACJp7E110Y791.png-wh_500x0-wm_ 3-wmp_4-s_2286031229.png "title=" 1.png "alt=" Wkiol1nfnv6x5tqkaacjp7e110y791.png-wh_50 "/>

This article from "Chop Month" blog, declined reprint!

Configure MySQL Group replication