--- title: "How to Configure MySQL Group Replication on Ubuntu 24.04" description: "Set up a three-node MySQL 8.0 Group Replication cluster on Ubuntu 24.04 in single-primary mode, test automatic failover and learn when to use multi-primary." category: "Database Advanced" language: en source: https://cubepath.com/docs/database-advanced/mysql-group-replication-configuration updated: 2026-09-25 --- # How to Configure MySQL Group Replication on Ubuntu 24.04 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 `sudo` privileges 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: ```bash sudo apt update sudo apt install mysql-server ``` Check the version and that the service is running: ```bash mysql --version sudo systemctl status mysql --no-pager ``` ```output 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: ```bash 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`: ```bash cat /proc/sys/kernel/random/uuid ``` ```output 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: ```bash 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: ```ini bind-address = 10.0.0.11 ``` Then add the following block at the end of the `[mysqld]` section. On `db1`: ```ini # 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_id` must be unique per member: use `2` on `db2` and `3` on `db3`. - `gtid_mode` and `enforce_gtid_consistency` are 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_engines` blocks non-transactional engines: only InnoDB tables can be replicated. - `report_host` makes each member announce its private IP to the group. - `group_replication_group_name` is the UUID from Step 2, identical on every member. - `group_replication_local_address` is this member's own IP and port 33061, so change it on `db2` and `db3`. `group_replication_group_seeds` lists all members and is identical everywhere. - `group_replication_start_on_boot = OFF` keeps a member from joining before you finish the setup. You will turn it on at the end. - `group_replication_recovery_use_ssl = ON` encrypts the recovery connection that a new member uses to copy missing data. It is also needed because the recovery user authenticates with `caching_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: ```bash sudo systemctl restart mysql ``` Confirm that the plugin is loaded: ```bash sudo mysql -e "SELECT PLUGIN_NAME, PLUGIN_STATUS FROM information_schema.PLUGINS WHERE PLUGIN_NAME = 'group_replication';" ``` ```output +-------------------+---------------+ | 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`: ```bash 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: ```sql 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: ```sql CHANGE REPLICATION SOURCE TO SOURCE_USER = 'repl', SOURCE_PASSWORD = 'your_repl_password' FOR CHANNEL 'group_replication_recovery'; ``` ```output 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: ```sql 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: ```sql SELECT MEMBER_HOST, MEMBER_PORT, MEMBER_STATE, MEMBER_ROLE FROM performance_schema.replication_group_members; ``` ```output +-------------+-------------+--------------+-------------+ | 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: ```sql 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: ```sql SELECT MEMBER_HOST, MEMBER_STATE, MEMBER_ROLE FROM performance_schema.replication_group_members; ``` ```output +-------------+--------------+-------------+ | 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: ```ini 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: ```sql 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: ```bash sudo mysql -e "SELECT id, customer FROM shop.orders;" ``` ```output +----+----------+ | id | customer | +----+----------+ | 1 | alice | | 2 | bob | +----+----------+ ``` In single-primary mode, secondaries are read-only. A write on `db2` fails: ```bash sudo mysql -e "INSERT INTO shop.orders (customer) VALUES ('carol');" ``` ```output 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`: ```bash sudo systemctl stop mysql ``` After a few seconds, list the members from `db2`: ```bash sudo mysql -e "SELECT MEMBER_HOST, MEMBER_STATE, MEMBER_ROLE FROM performance_schema.replication_group_members;" ``` ```output +-------------+--------------+-------------+ | 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`: ```bash 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: ```sql 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: ```sql SELECT group_replication_switch_to_multi_primary_mode(); ``` ```output +--------------------------------------------------+ | 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: ```sql 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: ```sql SELECT MEMBER_HOST, MEMBER_STATE, MEMBER_ROLE, MEMBER_VERSION FROM performance_schema.replication_group_members; ``` Per-member statistics, including the certification queue and conflicts: ```sql 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 `RECOVERING` and then goes to `ERROR`**: check `/var/log/mysql/error.log` on that member. `Access denied for user 'repl'` means the recovery credentials differ; rerun the `CHANGE REPLICATION SOURCE` statement. `Authentication requires secure connection` means `group_replication_recovery_use_ssl` is not enabled. - **`The member contains transactions not present in the group`**: something was written on the member outside the group, often a user created without `SET SQL_LOG_BIN = 0`. For a fresh member, run `RESET MASTER;` (it clears its binary logs and GTID history) and start Group Replication again. - **`Unable to join the group: peers are not reachable` or timeouts on 33061**: check the UFW rules, `group_replication_local_address` and that `bind-address` is not still `127.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 run `START 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.