MySQL Group Replication Setup Guide

This guide walks through configuring MySQL Group Replication across multiple servers, which provides built-in high availability and automatic failover. You’ll learn how to set up a group of MySQL servers that stay in sync, handle member failures gracefully, and let you perform both reads and writes across the cluster. By the end, you’ll have a working group replication setup ready for production use.

What You’ll Need

• At least three MySQL 8.0 or later instances on separate servers (or three MySQL 5.7.17+ instances) – odd numbers are recommended for quorum.
• Each server must have a unique server_id and a properly configured hostname.
• Network connectivity between all servers on the ports you’ll use (default 33061 for group communication and 3306 for MySQL client connections).
• Sudo or root access on each server to edit MySQL configuration files and restart the service.
• MySQL binaries installed and initialized on each server (use the same version across all members).
• SSL certificates if you plan to secure group communication (recommended for production).

Step-by-Step

1. Configure each server’s my.cnf – Edit the MySQL configuration file (typically /etc/my.cnf or /etc/mysql/my.cnf) on every server. Add or modify these lines under the [mysqld] section:
   server_id=1 (use a unique number per server, e.g., 1, 2, 3)
   gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE
log_bin=ON
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" (replace with a valid UUID)
   group_replication_start_on_boot=off
group_replication_local_address="server1_ip:33061" (use each server’s IP and port)
   group_replication_group_seeds="server1_ip:33061,server2_ip:33061,server3_ip:33061" (list all servers)
   group_replication_bootstrap_group=off
group_replication_ip_allowlist="server1_ip,server2_ip,server3_ip" (or use CIDR notation like “192.168.1.0/24”)
2. Restart MySQL on each server – After saving the configuration, restart the MySQL service: sudo systemctl restart mysql (or mysqld on some systems). Verify it starts without errors.
3. Create a replication user on the first server – Connect to the first server as root: mysql -u root -p. Then run:
   SET SQL_LOG_BIN=0;
CREATE USER 'repl'@'%' IDENTIFIED BY 'YourStrongPassword';
GRANT REPLICATION SLAVE, BACKUP_ADMIN ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
   This user will be used for group replication recovery.
4. Install the group replication plugin on each server – On each server, run: INSTALL PLUGIN group_replication SONAME 'group_replication.so'; (if not already loaded via config). Verify with SHOW PLUGINS;.
5. Configure the replication channel on each server – On each server, connect as root and run:
   CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='YourStrongPassword' FOR CHANNEL 'group_replication_recovery';
6. Start group replication on the first server (bootstrap) – On the first server only, run:
   SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;
   This creates the group with this server as the initial primary.
7. Start group replication on the other servers – On each of the remaining servers, simply run: START GROUP_REPLICATION; They will join the group automatically using the seed list.
8. Verify the group status – On any server, run: SELECT * FROM performance_schema.replication_group_members; You should see all servers listed with ONLINE status. Also check SHOW STATUS LIKE 'group_replication_primary_member'; to see which server is primary.
9. Test replication – Create a test database and table on the primary, insert rows, and verify they appear on other members. For example: CREATE DATABASE testdb; USE testdb; CREATE TABLE t1 (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50)); INSERT INTO t1 (name) VALUES ('hello'); Then query from another member to confirm the row is there.

Common Mistakes

• Forgetting to set server_id uniquely – All servers must have different server_id values; duplicate IDs cause conflicts.
• Incorrect group_replication_group_name – The group name must be a valid UUID (e.g., generated with SELECT UUID();). Using the same name is fine, but it must be valid.
• Not disabling binary log during user creation – If you don’t set SQL_LOG_BIN=0 when creating the replication user, the user creation gets replicated, which can cause issues.
• Starting all servers with bootstrap_group=ON – Only the first server should bootstrap the group; others must join normally.
• Firewall blocking group communication ports – Ensure port 33061 (or your chosen port) is open between all servers. Test with telnet server_ip 33061.
• Using a single server for testing – Group replication requires at least three members to maintain quorum; with two, one failure stops the group.

Tips

• Use a dedicated network interface or VLAN for group communication to reduce latency and improve performance.
• Set group_replication_consistency to ‘EVENTUAL’ or ‘AFTER’ based on your needs; ‘AFTER’ ensures reads see the latest committed data but may increase latency.
• Enable SSL for group replication by setting group_replication_ssl_mode=REQUIRED and providing certificates in the config.
• Monitor group health with performance_schema.replication_group_members and set up alerts for member state changes.
• Test failover by stopping the primary and observing that another member becomes primary automatically.
• Back up the group_replication_group_name and seed list; you’ll need them when adding new members later.

Troubleshooting

If group replication fails to start, check the MySQL error log (/var/log/mysql/error.log) for details. Common issues include:

• Members not appearing as ONLINE – Check that all servers can reach each other on the group communication port. Use SHOW STATUS LIKE 'group_replication_communication_*'; to see connection stats.
• Error 3092 or 3093 – These indicate that the group name or local address is not correctly configured. Verify the UUID format and that the local address matches the actual IP.
• Recovery fails – If the recovery channel fails, ensure the replication user exists and has the correct password on all servers. Try START GROUP_REPLICATION; after fixing credentials.
• Quorum loss – If the group loses quorum (e.g., two out of three servers fail), the remaining server cannot accept writes. Restart the failed servers or restore them from backup to regain quorum.
• Performance issues – Group replication can be slower than async replication due to consensus. Ensure your network is fast and consider using group_replication_transaction_size_limit to avoid large transactions.

Frequently Asked Questions

Can I use two servers for group replication?

Technically yes, but it’s not recommended because if one server fails, the group loses quorum and becomes read-only. A minimum of three servers is the standard for high availability.

What happens if the primary server crashes?

Group replication automatically elects a new primary from the remaining members (in single-primary mode). The new primary is selected based on the group’s internal election process, and clients need to be redirected to it.

How do I add a new server to an existing group?

Configure the new server with the same group name and seed list, create the replication user, and run START GROUP_REPLICATION;. It will join automatically, but you must ensure its server_id is unique.

Can I use group replication with existing data?

Yes, but you need to synchronize data first. You can use mysqldump or clone plugin to copy data from an existing member, then start replication. Ensure the data is consistent before starting.

How does group replication handle conflicts?

Group replication uses a certification mechanism: transactions are checked against those from other members, and if a conflict is detected, the transaction is rolled back on the conflicting member. This ensures consistency across the group.

Is group replication the same as MySQL InnoDB Cluster?

No. InnoDB Cluster is a higher-level solution that uses Group Replication underneath, plus MySQL Router for client routing and MySQL Shell for administration. Group Replication is the underlying technology.

Related

• Delayed VR Accessory: What to Check Before Buying
• Check Message Requests Messenger: How to Find Them
• Motion Tracking Sensor Buying Guide