Home > Database > Mysql Tutorial > body text

MySQL database Group Replication builds the pitfall of stepping on the IP address

黄舟
Release: 2017-02-07 11:42:35
Original
3025 people have browsed it

Introduction

The replication function of MySQL version 5.7.17 has ushered in a new feature, the shining star function Group Replicatioin. Naturally, it is indispensable to have some experience in building and testing, but during the building process, we encountered pitfalls related to the host name. The following will explain how to build it and the process of encountering this pitfall.

2

Expected

Two instances 3306 and 3307 will be built on the virtual machine 192.168.56.102, and one instance 3308 will be built on 192.168.56.105. According to the characteristics, you need to select a node as the node to start Group Replication. This article selects 3306 as the startup node.

3

Build

Instance 192.168.56.102:3306

my.cnf Key content:

[mysqld3306]
gtid-mode=on
enforce-gtid-consistency=on
 
master-info-repository=table
relay-log-info-repository=table
 
binlog-checksum=none
log-slave-updates=on
binlog-format=row
 
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="2dc52aec-cfea-11e6-95f3-080027264cfa"
loose-group_replication_start_on_boot=off # 开机启动OFF
loose-group_replication_local_address="192.168.56.102:33061"
loose-group_replication_group_seeds="192.168.56.102:33061,192.168.56.102:33071,192.168.56.105:33081"
loose-group_replication_bootstrap_group=off # 作为首个启动OFF
Copy after login

Note:

1. The ports listed in the configuration are required and not used.

2. The format of group_name is UUID, you can execute select uuid(); in MySQL to obtain one.

Create a copy account:

mysql> set sql_log_bin=0;
mysql> create user 'group_repl'@'%' identified by 'group_repl';
mysql> grant replication slave on *.* to 'group_repl'@'%';
mysql> flush privileges;
mysql> set sql_log_bin=1;
Copy after login

Create a copy channel:

mysql> change master to master_user='group_repl',master_password='group_repl' for channel 'group_replication_recovery';
Copy after login


Load Plug-in:

mysql> install plugin group_replication soname 'group_replication.so';
Copy after login

Start Group Replication as the first node:

mysql> set @@global.group_replication_bootstrap_group=1;
mysql> start group_replication;
mysql> set @@global.group_replication_bootstrap_group=0;
mysql> select * from performance_schema.replication_group_members\G
*************************** 1. row ***************************
CHANNEL_NAME: group_replication_applier
   MEMBER_ID: 4f987d01-cff0-11e6-9afa-080027264cfa
 MEMBER_HOST: localhost
 MEMBER_PORT: 3306
MEMBER_STATE: ONLINE
Copy after login

Instances 192.168.56.102:3307 and 192.168.56.105:3308

The operations of the remaining instances are large The parts are the same as 102:3306. Only the different parts are listed below:

1. In my.cnf, the parameters of 102:3307:

loose-group_replication_local_address="192.168.56.102:33071",105:3308的参数:loose-group_replication_local_address="192.168.56.105:33081";
Copy after login

2. When creating a copy account, After copying the channel and loading the plug-in, both 102:3307 and 105:3308 only need to execute: start group_replication;.

4

Check

To check whether to join Group Replication, just query the table performance_schema.replication_group_members. The following is the situation of 102:3307 joining the group:

mysql> select * from performance_schema.replication_group_members\G
*************************** 1. row ***************************
CHANNEL_NAME: group_replication_applier
   MEMBER_ID: 4f987d01-cff0-11e6-9afa-080027264cfa
 MEMBER_HOST: localhost
 MEMBER_PORT: 3306
MEMBER_STATE: ONLINE
*************************** 2. row ***************************
CHANNEL_NAME: group_replication_applier
   MEMBER_ID: d8f7405d-cff1-11e6-b449-080027264cfa
 MEMBER_HOST: localhost
 MEMBER_PORT: 3307
MEMBER_STATE: ONLINE
Copy after login

You can see that there are already two nodes forming the group, namely 102:3306 and 102:3307.

5

Problem

When I started to join 105:3308, something went wrong. It can be found that 3308 has been unable to join the group:

mysql> select * from performance_schema.replication_group_members;
*************************** 1. row ***************************
CHANNEL_NAME: group_replication_applier
   MEMBER_ID: 4f987d01-cff0-11e6-9afa-080027264cfa
 MEMBER_HOST: localhost
 MEMBER_PORT: 3306
MEMBER_STATE: ONLINE
*************************** 2. row ***************************
CHANNEL_NAME: group_replication_applier
   MEMBER_ID: 645aef9a-d000-11e6-a756-080027d54077
 MEMBER_HOST: localhost
 MEMBER_PORT: 3308
MEMBER_STATE: RECOVERING
*************************** 3. row ***************************
CHANNEL_NAME: group_replication_applier
   MEMBER_ID: d8f7405d-cff1-11e6-b449-080027264cfa
 MEMBER_HOST: localhost
 MEMBER_PORT: 3307
MEMBER_STATE: ONLINE
Copy after login

Through the error log:

...
2017-01-02T17:35:02.123501Z 32 [Note] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_recovery' executed'. 
Previous state master_host=&#39;<NULL>&#39;, master_port= 0, master_log_file=&#39;&#39;, master_log_pos= 4, master_bind=&#39;&#39;. 
New state master_host=&#39;localhost&#39;, master_port= 3306, master_log_file=&#39;&#39;, master_log_pos= 4, master_bind=&#39;&#39;.
...
2017-01-02T17:35:02.133661Z 34 [ERROR] Slave I/O for channel &#39;group_replication_recovery&#39;: error connecting to master &#39;group_repl@localhost:3306&#39; - retry-time: 60
  retries: 1, Error_code: 2003
...
Copy after login

It can be guessed that the problem is with MEMBER_HOST, so change the host name to the IP address. After testing, it was found to be feasible:

mysql> select * from performance_schema.replication_group_members\G
*************************** 1. row ***************************
CHANNEL_NAME: group_replication_applier
   MEMBER_ID: 4f987d01-cff0-11e6-9afa-080027264cfa
 MEMBER_HOST: 192.168.56.102
 MEMBER_PORT: 3306
MEMBER_STATE: ONLINE
*************************** 2. row ***************************
CHANNEL_NAME: group_replication_applier
   MEMBER_ID: 645aef9a-d000-11e6-a756-080027d54077
 MEMBER_HOST: 192.168.56.105
 MEMBER_PORT: 3308
MEMBER_STATE: ONLINE
*************************** 3. row ***************************
CHANNEL_NAME: group_replication_applier
   MEMBER_ID: d8f7405d-cff1-11e6-b449-080027264cfa
 MEMBER_HOST: 192.168.56.102
 MEMBER_PORT: 3307
MEMBER_STATE: ONLINE
Copy after login

But in actual circumstances it is impossible to set the host name to IP. So after many attempts, I found that this problem can be solved in the following ways:

1. Set different host names for different physical machines (virtual machines);

2. Modify /etc/ hosts enables ping between physical machines (virtual machines) through host names.

After completing the above work, finally check the group members:

mysql> select * from performance_schema.replication_group_members
\G*************************** 1. row ***************************
CHANNEL_NAME: group_replication_applier
   MEMBER_ID: 4f987d01-cff0-11e6-9afa-080027264cfa
 MEMBER_HOST: local-102
 MEMBER_PORT: 3306
MEMBER_STATE: ONLINE
*************************** 2. row ***************************
CHANNEL_NAME: group_replication_applier
   MEMBER_ID: 645aef9a-d000-11e6-a756-080027d54077
 MEMBER_HOST: local-105
 MEMBER_PORT: 3308
MEMBER_STATE: ONLINE
*************************** 3. row ***************************
CHANNEL_NAME: group_replication_applier
   MEMBER_ID: d8f7405d-cff1-11e6-b449-080027264cfa
 MEMBER_HOST: local-102
 MEMBER_PORT: 3307
MEMBER_STATE: ONLINE
Copy after login

The problem is solved. Another thing to note is that even instances under a physical machine (virtual machine) need to have a mapping relationship between host name and IP to form a group. The reason why groups can be formed under the same physical machine in the localhost situation above is because the system has a mapping relationship from 127.0.0.1 to localhost by default.

6

Summary

The above problem occurs because MySQL directly uses the hostname of the operating system instead of the IP address commonly used when configuring replication. This should It is a bug, and someone has submitted a bug request to the official website a few days earlier than me. It is recommended that when configuring Group Replication and before the bug is fixed, it is best to check whether the mapping relationship between the host name and the IP address is set.


The above is the content of the MySQL database Group Replication building that steps on the IP address. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!


source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template