Build a MySQL 8.4 source-replica pair with GTIDs and rehearse a manual failover

A replica is only useful in an emergency if you have already practised turning it into the source. This tutorial builds a two-server MySQL 8.4 replication pair with global transaction identifiers (GTIDs), copies the initial data with the clone plugin, and then walks through a complete failover by hand: the source goes down, the replica is promoted, the application is moved, and the old source rejoins as the new replica.

It is meant for administrators who already run MySQL and want a tested runbook rather than a theory lesson. Do it in a lab or on a staging pair first; the failover part deliberately stops a database server.

What you will build

• db1.example.com (10.0.0.11): the source at the start, the replica at the end.
• db2.example.com (10.0.0.12): the replica at the start, the source at the end.
• GTID-based asynchronous replication with SOURCE_AUTO_POSITION = 1, so neither server needs binary log file names or positions.
• A replica that is protected against accidental writes with super_read_only.
• A written and tested failover procedure: check, fence, drain, promote, re-point the application, and rejoin the old source safely.

Before you start

• Two Linux servers with the same MySQL 8.4 LTS release installed from the same repository (for example the MySQL APT or Yum repository), on the same OS and CPU architecture. The clone plugin requires the same server series and platform on both sides.
• Network: each server can reach the other on TCP 3306, and the application server (10.0.0.21 in the examples) can reach both.
• Distinct servers: if you built db2 by copying db1’s virtual machine, delete auto.cnf from db2’s data directory before first start, or both servers will have the same server_uuid, which breaks replication.
• Access: sudo on both hosts and the MySQL root account (or another account with full administrative privileges) on db1.
• Data: InnoDB tables only. The clone plugin copies only InnoDB data; MyISAM tables arrive empty.
• Time: about two hours, including the failover rehearsal.
• Knowledge: MySQL accounts and privileges, editing my.cnf, and how asynchronous replication works in general: the source writes changes to its binary log, the replica’s receiver thread copies them into relay logs and its applier thread executes them.

The configuration file is /etc/my.cnf on RHEL-family systems with the MySQL repository and /etc/mysql/mysql.conf.d/mysqld.cnf on Debian and Ubuntu. The service is called mysqld or mysql respectively; the examples use mysqld.

Step 1: Check that the two servers are compatible

Run this on both servers and compare:

SELECT @@hostname, @@server_id, @@server_uuid, @@version,
       @@character_set_server, @@collation_server,
       @@innodb_page_size, @@max_allowed_packet;

The version series (8.4), character set, collation and innodb_page_size must match, max_allowed_packet must be at least 2 MB on both for cloning, and server_uuid must differ. server_id will be set in the next step.

Step 2: Configure both servers for GTID replication

Every server in a replication topology needs a unique server_id. GTIDs require gtid_mode=ON together with enforce_gtid_consistency=ON, which rejects statements that cannot be logged safely as a single transaction. Binary logging is on by default in 8.4, and the replica keeps its own binary log too, so it is ready to become a source. The clone plugin is loaded at startup on both sides.

On db1, in the [mysqld] section:

[mysqld]
server_id = 1
log_bin = binlog
gtid_mode = ON
enforce_gtid_consistency = ON
plugin-load-add = mysql_clone.so

On db2:

[mysqld]
server_id = 2
log_bin = binlog
gtid_mode = ON
enforce_gtid_consistency = ON
plugin-load-add = mysql_clone.so
skip_replica_start = ON

skip_replica_start keeps replication from starting automatically after a restart, so you always decide when the replica connects. Restart both servers and verify:

sudo systemctl restart mysqld
mysql -u root -p -e "SELECT @@server_id, @@gtid_mode, @@enforce_gtid_consistency; SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME = 'clone';"

Expect the right server_id, ON for both GTID settings and clone ACTIVE.

Step 3: Create the accounts on the source

Create everything on db1 only. The clone in Step 4 copies the source’s system tables, so these accounts also appear on db2, which is exactly what you want after a failover.

CREATE USER 'repl'@'10.0.0.12' IDENTIFIED BY 'CHANGE_ME_REPL';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.0.12';

CREATE USER 'repl'@'10.0.0.11' IDENTIFIED BY 'CHANGE_ME_REPL';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.0.11';

CREATE USER 'clone_donor'@'10.0.0.12' IDENTIFIED BY 'CHANGE_ME_CLONE';
GRANT BACKUP_ADMIN ON *.* TO 'clone_donor'@'10.0.0.12';

CREATE DATABASE appdb;
CREATE USER 'appuser'@'10.0.0.21' IDENTIFIED BY 'CHANGE_ME_APP';
GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.* TO 'appuser'@'10.0.0.21';

CREATE TABLE appdb.heartbeat (
  id INT PRIMARY KEY,
  written_at DATETIME(3) NOT NULL,
  written_by VARCHAR(64) NOT NULL
);
INSERT INTO appdb.heartbeat VALUES (1, NOW(3), @@hostname);

• [email protected] is used by db2 to replicate from db1 now; [email protected] is used by db1 when it rejoins as a replica of db2 later.
• REPLICATION SLAVE is still the privilege name in 8.4, even though statements use the words source and replica.
• BACKUP_ADMIN lets the clone user read the data and block DDL on the donor during the copy.
• The heartbeat table gives you a row to update and compare during the tests.

New accounts use caching_sha2_password. The manual says a replica connecting with such an account needs either an encrypted connection or RSA key exchange for the password; Step 5 uses GET_SOURCE_PUBLIC_KEY = 1 for the lab, and you should prefer TLS when traffic leaves a trusted network.

Step 4: Copy the initial data with the clone plugin

The replica must start from the same data the source had at a known GTID set. The clone plugin takes a physical snapshot of all InnoDB data over the network and carries the source’s GTID set with it, which is faster than replaying a dump for anything but small databases.

Warning: a remote clone deletes all user-created schemas, tables and binary logs on the recipient and replaces them with the donor’s data. Run these statements only on db2, and confirm with SELECT @@hostname; first.

SELECT @@hostname;

CREATE USER 'clone_admin'@'localhost' IDENTIFIED BY 'CHANGE_ME_CLONE_ADMIN';
GRANT CLONE_ADMIN ON *.* TO 'clone_admin'@'localhost';
SET GLOBAL clone_valid_donor_list = '10.0.0.11:3306';

Then log in to db2 as clone_admin and start the clone:

mysql -u clone_admin -p -h localhost

CLONE INSTANCE FROM 'clone_donor'@'10.0.0.11':3306
  IDENTIFIED BY 'CHANGE_ME_CLONE';

When the copy finishes, the server restarts itself. That needs a process supervisor such as systemd; if db2 was started some other way, you get ERROR 3707 (HY000): Restart server failed (mysqld is not managed by supervisor process). The manual notes that this does not mean the clone failed: start the server by hand and continue.

After the restart, db2 has db1’s accounts, so log in with db1’s root password (the clone_admin account no longer exists). Check the result:

SELECT STATE, ERROR_NO, ERROR_MESSAGE FROM performance_schema.clone_status;
SELECT @@GLOBAL.gtid_executed;
SELECT * FROM appdb.heartbeat;

STATE should be Completed, gtid_executed should show db1’s UUID with a range of transactions, and the heartbeat row should be there. The clone does not copy configuration, so db2 keeps its own server_id and skip_replica_start.

Now make db2 read-only for everyone, including administrators. Replication threads may still write. SET PERSIST keeps the setting across restarts:

SET PERSIST super_read_only = ON;
SELECT @@read_only, @@super_read_only;

Setting super_read_only to ON also turns on read_only, so both columns show 1.

Step 5: Start replication

On db2, point the replica at db1 with GTID auto-positioning and start it:

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST = '10.0.0.11',
  SOURCE_PORT = 3306,
  SOURCE_USER = 'repl',
  SOURCE_PASSWORD = 'CHANGE_ME_REPL',
  SOURCE_AUTO_POSITION = 1,
  GET_SOURCE_PUBLIC_KEY = 1;

START REPLICA;

Because the clone carried db1’s GTID set, the replica asks the source only for transactions it does not have yet. Nothing has to be copied by file name or position.

Step 6: Check replication status

SHOW REPLICA STATUS\G

The fields to read every time:

• Replica_IO_Running: Yes: the receiver thread is connected to the source.
• Replica_SQL_Running: Yes: the applier thread is executing transactions.
• Last_IO_Error and Last_SQL_Error: empty. Connection and authentication problems show up in Last_IO_Error.
• Seconds_Behind_Source: 0 when caught up. The manual warns that it can show 0 briefly even when the network link has silently stalled, so do not rely on it alone.
• Retrieved_Gtid_Set and Executed_Gtid_Set: what the replica has received and what it has applied. When they cover the same transactions from db1’s UUID, the replica is in sync.
• Auto_Position: 1: GTID auto-positioning is in use.

Prove it end to end. On db1:

UPDATE appdb.heartbeat SET written_at = NOW(3), written_by = @@hostname WHERE id = 1;
SELECT @@GLOBAL.gtid_executed;

On db2, a moment later, the row shows the new timestamp, and gtid_executed matches db1’s. Also prove the protection works: on db2, as root, INSERT INTO appdb.heartbeat VALUES (2, NOW(3), 'test'); must fail with the message that the MySQL server is running with the --super-read-only option so it cannot execute this statement.

On db1, SHOW PROCESSLIST shows a thread whose Command starts with Binlog Dump for the connected replica.

Step 7: Simulate a failure of the source

Point the application at db1 (for example DB_HOST=db1.example.com in its configuration), let it write for a while, then stop the source the hard way:

# on db1
sudo systemctl stop mysqld

On db2, SHOW REPLICA STATUS\G now shows Replica_IO_Running: Connecting and a connection error in Last_IO_Error, while Replica_SQL_Running stays Yes. The application starts failing.

In a real incident the first decision is whether to fail over at all. If the source will be back in a few minutes after a reboot, waiting is often safer than promoting. Fail over when the source is gone for longer than your application can tolerate.

Step 8: Fence the old source and drain the replica

Before promoting, make sure the old source cannot come back by itself and accept writes; two writable servers with diverging data are far worse than a short outage. Depending on what failed, that means keeping mysqld stopped and disabled (sudo systemctl disable mysqld on db1), powering off the VM, or blocking port 3306 on db1 from the application network.

Then let db2 apply everything it has already received. Copy the Retrieved_Gtid_Set value from SHOW REPLICA STATUS and wait until it is executed. The function returns 0 when done and 1 after the timeout:

SELECT WAIT_FOR_EXECUTED_GTID_SET('<Retrieved_Gtid_Set value>', 60);

Transactions that db1 committed but never sent to db2 are lost with asynchronous replication. That is the price of this setup; semi-synchronous replication or Group Replication reduce it.

Step 9: Promote the replica

On db2, stop replication, remove its connection settings so it can never reconnect to the old source by accident, and allow writes:

STOP REPLICA;
RESET REPLICA ALL;
SET PERSIST super_read_only = OFF;
SET PERSIST read_only = OFF;
SELECT @@read_only, @@super_read_only;

Both settings are persisted explicitly, so the persisted super_read_only = ON from Step 4 cannot switch the server back to read-only at its next restart. Both columns show 0. RESET REPLICA ALL does not change gtid_executed: db2 keeps the full GTID history, which is what lets db1 rejoin later with auto-positioning. Do not run RESET BINARY LOGS AND GTIDS here.

Write a test row on the new source and look at the GTIDs: a second UUID, db2’s own, now appears in gtid_executed:

UPDATE appdb.heartbeat SET written_at = NOW(3), written_by = @@hostname WHERE id = 1;
SELECT @@GLOBAL.gtid_executed;

Step 10: Re-point the application

How you move the application depends on how it finds the database. Use the approach you will also use in production, so the rehearsal tests it:

• Configuration: change DB_HOST from db1 to db2 and restart or reload the application.
• DNS: point a name such as db.example.com at 10.0.0.12. Keep the TTL low ahead of time and remember that connection pools keep old connections until they are recycled.
• Virtual IP or proxy: move the address to db2 or change the backend in the proxy.

[email protected] already exists on db2 because it was cloned and replicated, so no account changes are needed. Check from the app server:

mysql -h db2.example.com -u appuser -p -e "SELECT @@hostname, @@read_only; SELECT * FROM appdb.heartbeat;"

Step 11: Bring the old source back as a replica

When db1 is repaired, it must come back read-only and must not start replication on its own. Add these lines to db1’s [mysqld] section before you start it:

[mysqld]
skip_replica_start = ON
super_read_only = ON

sudo systemctl enable --now mysqld

Next, check for errant transactions: anything db1 committed that db2 never received. Read db1’s set with SELECT @@GLOBAL.gtid_executed;, then on db2 subtract its own set from it:

SELECT GTID_SUBTRACT('<gtid_executed from db1>', @@GLOBAL.gtid_executed) AS only_on_db1;

If the result is empty, db1 is a strict subset of db2 and can safely replicate from it. If it is not empty, those transactions exist only on db1: inspect them in db1’s binary log and decide what to do with them, and rebuild db1 with a fresh clone from db2 rather than attaching it as it is.

With an empty result, on db1:

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST = '10.0.0.12',
  SOURCE_PORT = 3306,
  SOURCE_USER = 'repl',
  SOURCE_PASSWORD = 'CHANGE_ME_REPL',
  SOURCE_AUTO_POSITION = 1,
  GET_SOURCE_PUBLIC_KEY = 1;

START REPLICA;
SHOW REPLICA STATUS\G

db1 connects as [email protected], the account you created in Step 3, and fetches only db2’s new transactions. Both threads should report Yes, and the heartbeat row on db1 should show db2 as written_by.

Check that it works

1. On the current replica (db1), SHOW REPLICA STATUS\G shows Replica_IO_Running: Yes, Replica_SQL_Running: Yes, empty error fields and Auto_Position: 1.
2. On the current source (db2), SELECT @@read_only; returns 0; on db1 SELECT @@super_read_only; returns 1.
3. An UPDATE of the heartbeat row on db2 appears on db1 within a second or two.
4. SELECT GTID_SUBTRACT(@@GLOBAL.gtid_executed, '<db1 gtid_executed>'); on db2 returns an empty string once db1 has caught up.
5. The application writes to db2 and does not have any connection string that still points at db1.

If something goes wrong

• Replica_IO_Running: Connecting with an authentication error in Last_IO_Error. Usually the caching_sha2_password exchange: add GET_SOURCE_PUBLIC_KEY = 1 or configure TLS for the replication channel. Also check that the repl account exists for the replica’s exact IP address.
• The clone stops with ERROR 3707 (HY000): Restart server failed (mysqld is not managed by supervisor process). The data was cloned, but nothing restarted the server. Start it with systemctl start mysqld and check performance_schema.clone_status.
• The clone is refused before it starts. Check the prerequisites in this order: clone_valid_donor_list on db2 contains 10.0.0.11:3306, the plugin is ACTIVE on both servers, both run the same 8.4 series, and max_allowed_packet is at least 2 MB on both.
• Replica_SQL_Running: No with an error in Last_SQL_Error. Someone wrote to the replica directly, or the replica was built from inconsistent data. Find out what changed before skipping anything; rebuilding the replica with a fresh clone is often quicker and always safer than skipping transactions.
• GTID_SUBTRACT shows transactions that exist only on the old source. Do not attach it. Re-clone it from the new source, after saving its binary logs if those transactions might matter.
• Both servers have the same server_uuid. The VM was copied with its data directory. Stop the copy, delete auto.cnf in its data directory, start it again and re-check.

Clean up

To dissolve the pair and keep both servers as independent instances, run this on whichever server is currently the replica:

STOP REPLICA;
RESET REPLICA ALL;
SET PERSIST super_read_only = OFF;
SET PERSIST read_only = OFF;

Warning: the following statements delete the test database and accounts. Run them on the current source only (they replicate if the pair is still connected), and check with SELECT @@hostname; and SHOW DATABASES; first that appdb is the lab database and not a real one:

DROP DATABASE appdb;
DROP USER 'appuser'@'10.0.0.21', 'clone_donor'@'10.0.0.12',
          'repl'@'10.0.0.11', 'repl'@'10.0.0.12';

Finally remove plugin-load-add = mysql_clone.so, skip_replica_start and super_read_only from the configuration files you edited, delete the persisted settings with RESET PERSIST read_only; and RESET PERSIST super_read_only; where you set them, and restart both servers.

Next steps

• Encrypt the replication connection with TLS and set SOURCE_SSL = 1 plus certificate verification instead of relying on RSA key exchange.
• Monitor Replica_IO_Running, Replica_SQL_Running and lag on every replica, and alert when either thread stops.
• Keep separate backups: replication copies a mistaken DROP TABLE to the replica within seconds.
• Look at semi-synchronous replication to reduce the transactions you can lose in a failover.
• When you are comfortable with the manual procedure, evaluate MySQL InnoDB ReplicaSet or InnoDB Cluster with MySQL Router, which automate parts of it.

Official documentation

• MySQL 8.4: Setting up replication using GTIDs
• MySQL 8.4: Cloning remote data
• MySQL 8.4: Switching sources during failover