MySQL Group Replication is a plugin built into MySQL that keeps a group of servers in sync through a consensus protocol. Every transaction is certified by the group before it commits, and if the primary fails the remaining members elect a new one automatically, without external tools. In this tutorial you will build a three-node Group Replication cluster with MySQL 8.0 on Ubuntu 24.04 in single-primary mode, verify replication, test failover and see how to switch to multi-primary mode.
Prerequisites
To follow this tutorial, you will need:
- Three servers running Ubuntu 24.04 LTS, for example three CubePath VPS, each with at least 2 GB of RAM.
- A non-root user with
sudoprivileges on every server. - A private network between the servers. This guide uses these addresses; replace them with yours:
| Hostname | Private IP |
|---|---|
db1 | 10.0.0.11 |
db2 | 10.0.0.12 |
db3 | 10.0.0.13 |
Group Replication needs at least three members to tolerate the failure of one, because the group only keeps working while a majority of members is reachable.
Unless a step says otherwise, run each command on all three servers.
Step 1 - Installing MySQL and opening the ports
Install MySQL Server 8.0 from the Ubuntu repositories. It includes the Group Replication plugin:
sudo apt update
sudo apt install mysql-server
Check the version and that the service is running:
mysql --version
sudo systemctl status mysql --no-pager
mysql Ver 8.0.xx-0ubuntu0.24.04.x for Linux on x86_64 ((Ubuntu))
● mysql.service - MySQL Community Server
Active: active (running)
Members use two ports: 3306 for client connections and distributed recovery, and 33061 for group communication. Allow both only from the private subnet:
sudo ufw allow OpenSSH
sudo ufw allow from 10.0.0.0/24 proto tcp to any port 3306,33061
sudo ufw enable
Step 2 - Generating a group name
Every group is identified by a UUID that must be the same on all members. Generate one on db1:
cat /proc/sys/kernel/random/uuid
3f5d1c6a-8a2b-4e0f-9b7c-2d4e6f8a1b3c
Copy the value. You will use it on all three servers in the next step.
Step 3 - Configuring MySQL for Group Replication
Open the main server configuration file:
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
Find the bind-address line, which by default only listens on 127.0.0.1, and change it to the server's private IP:
bind-address = 10.0.0.11
Then add the following block at the end of the [mysqld] section. On db1:
# General replication settings
server_id = 1
gtid_mode = ON
enforce_gtid_consistency = ON
disabled_storage_engines = "MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
report_host = 10.0.0.11
# Group Replication
plugin_load_add = "group_replication.so"
group_replication_group_name = "3f5d1c6a-8a2b-4e0f-9b7c-2d4e6f8a1b3c"
group_replication_start_on_boot = OFF
group_replication_bootstrap_group = OFF
group_replication_local_address = "10.0.0.11:33061"
group_replication_group_seeds = "10.0.0.11:33061,10.0.0.12:33061,10.0.0.13:33061"
group_replication_recovery_use_ssl = ON
What these settings do:
server_idmust be unique per member: use2ondb2and3ondb3.gtid_modeandenforce_gtid_consistencyare required, because Group Replication tracks transactions by global transaction ID. Binary logging and row-based logging are already the defaults in MySQL 8.0.disabled_storage_enginesblocks non-transactional engines: only InnoDB tables can be replicated.report_hostmakes each member announce its private IP to the group.group_replication_group_nameis the UUID from Step 2, identical on every member.group_replication_local_addressis this member's own IP and port 33061, so change it ondb2anddb3.group_replication_group_seedslists all members and is identical everywhere.group_replication_start_on_boot = OFFkeeps a member from joining before you finish the setup. You will turn it on at the end.group_replication_recovery_use_ssl = ONencrypts the recovery connection that a new member uses to copy missing data. It is also needed because the recovery user authenticates withcaching_sha2_password.
On db2 and db3, change bind-address, server_id, report_host and group_replication_local_address accordingly. Then restart MySQL on all three servers:
sudo systemctl restart mysql
Confirm that the plugin is loaded:
sudo mysql -e "SELECT PLUGIN_NAME, PLUGIN_STATUS FROM information_schema.PLUGINS WHERE PLUGIN_NAME = 'group_replication';"
+-------------------+---------------+
| PLUGIN_NAME | PLUGIN_STATUS |
+-------------------+---------------+
| group_replication | ACTIVE |
+-------------------+---------------+
If MySQL fails to start, read the error with sudo journalctl -u mysql -n 50 and sudo tail -n 50 /var/log/mysql/error.log. A typo in a variable name is the usual cause.
Step 4 - Creating the replication user
When a member joins, it copies the transactions it is missing from an existing member through a channel called group_replication_recovery. That channel needs a user with replication privileges on every server.
Open the MySQL shell. On Ubuntu, the MySQL root account authenticates through the system root user, so use sudo:
sudo mysql
Run the following statements on all three servers. SET SQL_LOG_BIN = 0 keeps the user creation out of the binary log, so it is not replicated and does not conflict between members. Replace your_repl_password with a strong password and use the same one everywhere:
SET SQL_LOG_BIN = 0;
CREATE USER 'repl'@'%' IDENTIFIED BY 'your_repl_password' REQUIRE SSL;
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;
Then store the credentials for the recovery channel:
CHANGE REPLICATION SOURCE TO SOURCE_USER = 'repl', SOURCE_PASSWORD = 'your_repl_password' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.01 sec)
The warnings only say that the credentials are stored in the replication metadata tables, which is expected.
Step 5 - Bootstrapping the group on the first member
Exactly one member must create the group. It does so by enabling group_replication_bootstrap_group for a single start. In the MySQL shell on db1 only, run:
SET GLOBAL group_replication_bootstrap_group = ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group = OFF;
Turn the bootstrap flag off immediately after starting. If two members bootstrap, you end up with two separate groups that have the same name.
Check the group membership:
SELECT MEMBER_HOST, MEMBER_PORT, MEMBER_STATE, MEMBER_ROLE FROM performance_schema.replication_group_members;
+-------------+-------------+--------------+-------------+
| MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE |
+-------------+-------------+--------------+-------------+
| 10.0.0.11 | 3306 | ONLINE | PRIMARY |
+-------------+-------------+--------------+-------------+
Step 6 - Adding the other members
In the MySQL shell on db2, and then on db3, start Group Replication without the bootstrap flag:
START GROUP_REPLICATION;
Each member contacts the seeds, runs distributed recovery through the repl user and then moves to ONLINE. From any member, list the group again:
SELECT MEMBER_HOST, MEMBER_STATE, MEMBER_ROLE FROM performance_schema.replication_group_members;
+-------------+--------------+-------------+
| MEMBER_HOST | MEMBER_STATE | MEMBER_ROLE |
+-------------+--------------+-------------+
| 10.0.0.11 | ONLINE | PRIMARY |
| 10.0.0.12 | ONLINE | SECONDARY |
| 10.0.0.13 | ONLINE | SECONDARY |
+-------------+--------------+-------------+
A member that stays in RECOVERING for a long time or ends in ERROR has a problem with the recovery connection. See the Troubleshooting section.
Now that the group exists, let members rejoin automatically after a reboot. On all three servers, open /etc/mysql/mysql.conf.d/mysqld.cnf again and change:
group_replication_start_on_boot = ON
Leave group_replication_bootstrap_group = OFF. A restarted member will then rejoin the running group, but will never create a new one on its own.
Step 7 - Verifying replication
On the primary, db1, create a database and a table. Every replicated table needs a primary key, because Group Replication uses it to detect conflicting changes:
CREATE DATABASE shop;
CREATE TABLE shop.orders (
id INT AUTO_INCREMENT PRIMARY KEY,
customer VARCHAR(100) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO shop.orders (customer) VALUES ('alice'), ('bob');
On db2, read the data:
sudo mysql -e "SELECT id, customer FROM shop.orders;"
+----+----------+
| id | customer |
+----+----------+
| 1 | alice |
| 2 | bob |
+----+----------+
In single-primary mode, secondaries are read-only. A write on db2 fails:
sudo mysql -e "INSERT INTO shop.orders (customer) VALUES ('carol');"
ERROR 1290 (HY000) at line 1: The MySQL server is running with the --super-read-only option so it cannot execute this statement
Step 8 - Testing automatic failover
Stop MySQL on the primary to simulate a failure. On db1:
sudo systemctl stop mysql
After a few seconds, list the members from db2:
sudo mysql -e "SELECT MEMBER_HOST, MEMBER_STATE, MEMBER_ROLE FROM performance_schema.replication_group_members;"
+-------------+--------------+-------------+
| MEMBER_HOST | MEMBER_STATE | MEMBER_ROLE |
+-------------+--------------+-------------+
| 10.0.0.12 | ONLINE | PRIMARY |
| 10.0.0.13 | ONLINE | SECONDARY |
+-------------+--------------+-------------+
One secondary was elected as the new primary and its super_read_only flag was removed. Start MySQL again on db1:
sudo systemctl start mysql
Because group_replication_start_on_boot is now ON, db1 rejoins as a secondary and catches up on the transactions it missed.
To choose the primary yourself, for example before maintenance, look up the member's MEMBER_ID in performance_schema.replication_group_members and run on any member:
SELECT group_replication_set_as_primary('member_uuid');
Your application must follow the primary. MySQL Router, which reads the group metadata, or a proxy such as ProxySQL can do this for you.
Step 9 - Switching between single-primary and multi-primary
In multi-primary mode every member accepts writes. It removes the need to redirect writes after a failover, but concurrent transactions that change the same rows on different members conflict: the first one to be certified commits and the others are rolled back with an error that your application must retry. In multi-primary mode Group Replication also changes auto_increment_increment (7 by default) and auto_increment_offset on each member so that inserts on different servers never generate the same key, which leaves gaps in AUTO_INCREMENT columns. Multi-primary mode also does not support the SERIALIZABLE isolation level or tables with cascading foreign keys.
You can change the mode online, from any member:
SELECT group_replication_switch_to_multi_primary_mode();
+--------------------------------------------------+
| group_replication_switch_to_multi_primary_mode() |
+--------------------------------------------------+
| Mode switched to multi-primary successfully. |
+--------------------------------------------------+
All members now show PRIMARY as their role. To go back to single-primary, pass the UUID of the member that should become the primary:
SELECT group_replication_switch_to_single_primary_mode('member_uuid');
For most applications single-primary mode is the safer choice. Use multi-primary only when your write pattern rarely touches the same rows from different servers.
Step 10 - Monitoring the group
Two Performance Schema tables tell you most of what you need. Membership and roles, as you have seen:
SELECT MEMBER_HOST, MEMBER_STATE, MEMBER_ROLE, MEMBER_VERSION FROM performance_schema.replication_group_members;
Per-member statistics, including the certification queue and conflicts:
SELECT MEMBER_ID, COUNT_TRANSACTIONS_IN_QUEUE, COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE, COUNT_CONFLICTS_DETECTED
FROM performance_schema.replication_group_member_stats;
A growing COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE means that member is falling behind in applying changes, and a rising COUNT_CONFLICTS_DETECTED in multi-primary mode means transactions are being rolled back. Alert on any member whose state is not ONLINE.
Troubleshooting
- A member stays in
RECOVERINGand then goes toERROR: check/var/log/mysql/error.logon that member.Access denied for user 'repl'means the recovery credentials differ; rerun theCHANGE REPLICATION SOURCEstatement.Authentication requires secure connectionmeansgroup_replication_recovery_use_sslis not enabled. The member contains transactions not present in the group: something was written on the member outside the group, often a user created withoutSET SQL_LOG_BIN = 0. For a fresh member, runRESET MASTER;(it clears its binary logs and GTID history) and start Group Replication again.Unable to join the group: peers are not reachableor timeouts on 33061: check the UFW rules,group_replication_local_addressand thatbind-addressis not still127.0.0.1.- The whole group stopped (all members down): when you restart, no member will bootstrap on its own. Start MySQL everywhere, find the member with the most recent data by comparing
SELECT @@GLOBAL.gtid_executed;, bootstrap the group on that member as in Step 5 and runSTART GROUP_REPLICATION;on the others.
Conclusion
You have a three-node MySQL Group Replication cluster in single-primary mode that elects a new primary automatically, rejoins recovered members and can be switched to multi-primary online. As next steps, put MySQL Router or ProxySQL in front of the group so applications always reach the primary, schedule consistent backups with a tool such as Percona XtraBackup, and monitor the Performance Schema tables above from your monitoring system.