Bug #100221 MySQL Group Replication between hosts running containers not working
Submitted: 14 Jul 2020 21:13 Modified: 13 Aug 2020 1:32
Reporter: Steven Glogger Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Group Replication Severity:S2 (Serious)
Version:8.0 OS:Ubuntu
Assigned to: MySQL Verification Team CPU Architecture:x86
Tags: docker, group replication, MySQL, replication

[14 Jul 2020 21:13] Steven Glogger
Description:
Hi there, 

I'm trying to get MySQL Group Replication running which are trying to replicate between two hosts. Each host having a docker-container running. 

(host1)----{network}----(host2) 

host1/host2 have a LAN address of 10.x.x.x/24 and a WAN address of 198.x.x.x 
they have full connectivity. 

When I implement group replication running on both hosts with docker it will not start replicating.

How to repeat:
both sides have docker configured via docker-compose like this:

# cat docker-compose.yml 
version: "3.3" 

services: 

mysqldb: 
image: mysql/mysql-server:8.0 
container_name: mysqldb 
command: ["mysqld", 
"--server-id=99", 
"--log-bin=mysql-bin.log", 
"--enforce-gtid-consistency=ON", 
"--log-slave-updates=ON", 
"--gtid-mode=ON", 
"--transaction-write-set-extraction=XXHASH64", 
"--binlog-checksum=NONE", 
"--master-info-repository=TABLE", 
"--relay-log-info-repository=TABLE", 
"--plugin-load=group_replication.so", 
"--relay-log-recovery=ON", 
"--loose-group-replication-start-on-boot=OFF", 
"--loose-group_replication_bootstrap_group=ON", 
"--loose-group-replication-group-name=aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee", 
"--loose-group-replication-local-address=198.18.186.1:33061", 
"--loose-group-replication-group-seeds=198.19.84.1:33061", 
"--loose-group-replication-single-primary-mode=OFF", 
"--loose-group-replication-enforce-update-everywhere-checks=ON"] 
env_file: 
- ./secret-mysql.env 
healthcheck: 
test: "mysqladmin ping -u root -p$${MYSQL_ROOT_PASSWORD}" 
interval: 2s 
retries: 20 
ports: 
- 3306:3306 
- 33061:33061 
network_mode: "host" 
restart: unless-stopped 
volumes: 
- /data/var/mysqldb/:/var/lib/mysql 

of course, following parameters are adapted to the other nodes:
- server-id
- replication-local-address
- replication-group-seeds

on host1 I start replication with:
docker exec -t mysqldb mysql -uroot -pmypassword \
  -e "SET @@GLOBAL.group_replication_bootstrap_group=1;" \
  -e "create user 'repl'@'%';" \
  -e "GRANT REPLICATION SLAVE ON *.* TO repl@'%';" \
  -e "flush privileges;" \
  -e "change master to master_user='root' for channel 'group_replication_recovery';" \
  -e "SET GLOBAL group_replication_ip_whitelist='10.0.0.0/8,198.18.0.0/15';" \
  -e "START GROUP_REPLICATION;" \
  -e "SET @@GLOBAL.group_replication_bootstrap_group=0;" \
  -e "SELECT * FROM performance_schema.replication_group_members;"

on host2 I start replication with:
docker exec -t mysqldb  mysql -uroot -pmypassword \
  -e "change master to master_user='repl' for channel 'group_replication_recovery';" \
  -e "SET GLOBAL group_replication_ip_whitelist='10.0.0.0/8,198.18.0.0/15';" \
  -e "START GROUP_REPLICATION;"

checking both hosts/containers with: "SELECT * FROM performance_schema.replication_group_members;":
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | ce79c45b-c605-11ea-a04d-000c29d4b84a | host2       |        3306 | ONLINE       | PRIMARY     | 8.0.21         |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+

and

+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 18dcf18a-c60d-11ea-87c9-000c294b79b0 | host1       |        3306 | ONLINE       | PRIMARY     | 8.0.21         |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+

when I sniff traffic between the hosts I dont see any connection to port 33061 from either host.

both logs are identical except:
2020-07-14T19:11:55.445122Z 0 [System] [MY-011503] [Repl] Plugin group_replication reported: 'Group membership changed to host2:3306 on view 15947539144442389:1.'

and:

2020-07-14T20:24:47.588152Z 0 [System] [MY-011503] [Repl] Plugin group_replication reported: 'Group membership changed to host1:3306 on view 15947582865872850:1.'

I've tried also:
- instead of 198 address using the 10.x ip addresses
- putting "privileged: true to docker-compose.yml
- working without network_mode:host and forwarding ports (3306>3306, 33061>33061, ...)
- mysql... -e "SET GLOBAL group_replication_ip_whitelist='10.0.0.0/8,198.18.0.0/15';" \

versions:
- Docker version 19.03.12, build 48a66213fe
- docker-compose version 1.25.0, build unknown
- docker image > mysql/mysql-server   8.0                 8a3a24ad33be        40 hours ago        366MB
- OS: Ubuntu 20.04 LTS
[13 Aug 2020 1:32] MySQL Verification Team
Hi Steven,

This is not a bug. You are not configuring it properly.

Since bootstrap_group=ON and group-seeds is equal to the local address, which is
the same for both containers, each container is able to form a independent group therefore it is expected that they will not try to communicate with each other.

In order to solve this:
* Do not set "--loose-group_replication_bootstrap_group=ON" . (It is off by default)
* Set different values of loose-group-replication-local-address for each container (e.g different ports), and set
 group-replication-group-seeds to the address of the first container ( which bootstraps the group)  or to both containers.

You can look at the project of one of our GR devs here for more details:
https://github.com/wagnerjfr/mysql-group-replication-docker-compose

kind regards
Bogdan