How to Set Up MySQL Group Replication

How to Set Up MySQL Group Replication

MySQL Group Replication provides a fault-tolerant, highly available database layer by replicating data across multiple MySQL instances with automatic conflict detection and resolution. Running a Group Replication cluster on your Breeze instances ensures your application survives individual node failures without manual intervention.

Prerequisites

You need at least three Breeze instances running MySQL 8.0 or later. Each instance must have a unique server_id, and all nodes must be able to communicate over the network on the Group Replication port (default 33061). Ensure your Breeze firewall allows traffic between nodes on ports 3306 and 33061.

Configuring the First Node

Edit /etc/mysql/mysql.conf.d/mysqld.cnf on your first Breeze instance:

[mysqld]
server_id = 1
gtid_mode = ON
enforce_gtid_consistency = ON
binlog_checksum = NONE
log_bin = binlog
log_slave_updates = ON
binlog_format = ROW
master_info_repository = TABLE
relay_log_info_repository = TABLE
transaction_write_set_extraction = XXHASH64

plugin_load_add = 'group_replication.so'
group_replication_group_name = "aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee"
group_replication_start_on_boot = OFF
group_replication_local_address = "breeze1-ip:33061"
group_replication_group_seeds = "breeze1-ip:33061,breeze2-ip:33061,breeze3-ip:33061"
group_replication_bootstrap_group = OFF

Restart MySQL and create the replication user:

sudo systemctl restart mysql

mysql -u root -p -e "
SET SQL_LOG_BIN = 0;
CREATE USER 'repl'@'%' IDENTIFIED BY 'StrongPassword123!';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
GRANT CONNECTION_ADMIN ON *.* TO 'repl'@'%';
GRANT BACKUP_ADMIN ON *.* TO 'repl'@'%';
GRANT GROUP_REPLICATION_STREAM ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN = 1;
CHANGE REPLICATION SOURCE TO SOURCE_USER='repl', SOURCE_PASSWORD='StrongPassword123!' FOR CHANNEL 'group_replication_recovery';
"

Bootstrapping the Group

On the first node only, bootstrap the group:

mysql -u root -p -e "
SET GLOBAL group_replication_bootstrap_group = ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group = OFF;
"

Verify the group is running:

SELECT * FROM performance_schema.replication_group_members;

Adding Additional Nodes

On each additional Breeze instance, apply the same configuration (changing server_id and group_replication_local_address), create the replication user, then start group replication without bootstrapping:

START GROUP_REPLICATION;

Monitoring and Troubleshooting

Monitor group health with:

SELECT MEMBER_ID, MEMBER_HOST, MEMBER_STATE, MEMBER_ROLE
FROM performance_schema.replication_group_members;

All members should show ONLINE. If a node shows RECOVERING, check the error log at /var/log/mysql/error.log. Common issues include network timeouts, GTID inconsistencies, and large transaction limits. Set group_replication_transaction_size_limit to handle large transactions when needed.