Replication

Article · 3:00

Source and replica, what replication is and is not for, and the lag that breaks applications.

In replication, a source server writes its changes to a binary log and one or more replicas read that log and apply the same changes. The older names were master and slave; the modern terms are source and replica, and you will meet both.

Replication gives you three things. Read scaling, by sending reports and read-only traffic to replicas. High availability, since a replica can be promoted if the source fails. And backups without load, taken from a replica instead of from the server serving users.

What it does not give you is a backup. Replication copies everything faithfully, including DROP TABLE - an accidental delete arrives on the replica in seconds. Backups and replication solve different problems.

The thing that catches applications is lag. Replication is asynchronous by default: the source commits and confirms without waiting for any replica. So a user who writes a row and is immediately redirected to a page that reads from a replica may not find it. The usual answers are to read from the source after a write, or use semi-synchronous replication, where the source waits for at least one replica to acknowledge.

GTIDs - global transaction identifiers - give each transaction a unique id across the topology, which makes failover and reconnecting a replica to a new source far less error-prone than tracking file names and positions by hand.

-- On the source: a unique id, binary logging, and GTIDs.
--   [mysqld]
--   server_id                = 1
--   log_bin                  = mysql-bin
--   binlog_format            = ROW
--   gtid_mode                = ON
--   enforce_gtid_consistency = ON

CREATE USER 'repl'@'10.0.1.%' IDENTIFIED BY 'a-long-password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.1.%';

SHOW BINARY LOG STATUS;

-- On the replica: a different server_id, then point it at the source.
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST       = '10.0.1.10',
  SOURCE_USER       = 'repl',
  SOURCE_PASSWORD   = 'a-long-password',
  SOURCE_AUTO_POSITION = 1;      -- GTIDs: no file names or positions

START REPLICA;

-- The health check. Seconds_Behind_Source is the number that breaks
-- applications: a user who writes and then reads from a replica may
-- not find their own row.
SHOW REPLICA STATUS\G

-- Keep a replica read-only so nothing writes to it by accident and
-- silently diverges from the source.
--   read_only        = ON
--   super_read_only  = ON

-- Replication is NOT a backup. A DROP TABLE replicates perfectly,
-- arriving on every replica within seconds.