Delayed Replicas, Read Scaling, Multi-Source Replication, and Reporting Patterns

Learning outcomes

Not every replica should be “as current as possible.” A delayed replica intentionally keeps a safety gap against operator mistakes. A reporting replica accepts bounded staleness to isolate analytical reads. A multi-source replica combines independent streams. These are different contracts, and each requires application routing and observability rather than a generic “replica” label.

1. Delayed replication is intentional apply lag

With MASTER_DELAY=60, the replica can continue receiving events while postponing SQL execution until the configured delay has elapsed relative to event timing. This can preserve a window in which an accidental destructive transaction has reached the relay log but has not yet changed the replica's data.

Delay protects only if operators detect the incident and stop/recover correctly before the harmful event applies. It does not replace immutable backups, because a long-running unnoticed error will eventually cross the delay window.

2. Independent delayed-replica lab

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 down -vdocker compose up -d

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;

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\GSTOP REPLICA;CHANGE MASTER TO MASTER_DELAY=20;START REPLICA;SHOW REPLICA STATUS\G

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);INSERT INTO servicehub_replication_lab.tickets(ticket_id,customer_id,state) VALUES (50,150,'delayed');SELECT @@global.gtid_binlog_pos;

SHOW REPLICA STATUS\GSELECT * FROM servicehub_replication_lab.tickets WHERE ticket_id=50;

3. Human-error recovery pattern

If the primary receives DELETE FROM tickets by mistake, immediately freezing the delayed replica's SQL thread can preserve the pre-delete state already downloaded to relay logs. The correct recovery action depends on transaction boundaries and GTID position: stop apply before the damaging transaction, export/recover required data, and rebuild/repoint as needed.

STOP REPLICA SQL_THREAD;SHOW REPLICA STATUS\GSELECT @@global.gtid_slave_pos;

4. Read scaling creates a consistency contract

An asynchronous reporting replica can answer reads without loading the primary, but a client that writes and immediately reads from a replica may observe old data. That is expected asynchronous behavior, not necessarily a replication failure.

For a single-writer lab, capture the primary's current GTID after the commit, then call MASTER_GTID_WAIT(target, timeout) on the replica before a causally dependent read. In a busy production system, define precisely which committed GTID represents the request; a broad global position can wait for unrelated concurrent transactions.

SELECT MASTER_GTID_WAIT('14-141-203', 2.0) AS wait_result;-- 0 = target reached, -1 = timeout. Handle timeout in the application.

5. read_only is useful but not absolute fencing

Set replicas read-only to prevent ordinary application writes. Current MariaDB versions use READ ONLY ADMIN privilege for bypass; replication threads are not blocked by read_only. From MariaDB 12.0, additional read_only enum modes provide stronger lock/admin restrictions, but those modes are version-sensitive. Always verify the exact target release before using them in automation.

SHOW VARIABLES LIKE 'read_only';SHOW GRANTS FOR CURRENT_USER;

Network ACLs, credentials, role grants, and client routing should reinforce the write boundary. A single boolean does not fence a failed primary or stop a privileged operator from writing to a replica.

6. Multi-source replication: one replica, several named input streams

MariaDB multi-source replication lets one server maintain multiple primary connections. Each connection has its own relay logs and thread pair. This is valuable for consolidated reporting or migrations, but it does not merge conflicting schemas/data intelligently. Design independent namespaces or application-level conflict rules.

The following optional extension is still entirely free/local, but it uses three containers and therefore consumes more memory.

services:  primary_north:    image: mariadb:12.3.2    container_name: mdb14-north    environment: { MARIADB_ROOT_PASSWORD: labroot }    command: ["--server-id=141", "--log-bin=north-bin", "--binlog-format=ROW", "--gtid-domain-id=14", "--gtid-strict-mode=ON"]  primary_south:    image: mariadb:12.3.2    container_name: mdb14-south    environment: { MARIADB_ROOT_PASSWORD: labroot }    command: ["--server-id=143", "--log-bin=south-bin", "--binlog-format=ROW", "--gtid-domain-id=24", "--gtid-strict-mode=ON"]  reporting:    image: mariadb:12.3.2    container_name: mdb14-reporting    environment: { MARIADB_ROOT_PASSWORD: labroot }    command: ["--server-id=142", "--log-bin=report-bin", "--log-slave-updates=ON", "--gtid-strict-mode=ON", "--read-only=ON"]

docker compose -f compose-multisource.yaml up -d

CREATE USER 'repl'@'%' IDENTIFIED BY 'lab-repl';GRANT REPLICATION REPLICA ON *.* TO 'repl'@'%';RESET MASTER;CREATE DATABASE north_orders;CREATE TABLE north_orders.orders(id INT PRIMARY KEY, amount DECIMAL(10,2)) ENGINE=InnoDB;INSERT INTO north_orders.orders VALUES (1,10.00);

CREATE USER 'repl'@'%' IDENTIFIED BY 'lab-repl';GRANT REPLICATION REPLICA ON *.* TO 'repl'@'%';RESET MASTER;CREATE DATABASE south_orders;CREATE TABLE south_orders.orders(id INT PRIMARY KEY, amount DECIMAL(10,2)) ENGINE=InnoDB;INSERT INTO south_orders.orders VALUES (1,20.00);

CHANGE MASTER 'north' TO  MASTER_HOST='primary_north', MASTER_USER='repl', MASTER_PASSWORD='lab-repl',  MASTER_USE_GTID=slave_pos;CHANGE MASTER 'south' TO  MASTER_HOST='primary_south', MASTER_USER='repl', MASTER_PASSWORD='lab-repl',  MASTER_USE_GTID=slave_pos;START REPLICA 'north';START REPLICA 'south';SHOW ALL REPLICAS STATUS\G

Because the sources use distinct schemas and GTID domains, the lab avoids obvious name/key collisions. If both sources wrote orders.id=1 into the same target table, standard replication would not invent a conflict-resolution policy for you.

7. Connection names and FOR CHANNEL

MariaDB supports connection-name syntax such as START REPLICA 'north'. From MariaDB 10.7, FOR CHANNEL forms exist for MySQL compatibility. They address the same concept, but MariaDB's underlying GTID/domain semantics remain MariaDB-specific.

SHOW REPLICA 'north' STATUS\GSHOW REPLICA STATUS FOR CHANNEL 'north'\GSHOW ALL REPLICAS STATUS\G

8. Production judgment

• Use delayed replicas for a documented recovery purpose; monitor both transport health and the actual delay window.
• Use reporting/read replicas only with an application-visible staleness contract and fallback behavior.
• Use MASTER_GTID_WAIT sparingly for causal reads; timeouts are normal failure modes that code must handle.
• Use multi-source only when namespace/domain/conflict ownership is explicit. It is not a general data-integration engine.
• Client routing remains external to core replication. Without a proxy/orchestrator/application policy, MariaDB does not know which reads may tolerate staleness.

Next, turn these concepts into an incident runbook for lag, broken threads, drift, missing binlogs, rebuilds, and controlled promotion.

docker compose down -vdocker compose -f compose-multisource.yaml down -v