Resolving MySQL Group Replication Configuration Issues

When deploying MySQL Group Replication (MGR), several common errors can occur during setup and operation. Below are the most frequent issues and their solutions.

Transaction Mismatch Error

[ERROR] Plugin group_replication reported: 'This member has more executed transactions than those present in the group. Local transactions: xyz12345-abcd-11e8-8b52-000c2934472e:1 > Group transactions: def67890-0e51-409f-a61d-c99756e90155:1-11'
[ERROR] Plugin group_replication reported: 'The member contains transactions not present in the group. The member will now exit the group.'
[Note] Plugin group_replication reported: 'To force this member into the group you can use the group_replication_allow_local_disjoint_gtids_join option'

Solution: Enable the parameter as suggested:

SET GLOBAL group_replication_allow_local_disjoint_gtids_join=ON;

Local Transaction Warning with Parameter Enabled

[ERROR] Plugin group_replication reported: 'This member has more executed transactions than those present in the group. Local transactions: xyz12345-c485-11e8-8b52-000c2934472e:1 > Group transactions: def67890-0e51-409f-a61d-c99756e90155:1-15'
[Warning] Plugin group_replication reported: 'The member contains transactions not present in the group. It is only allowed to join due to group_replication_allow_local_disjoint_gtids_join option'

Solution: Execute RESET MASTER and reconfigure the replication channel:

RESET MASTER;
CHANGE MASTER TO MASTER_USER='repl_user', MASTER_PASSWORD='repl_pass' FOR CHANNEL 'group_replication_recovery';

Connection Error to Donor Server

[ERROR] Slave I/O for channel 'group_replication_recovery': error connecting to master 'repl_user@localhost.localdomain:' - retry-time: 60 retries: 1, Error_code: 2005
[ERROR] Plugin group_replication reported: 'There was an error when connecting to the donor server. Please check that group_replication_recovery channel credentials and all MEMBER_HOST column values of performance_schema.replication_group_members table are correct and DNS resolvable.'

Solution: Ensure unique hostnames for each node in the group.

Server Configuration Error

ERROR 3096 (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log.
[Warning] Plugin group_replication reported: 'Group Replication requires slave-preserve-commit-order to be set to ON when using more than 1 applier threads.'

Soluiton: Enable the required parameter:

SET GLOBAL slave_preserve_commit_order=1;

IP Whitelist Warning

[Warning] Plugin group_replication reported: '[GCS] Connection attempt from IP address 192.168.9.208 refused. Address is not in the IP whitelist.'

Solution: Connfigure group_replication_ip_whitelist in my.cnf.

Group Join Timeout

[Warning] Plugin group_replication reported: 'read failed'
[ERROR] Plugin group_replication reported: '[GCS] The member was unable to join the group. Local port: 24801'
[ERROR] Plugin group_replication reported: 'Timeout on wait for view after joining group'

Solution: Set group_replication_group_seeds to include only other group members' IP addresses and ports, excluding the local node.

Firewall Connection Issues

[ERROR] Plugin group_replication reported: '[GCS] Error on opening a connection to node07: on local port: .'

Solution: Open the required firewall ports for group communication.

User Access Denied

[ERROR] Slave I/O for channel 'group_replication_recovery': Master command COM_REGISTER_SLAVE failed: Access denied for user 'repl_user'@'%' (using password: YES) (Errno: 1045), Error_code: 1597

Solution: Create and grant necessary privileges on all nodes:

CREATE USER repl_user@'%';
GRANT REPLICATION SLAVE ON *.* TO repl_user@'%' IDENTIFIED BY 'repl_pass';

Relay Log Initialization Failure

[ERROR] Failed to open the relay log './localhost-relay-bin.000011' (relay_log_pos )
[ERROR] Could not find target log file mentioned in relay log info in the index file './work_NAT_1-relay-bin.index' during relay log initialization.

Solution: Reset the slave configuration:

RESET SLAVE;

Primary Member Election Query

To identify the current primary member in single-primary mode:

SELECT * FROM performance_schema.replication_group_members 
WHERE member_id = (
    SELECT variable_value FROM performance_schema.global_status 
    WHERE VARIABLE_NAME='group_replication_primary_member'
);