How to Manually Re‑Add a Detached MySQL MGR Cluster Node

Background

When a MySQL instance leaves a Group Replication (MGR) cluster, re‑adding it can fail due to GTID mismatches and other data inconsistencies.

Export business databases

Run mysqldump with --single-transaction to avoid table locks and --master-data=2 to record the GTID position (use 1 for binlog‑based setups).

mysqldump -uroot -p<password> \
  --single-transaction \
  --socket=/app/mysql/data/mysql.sock \
  --master-data=2 \
  --databases db1 db2 > all.sql

--single-transaction

prevents table locking during backup. --master-data=2 records GTID position for GTID‑based replication; use 1 for binlog‑based replication.

Re‑initialize the database

# Initialize database
mysqld --initialize --user=mysql --basedir=/app/mysql/ --datadir=/app/mysql/TTclMember_3308/data

# Note: after initialization a temporary password is printed to the console; use it for the next login.

# Restart database
su - mysql -c "/etc/init.d/mysqld TTclMember_3308 start"

# Change root password
mysql -uroot -h127.0.0.1 -p -P3308
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';
ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER;
FLUSH PRIVILEGES;

Import the exported data

source /root/data/all.sql;

View GTID information in the dump

grep -C 5 GLOBAL.GTID_PURGED all.sql
-- GTID state at the beginning of the backup
SET @@GLOBAL.GTID_PURGED = /*!80000 '+'*/ 'f2d42556-ca6b-11eb-b2ba-0a3f4b18d570:1,f2d42556-ca6b-11eb-b2ba-0a3f4b18d588:1-231968';
-- Position to start replication or point‑in‑time recovery from

Create replication, monitoring accounts and helper functions

The following accounts are examples for testing; adjust privileges according to actual business needs.

# Create replication user
CREATE USER grpl_user@'%' IDENTIFIED BY 'grpl_user';
ALTER USER 'grpl_user'@'%' IDENTIFIED WITH mysql_native_password BY 'grpl_user';
GRANT REPLICATION SLAVE ON *.* TO grpl_user@'%';
FLUSH PRIVILEGES;

# Join cluster
CHANGE MASTER TO MASTER_USER='grpl_user', MASTER_PASSWORD='grpl_user' FOR CHANNEL 'group_replication_recovery';

# Create monitoring user
CREATE USER 'monitor'@'%' IDENTIFIED BY "monitor";
ALTER USER 'monitor'@'%' IDENTIFIED WITH mysql_native_password BY 'monitor';
GRANT REPLICATION CLIENT ON *.* TO 'monitor'@'%';
GRANT SELECT ON sys.* TO 'monitor'@'%';
FLUSH PRIVILEGES;

# Create monitoring functions and views
USE sys;
DELIMITER $$
CREATE FUNCTION IFZERO(a INT, b INT)
RETURNS INT DETERMINISTIC
RETURN IF(a = 0, b, a)$$

CREATE FUNCTION LOCATE2(needle TEXT(10000), haystack TEXT(10000), offset INT)
RETURNS INT DETERMINISTIC
RETURN IFZERO(LOCATE(needle, haystack, offset), LENGTH(haystack) + 1)$$

CREATE FUNCTION GTID_NORMALIZE(g TEXT(10000))
RETURNS TEXT(10000) DETERMINISTIC
RETURN GTID_SUBTRACT(g, '')$$

CREATE FUNCTION GTID_COUNT(gtid_set TEXT(10000))
RETURNS INT DETERMINISTIC
BEGIN
  DECLARE result BIGINT DEFAULT 0;
  DECLARE colon_pos INT;
  DECLARE next_dash_pos INT;
  DECLARE next_colon_pos INT;
  DECLARE next_comma_pos INT;
  SET gtid_set = GTID_NORMALIZE(gtid_set);
  SET colon_pos = LOCATE2(':', gtid_set, 1);
  WHILE colon_pos != LENGTH(gtid_set) + 1 DO
    SET next_dash_pos = LOCATE2('-', gtid_set, colon_pos + 1);
    SET next_colon_pos = LOCATE2(':', gtid_set, colon_pos + 1);
    SET next_comma_pos = LOCATE2(',', gtid_set, colon_pos + 1);
    IF next_dash_pos < next_colon_pos AND next_dash_pos < next_comma_pos THEN
      SET result = result + SUBSTR(gtid_set, next_dash_pos + 1, LEAST(next_colon_pos, next_comma_pos) - (next_dash_pos + 1)) - SUBSTR(gtid_set, colon_pos + 1, next_dash_pos - (colon_pos + 1)) + 1;
    ELSE
      SET result = result + 1;
    END IF;
    SET colon_pos = next_colon_pos;
  END WHILE;
  RETURN result;
END$$

CREATE FUNCTION sys.gr_applier_queue_length() RETURNS INT DETERMINISTIC
BEGIN
  RETURN (SELECT sys.gtid_count(GTID_SUBTRACT((SELECT Received_transaction_set FROM performance_schema.replication_connection_status WHERE Channel_name = 'group_replication_applier'), (SELECT @@global.GTID_EXECUTED))));
END$$

CREATE FUNCTION gr_member_in_primary_partition() RETURNS VARCHAR(3) DETERMINISTIC
BEGIN
  RETURN (SELECT IF(MEMBER_STATE='ONLINE' AND ((SELECT COUNT(*) FROM performance_schema.replication_group_members WHERE MEMBER_STATE != 'ONLINE') >= (SELECT COUNT(*) FROM performance_schema.replication_group_members)/2) = 0, 'YES', 'NO') FROM performance_schema.replication_group_members JOIN performance_schema.replication_group_member_stats rgms USING(member_id) WHERE rgms.MEMBER_ID = @@SERVER_UUID);
END$$

CREATE VIEW gr_member_routing_candidate_status AS
SELECT
  sys.gr_member_in_primary_partition() AS viable_candidate,
  IF((SELECT GROUP_CONCAT(variable_value) FROM performance_schema.global_variables WHERE variable_name IN ('read_only','super_read_only')) != 'OFF,OFF', 'YES', 'NO') AS read_only,
  sys.gr_applier_queue_length() AS transactions_behind,
  Count_Transactions_in_queue AS 'transactions_to_cert'
FROM performance_schema.replication_group_member_stats rgms
WHERE rgms.MEMBER_ID = (SELECT gv.VARIABLE_VALUE FROM performance_schema.global_variables gv WHERE gv.VARIABLE_NAME = 'server_uuid');
DELIMITER ;

# Verify view creation success after starting the cluster
SELECT * FROM sys.gr_member_routing_candidate_status;

Re‑join the cluster

# Reset local logs
RESET MASTER;
# Apply the GTID_PURGED value obtained earlier
SET GLOBAL gtid_purged = 'f2d42556-ca6b-11eb-b2ba-0a3f4b18d570:1,f2d42556-ca6b-11eb-b2ba-0a3f4b18d588:1-231968';
# Set the instance to read‑only
SET GLOBAL read_only = ON;
# Configure master‑slave replication for recovery channel
CHANGE MASTER TO MASTER_USER='grpl_user', MASTER_PASSWORD='Grpl_user123' FOR CHANNEL 'group_replication_recovery';
# Start group replication; check the error log if problems appear
START GROUP_REPLICATION;
# Check cluster status
SELECT * FROM performance_schema.replication_group_members;