MySQL database Group Replication builds the pitfall of stepping on the IP address
- 黄舟Original
- 2017-02-07 11:42:353035browse
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
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;
Create a copy channel:
mysql> change master to master_user='group_repl',master_password='group_repl' for channel 'group_replication_recovery';
Load Plug-in:
mysql> install plugin group_replication soname 'group_replication.so';
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
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"；
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
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
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='<NULL>', master_port= 0, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='localhost', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. ... 2017-01-02T17:35:02.133661Z 34 [ERROR] Slave I/O for channel 'group_replication_recovery': error connecting to master 'group_repl@localhost:3306' - retry-time: 60 retries: 1, Error_code: 2003 ...
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
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
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)!