Replication and High Availability

Replication copies changes from a source server to one or more replicas using the binary log.

Concept

Replication copies changes from a source server to one or more replicas using the binary log.

Modes and technologies:

Default; source does not wait for replicas (possible data loss on failover)

Source waits until at least one replica has received (not applied) the transaction

Each transaction has a Global Transaction ID; simplifies failover and topology changes

One replica from several sources (consolidation) Multiple applier threads (replica_parallel_workers) Group Replication

Virtually synchronous, consensus-based (Paxos) replication among 3+ members; single-primary (default) or multi-primary; automatic failover InnoDB Cluster

Group Replication + MySQL Router + MySQL Shell AdminAPI for managed HA

Managed async replication with Router and Shell Disaster recovery across data centers (primary cluster + replica clusters)

Terminology: MySQL 8.0.22+ uses source/replica (CHANGE REPLICATION SOURCE TO, START REPLICA, SHOW REPLICA STATUS); the old MASTER/SLAVE statements were deprecated and removed in 8.4.

flowchart LR
    APP[Application writes] --> SRC[(Source)]
    SRC -->|binary log events| IO1[Replica 1<br/>receiver thread → relay log → applier threads]
    SRC -->|binary log events| IO2[Replica 2]
    IO1 --> R1[(Replica 1 data)]
    IO2 --> R2[(Replica 2 data)]
    RD[Read-only queries / reports / backups] --> R1
    RD --> R2

Why It Matters

Replication provides read scaling, high availability, zero-downtime maintenance, geographic distribution, and a place to run backups and analytics without loading the primary.

Syntax

-- On the replica
CHANGE REPLICATION SOURCE TO
    SOURCE_HOST = 'db-source.internal',
    SOURCE_PORT = 3306,
    SOURCE_USER = 'repl',
    SOURCE_PASSWORD = '...',
    SOURCE_AUTO_POSITION = 1,        -- GTID auto-positioning
    SOURCE_SSL = 1;
START REPLICA;
SHOW REPLICA STATUS\G
STOP REPLICA;

Example

31.1 Setting up GTID replication (outline) Source my.cnf:

Replica my.cnf:

Seed the replica with a consistent copy (Clone plugin, XtraBackup, or MySQL Shell dump/load), then run CHANGE REPLICATION SOURCE TO ... SOURCE_AUTO_POSITION = 1; START REPLICA;.

31.2 Monitoring replication Small (0–few seconds) Last_IO_Error, Last_SQL_Error

Retrieved_Gtid_Set / Executed_Gtid_Set Performance Schema tables replication_connection_status and replication_applier_status_by_worker give more detail.

Common causes of lag: large transactions (bulk UPDATE/DELETE), single-threaded apply of dependent transactions, slow replica disks, tables without primary keys under row-based replication (each row change may cause a full table scan on the replica).

31.3 InnoDB Cluster with MySQL Shell (outline) Group Replication requirements: InnoDB tables with primary keys, GTIDs, row-based binlog, odd number of members (3 tolerates 1 failure, 5 tolerates 2).

31.4 Read/write splitting and consistency Send writes to the primary, reads to replicas — but asynchronous replicas can return stale data (read-your-own-writes problem).

Solutions: read critical data from the primary; use Group Replication consistency levels (group_replication_consistency = BEFORE_ON_PRIMARY_FAILOVER, AFTER, BEFORE); or wait for a GTID (WAIT_FOR_EXECUTED_GTID_SET).

Tools: MySQL Router, ProxySQL, application connection routing.

31.5 Scaling beyond one primary Replication scales reads, not writes. For write scaling: vertical scaling, sharding (application-level, Vitess, ProxySQL sharding rules), or distributed MySQL-compatible systems (TiDB, PlanetScale/Vitess, Amazon Aurora for storage-level scaling).

[mysqld]
server_id                 = 1
log_bin                   = binlog
gtid_mode                 = ON
enforce_gtid_consistency  = ON
binlog_format             = ROW

[mysqld]
server_id                 = 2
gtid_mode                 = ON
enforce_gtid_consistency  = ON
read_only                 = ON
super_read_only           = ON
relay_log                 = relay-bin
replica_parallel_workers  = 4

-- On the source: replication account
CREATE USER 'repl'@'10.0.2.%' IDENTIFIED BY '...' REQUIRE SSL;
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.2.%';

SHOW REPLICA STATUS\G

MySQL  JS > dba.configureInstance('admin@db1:3306')
MySQL  JS > var cluster = dba.createCluster('ehCluster')
MySQL  JS > cluster.addInstance('admin@db2:3306', {recoveryMethod: 'clone'})
MySQL  JS > cluster.addInstance('admin@db3:3306', {recoveryMethod: 'clone'})
MySQL  JS > cluster.status()

mysqlrouter --bootstrap admin@db1:3306 --directory /opt/router --user=mysqlrouter
# Applications connect to Router: 6446 = read/write (primary), 6447 = read-only (secondaries)

Expected Result

Healthy replica excerpt:

Replica_IO_Running: Yes
       Replica_SQL_Running: Yes
     Seconds_Behind_Source: 0
             Last_IO_Error:
            Last_SQL_Error:

Common Mistakes

Same server_id on source and replica.

Writing to replicas (use super_read_only = ON).

Tables without primary keys.

Treating replicas as backups (a DROP TABLE replicates instantly).

Ignoring lag when reading from replicas.

Best Practices

GTIDs, row-based binlogs, super_read_only on replicas, TLS for replication.

Monitor lag and errors; alert on thresholds.

Use InnoDB Cluster/ReplicaSet with Router for managed failover.

Keep transactions small to avoid lag.

Interview Relevance

High for backend/DBA: "How does MySQL replication work?", "Async vs semi-sync vs Group Replication?", "What is GTID?", "What causes replication lag?", "What is InnoDB Cluster?".

Practice Questions

Set up a source and replica with Docker and verify replication of a new table.

Cause replication lag with a large DELETE and observe Seconds_Behind_Source.

Explain the read-your-own-writes problem and two solutions.