Setting up MySQL 8.4 Group Replication involves several steps to configure multiple MySQL instances for high availability and fault tolerance. Below is a step-by-step guide to setting up MySQL Group Replication:

Prerequisites:

1. MySQL 8.4 installed on all the servers.
2. At least 3 servers (for fault tolerance, it is recommended to use an odd number of nodes, typically 3 or 5).
3. Network connectivity between all nodes (servers must be able to communicate over the network).
4. Properly configured firewall and ports (e.g., port 3306 for MySQL, port 33061 for Group Replication communication).

Step 1: Install MySQL 8.4 on All Nodes

Ensure MySQL 8.4 is installed on all nodes. If it’s not installed, you can use the following commands:

sudo apt update
sudo apt install mysql-server

Make sure to start MySQL on all nodes:

sudo systemctl start mysql

Step 2: Configure MySQL Configuration Files

1. Edit my.cnf or mysqld.cnf on each node. Open the MySQL configuration file:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

2. Configure each node with a unique server ID and enable Group Replication:

[mysqld]
server-id=1 # Change to a unique ID for each node (e.g., 1, 2, 3, etc.)
log_bin=mysql-bin
binlog_format=ROW
gtid_mode=ON
enforce-gtid-consistency=TRUE
master-info-repository=TABLE
relay-log-info-repository=TABLE
transaction-write-set-extraction=XXHASH64
plugin-load-add=group_replication.so
group_replication=ON
group_replication_group_name=”my_group_replication”
group_replication_start_on_boot=off
group_replication_local_address=”localhost:33061″ # Change the address to each node’s IP
group_replication_group_seeds=”node1_ip:33061,node2_ip:33061,node3_ip:33061″ # List all nodes

Replace localhost:33061 with the actual IP and port for each node. The group_replication_group_name must be the same for all nodes.

3. Restart MySQL on each node:

sudo systemctl restart mysql

Step 3: Set Up Replication User and GTID

1. Log into MySQL on each node:

mysql -u root -p

2. Create a replication user on each node (run this on all nodes):

CREATE USER ‘replication_user’@’%’ IDENTIFIED BY ‘your_password’;
GRANT REPLICATION SLAVE ON . TO ‘replication_user’@’%’;
FLUSH PRIVILEGES;

3. Ensure GTID is enabled:

SHOW VARIABLES LIKE ‘gtid_mode’;

It should return ON for GTID mode to be active.

Step 4: Configure Group Replication on Node 1

1. Enable Group Replication on Node 1 (first node): After configuring my.cnf and restarting MySQL, you can start the group replication on the first node.

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

2. Check if the group is formed correctly:

SHOW STATUS LIKE ‘group_replication%’;

You should see that the group is initialized and running.

Step 5: Join Node 2 and Node 3 to the Group

On the second and third nodes, after making sure the configuration is correct and MySQL is restarted, run the following commands:

1. On Node 2 and Node 3: Log in to MySQL and run:

START GROUP_REPLICATION;

2. Check the replication status on each node:

SHOW STATUS LIKE ‘group_replication%’;

This should confirm that all nodes are part of the group replication.

Step 6: Verify Group Replication Status

Once all nodes are up and running with group replication, you can check the group replication status from any node:

SHOW STATUS LIKE ‘group_replication%’;

You should see that the group_replication_group_name and the group_replication_member list are correctly populated with the member nodes.

Step 7: Test Group Replication

Now, test that the replication is working:

1. Create a test table on any node (Node 1 for example):

CREATE DATABASE test_db;
USE test_db;
CREATE TABLE test_table (id INT PRIMARY KEY, name VARCHAR(255));
INSERT INTO test_table VALUES (1, ‘Hello World’);

2. Check the table on Node 2 or Node 3:

SELECT * FROM test_db.test_table;

You should see the inserted data on the other nodes, confirming that replication is working.

Step 8: Monitor and Maintain Group Replication

• Use the following command to check the status of the group:

SHOW STATUS LIKE 'group_replication%';

• To add new nodes to the group replication, configure them as done previously and run

START GROUP_REPLICATION

• on those nodes. If a node becomes unavailable, the group replication should automatically handle failover (if the number of nodes is odd, it can tolerate a node failure).