MySQL, Group Replication not surviving after a single restart

Question

Can anyone see where I am going wrong, as this is driving me round the bend.

I have set up a clean install of MySQL 8.0.17 on my Windows 10 laptop. I have gone through the whole initialize process, logged in and set up a root user and set it up as a Service.

The my.ini file contains (amongst others):

port = 3306
server-id = 8306
binlog_format = row
relay-log-info-repository = TABLE
master-info-repository = TABLE
gtid_mode = ON
enforce_gtid_consistency = ON
binlog_checksum = NONE

So far so good. I log, in it all works.

Next I switch off MySQL, and I add the following to the my.cnf file.

#GROUP REPLICATION#
loose-transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="1587d451-feca-11e8-a7b3-c85b768dc0dd"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "127.0.0.1:33061"
loose-group_replication_group_seeds= "127.0.0.1:33061"
loose-group_replication_bootstrap_group=off
loose-group_replication_member_weight=100
loose-group_replication_ip_whitelist="169.254.243.231"

The last entry was added as previous attempts complained that it wasn't in the whitelist.

I restart MySQL again.

Now I start to set up Group Replication.

SET SQL_LOG_BIN=0;
CREATE USER 'GReplication'@'%' IDENTIFIED WITH 'mysql_native_password' BY '@Replication123';
GRANT REPLICATION SLAVE ON *.* TO 'GReplication'@'%';
SET SQL_LOG_BIN=1;

CHANGE MASTER TO MASTER_USER='GReplication', MASTER_PASSWORD='@Replication123' FOR CHANNEL 'group_replication_recovery';

INSTALL PLUGIN group_replication SONAME 'group_replication.dll';
SHOW PLUGINS;

SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;

SELECT * FROM performance_schema.replication_group_members;

This shows one active member.

MySQL  localhost:3306 ssl  SQL > SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 454b0127-b204-11e9-a032-c85b768dc0db | pc12345     |        3306 | ONLINE       | PRIMARY     | 8.0.17         |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
1 row in set (0.0005 sec)

I now change the following line in my.ini file to "on"

loose-group_replication_start_on_boot=on

And restart MySQL.

However, when I check, I now get:

MySQL  localhost:3306 ssl  SQL > SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 454b0127-b204-11e9-a032-c85b768dc0db | pc12345     |        3306 | OFFLINE      |             |                |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
1 row in set (0.0005 sec)

Looking in the error log, it all appears to be working fine up until:

2019-07-29T15:26:17.651950Z 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Error connecting to all peers. Member join failed. Local port: 33061'
2019-07-29T15:26:17.791783Z 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] The member was unable to join the group. Local port: 33061'
2019-07-29T15:27:17.469988Z 2 [ERROR] [MY-011640] [Repl] Plugin group_replication reported: 'Timeout on wait for view after joining group'
2019-07-29T15:27:17.473776Z 2 [Note] [MY-011649] [Repl] Plugin group_replication reported: 'Requesting to leave the group despite of not being a member'
2019-07-29T15:27:17.476534Z 2 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] The member is leaving a group without being on one.'
2019-07-29T15:27:17.492689Z 12 [Note] [MY-010596] [Repl] Error reading relay log event for channel 'group_replication_applier': slave SQL thread was killed
2019-07-29T15:27:17.495987Z 12 [Note] [MY-010587] [Repl] Slave SQL thread for channel 'group_replication_applier' exiting, replication stopped in log 'FIRST' at position 0
2019-07-29T15:27:17.498976Z 9 [Note] [MY-011444] [Repl] Plugin group_replication reported: 'The group replication applier thread was killed.'

I assume I am missing a step somewhere, but what is it? I have gone through all the documentation I can find, and can't see anything different?

I have also tried setting it up on Linux, with much the same result.

Answer 1

Yes, you are restarting the server with

loose-group_replication_start_on_boot=on

But what you are not doing is telling the server to bootstrap the group. Since you have only one member, you are basically trying to create a group on start while having

loose-group_replication_bootstrap_group=off

Group Replication is ideally used with several members, and on such a setup, the member would have other members in the group when it restarted and your test would be successful.

Alternatively you can set

loose-group_replication_bootstrap_group=on

But remember that when you add more members that this setting should be changed.