At 11:20 on a Tuesday an ORM migration, pointed at production by an environment variable that lied, ran DROP TABLE tenant_usage. The table held 340 million rows of usage events and every byte of the current quarter's billing input. Our point-in-time recovery tooling would have worked — full backup from 02:00, then nine hours of binlog replay, minus one statement — and the estimate for that was four to six hours if nothing went wrong. What actually happened took forty minutes: we stopped the SQL thread on the delayed replica, which by configuration applied changes six hours behind the primary, confirmed the DROP was still sitting harmlessly in its relay logs, dumped the table from the replica with all of that morning's writes intact, loaded it back onto the primary, and let the replica resume after skipping the one poisonous event. Zero data lost, and the only downtime was the forty minutes the billing pipeline waited.
A delayed replica is not exotic technology. It is one line of replication configuration that buys you a time machine with a fixed, known depth, and it protects against the failure class backups are worst at: human-error data destruction discovered quickly. This is the setup we run, the disk and alerting math nobody warns you about, and the recovery procedure rehearsed enough times that Tuesday was boring.
What does MASTER_DELAY actually delay?
MASTER_DELAY delays only the SQL thread — the replica's IO thread keeps downloading the primary's binary log in near-real-time, and the events sit in local relay logs until the configured delay has passed. That split is the whole design: your protection window is full-fidelity data already on the replica's disk, not data that still has to cross the network during an emergency. Setting it requires the SQL thread to be stopped first, which is the kind of detail you learn once from an error message:
STOP SLAVE SQL_THREAD;
CHANGE MASTER TO MASTER_DELAY = 21600; -- 6 hours, in seconds
START SLAVE;
SHOW SLAVE STATUS\G
-- the fields that matter on a delayed replica:
-- SQL_Delay -> the configured delay (21600)
-- SQL_Remaining_Delay -> seconds until the NEXT event applies
-- Seconds_Behind_Master -> will hover near 21600 forever. That is HEALTHY.The delay value is a business decision wearing a technical costume. It must be longer than your slowest plausible detection time — the window between "someone runs the destructive statement" and "someone with access and knowledge acts on it." Six hours covers a bad-migration-at-lunch discovered in the afternoon, which is the modal case; it does not cover a slow leak noticed in a monthly review, and no delay value does — that is what backups are for. We sized ours from incident history: every human-error event in five years was detected within two hours, so six hours carries a 3x margin. Pick your number from your own history, not from mine.
How much disk does the delay window cost?
The disk cost is your binlog generation rate multiplied by the delay, plus headroom for the primary's busy hours — measure it from your own binlog volume, not from averages. Our primary generates about 300 MB of binlog per hour on a typical day, so six hours of relay logs costs roughly 1.8 GB; I provision 10 GB for the replica's relay volume because month-end batch windows triple the hourly rate and a disk-full delayed replica is a delayed replica that has stopped being insurance. Measure with a week of binlog file sizes before you pick the number.
The trap that quietly deletes your protection is relay_log_space_limit. It exists to cap exactly this disk growth, and on a normal replica a sane cap is good hygiene — but on a delayed replica the cap is your safety window: when the IO thread hits the limit it stops downloading until the SQL thread catches up, which means your six-hour cushion silently compresses toward zero during a write burst, precisely when the primary is busy and mistakes are likelier. Do not set it on the delayed replica. Give the volume real headroom and alert on free space instead. One more sizing note: the replica is also a real server doing real apply work six hours behind, so size its write path for burst catch-up — after a primary-side batch window, the delayed box replays that burst six hours later while you may have forgotten it happened.
How do you recover a dropped table from a delayed replica?
Stop the SQL thread before the poisonous event applies, recover the object from the replica's copy of the data, and resume replication past the event — in that order, and the clock you are racing is SQL_Remaining_Delay, not wall time. The rehearsed procedure:
-- 1. freeze the window: the DROP is still waiting in relay logs
STOP SLAVE SQL_THREAD;
SHOW SLAVE STATUS; -- note SQL_Remaining_Delay: your reaction budget
-- 2. locate the poison in the relay logs (shell, not SQL):
-- mariadb-binlog --base64-output=decode-rows -v relay-bin.000042 \
-- | grep -n "DROP TABLE"
-- 3. apply everything UP TO the event before the drop
START SLAVE SQL_THREAD
UNTIL RELAY_LOG_FILE = 'relay-bin.000042',
RELAY_LOG_POS = 98123477;
-- 4. export the object from the replica (shell):
-- mariadb-dump --single-transaction app tenant_usage > tenant_usage.sql
-- 5. load it on the primary, then resume the replica PAST the drop:
-- with MASTER_USE_GTID, sql_slave_skip_counter does NOT apply —
-- advance past the event by letting the replica re-apply and fail
-- is wrong; set the next position via GTID or relay-log coordinates.Two of those steps deserve the bold print. Step 1 is time-critical and must be drilled: every person on call should be able to run it cold, because the value of a six-hour window is zero if the response takes seven. We pair it with a page-level alert on DDL against the protected schemas — the audit-plugin event classes from the server_audit production notes are exactly the right feed for that alert. And step 5 hides the sharpest GTID edge: if your channel uses MASTER_USE_GTID, the classic sql_slave_skip_counter escape hatch is ignored, and skipping the event means working in GTID coordinates — the domain-aware position arithmetic from the multi-source GTID notes applies here unchanged. Rehearse step 5 on a scratch pair before you need it; the first time should not be during a real DROP TABLE.
What breaks on a delayed replica in day-to-day operations?
The daily breakages are monitoring false positives and automation that forgets the replica is special. Seconds_Behind_Master sits at roughly the configured delay forever, so every off-the-shelf replication-lag alert fires permanently and people learn to ignore lag alerts — including on the undelayed replicas. The fix is to alert on divergence from the configured delay (lag materially above SQL_Delay means the apply is falling behind the schedule, not just the primary) and on IO thread health, which is the thread that actually keeps your insurance valid. The general lag-diagnosis discipline from replication lag diagnosis applies, with the baseline shifted by the delay.
The automation hazards are three. Failover tooling must never promote the delayed replica — tag it out of every candidate list, because a promotion hands you a primary six hours stale at the worst possible moment. Backup jobs should not take the nightly logical dump from the delayed box; its data is by definition old, and a restore-from-backup drill that silently used it once cost a colleague a confused hour. And point-in-time recovery tooling needs the delayed replica excluded from binlog-position calculations, or your PITR math inherits a six-hour error. The delayed replica earns its keep exactly once, on the bad Tuesday; the other 364 days it is a liability unless the runbooks name it explicitly. The PITR side of that boundary is covered in the flashback recovery notes — flashback replays the primary's binlog backwards, the delayed replica simply never went forward, and knowing which tool fits which accident is the actual skill.
Where MonPG fits
The signals worth trending on a delayed replica are the insurance-policy metrics: IO thread liveness, lag divergence from the configured delay rather than from zero, relay-volume free space against the binlog rate, and SQL_Remaining_Delay during incidents as your reaction budget. Full disclosure, as in every article of this series: I work on MonPG, which monitors PostgreSQL in production today and does not monitor MariaDB yet. MariaDB support is coming soon and in active development — the /mariadb-monitoring page tracks where it stands — and replication health is on the list of signals it is being built around, including delay-aware lag alerting so a healthy delayed replica never pages anyone. Until that ships, the status fields above are your kit. If PostgreSQL is also in your fleet, that monitoring is live today — see the PostgreSQL overview, or browse more field notes on the blog.