MySQL replication: GTID, source-replica, Group Replication and InnoDB Cluster

A lone MySQL is a single point of failure (SPOF). Disk failure, system crash, planned maintenance: your application is unavailable until the server is restarted. Replication solves this problem by continuously copying changes to one or more replicas that can take over in case of failure, absorb reads, or serve as a base for backups.

This guide builds a source-replica cluster with asynchronous GTID replication, covers lag monitoring and manual failover, then presents the advanced architectures: semi-synchronous, Group Replication and InnoDB Cluster.

Current version

This guide uses MySQL 8.4 LTS (April 2024). Notable changes: the source/replica terms officially replace master/slave, tagged GTIDs are introduced, and the clone plugin simplifies replica initialization.

What you will learn

• Configure a source and a replica with asynchronous GTID replication, end to end
• Understand the binary logs, relay logs and GTID positions that drive replication
• Monitor replication lag and diagnose delays
• Run a manual failover with CHANGE REPLICATION SOURCE TO
• Tell apart classic replication, semi-synchronous, Group Replication and InnoDB Cluster
• Choose the right architecture for your availability requirements

In what context?

MySQL replication answers concrete administration needs:

• You administer a MySQL in production and want to eliminate the single point of failure (SPOF)
• You need a read replica to offload reporting or analytics queries from the source
• You are preparing a planned maintenance (version upgrade, migration) with a controlled switchover
• You must guarantee a fast recovery (short RTO) if the main server crashes
• Your application needs automated high availability (Group Replication, InnoDB Cluster)

What this guide does not cover

Two neighbouring subjects are covered elsewhere and remain indispensable despite replication. Backup (mysqldump, MySQL Shell, XtraBackup, binlog archiving and PITR), detailed in the Back up and restore MySQL guide, is the only protection against human error. Monitoring (Performance Schema, slow queries, alerts), detailed in the Monitor and maintain MySQL guide, detects a detached replica before the switchover becomes impossible.

Prerequisites

• Two servers with MySQL 8.4 installed (same version), see the installation guide
• A network allowing a TCP connection between the two nodes (port 3306)
• MySQL root access on both servers
• The basics of the configuration guide (in particular the binary log section and the parameters that weigh on replication)

Environment of this guide's examples (the lab at the end of the page uses db1.lab and db2.lab):

Role	Hostname	IP	OS	MySQL
Source	mysql-source	192.168.122.71	Debian 12	8.4 LTS
Replica	mysql-replica	192.168.122.72	Debian 12	8.4 LTS

Swipe to see more

Replication is not a backup

This is the most expensive confusion in database administration, and it always gets paid at the worst moment. A replica faithfully reproduces the state of the source, including its destructions: it protects against failing hardware, never against the one command too many.

Replication ≠ backup

Replication propagates everything from the source to the replica: the INSERTs but also the DELETEs, the DROP TABLEs and human errors. A DROP DATABASE on the source is replicated immediately.

To protect yourself against human errors and corruption, you need backups with binlog archiving and PITR. See the backup and restore guide.

The three MySQL replication models

MySQL offers three replication models, from the simplest to the most resilient.

Classic replication (source → replica, asynchronous)

The most common model. The source writes the changes into the binary log. The replica reads this binary log, copies it into its local relay log, then applies it.

The source does not wait for the replica's confirmation before validating a COMMIT: this is asynchronous replication. Simple and fast, but a few transactions may be lost if the source crashes before the replica has received the latest binlogs.

Semi-synchronous (rpl_semi_sync plugin)

The source waits for the replica's confirmation before returning the COMMIT to the client. It guarantees that at least one replica has received the data, almost zero RPO. In exchange, each COMMIT has an extra network latency.

Group Replication (Paxos consensus, multi-primary)

Several MySQL nodes form a group that maintains consistency through distributed consensus (Paxos protocol). Each node can accept writes (multi-primary) or only one can (single-primary). Failure detection and failover are automatic.

When to use which

The choice comes down to two quantities: the RPO (Recovery Point Objective, the amount of data you accept to lose) and the RTO (Recovery Time Objective, the time you accept to stay unavailable). Each gain on one is paid for in write latency or in operational complexity.

Criterion	Asynchronous	Semi-synchronous	Group Replication
Complexity	Simple	Moderate	High
RPO	A few transactions	Almost zero	Zero (consensus)
RTO	Manual (failover)	Manual	Automatic
COMMIT latency	No impact	+1 network round trip	+consensus
Number of nodes	2+	2+	3+ (odd number recommended)
Use case	Distributed reads, backup	Critical data, finance	Automated high availability

Swipe to see more

Rule of thumb: start with asynchronous GTID replication. Move to semi-synchronous for critical data, and to Group Replication when automatic failover is indispensable.

GTID: the essential building block

Before GTIDs, a replica located itself with a binlog file and a byte position, a coordinate valid only on the server that produced it. The slightest switchover then forced you to manually recompute where to resume on the new source, a delicate and deceptively simple operation. GTIDs replace this local coordinate with a global identifier that all nodes share, which makes repositioning automatic.

Principle: global transaction identifier

A GTID (Global Transaction Identifier) uniquely identifies each transaction committed on a MySQL server. The format is source_uuid:transaction_id:

3E11FA47-71CA-11E1-9E33-C80AA9429562:23

• source_uuid: unique identifier of the server that created the transaction (the server_uuid variable)
• transaction_id: sequential number incremented at each COMMIT

The set of GTIDs executed on a server is called the GTID set (visible in gtid_executed). During replication, the replica knows exactly which transactions it has already applied, and it never receives a duplicate.

Tagged GTIDs in MySQL 8.4

MySQL 8.4 introduces tagged GTIDs in the UUID:TAG:NUMBER format. Tags let you categorize transactions (system vs application operations) and simplify filtering in multi-source scenarios. This feature is optional and does not change the default behaviour.

Advantages vs binlog position replication

The comparison below matters most on the day of the incident: it is at failover time that the gap between the two methods becomes concrete. Position-based replication remains supported for compatibility, but no new deployment should choose it.

Criterion	Binlog position (old)	GTID (recommended)
Failover	Recompute the position on the new source, complex and fragile	The replica automatically finds its position through the GTID set
Resume point	Depends on the file + offset in the binlog	Global identifier, independent of the file
Multi-source	Very complex	Handled natively (each source has its UUID)
Duplicate detection	Impossible	The replica refuses GTIDs already applied
Recommendation	Legacy, for compatibility	Modern standard, recommended

Swipe to see more

Configure the source

Configuring the source takes four steps: server parameters, GTID, dedicated role and network authorization.

Enable the binary log and GTID

Edit /etc/mysql/mysql.conf.d/mysqld.cnf on the source:

[mysqld]
# Unique server identifier: MUST be different on each node
server_id = 1

# Binary log (enabled by default in 8.4, but explicit naming)
log_bin = /var/lib/mysql/binlog

# GTID mandatory
gtid_mode = ON
enforce_gtid_consistency = ON

# ROW format for consistency (default in 8.4)
binlog_format = ROW

# Maximum durability
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1

# Binlog retention (30 days)
binlog_expire_logs_seconds = 2592000

Restart MySQL:

sudo systemctl restart mysql

Check:

SHOW VARIABLES LIKE 'gtid_mode';
SHOW VARIABLES LIKE 'enforce_gtid_consistency';
SHOW VARIABLES LIKE 'server_id';

+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| gtid_mode                | ON    |
+--------------------------+-------+

+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| enforce_gtid_consistency | ON    |
+--------------------------+-------+

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1     |
+---------------+-------+

Unique, non-zero server_id

Each server in the replication cluster must have a unique server_id greater than 0. If two servers share the same ID, replication does not work. Convention: 1 for the source, 2 for the first replica, 3 for the second, and so on.

Why does SET GLOBAL gtid_mode = ON fail on a running server?

Setting gtid_mode = ON in the configuration file is correct on a server that is starting: it is born directly in GTID mode. On a server already in service, SET GLOBAL gtid_mode = ON is refused, because MySQL requires going through intermediate states. A replay on a two-machine lab (MySQL 8.4) recorded the following error:

ERROR 1788 (HY000): The value of @@GLOBAL.GTID_MODE can only be changed
one step at a time: OFF <-> OFF_PERMISSIVE <-> ON_PERMISSIVE <-> ON.
Also note that this value must be stepped up or down simultaneously on
all servers.

The message says two things. First, gtid_mode moves one notch at a time: OFF, OFF_PERMISSIVE, ON_PERMISSIVE, then ON. The two PERMISSIVE states tolerate transactions with GTIDs and anonymous transactions (without a GTID) at the same time, which gives the old ones time to finish. Second, each notch must be crossed on all the servers of the topology: a source in ON facing a replica in OFF no longer understand each other.

The online sequence, as described by the MySQL 8.4 documentation ("Enabling GTID Transactions Online", consulted on 2026-10-20, not replayed here), starts from gtid_mode = OFF on all servers. enforce_gtid_consistency, which refuses queries incompatible with GTIDs, must first be moved to ON, going through WARN to spot these queries without blocking them. Each statement is played on each server, and all of them must have finished it before moving on to the next:

SET GLOBAL enforce_gtid_consistency = WARN;
-- let the normal workload run and read the error log:
-- fix the application as long as warnings appear
SET GLOBAL enforce_gtid_consistency = ON;
SET GLOBAL gtid_mode = OFF_PERMISSIVE;
SET GLOBAL gtid_mode = ON_PERMISSIVE;
SHOW STATUS LIKE 'Ongoing_anonymous_transaction_count';  -- wait for 0 on every server

On a replica, the value can go back above 0 after reaching 0: the documentation requires that it has reached 0 at least once. You must then wait until all the anonymous transactions produced so far are replicated everywhere, without stopping writes. If the binlogs serve something other than replication (point-in-time restore), also wait until you no longer need those that contain transactions without a GTID: after the last notch, they are no longer usable. Only then:

SET GLOBAL gtid_mode = ON;

For the setting to be durable, the documentation asks you to add gtid-mode=ON and enforce-gtid-consistency=ON to the configuration file of each server. For automatic repositioning, you must also, on each replica and each channel, run STOP REPLICA, CHANGE REPLICATION SOURCE TO SOURCE_AUTO_POSITION = 1, then START REPLICA.

This path is reserved for converting an existing topology. For a lab or a new installation, it is better to set both variables in the configuration file before the first startup, as above.

Create a dedicated REPLICATION SLAVE role

Do not use the root account for replication. Create a dedicated role with the only privileges required:

CREATE USER 'replicator'@'192.168.122.72' IDENTIFIED BY 'strong_replication_password';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'192.168.122.72';

The REPLICATION SLAVE privilege only allows reading the binary log, not accessing the data.

Credentials stored in cleartext on the replica

The replication account's credentials are stored in cleartext in the replication metadata repository on the replica side (mysql.slave_master_info). Create a dedicated account with the strict minimum of privileges and protect the connection with TLS in production.

For a reader coming from PostgreSQL, the correspondence is as follows. In PostgreSQL, the replication role has the REPLICATION attribute and the connection is authorized through a replication line in pg_hba.conf. In MySQL, the REPLICATION SLAVE privilege plays the same role, and the network authorization goes through the @host in CREATE USER.

Allow the replica to connect

If bind_address is 127.0.0.1, the replica cannot connect. Edit the source:

[mysqld]
bind_address = 0.0.0.0

After restarting, check that MySQL listens on all interfaces:

ss -tlnp | grep 3306

LISTEN  0  151  0.0.0.0:3306  0.0.0.0:*  users:(("mysqld",pid=1234,fd=33))

Initialize the replica

A replica cannot catch up with a source starting from an empty database: you must first give it a consistent copy of the data, then tell it from which point to resume. This initial copy is made with the clone plugin, a dump or a storage snapshot, and it is the longest step of the setup. Order matters: the server configuration first (it requires a restart), then the copy, then START REPLICA, and only afterwards the read-only lock.

The replay on the two-machine lab (MySQL 8.4) shows what happens without this copy, on a source whose databases existed before GTIDs were enabled: the replica connects, then its applier stops on error 1049, whose message ends with Unknown database '<database name>' (the name is that of the database missing from the replica), as soon as the source sends it a write on a database it never created. The reason lies in the transactions executed before GTIDs were enabled: they are anonymous, without an identifier, and do not transfer under automatic positioning (SOURCE_AUTO_POSITION = 1). Only a copy of the data carries them.

Configure the replica

Edit /etc/mysql/mysql.conf.d/mysqld.cnf on the replica:

[mysqld]
# Unique identifier, different from the source
server_id = 2

# GTID mandatory
gtid_mode = ON
enforce_gtid_consistency = ON

# read_only and super_read_only are NOT set here: see below,
# they are set after cloning (section "START REPLICA and verification")

# Binary log enabled (useful for a replication chain or failover)
log_bin = /var/lib/mysql/binlog

# Relay log
relay_log = /var/lib/mysql/relay-bin

# Multi-threaded applier (speeds up catch-up)
replica_parallel_workers = 4
replica_parallel_type = LOGICAL_CLOCK
replica_preserve_commit_order = ON

Restart MySQL on the replica:

sudo systemctl restart mysql

The file deliberately contains neither read_only nor super_read_only. A replica locked in read-only mode refuses the statements that the next steps need: INSTALL PLUGIN, CREATE USER and GRANT write into MySQL's system tables, and super_read_only refuses them even to root, with the error 1290 detailed in the "read_only and super_read_only" section below. Measured on MySQL 8.4.11 (official Docker image): all three fail this way, and succeed once super_read_only is put back to OFF. The lock is therefore set last, once the cloning and START REPLICA are done.

Clone plugin: simplified hot copy

The clone plugin (built into MySQL 8.0+) copies the entire data directory from the source to the replica; it is the MySQL equivalent of pg_basebackup -R for PostgreSQL.

1. Install the plugin on the source AND the replica:

   INSTALL PLUGIN clone SONAME 'mysql_clone.so';

2. Create a clone user on the source (donor):

   CREATE USER 'clone_user'@'192.168.122.72' IDENTIFIED BY 'clone_password';
   GRANT BACKUP_ADMIN ON *.* TO 'clone_user'@'192.168.122.72';

3. Grant CLONE_ADMIN to the account that will run the cloning on the replica (recipient):

   -- On the replica
   GRANT CLONE_ADMIN ON *.* TO 'root'@'localhost';

4. Configure the donor on the replica:

   SET GLOBAL clone_valid_donor_list = '192.168.122.71:3306';

   Cloning erases the recipient's data

   Remote cloning deletes the user data and the binary logs of the recipient before the copy. The recipient server restarts automatically at the end of the operation. Run this command only on a server you are ready to reinitialize.

5. Launch the cloning from the replica:

   CLONE INSTANCE FROM 'clone_user'@'192.168.122.71':3306
   IDENTIFIED BY 'clone_password';

   The replica restarts automatically with the source's data.

This sequence follows the MySQL 8.4 documentation: CLONE INSTANCE requires two servers and was not replayed for this guide. The cloning also copies the source's system tables: check the replica's accounts (notably root) when it comes back, before relying on them. This point was not measured either.

Alternative: mysqldump or MySQL Shell

If the clone plugin is not available, initialize the replica with mysqldump (loading the dump also writes: the replica must therefore not yet be in read-only mode):

mysqldump -u root -p --all-databases --single-transaction \
  --routines --triggers --events --set-gtid-purged=ON \
  -h 192.168.122.71 > /tmp/source_dump.sql

mysql -u root -p < /tmp/source_dump.sql

Or with MySQL Shell util.dumpInstance() / util.loadDump() for a parallel transfer.

CHANGE REPLICATION SOURCE TO with GTID

Once the data is synchronized (clone, dump or storage snapshot), configure replication on the replica, which at this stage remains unlocked (no read_only yet), whichever bootstrap path you chose:

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST = '192.168.122.71',
  SOURCE_PORT = 3306,
  SOURCE_USER = 'replicator',
  SOURCE_PASSWORD = 'strong_replication_password',
  SOURCE_AUTO_POSITION = 1,
  GET_SOURCE_PUBLIC_KEY = 1;

Option	Role
SOURCE_HOST	Address of the source
SOURCE_USER	Replication role
SOURCE_AUTO_POSITION = 1	Enables automatic repositioning through GTIDs, no need to specify the binlog file and offset
GET_SOURCE_PUBLIC_KEY = 1	Fetches the source's public key for caching_sha2_password

Swipe to see more

GET_SOURCE_PUBLIC_KEY vs TLS

GET_SOURCE_PUBLIC_KEY = 1 fetches the public key on the fly from the source, which exposes the first connection to a man-in-the-middle attack: an attacker on the network can substitute their own key. It is therefore reserved for labs. In production, there are two remedies. The first is to supply the public key locally with SOURCE_PUBLIC_KEY_PATH (a PEM file copied to the replica). The second is a TLS connection that verifies the source's identity (the setup of certificates is described in the Secure MySQL guide).

For this second remedy, SOURCE_SSL = 1 is not enough. According to the documentation of CHANGE REPLICATION SOURCE TO, it encrypts the channel (equivalent to --ssl-mode=REQUIRED) but does not verify the identity of the source: an intermediary presenting any certificate is accepted. To rule out the man-in-the-middle, add SOURCE_SSL_CA (the certificate of the authority that signed the source's) and SOURCE_SSL_VERIFY_SERVER_CERT = 1. These options are changed with the replica stopped (STOP REPLICA).

Without TLS or a public key (fetched or supplied), the channel does not come up, and the symptom misleads. The replicator account uses the default plugin of MySQL 8.4, caching_sha2_password, which refuses to transmit a password over a connection without TLS as long as the replica does not have the source's public key. The replay on the two-machine lab shows a channel stuck in CONNECTING, with error 2061 (Authentication requires secure connection), retried every 60 seconds. This is not a wrong password: fixing it will change nothing. The full diagnosis is in the troubleshooting section below.

These commands no longer exist in 8.4

The CHANGE MASTER TO, SHOW SLAVE STATUS, START SLAVE and STOP SLAVE statements were deprecated in MySQL 8.0.22, then removed in 8.4. They no longer emit a warning: they return a syntax error. Oracle's documentation puts it plainly: "Attempting to use any of these statements now produces a syntax error."

The replacements are CHANGE REPLICATION SOURCE TO, SHOW REPLICA STATUS, START REPLICA and STOP REPLICA, and the options follow the same move: MASTER_HOST becomes SOURCE_HOST, MASTER_USER becomes SOURCE_USER. That is the syntax this page uses throughout.

The trap lies elsewhere: most online tutorials date back to 5.7 or 8.0 and show the old syntax, which 8.4 rejects outright. The REPLICATION SLAVE privilege, however, keeps its name and remains the one you grant to the replication account.

START REPLICA and verification

START REPLICA starts the two replication threads: the one that fetches the binlogs and the one that applies them. The command returns immediately, without saying whether the connection to the source succeeded, hence the verification that follows.

START REPLICA;

Immediate check:

SHOW REPLICA STATUS\G

*************************** 1. row ***************************
             Replica_IO_Running: Yes
            Replica_SQL_Running: Yes
              Source_Log_File: binlog.000003
          Read_Source_Log_Pos: 1234
               Relay_Log_File: relay-bin.000002
          Exec_Source_Log_Pos: 1234
        Seconds_Behind_Source: 0
           Retrieved_Gtid_Set: 3e11fa47-71ca-11e1-9e33-c80aa9429562:1-23
            Executed_Gtid_Set: 3e11fa47-71ca-11e1-9e33-c80aa9429562:1-23

The three critical indicators:

• Replica_IO_Running: Yes, the I/O thread receives the binlogs from the source
• Replica_SQL_Running: Yes, the SQL thread applies the events
• Seconds_Behind_Source: 0, the replica is synchronized

The replica is now running, and it is time to lock it in read-only mode. The replication applier is not an SQL client: it keeps writing despite super_read_only. Set the setting on the fly first, then add it to the replica's mysqld.cnf so that it survives a restart:

SET GLOBAL read_only = ON;
SET GLOBAL super_read_only = ON;

[mysqld]
read_only = ON
super_read_only = ON

To then run a CREATE USER or a GRANT on this replica, disable it with SET GLOBAL super_read_only = OFF, run the statement, then re-enable it with ON. Do not leave the lock lifted: an unlocked replica accepts accidental writes. The details of both settings, and of the 1290 error they cause, are in the "read_only and super_read_only" section.

Check the replication

A working replication is not something you observe at startup, it is something you monitor continuously (the Monitor and maintain MySQL guide details Performance Schema for the whole server). Three questions sum up the state of health: are the threads running, does the replica have any delay, and do any errors come from the connection or from applying the transactions? The commands below answer them, from the quickest to the most precise.

SHOW REPLICA STATUS: the key columns

SHOW REPLICA STATUS\G contains more than 60 columns. Here are the essential ones:

Column	Meaning
Replica_IO_Running	Yes = I/O thread active (reading the source's binlog)
Replica_SQL_Running	Yes = SQL thread active (applying the relay log)
Seconds_Behind_Source	Lag in seconds (0 = synchronized)
Retrieved_Gtid_Set	GTIDs received from the source
Executed_Gtid_Set	GTIDs applied on the replica
Last_IO_Error	Last error of the I/O thread
Last_SQL_Error	Last error of the SQL thread

Swipe to see more

performance_schema.replication_*

For more structured monitoring, use the performance_schema tables:

-- State of the replication connections
SELECT CHANNEL_NAME, SERVICE_STATE, SOURCE_UUID,
       LAST_ERROR_NUMBER, LAST_ERROR_MESSAGE
FROM performance_schema.replication_connection_status;

-- Overall state of the applier
SELECT CHANNEL_NAME, SERVICE_STATE
FROM performance_schema.replication_applier_status;

-- State of the appliers (parallel threads)
SELECT CHANNEL_NAME, WORKER_ID, SERVICE_STATE,
       LAST_APPLIED_TRANSACTION, APPLYING_TRANSACTION
FROM performance_schema.replication_applier_status_by_worker;

SHOW REPLICA STATUS is a display meant for the eye, not a data source: it parses badly and mixes the state of the connection with that of the application. The performance_schema tables can be filtered, joined and exported to a monitoring system. Above all, both count: replication_connection_status tells whether the channel receives the transactions, replication_applier_status tells whether they are applied. A replay on the two-machine lab (MySQL 8.4) recalls the trap: a connected channel whose applier is stopped falls behind indefinitely, without any error appearing. An alert that only watches the connection sees nothing.

Compute the lag

Seconds_Behind_Source is the most common metric but it can be imprecise (it measures the delta between the event's timestamp and local time). For a more reliable calculation, count the transactions received but not yet applied:

-- Compare received vs executed GTIDs, for the default channel
SELECT
  GTID_SUBTRACT(
    (SELECT Received_transaction_set
     FROM performance_schema.replication_connection_status
     WHERE CHANNEL_NAME = ''),
    @@global.gtid_executed
  ) AS gtid_lag;

If the result is empty, the replica has applied every GTID received: zero lag. The CHANNEL_NAME = '' filter designates the default channel, the one this guide creates. Without it, the subquery would return one row per replication channel and would fail as soon as the replica has several (error 1242, "Subquery returns more than 1 row"). For a multi-source replica, run the query once per channel, replacing '' with its name.

Multi-threaded applier (replica_parallel_workers)

In MySQL 8.4, replicas are multi-threaded by default. The replica_parallel_workers parameter is 4, with replica_preserve_commit_order = ON.

To increase the catch-up capacity on a heavily loaded replica:

STOP REPLICA;
SET PERSIST replica_parallel_workers = 8;
SET PERSIST replica_preserve_commit_order = ON;
START REPLICA;

Parameter	8.4 default	Role
replica_parallel_workers	4	Number of applier threads (0 = single-threaded)
replica_parallel_type	LOGICAL_CLOCK	Parallelizes by group of transactions committed at the same time on the source
replica_preserve_commit_order	ON	Guarantees that the commit order is respected even in parallel

Swipe to see more

Test the replication in real time

On the source, insert a row:

INSERT INTO lab_mysql.clients (nom, email, ville)
VALUES ('Test Replication', 'repl@example.com', 'Lyon');

On the replica, check immediately:

SELECT nom, email FROM lab_mysql.clients WHERE nom = 'Test Replication';

+------------------+------------------+
| nom              | email            |
+------------------+------------------+
| Test Replication | repl@example.com |
+------------------+------------------+

The row is visible almost instantly.

Reads on the replica

Offloading reads to the replica is the most immediate benefit of replication, and the easiest to implement badly. Two precautions are needed: lock the replica in read-only mode so that no accidental write creates a divergence, and accept that the data there will sometimes be a few moments behind.

read_only and super_read_only

The replica is locked in read-only mode through two parameters, read_only and super_read_only, set once replication has started (see the end of "START REPLICA and verification"):

SHOW VARIABLES LIKE '%read_only%';

+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| read_only             | ON    |
| super_read_only       | ON    |
+-----------------------+-------+

• read_only = ON: blocks writes for normal users
• super_read_only = ON: also blocks writes for accounts with SUPER or CONNECTION_ADMIN, recommended to prevent accidental writes by a DBA

The difference shows in use. With read_only alone, an all-powerful account (root) can still write to the replica, and unknowingly creates a divergence with the source. With super_read_only, the same account is refused, as shown by this INSERT run as root on the replica:

INSERT INTO lab_mysql.clients (nom, email, ville)
VALUES ('Test', 'test@example.com', 'Paris');

ERROR 1290 (HY000): The MySQL server is running with the
--super-read-only option so it cannot execute this statement

This lock does not hinder replication itself: the applier is not an SQL client and writes despite super_read_only. The replay on the two-machine lab (MySQL 8.4) measured it: the INSERT refused to root on the replica, then the same kind of row inserted on the source, which reaches the replica in about 1 second, with the applier ON and the last error at 0. The two settings therefore do not contradict each other.

Connect applications for reading

To distribute reads, configure your applications to send writes to the source and reads to the replica:

• At the application level: two connections (source for write, replica for read)
• With a proxy: ProxySQL or MySQL Router does the routing automatically depending on the type of query

Mind the lag

Right after a write on the source, the data may not yet be visible on the replica (asynchronous lag). For reads that must see fresh data ("read-your-writes"), read from the source.

Manual failover

Failover consists of promoting the replica to the new source when the old source is unavailable.

Failover requires reconfiguration

Unlike PostgreSQL (pg_promote() in one command), MySQL failover requires several steps: stop replication, disable read-only, reconfigure the applications. The procedure is more manual but GTID considerably simplifies repositioning.

Check the state before the failover

Before promoting, make sure the replica is synchronized:

SHOW REPLICA STATUS\G

Check:

• Replica_IO_Running: Yes and Replica_SQL_Running: Yes
• Seconds_Behind_Source: 0
• Retrieved_Gtid_Set = Executed_Gtid_Set (all received GTIDs are applied)

Promote the replica

The promotion is irreversible in practice: once writes are accepted on the old replica, the two servers diverge and the old source will have to be rebuilt. Start this sequence only after confirming that the old source is really out of service, or you risk ending up with two servers accepting writes.

1. Stop replication:

   STOP REPLICA;

2. Remove the replication configuration:

   RESET REPLICA ALL;

   RESET REPLICA ALL erases all the source configuration (host, user, etc.). The replica forgets it was a replica.

   Important: with GTID enabled, RESET REPLICA ALL does not reset the GTID history (gtid_executed, gtid_purged). This is the desired behaviour for failover: the new source keeps the full history of transactions.

3. Disable read-only mode:

   SET GLOBAL read_only = OFF;
   SET GLOBAL super_read_only = OFF;
   SET PERSIST read_only = OFF;
   SET PERSIST super_read_only = OFF;

4. Check that writes work:

   INSERT INTO lab_mysql.clients (nom, email, ville)
   VALUES ('After failover', 'failover@example.com', 'Marseille');

   Query OK, 1 row affected

5. Reconfigure the applications to point to the new source (192.168.122.72).

Rebuild the old source as a new replica

Once the old source is available again, rebuild it as a replica of the new source:

1. Reinitialize the data (clone plugin or dump from the new source):

   -- On the old source
   SET GLOBAL clone_valid_donor_list = '192.168.122.72:3306';
   CLONE INSTANCE FROM 'clone_user'@'192.168.122.72':3306
   IDENTIFIED BY 'clone_password';

2. Configure replication towards the new source:

   CHANGE REPLICATION SOURCE TO
     SOURCE_HOST = '192.168.122.72',
     SOURCE_PORT = 3306,
     SOURCE_USER = 'replicator',
     SOURCE_PASSWORD = 'strong_replication_password',
     SOURCE_AUTO_POSITION = 1,
     GET_SOURCE_PUBLIC_KEY = 1;
   
   START REPLICA;

   GET_SOURCE_PUBLIC_KEY = 1 is necessary for the same reason as in the first setup (error 2061 without TLS or a public key), and remains reserved for labs: in production, replace it with SOURCE_PUBLIC_KEY_PATH or with a TLS that verifies the source's certificate.

3. Check:

   SHOW REPLICA STATUS\G
   -- Replica_IO_Running: Yes
   -- Replica_SQL_Running: Yes

   Once replication is verified, set the read_only and super_read_only lock again, as at the end of the "START REPLICA and verification" section: the old source had none, and it becomes a replica.

Semi-synchronous replication

The semi-synchronous mode is the compromise between asynchronous replication, fast but able to lose the latest transactions, and a full consensus, safe but heavy. It is enabled by a plugin to install on both sides, without changing the topology or the replication commands already in place. The price to pay is an extra latency on each COMMIT.

Principle: the COMMIT waits for the replica's ACK

In semi-synchronous mode, the source returns the COMMIT to the client only when at least one replica has confirmed that it received the binlog event and wrote it into its relay log. This guarantees that the data exists on at least two servers before confirmation, almost zero RPO.

Enable the rpl_semi_sync_source / replica plugins

Order matters: the plugin is installed and enabled first on the source, then on the replica. The most often forgotten point is restarting the I/O thread on the replica side, without which the existing connection stays asynchronous despite the activation.

1. On the source:

   INSTALL PLUGIN rpl_semi_sync_source SONAME 'semisync_source.so';
   SET GLOBAL rpl_semi_sync_source_enabled = ON;
   SET PERSIST rpl_semi_sync_source_enabled = ON;

2. On the replica:

   INSTALL PLUGIN rpl_semi_sync_replica SONAME 'semisync_replica.so';
   SET GLOBAL rpl_semi_sync_replica_enabled = ON;
   SET PERSIST rpl_semi_sync_replica_enabled = ON;
   
   -- Restart the I/O thread to enable semi-sync
   STOP REPLICA IO_THREAD;
   START REPLICA IO_THREAD;

3. Check on the source:

   SHOW STATUS LIKE 'Rpl_semi_sync_source_status';

   +-----------------------------+-------+
   | Variable_name               | Value |
   +-----------------------------+-------+
   | Rpl_semi_sync_source_status | ON    |
   +-----------------------------+-------+

Trade-off: latency vs data guarantee

Three parameters are enough to tune this trade-off. They determine how long the source waits, how many replicas must confirm, and at which exact moment of the transaction the wait takes place.

Parameter	Default	Role
rpl_semi_sync_source_timeout	10000 ms	Wait time before falling back to asynchronous
rpl_semi_sync_source_wait_for_replica_count	1	Number of replicas that must confirm
rpl_semi_sync_source_wait_point	AFTER_SYNC	The COMMIT waits after the write to the binlog (before the InnoDB commit), loss-less semi-sync

Swipe to see more

Automatic fallback to asynchronous mode

If no replica confirms within the timeout, the source automatically switches to asynchronous mode so as not to block writes. It goes back to semi-synchronous as soon as a replica reconnects.

-- Check the number of fallbacks
SHOW STATUS LIKE 'Rpl_semi_sync_source_no_tx';
SHOW STATUS LIKE 'Rpl_semi_sync_source_yes_tx';

A single semi-synchronous replica = risk

If your only replica goes down, the source switches to asynchronous silently. Transactions are no longer replicated synchronously, and you lose the RPO=0 guarantee without any explicit notification. In production, use at least two semi-synchronous replicas.

Group Replication: an overview

Group Replication (GR) is the high availability solution built into MySQL. It provides distributed state machine replication with strong coordination between nodes. The group's communication engine, XCom, is a variant of Paxos. Failure detection and failover are automatic.

Single-primary vs multi-primary

The mode is chosen when the group is created and conditions everything else. In single-primary, the group itself designates the node that accepts writes and elects another if it disappears; in multi-primary, two concurrent transactions on the same row can be rejected at commit time, which the application must know how to handle.

Mode	Description	Use case
Single-primary	Only one node accepts writes, the others are read-only	The most common, the simplest, avoids conflicts
Multi-primary	All nodes accept writes	Requires conflict handling at the application level

Swipe to see more

Requirements

Group Replication imposes strict constraints, and they are not negotiable: the group simply refuses to start if one of them is missing. The most constraining in practice is the primary key required on every table, because it often forces you to modify an existing schema before any migration.

• InnoDB only, all tables must use InnoDB
• Primary key on every table (no table without a PK)
• GTID enabled (gtid_mode = ON, enforce_gtid_consistency = ON)
• Binary log in ROW format
• 3 nodes minimum (odd number recommended for quorum)
• Reliable network between the nodes (low latency)

group_replication_consistency in 8.4

MySQL 8.4 changes the default value of group_replication_consistency from EVENTUAL (in 8.0) to BEFORE_ON_PRIMARY_FAILOVER, a significant improvement in consistency after failover.

Value	Behaviour
EVENTUAL	Reads may see slightly stale data
BEFORE_ON_PRIMARY_FAILOVER	8.4 default, after a failover, the new primary waits for the pending transactions to be applied before accepting reads
BEFORE	Each read waits for all the group's transactions to be applied
AFTER	The COMMIT waits for all nodes to have applied the transaction
BEFORE_AND_AFTER	Combines BEFORE and AFTER, maximum consistency at the cost of latency

Swipe to see more

-- Default in 8.4, but can be adjusted if needed
SET PERSIST group_replication_consistency = 'BEFORE_ON_PRIMARY_FAILOVER';

InnoDB Cluster and InnoDB ReplicaSet: orchestration

Group Replication provides the mechanism, not the day-to-day operation: you still have to create the group, add nodes, and above all redirect the applications when the primary's role changes. This is what Oracle's two packaged solutions cover; they rest on the same building blocks but do not target the same level of requirements.

MySQL Shell + MySQL Router + Group Replication

InnoDB Cluster combines three components:

1. Group Replication, the underlying replication engine
2. MySQL Shell (mysqlsh), the administration tool to create and manage the cluster
3. MySQL Router, the proxy that automatically routes connections to the primary (writes) and the secondaries (reads)

Application
    │
    ▼
MySQL Router (port 6446 R/W, port 6447 R/O)
    │
    ├── Node 1 (Primary)    ← writes
    ├── Node 2 (Secondary)  ← reads
    └── Node 3 (Secondary)  ← reads

Creating a cluster with MySQL Shell:

// Connected to the first node
var cluster = dba.createCluster('myCluster');

// Add the other nodes
cluster.addInstance('root@192.168.122.72:3306');
cluster.addInstance('root@192.168.122.73:3306');

// Check the state
cluster.status();

InnoDB ReplicaSet: a simple alternative (asynchronous)

If Group Replication is too constraining, InnoDB ReplicaSet offers simplified orchestration for classic asynchronous replication through MySQL Shell:

var rs = dba.createReplicaSet('myReplicaSet');
rs.addInstance('root@192.168.122.72:3306');
rs.status();

// Failover
rs.setPrimaryInstance('root@192.168.122.72:3306');

When to choose Cluster vs ReplicaSet

The question to settle is that of the switchover: if it must happen without human intervention, you need the consensus of Group Replication and therefore InnoDB Cluster. If an operator can run a command, InnoDB ReplicaSet offers the tooling without the schema constraints or the three-node minimum.

Criterion	InnoDB Cluster	InnoDB ReplicaSet
Replication	Group Replication (consensus)	Classic asynchronous
Failover	Automatic	Semi-automatic (via mysqlsh)
Number of nodes	3+ (odd)	2+
Constraints	Primary key required, InnoDB only	Less constraining
Complexity	High	Moderate
Use case	Critical HA in production	Simple replication with orchestration

Swipe to see more

Third-party tools

Three projects keep coming up around MySQL replication, and they do not play the same role. Orchestrator decides on the switchover at the topology level; ProxySQL and MySQL Router sit in front of the servers to direct application connections. A complete architecture often combines two, a decider and a router.

Orchestrator (GitHub)

Orchestrator is an open source tool (developed by GitHub) for managing and automatically failing over MySQL replication topologies. It detects failures, rearranges the topology and performs the failover without human intervention.

ProxySQL

ProxySQL is a high-performance MySQL proxy that handles read/write routing, connection pooling, query caching and automatic failover. A popular alternative to MySQL Router.

MySQL Router

MySQL Router is Oracle's official proxy, optimized for InnoDB Cluster. It routes connections according to the role (R/W → primary, R/O → secondaries) and updates its configuration automatically when the topology changes.

Troubleshooting

Diagnosis always starts in the same place: SHOW REPLICA STATUS\G and the Last_IO_Error and Last_SQL_Error columns. The distinction between the two guides everything else, since an error of the I/O thread relates to the network, the credentials or vanished binlogs, whereas an error of the SQL thread signals a data conflict on the replica.

Symptom	Likely cause	Fix
Replica_IO_Running: No	Network connectivity, wrong credentials or duplicated server_id	Check with mysql -h source -u replicator -p, check that server_id is unique
Channel in CONNECTING, Last_IO_Error: error 2061 Authentication requires secure connection, retried every 60 s	The caching_sha2_password plugin (8.4 default) refuses a channel without TLS or a public key; the password is not at fault	Provide SOURCE_PUBLIC_KEY_PATH, or enable TLS (SOURCE_SSL = 1, with SOURCE_SSL_CA and SOURCE_SSL_VERIFY_SERVER_CERT = 1 to verify the source's identity), or GET_SOURCE_PUBLIC_KEY = 1 (labs only), then START REPLICA
ERROR 1788 on SET GLOBAL gtid_mode = ON	gtid_mode only changes one notch at a time, and on all servers at once	Follow OFF_PERMISSIVE then ON_PERMISSIVE then ON, or set the value in the configuration file before startup
Applier stopped on error 1049, Unknown database '<database name>'	The replica never received an initial copy of the source	Rebuild the replica with the clone plugin or a dump, then START REPLICA
Replica_IO_Running: Yes but Replica_SQL_Running: No, with no visible error in the connection	Healthy connection, applier stopped: the delay grows silently	Read Last_SQL_Error and performance_schema.replication_applier_status, not only replication_connection_status; if the stop is not intended, restart with START REPLICA SQL_THREAD
ERROR 1290 on an INSERT as root, or on a CREATE USER, a GRANT or an INSTALL PLUGIN during setup	super_read_only = ON: expected on a replica in service, but set too early if the bootstrap is not finished	Write on the source; for an administration statement on the replica, SET GLOBAL super_read_only = OFF, the statement, then ON. Replication itself is not blocked
Got fatal error from source when reading data	Binlog purged before the replica read it	Increase binlog_expire_logs_seconds or rebuild the replica (clone/dump)
Seconds_Behind_Source increases	The replica cannot keep up	Enable replica_parallel_workers, check the replica's disk IOPS
Duplicate entry for key 'PRIMARY'	Transaction already applied (missing GTID)	With GTID, the duplicate is normally avoided. Check gtid_executed on both nodes
Could not find first log file name in binary log index	The replica asks for a binlog that has been purged	Rebuild the replica with the clone plugin or a fresh dump
Errant GTID (errant transaction)	Transaction executed directly on the replica	Identify with GTID_SUBTRACT(replica_gtid, source_gtid). Inject an empty transaction on the source to fill the gap, or rebuild the replica
Constant semi-sync timeout	Slow network or overloaded replica	Check network latency, temporarily increase rpl_semi_sync_source_timeout, check IOPS
ERROR 3100 (HY000): The function is not allowed when Group Replication is running	Command incompatible with Group Replication	Use the MySQL Shell APIs (dba.getCluster()) instead of direct SQL commands

Swipe to see more

Put it into practice

The lab has you set up GTID replication between two servers, db1.lab (the source) and db2.lab (the replica), then prove it works. The replica has already received a copy of the appli database, as the section on initialization requires: you do not clone, you enable GTIDs, create the account, open the channel and lock the replica. Note that the lab brings up two virtual machines, so dsoxlab provision creates two of them: plan your host's resources accordingly.

The validation never reads SHOW REPLICA STATUS. It checks that gtid_mode and enforce_gtid_consistency are ON on both servers, that the replication account holds REPLICATION SLAVE without a global SELECT, then the state of the replica's performance_schema tables: the connection thread and the applier thread must both be ON, with no error. The central test then writes a row on the source and checks that it arrives on the replica within 30 seconds, because a replication that reports ON while carrying nothing does exist. Finally, the replica must refuse a direct write, even from an administration account (super_read_only), and a small report, /home/student/rapport-replication.txt, is checked against the real state of both servers.

Three points specific to the lab. The replication account must be named repl: the validation looks it up by that name, not replicator as in this guide's examples. It notes that the channel is connected, not how it authenticates: GET_SOURCE_PUBLIC_KEY = 1 is enough for the lab, but stays reserved for labs as the box above explains. Finally, from db2, the name db1.lab does not designate the source on this infrastructure: read its address on the source (ip -4 addr show) and use it in SOURCE_HOST.

Guided lab Practice

Learn how to do it

Replicate with GTID, and prove a row written on the source arrives

$ dsoxlab run mysql-07-repliquer

Environment: VM·Duration: ~50 min

How it is checked: the state of the system is checked after a reboot, not the command you typed.

First time? What DSOXLab is and how to install it

DSOXLab is the command line that prepares a disposable environment (an isolated shell or a VM), runs the lab scenario, then checks the real state of the system and computes a score. Nothing is installed or broken on your workstation.

It needs Python 3.11 or later, uv or pipx, and git. The installation guide details these prerequisites; on Windows, or on a Mac without Python, use the appliance instead.

This lab starts virtual machines: it also needs ansible-playbook, a hypervisor (KVM or Incus), Terraform and hardware virtualization (/dev/kvm), which inside a VirtualBox VM means nested virtualization. The first time, dsoxlab start mysql-07-repliquer checks all of it before creating the machines. Prepare your machine for vm labs.

Install the CLI and add the catalogue, once:

uv tool install dsoxlab   # or: pipx install dsoxlab
dsoxlab catalog add https://github.com/stephrobert/bdd-dsoxlab-training

Before you run the lab, check:

• DSOXLab installed
• catalogue available
• compatible environment (isolated shell or VM)

To check, nothing is probed from this page: run dsoxlab doctor. Read the diagnosis

Run this lab, then validate your work:

dsoxlab run mysql-07-repliquer
dsoxlab check mysql-07-repliquer

See every lab: dsoxlab list-labs. Track your progress: dsoxlab progress. The full dsoxlab guide covers scoring and the pitfalls.

Key takeaways

1. GTID replication is the standard in MySQL 8.4; it simplifies failover by uniquely identifying each transaction
2. Replication is not a backup: a DROP TABLE is replicated immediately
3. The clone plugin simplifies replica initialization by copying the full data directory; it is the MySQL equivalent of pg_basebackup
4. CHANGE REPLICATION SOURCE TO with SOURCE_AUTO_POSITION = 1 makes use of GTIDs, no need to compute the binlog position manually
5. read_only + super_read_only protect the replica against accidental writes, always enable both, but after the bootstrap (cloning and START REPLICA), otherwise INSTALL PLUGIN, CREATE USER and GRANT fail with 1290
6. The multi-threaded applier (replica_parallel_workers) significantly speeds up the replica's catch-up
7. Semi-synchronous replication guarantees an almost zero RPO but adds latency; with a single replica, the fallback to asynchronous is silent
8. Group Replication provides automatic failover through consensus and imposes strict constraints (InnoDB, primary key, 3+ nodes)
9. InnoDB Cluster = Group Replication + MySQL Shell + MySQL Router, Oracle's complete HA solution
10. In production, entrust the switchover decision to a tool (Orchestrator or InnoDB Cluster) and the routing of connections to MySQL Router or ProxySQL, rather than to a manual failover
11. gtid_mode is set in one go in the configuration file, but only goes from OFF to ON one notch at a time (ERROR 1788), on all servers at once
12. A channel stuck in CONNECTING with error 2061 is an authentication plugin problem (TLS or public key), not a password problem
13. SOURCE_SSL = 1 encrypts the channel without authenticating the source: against the man-in-the-middle, you also need SOURCE_SSL_CA and SOURCE_SSL_VERIFY_SERVER_CERT = 1, or SOURCE_PUBLIC_KEY_PATH
14. A replica always starts from a copy of the data, and performance_schema is read on two tables: connection and applier

Knowledge check

Check that you have the essentials of this guide. The questions cover only what has just been explained here.

Knowledge check

Check what you have learned with this interactive quiz

8 questions

8 min

70% to pass

How it works

• The timer starts when you click Start
• Multiple choice, true/false and short answer questions
• You can move between questions
• Detailed results are shown at the end

Start the quiz

Starts the quiz and the timer

×

See in the glossary →