Primary/Replica Flow, Binary Logs, Relay Logs, Replication Threads, and Filters

Learning outcomes

ServiceHub is adding a read replica for reporting and backup offload. The application team sees “replication is running” and assumes the copy is current and complete. That assumption is unsafe. Asynchronous replication is a pipeline: the primary commits a transaction, writes replication events to its binary log, the replica's I/O thread fetches them into a relay log, and the SQL coordinator/workers apply them later. Each stage can be healthy, blocked, filtered, or behind independently.

Primary is the server accepting the authoritative write stream. A replica receives and replays that stream. A binary log (binlog) is the ordered replication/recovery event stream on the source. A relay log is the replica-side queue of received events. MariaDB's current documentation increasingly uses primary/replica terminology, while some SQL options and status fields still retain historical MASTER_*, Slave_*, and slave_* names for compatibility.

1. The replication pipeline is three different pieces of evidence

Suppose transaction T42 commits on the primary. The primary first makes the transaction durable according to its storage/binlog durability settings, then exposes its binlog events to replicas. The replica I/O thread can receive T42 and persist it in the relay log while the SQL side is blocked behind a metadata lock. At that moment network transport is current but the queryable replica is stale.

2. Reproducible two-node local lab

Create an empty directory for this lesson and save the following as compose.yaml. The explicit server IDs are mandatory because replication servers must have distinct identities. The lab uses row-based binary logging so data-change filtering semantics are less surprising than statement-based logging, but DDL is still statement-like and deserves separate validation.

services:  primary:    image: mariadb:12.3.2    container_name: mdb14-primary    environment:      MARIADB_ROOT_PASSWORD: labroot    command:      - --server-id=141      - --log-bin=mariadb-bin      - --binlog-format=ROW      - --gtid-domain-id=14      - --gtid-strict-mode=ON    ports:      - "33141:3306"  replica:    image: mariadb:12.3.2    container_name: mdb14-replica    environment:      MARIADB_ROOT_PASSWORD: labroot    command:      - --server-id=142      - --log-bin=replica-bin      - --log-slave-updates=ON      - --relay-log=relay-bin      - --gtid-domain-id=14      - --gtid-strict-mode=ON      - --read-only=ON    ports:      - "33142:3306"

docker compose up -ddocker ps --filter name=mdb14-

Wait for both servers to report healthy enough to accept connections, then initialize the replication account on the primary. RESET MASTER is intentionally used only because this is a throwaway empty lab; never copy that step into a production runbook without understanding that it discards binary-log history.

CREATE USER IF NOT EXISTS 'repl'@'%' IDENTIFIED BY 'lab-repl';GRANT REPLICATION REPLICA ON *.* TO 'repl'@'%';-- Disposable lab only: discard initialization-era binlogs before application data.RESET MASTER;SHOW MASTER STATUS;SELECT @@server_id, @@gtid_domain_id, @@global.gtid_binlog_pos;

Now connect the replica. MariaDB still configures the connection with CHANGE MASTER TO; START REPLICA, STOP REPLICA, and SHOW REPLICA STATUS are current synonyms for the historical SLAVE forms. MASTER_USE_GTID=slave_pos tells the replica to request events after its applied MariaDB GTID position.

STOP REPLICA;RESET REPLICA ALL;SET GLOBAL gtid_slave_pos='';CHANGE MASTER TO  MASTER_HOST='primary',  MASTER_PORT=3306,  MASTER_USER='repl',  MASTER_PASSWORD='lab-repl',  MASTER_USE_GTID=slave_pos;START REPLICA;SHOW REPLICA STATUS\G

3. Watch one transaction cross the pipeline

Create the ServiceHub fixture only after replication is running. This makes the sequence easy to reason about: every application object is born in the primary's binlog and should appear through normal replay.

CREATE DATABASE IF NOT EXISTS servicehub_replication_lab;CREATE TABLE IF NOT EXISTS servicehub_replication_lab.tickets (  ticket_id BIGINT PRIMARY KEY,  customer_id BIGINT NOT NULL,  state VARCHAR(20) NOT NULL,  updated_at TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)             ON UPDATE CURRENT_TIMESTAMP(6)) ENGINE=InnoDB;INSERT INTO servicehub_replication_lab.tickets(ticket_id,customer_id,state)VALUES (1,101,'open'),(2,102,'open')ON DUPLICATE KEY UPDATE state=VALUES(state);

UPDATE servicehub_replication_lab.ticketsSET state='assigned'WHERE ticket_id=1;SHOW MASTER STATUS;SELECT @@global.gtid_binlog_pos;

SHOW REPLICA STATUS\GSELECT @@global.gtid_slave_pos;SELECT ticket_id,state,updated_atFROM servicehub_replication_lab.ticketsWHERE ticket_id=1;

If the row says assigned and the applied position includes the primary transaction, you have direct evidence of replay. You still have not proven that every table is replicated, that no filters exist, or that future reads are causally fresh.

4. Transport lag and apply lag are different incidents

Stop only the SQL thread and leave the I/O thread running. Then write to the primary. The replica can continue downloading events into the relay log while deliberately not applying them.

STOP REPLICA SQL_THREAD;SHOW REPLICA STATUS\G

INSERT INTO servicehub_replication_lab.tickets(ticket_id,customer_id,state)VALUES (3,103,'open'),(4,104,'open'),(5,105,'open');

SHOW REPLICA STATUS\GSELECT COUNT(*) AS visible_rows FROM servicehub_replication_lab.tickets;

On MariaDB 11.6 and later, status also exposes Master_last_event_time, Slave_last_event_time, and Master_Slave_time_diff. These improve lag visibility, especially when simple Seconds_Behind_Master interpretation is ambiguous. Restart apply and verify the row count becomes five.

START REPLICA SQL_THREAD;SHOW REPLICA STATUS\GSELECT COUNT(*) AS visible_rows FROM servicehub_replication_lab.tickets;

5. Deliberately wrong approach: “green threads mean a full copy”

Replication filters intentionally suppress some events. They are useful for specialized replicas, but they change the data contract. A common operational mistake is to add a filter for storage savings and later treat the replica as a complete failover or backup source.

STOP REPLICA;SET GLOBAL replicate_ignore_table='servicehub_replication_lab.private_notes';START REPLICA;

CREATE TABLE servicehub_replication_lab.private_notes(  note_id BIGINT PRIMARY KEY, note_text VARCHAR(200) NOT NULL) ENGINE=InnoDB;INSERT INTO servicehub_replication_lab.private_notes VALUES (1,'internal-only');

SHOW REPLICA STATUS\GSHOW TABLES FROM servicehub_replication_lab;SELECT @@global.replicate_ignore_table;

STOP REPLICA;SET GLOBAL replicate_ignore_table='';START REPLICA;SHOW REPLICA STATUS\G

Do not “repair” missing history by assuming the next event will recreate it. For a production replica whose completeness contract changed, rebuild from a validated snapshot or perform a controlled reconciliation with acceptance checks.

6. Filters depend on binary-log semantics

MariaDB evaluates some database filters against the default database under statement-based logging, while row-based events carry the affected table/database more directly. DDL remains statement-oriented even with binlog_format=ROW. Cross-database statements can therefore defeat intuitive filter expectations. Treat filter design as part of schema/application design and test representative DML and DDL.

7. Production judgment

Use asynchronous replication when your workload can tolerate a nonzero replication delay and you have an explicit plan for read staleness, monitoring, failover, and independent backups. Do not market it internally as “HA” without a control plane: standard MariaDB replication moves events but does not itself fence an old primary, move client traffic, decide which replica is safest, or guarantee zero data loss.

• Prerequisites: unique server_id values, binary logging on the primary, TCP reachability, a least-privilege replication account, compatible server versions, and a consistent initial snapshot/position.
• Monitor: both thread states, last I/O/SQL errors, received versus applied positions, relay backlog, newer lag timestamps, binlog retention headroom, disk space, and application-level freshness SLOs.
• Security: production replication credentials should use TLS and credential rotation from Chapter 12; the plaintext lab secret is intentionally disposable.
• Durability: replication is not a backup. A destructive transaction is faithfully replicated unless delay/filtering interrupts it.

Next, replace file/position thinking with MariaDB GTIDs so failover and topology changes can reason about logical transaction identity rather than physical binlog filenames.

docker compose down -v