Lesson 21 advanced

Replication, High Availability and Upgrades

Scale reads and survive failures: how binary log replication works, GTIDs, setting up a source and replica with CHANGE REPLICATION SOURCE TO and the clone plugin, monitoring with SHOW REPLICA STATUS, replication lag and parallel appliers, semi-synchronous replication, read_only replicas, Group Replication, InnoDB Cluster and MySQL Router, and how to upgrade safely from 8.0 to 8.4 and 9.7 LTS.

One MySQL server is a single point of failure, and at some point it is also the limit for read traffic. Replication keeps copies of the data on other servers; on top of it, InnoDB Cluster adds automatic failover. This lesson also covers upgrades, because a replica is the safest way to do one.

How replication works

  1. The source writes every committed change to its binary log.
  2. Each replica has a receiver thread that streams those events into a local relay log.
  3. The replica’s applier threads replay the relay log, so its data follows the source.

Replication is asynchronous by default: the source commits without waiting for replicas, so a replica may be a little behind (replication lag). Replicas are normal servers: you can read from them, back them up and promote one if the source dies.

The binary log format is ROW by default, recording the changed rows themselves rather than the SQL that changed them, which makes replicas exact even with non-deterministic statements. Keep it.

GTIDs

A global transaction identifier (GTID) names every transaction uniquely: source_server_uuid:transaction_number, for example 3e11fa47-71ca-11e1-9e33-c80aa9429562:23. With GTIDs, a replica knows exactly which transactions it has applied, so reconnecting to a new source after a failover needs no file names or positions. Turn them on for every new setup:

[mysqld]
server_id                = 1          # unique per server
gtid_mode                = ON
enforce_gtid_consistency = ON

enforce_gtid_consistency rejects the few statements that cannot be logged safely with GTIDs, such as CREATE TABLE ... SELECT in older versions or mixing transactional and non-transactional tables in one transaction.

Setting up a replica

On the source, create an account for replication:

CREATE USER 'repl'@'10.0.%' IDENTIFIED BY 'a-long-random-secret' REQUIRE SSL;
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.%';

(The privilege keeps its historical name.)

Copy the data to the new replica. The clone plugin is the quickest way: on the replica, with clone installed on both servers and a donor account holding BACKUP_ADMIN:

SET GLOBAL clone_valid_donor_list = '10.0.0.5:3306';
CLONE INSTANCE FROM 'clone_user'@'10.0.0.5':3306 IDENTIFIED BY '...';

The replica restarts with an exact copy, including the GTIDs already applied. A dump restored with MySQL Shell or a physical backup works too.

Point the replica at the source and start it:

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST = '10.0.0.5',
  SOURCE_PORT = 3306,
  SOURCE_USER = 'repl',
  SOURCE_PASSWORD = 'a-long-random-secret',
  SOURCE_AUTO_POSITION = 1,
  SOURCE_SSL = 1;

START REPLICA;

SOURCE_AUTO_POSITION = 1 tells the replica to use GTIDs to find where to continue.

Note: MySQL 8.4 removed the old MASTER/SLAVE statements. CHANGE MASTER TO, START SLAVE and SHOW SLAVE STATUS are now syntax errors; use CHANGE REPLICATION SOURCE TO, START REPLICA and SHOW REPLICA STATUS. Update scripts and monitoring before you upgrade.

Make replicas refuse accidental writes:

SET PERSIST super_read_only = ON;   -- also implies read_only

Replication threads still apply changes; only clients are blocked.

Monitoring replication

SHOW REPLICA STATUS\G
*************************** 1. row ***************************
             Replica_IO_State: Waiting for source to send event
                  Source_Host: 10.0.0.5
           Replica_IO_Running: Yes
          Replica_SQL_Running: Yes
                   Last_Errno: 0
                   Last_Error:
        Seconds_Behind_Source: 0
           Retrieved_Gtid_Set: 3e11fa47-71ca-11e1-9e33-c80aa9429562:1-48213
            Executed_Gtid_Set: 3e11fa47-71ca-11e1-9e33-c80aa9429562:1-48213
                Auto_Position: 1
(output abridged)

The lines to check:

  • Replica_IO_Running and Replica_SQL_Running must both be Yes. A No comes with Last_IO_Error or Last_SQL_Error explaining why.
  • Seconds_Behind_Source is the lag estimate; alert when it stays above a threshold.
  • Retrieved_Gtid_Set versus Executed_Gtid_Set shows received versus applied transactions.

performance_schema.replication_applier_status_by_worker gives per-worker details and error history.

Fighting lag

  • Parallel apply: replicas use 4 applier threads by default (replica_parallel_workers), with commit order preserved. Raise the count for write-heavy sources.
  • Small transactions: a single DELETE of ten million rows replays as one huge event and stalls the replica. Batch large changes (lesson 6).
  • Primary keys everywhere: row-based replication finds rows by primary key; a table without one forces a scan per changed row on the replica.
  • Equal hardware: a replica that is much weaker than the source will fall behind under load.

Semi-synchronous replication

With asynchronous replication, a source that crashes may lose the last transactions that no replica received yet. Semi-synchronous replication makes the source wait, at commit, until at least one replica confirms it has received the transaction:

-- on the source
INSTALL PLUGIN rpl_semi_sync_source SONAME 'semisync_source.so';
SET PERSIST rpl_semi_sync_source_enabled = ON;
-- on each replica
INSTALL PLUGIN rpl_semi_sync_replica SONAME 'semisync_replica.so';
SET PERSIST rpl_semi_sync_replica_enabled = ON;

The cost is a network round trip on every commit. If no replica answers within rpl_semi_sync_source_timeout (10 seconds by default), the source falls back to asynchronous mode rather than stopping.

Group Replication and InnoDB Cluster

Classic replication does not fail over by itself; someone or some tool must promote a replica. Group Replication builds a group of usually three or more servers that agree on every transaction through a consensus protocol. If the primary fails, the others elect a new one automatically within seconds, without losing committed transactions.

InnoDB Cluster packages Group Replication with two tools:

  • MySQL Shell sets up and manages the cluster: dba.createCluster('orbit'), cluster.addInstance('10.0.0.6:3306'), cluster.status().
  • MySQL Router runs next to the application and sends connections to the current primary (one port for read-write) or to the secondaries (another port for read-only). After a failover the application keeps using the same address.
// in mysqlsh, connected to the first server
var cluster = dba.createCluster('orbit')
cluster.addInstance('admin@10.0.0.6:3306', {recoveryMethod: 'clone'})
cluster.addInstance('admin@10.0.0.7:3306', {recoveryMethod: 'clone'})
cluster.status()

Three members tolerate the loss of one; five tolerate two. InnoDB ClusterSet links clusters across data centres with asynchronous replication for disaster recovery, and InnoDB ReplicaSet offers managed classic replication with manual switchover for smaller setups. MySQL 9.7 improved cluster operations further, for example preferring the most up-to-date member when electing a new primary and automatically removing and rejoining unhealthy members.

Group Replication needs InnoDB tables with primary keys and works best on a low-latency network. Managed services (RDS Multi-AZ, Cloud SQL HA, HeatWave) provide similar failover without running it yourself.

Upgrading MySQL

Since MySQL 8.0.16 the server upgrades its own data dictionary and system tables when it starts on a newer version; there is no separate mysql_upgrade step any more. The work is in the preparation.

The path

FromToNotes
8.08.4 LTSSupported in place. 8.0 is end of life since April 2026
8.4 LTS9.7 LTSSupported in place: the next LTS
8.09.7Go through 8.4 first; skipping an LTS series is not supported
8.4.x8.4.yPatch updates within a series, any time
9.7 LTSnext LTS (calendar version)The next step after 9.7

Check before you upgrade

MySQL Shell’s upgrade checker inspects a running server for everything that would break on the target version:

$ mysqlsh root@localhost -- util check-for-server-upgrade --target-version=9.7.4

It reports accounts using mysql_native_password, removed system variables in your configuration, reserved words used as identifiers, foreign keys pointing at non-unique keys (rejected by default in 8.4), obsolete SQL modes and more. Fix every error and read every warning.

Also review:

  • my.cnf for removed options, such as default_authentication_plugin and expire_logs_days (gone in 8.4).
  • Application SQL and tools for MASTER/SLAVE statements.
  • Client libraries and connectors: update them first; new drivers work with old servers.

Upgrade with a replica

The low-risk way to upgrade production:

  1. Take and test a backup.
  2. Build a replica, upgrade it (stop, install new packages, start), and let it catch up. Replication from an older source to a newer replica is supported.
  3. Point read traffic or a test environment at it and watch for errors and slow queries.
  4. Switch over: stop writes briefly, let the replica catch up, promote it, and point the application (or MySQL Router) at it.
  5. Upgrade or rebuild the old source as a replica of the new one.

If something goes wrong before step 4, you simply discard the replica. Downgrading a major version in place is not supported; going back needs a logical dump and reload, which is why testing before the switchover matters.

On package-managed servers, pin the LTS track in the repository configuration so routine apt upgrade or dnf update runs only install patch releases. A repository update in 2026 moved some 8.4 servers to 9.7 unexpectedly; check which track is enabled before updating packages.

Quick reference

TaskSQL / command
Enable GTIDsgtid_mode = ON, enforce_gtid_consistency = ON, unique server_id
Replication accountGRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.%';
Copy data to a new replicaCLONE INSTANCE FROM 'u'@'host':3306 IDENTIFIED BY '...';
Configure the replicaCHANGE REPLICATION SOURCE TO SOURCE_HOST=..., SOURCE_AUTO_POSITION=1;
Start / stopSTART REPLICA; / STOP REPLICA;
StatusSHOW REPLICA STATUS\G
Protect a replicaSET PERSIST super_read_only = ON;
Create a clustermysqlsh → dba.createCluster('name')
Check an upgrademysqlsh -- util check-for-server-upgrade --target-version=...

Exercise

A monitoring script runs SHOW SLAVE STATUS and breaks after upgrading to MySQL 8.4. Why?

  1. The replica must be restarted after an upgrade
  2. SHOW SLAVE STATUS needs the SUPER privilege in 8.4
  3. Replication is disabled by default in 8.4
  4. MySQL 8.4 removed the MASTER/SLAVE syntax; use SHOW REPLICA STATUS
Show answer

Answer: D. The old statements were deprecated in 8.0 and removed in 8.4, where they are syntax errors. The REPLICA and SOURCE forms return the same information.

Exercise

What does SOURCE_AUTO_POSITION = 1 do in CHANGE REPLICATION SOURCE TO?

  1. The replica uses GTIDs to work out which transactions it still needs
  2. The replica picks the nearest source automatically
  3. Replication restarts automatically after errors
  4. The source assigns the replica a server_id
Show answer

Answer: A. With GTID auto-positioning, the replica sends the set of transactions it has applied and the source streams everything else, with no binary log file names or positions.

Exercise

A server runs MySQL 8.0. What is the supported way to reach MySQL 9.7 LTS?

  1. Install 9.7 directly over 8.0
  2. Dump with mysqlpump and restore into 9.7
  3. Upgrade to 8.4 LTS first, then from 8.4 to 9.7
  4. It is not possible; data must be re-entered
Show answer

Answer: C. In-place upgrades go from one LTS series to the next. 8.0 to 8.4 and then 8.4 to 9.7 are both supported, after running the upgrade checker for each target.

Key takeaways

  • Replication streams the binary log to replicas; it is asynchronous by default, so watch lag.
  • Use GTIDs with auto-positioning, the clone plugin to provision replicas and super_read_only on replicas.
  • 8.4 removed MASTER/SLAVE syntax: CHANGE REPLICATION SOURCE TO, START REPLICA, SHOW REPLICA STATUS.
  • InnoDB Cluster (Group Replication + MySQL Shell + MySQL Router) gives automatic failover.
  • Upgrade LTS to LTS (8.0 → 8.4 → 9.7) after util.checkForServerUpgrade, ideally through an upgraded replica.

FAQ

Can I read from replicas without seeing stale data?

Only if your application tolerates lag. For “read your own writes” after a user saves something, read from the primary for that request, or wait for the replica to apply the transaction with WAIT_FOR_EXECUTED_GTID_SET() using the GTID returned after the write.

Is replication a backup?

No. A DROP TABLE reaches every replica within milliseconds. A delayed replica (SOURCE_DELAY = 3600) gives you an hour to stop it before a mistake arrives, which is a useful extra safety net, but you still need real backups.

How many replicas can a source have?

Dozens, technically; each one adds some load on the source’s network and binary log threads. For many replicas, use intermediate replicas that replicate onward (log_replica_updates is on by default).

What about MariaDB or Percona Server replication?

Percona Server is compatible with Oracle MySQL replication. MariaDB uses a different GTID format and its replication between MariaDB and MySQL is limited; treat them as separate products when planning topologies.

↑ ↓ navigate Enter open Esc close