- The original content of GreatSQL community cannot be used without authorization. Please contact Xiaobian and indicate the source for reprint.
1. Problems encountered
Tester Xiao Ling plans to deploy the MGR cluster in the docker environment for some tests. She has three containers with IP addresses:
172.33.0.2 172.33.0.3 172.33.0.4
Install a MySQL instance in each container, and the group of each instance_ replication_ local_ Address and group_replication_group_seeds two configuration items are:
group_replication_local_address= "172.33.0.2:33061" group_replication_group_seeds= "172.33.0.2:33061,172.33.0.3:33061,172.33.0.4:33061" group_replication_local_address= "172.33.0.3:33061" group_replication_group_seeds= "172.33.0.2:33061,172.33.0.3:33061,172.33.0.4:33061" group_replication_local_address= "172.33.0.4:33061" group_replication_group_seeds= "172.33.0.2:33061,172.33.0.3:33061,172.33.0.4:33061"
After some basic preparation, Xiao Ling executed start group on 172.33.0.2_ Replication. As a result, an unexpected error message was encountered:
mysql> START GROUP_REPLICATION; ERROR 3092 (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log.
2. Troubleshooting
Observe the error log:
2021-07-13T03:11:42.645537Z 0 [Warning] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Connection attempt from IP address ::ffff:172.33.0.2 refused. Address is not in the IP allowlist.' 2021-07-13T03:11:42.645622Z 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Error connecting to the local group communication engine instance.'
According to the information in the error log, we can probably know that the direct cause of the error is that 172.33.0.2 is not in the white list.
This is a little strange, because by default, the relevant configuration options for MySQL are as follows:
mysql> show variables like "%group_replication_ip%"; +--------------------------------+-----------+ | Variable_name | Value | +--------------------------------+-----------+ | group_replication_ip_allowlist | AUTOMATIC | | group_replication_ip_whitelist | AUTOMATIC | +--------------------------------+-----------+ 2 rows in set (0.00 sec)
Parameter group_replication_ip_allowlist and group_replication_ip_whitelist is the same parameter, but due to some political correctness problems in the United States, words such as white and black should be gradually removed from MySQL code. However, in this paper, the author still calls it "white list" according to Chinese habits.
This parameter is used to control which hosts can be added to the current MGR group. Although you can set it in advance, it is only after the start group is executed_ Replication and the Group Communication System (GCS) becomes available. By default, its value is AUTOMATIC, which can automatically add localhost and the IP address of the current host in each private subnet to the white list.
By default, this system variable is set to AUTOMATIC, which permits connections from private subnetworks active on the host. The group communication engine for Group Replication (XCom) automatically scans active interfaces on the host, and identifies those with addresses on private subnetworks. These addresses and the localhost IP address for IPv4 and (from MySQL 8.0.14) IPv6 are used to create the Group Replication allowlist.
According to this description, the 172.33.0.2 IP should be in the white list, and the error message encountered above should not appear. However, in the official documents, the above paragraph is followed by a connection to another chapter, specifying some limitations.
According to the description in Group Replication IP Address Permissions, the IP address range that MGR can automatically identify and add to the whitelist is as follows.
IPv4 (as defined in RFC 1918) 10/8 prefix (10.0.0.0 - 10.255.255.255) - Class A 172.16/12 prefix (172.16.0.0 - 172.31.255.255) - Class B 192.168/16 prefix (192.168.0.0 - 192.168.255.255) - Class C IPv6 (as defined in RFC 4193 and RFC 5156) fc00:/7 prefix - unique-local addresses fe80::/10 prefix - link-local unicast addresses 127.0.0.1 - localhost for IPv4 ::1 - localhost for IPv6
It can be seen that 172.33.0.2 just goes beyond the scope of Class B. the error message does comply with the description of the document, but it is not very friendly to the user. If it is encountered for the first time, it will be a little confused.
3. Problem solving
Each node performs:
set global group_replication_ip_allowlist="172.33.0.2,172.33.0.3,172.33.0.4";
Select a node to execute:
SET GLOBAL group_replication_bootstrap_group=OFF; START GROUP_REPLICATION;
Other nodes execute:
START GROUP_REPLICATION;
The results are as follows:
mysql> START GROUP_REPLICATION; Query OK, 0 rows affected (2.70 sec) mysql> mysql> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | group_replication_applier | 2a3d09a5-e386-11eb-8323-0242ac210003 | 172.33.0.3 | 3306 | ONLINE | PRIMARY | 8.0.25 | | group_replication_applier | 58a2e9d0-e386-11eb-aff3-0242ac210002 | 172.33.0.2 | 3306 | ONLINE | PRIMARY | 8.0.25 | | group_replication_applier | 5dbb146a-e386-11eb-8a06-0242ac210004 | 172.33.0.4 | 3306 | ONLINE | PRIMARY | 8.0.25 | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ 3 rows in set (0.00 sec)
4. Extension of the problem
group_ replication_ ip_ The allowlist parameter can be set dynamically and will take effect immediately. Therefore, when adding a new node to an MGR replication group, you only need to add the address of the new node to this parameter.
Reference article:
[1] group_replication_ip_allowlist(https://dev.mysql.com/doc/refman/8.0/en/group-replication-options.html#sysvar_group_replication_ip_allowlist)
[2] Group Replication IP Address Permissions(https://dev.mysql.com/doc/refman/8.0/en/group-replication-ip-address-permissions.html)
Enjoy GreatSQL :)
Article recommendation:
GreatSQL MGR FAQ https://mp.weixin.qq.com/s/J6wkUpGXw3YkyEUJXiZ9xA
Wan Da #12, how can the MGR cluster automatically select the master without manual intervention https://mp.weixin.qq.com/s/07o1poO44zwQIvaJNKEoPA
"2021 data technology Carnival · ON LINE": evolution and practice of MySQL high availability architecture https://mp.weixin.qq.com/s/u7k99y6i7riq7ScYs7ySnA
Packet capture analysis of where an sql statement is slow https://mp.weixin.qq.com/s/AYibbzl860D90rOeyjB6IQ
Wan Da #15, what are the conditions that may cause the MGR service to fail to start https://mp.weixin.qq.com/s/inSGpd0Q_XIl2Mb-VsvNsA
Technology sharing | why MGR consistency mode does not recommend AFTER https://mp.weixin.qq.com/s/rNeq479RNsklY1BlfKOsYg
About GreatSQL
GreatSQL is a MySQL branch maintained by Wanli database. It focuses on improving the reliability and performance of MGR and supports the parallel query feature of InnoDB. It is a branch version of MySQL suitable for financial applications.
Gitee: https://gitee.com/GreatSQL/GreatSQL
GitHub: https://github.com/GreatSQL/GreatSQL
Bilibili: https://space.bilibili.com/1363850082/video
Wechat & QQ group: you can search and add GreatSQL community assistant wechat friends, send verification information "add group" to join GreatSQL/MGR communication wechat group
QQ group: 533341697 wechat assistant: wanlidbc
This article is composed of blog one article multi posting platform OpenWrite release!