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