Backup strategy, RPO, RTO and restore drills

A restore drill on a MySQL 8.4.11 server turned a 65,317-byte dump back into 2,500 rows and proved, by checksum, that not one of them had changed. The copy you take is never the point; the restore you can finish in time, and trust, is.

A backup strategy answers two questions with numbers. How much recent data may a failure cost, the recovery point objectiverecovery point objective: The most recent data a restore may lose, stated as a time span: an RPO of five minutes means a failure may cost up to the last five minutes of committed changes. or RPO? And how long may the service stay down while you restore, the recovery time objectiverecovery time objective: The longest a service may stay down while it is restored: an RTO of one hour means the data must be back and serving within an hour of the failure. or RTO? The usual baseline is a daily full backup plus binary logsbinary log: The server's log of committed changes, a numbered series of files plus an index file, written as events in commit order. Replicas apply it, and a restore replays it to bring a backup up to date. More in “The binary log and mysqlbinlog” copied away continuously: the full sets how far back you begin, the logs decide how little you lose. The sections below pick between logical, physical and snapshot backups, keep the logs long enough, take a consistent copy, and time a restore drillrestore drill: A rehearsed restore of a real backup into a throwaway server, timed and checked against the source, so the backup and its recovery time are proven before a real failure. that checks every row.

RPO and RTO decide the backup plan

RPO and RTO pull in different directions, and knowing which one is tight shapes everything else.

An RPO is a promise about loss. A daily full backup on its own gives an RPO of up to a day: a failure at 14:35 that destroys the data directory leaves you with last night’s dump and nothing since. The binary log closes that gap, because it records every committed change in order. Once last night’s dump is restored, replaying the log carries the data forward to any moment whose events you still hold; point-in-time recovery with binary logs walks through it step by step. Your real RPO is then how much of that stream has reached safety off the server when it dies: shipped to another host every few seconds, the exposure is seconds; copied once a night, it is a day.

An RTO is a promise about downtime, counted from the failure to the moment the service answers again. It is dominated by restore speed: fetching the backup, reloading or copying it, replaying the logs and rebuilding whatever the engine needs. A plan can meet a tight RPO and still miss its RTO when the restore itself takes hours. Step through one recovery from last night’s full and its logs, and note which stage settles the loss and where the downtime ends:

Replaying the logs and stopping at the right point both lean on the binary logs, and the logs only help while they still exist, so retention is part of the plan, not an afterthought.

Three kinds of backup trade speed for portability

Three kinds of backup copy a MySQL database, and the strategy’s first choice is between them.

A logical backup, which mysqldump writes, is SQL text: the CREATE TABLE and INSERT statements that rebuild the data on any server. A physical backup is a raw copy of the data directory’s files, which a tool such as Percona XtraBackup or the CLONE plugin takes while the server runs. A file-system snapshot freezes a whole volume at an instant, through the storage layer or LVM, and copies the frozen image; the manual’s recipe holds FLUSH TABLES WITH READ LOCK only while the snapshot is being made.

They trade speed against portability. A logical dump is slow in both directions: the server turns every row into SQL text to write it, and a reload replays the rows and rebuilds every index, but the text is the easiest to carry to a new version or a different machine. A physical copy or a snapshot copies files without converting them and restores by putting them back, which is fast, but the files only fit a server of a similar build and layout. Answer the table’s two questions for a database you look after, and it marks which kinds fit:

The MySQL manual draws the same line: physical backups suit large, important databases that must be recovered quickly, logical ones smaller data or a move to a different machine. For tens of gigabytes that must move across versions, a logical dump is the portable choice, and a drill shows whether its reload fits the RTO. For hundreds of gigabytes on matching hardware, a physical copy or a snapshot keeps the restore short.

A consistent copy needs one snapshot, not a frozen server

A backup is only useful if it is consistent: every table at one instant, not a smear across a busy minute.

InnoDB gives logical dumps that at almost no cost. mysqldump --single-transaction starts a transaction and takes one consistent readconsistent read: A SELECT that returns the version of each row the transaction's read view may see, without taking any lock on the rows it touches; the default read mode in InnoDB under REPEATABLE READ and READ COMMITTED., so the whole dump sees the database as it stood when the transaction began, even while other clients keep committing. Reading it blocks no reader and no writer. Other clients should still not run ALTER, CREATE, DROP, RENAME or TRUNCATE TABLE meanwhile: a consistent read does not shield the dump from those statements, which can make it read wrong contents or fail. With --source-data the dump also records the binary-log position of that instant, where a later replay begins. To read that position, or the GTID set of a server with GTIDs on, mysqldump holds FLUSH TABLES WITH READ LOCK for a moment at the start; the pause stays short unless a long-running update makes the lock, and every write queued behind it, wait.

A physical tool cannot open a read view over the files it copies, so Percona XtraBackup, on a MySQL server, holds a backup lockbackup lock: A light instance-level lock taken by LOCK INSTANCE FOR BACKUP that permits DML during an online physical backup while it blocks file changes and most DDL that would spoil the copy. instead. LOCK INSTANCE FOR BACKUP takes an instance-level lock that “permits DML during an online backup”, as the manual puts it, yet stops the operations that would leave the copy inconsistent: it blocks files from being created, renamed or removed and most DDL, and it needs the BACKUP_ADMIN privilege. It is far lighter than FLUSH TABLES WITH READ LOCK, which stops every write for the length of the copy. Step through both on a live server, the dump with its moment of locking first:

A restore drill times the RTO and proves the data

A backup you have never restored is a hope, not a plan. A restore drill turns it into a fact: reload a real backup into a throwaway server, time it, and check the result against the source. The session below does exactly that for a small shop schema, on two fresh 8.4.11 servers:

"""A restore drill on MySQL 8.4.11: dump a seeded schema with a consistent snapshot, reload it
into a throwaway server, and compare CHECKSUM TABLE ... EXTENDED on both.

The byte count of the dump and the row counts are the real, reproducible figures. The elapsed
times change from run to run, so they are shown through a mask (<elapsed>) and the record stays
the same on every run; run the drill yourself to read the real seconds.
"""

import re
import sys
import time

from mysql_session import Server

# A small, fixed data set so the dump is byte-for-byte the same on every run.
SEED = """
SET SESSION cte_max_recursion_depth = 5000;
CREATE DATABASE shop;
USE shop;
CREATE TABLE customers (
  id   INT PRIMARY KEY,
  name VARCHAR(60) NOT NULL,
  city VARCHAR(40) NOT NULL
) ENGINE=InnoDB;
CREATE TABLE orders (
  id           INT PRIMARY KEY,
  customer_id  INT NOT NULL,
  amount_paise BIGINT NOT NULL,
  status       VARCHAR(12) NOT NULL,
  KEY (customer_id),
  CONSTRAINT fk_cust FOREIGN KEY (customer_id) REFERENCES customers (id)
) ENGINE=InnoDB;
INSERT INTO customers (id, name, city)
WITH RECURSIVE n (seq) AS (SELECT 1 UNION ALL SELECT seq + 1 FROM n WHERE seq < 500)
SELECT seq, CONCAT('Customer ', seq), ELT(1 + seq % 4, 'Pune', 'Delhi', 'Chennai', 'Kochi') FROM n;
INSERT INTO orders (id, customer_id, amount_paise, status)
WITH RECURSIVE n (seq) AS (SELECT 1 UNION ALL SELECT seq + 1 FROM n WHERE seq < 2000)
SELECT seq, 1 + seq % 500, 1000 + seq * 7, ELT(1 + seq % 3, 'new', 'packed', 'shipped') FROM n;
"""

CHECKSUM = "CHECKSUM TABLE shop.customers, shop.orders EXTENDED"


def masked(line):
    """Replace every per-run duration with <elapsed> so two runs print the same line."""
    return re.sub(r"\d+\.\d+ s", "<elapsed>", line)


with Server() as src, Server() as dst:
    src.up()
    src.query(SEED)
    src.sql(
        "SELECT (SELECT COUNT(*) FROM shop.customers) AS customers,"
        " (SELECT COUNT(*) FROM shop.orders) AS orders",
        note="what the backup must protect",
    )

    # Dump with --single-transaction, so InnoDB reads one snapshot while writes could go on,
    # and --source-data=2, so the dump records the binary-log position to replay from.
    args = ["mysqldump", "--single-transaction", "--source-data=2", "--skip-dump-date",
            "--no-tablespaces", "--databases", "shop"]
    t0 = time.perf_counter()
    dump = src.output(*args, connect=True)
    dump_secs = time.perf_counter() - t0
    nbytes = len(dump.encode())
    position = next(l for l in dump.splitlines() if "CHANGE REPLICATION SOURCE" in l)
    print("$ " + " ".join(args) + " > shop.sql")
    print(f"-- the dump is {nbytes} bytes of SQL")
    print("-- " + position.strip().lstrip("- ").rstrip(";"))
    print()

    # Restore into a brand-new server and time it: this is the RTO you are measuring.
    dst.up()
    (dst.base / "shop.sql").write_text(dump)
    t1 = time.perf_counter()
    dst.query((dst.base / "shop.sql").read_text())
    restore_secs = time.perf_counter() - t1
    dst.sql(
        "SELECT (SELECT COUNT(*) FROM shop.customers) AS customers,"
        " (SELECT COUNT(*) FROM shop.orders) AS orders",
        note="reloaded into a throwaway server",
    )

    # The step that turns a copy into a drill: do the restored tables match the source?
    src.sql(CHECKSUM, note="source")
    dst.sql(CHECKSUM, note="restored")
    if src.query(CHECKSUM) != dst.query(CHECKSUM):
        sys.exit("the restored tables do not match the source")

    total = dump_secs + restore_secs
    print(masked(f"-- measured: dump {dump_secs:.3f} s, restore {restore_secs:.3f} s,"
                 f" {total:.3f} s in all for 2,500 rows (times vary per run, masked here)"))

-- what the backup must protect
$ mysql -t -e "SELECT (SELECT COUNT(*) FROM shop.customers) AS customers, (SELECT COUNT(*) FROM shop.orders) AS orders"
+-----------+--------+
| customers | orders |
+-----------+--------+
|       500 |   2000 |
+-----------+--------+

$ mysqldump --single-transaction --source-data=2 --skip-dump-date --no-tablespaces --databases shop > shop.sql
-- the dump is 65317 bytes of SQL
-- CHANGE REPLICATION SOURCE TO SOURCE_LOG_FILE='binlog.000001', SOURCE_LOG_POS=60525

-- reloaded into a throwaway server
$ mysql -t -e "SELECT (SELECT COUNT(*) FROM shop.customers) AS customers, (SELECT COUNT(*) FROM shop.orders) AS orders"
+-----------+--------+
| customers | orders |
+-----------+--------+
|       500 |   2000 |
+-----------+--------+

-- source
$ mysql -t -e "CHECKSUM TABLE shop.customers, shop.orders EXTENDED"
+----------------+------------+
| Table          | Checksum   |
+----------------+------------+
| shop.customers | 2435803798 |
| shop.orders    |  351868791 |
+----------------+------------+

-- restored
$ mysql -t -e "CHECKSUM TABLE shop.customers, shop.orders EXTENDED"
+----------------+------------+
| Table          | Checksum   |
+----------------+------------+
| shop.customers | 2435803798 |
| shop.orders    |  351868791 |
+----------------+------------+

-- measured: dump <elapsed>, restore <elapsed>, <elapsed> in all for 2,500 rows (times vary per run, masked here)

The seeded database held 500 customers and 2,000 orders. mysqldump --single-transaction --source-data=2 wrote a 65,317-byte dump and recorded its starting point as binlog.000001 position 60,525, so a point-in-time recovery would know exactly which events to replay next. Reloaded into a brand-new server, the 2,500 rows came back; the check that follows is what makes this a drill rather than a copy.

CHECKSUM TABLE ... EXTENDED reads the whole table row by row and returns one number for its contents; the QUICK form returns only the live checksum that a MyISAM table created with CHECKSUM=1 keeps, so on InnoDB it gives NULL. The source and the restored copy returned the same numbers, 2435803798 for customers and 351868791 for orders, so every row survived the round trip. The comparison is fair only between the same moment’s data on the same version and row format, because the value depends on the row format; here nothing wrote to the source after the dump. The statement also read-locks each table while it runs, so to check a busy source against its replicas, Percona’s pt-table-checksum works in small chunks instead: its checksum queries run on the source and replicate, and it exits non-zero when a replica differs.

The elapsed times the drill printed are masked in the record because they change from run to run. Repeat the drill on your own data and hardware and read the real seconds: the restore figure is what reloading that much data costs you, the part of the RTO you can measure today, and a far more honest number than a guess.

Practise: plan a backup for a service

A backup strategy is a set of numbers before it is a set of commands: how often to take a full, how long to keep the logs, and whether the two together meet the RPO and RTO a service was promised. The exercise hands your function a service’s needs as a dictionary of numbers and asks for a plan that meets them.

def plan_backup(needs):
    """Turn a service's needs into a backup plan, as the prompt describes.

    needs has the integer keys "data_gb", "rpo_minutes", "rto_minutes" and "pitr_window_days".
    Return a dict with "method", "full_backup", "ship_binlog_seconds" and "binlog_retention_days".
    """
    # Replace these lines with your plan.
    return {
        "method": "logical",
        "full_backup": "daily",
        "ship_binlog_seconds": 1,
        "binlog_retention_days": 0,
    }

"""Sample tests: five services with different needs, each wanting a plan that meets its RPO and RTO."""

from backup_plan import plan_backup


def test_small_portable_service():
    """small data, loose RTO: a logical dump, binlogs shipped every 5 minutes"""
    needs = {"data_gb": 20, "rpo_minutes": 5, "rto_minutes": 60, "pitr_window_days": 7}
    assert plan_backup(needs) == {
        "method": "logical",
        "full_backup": "daily",
        "ship_binlog_seconds": 300,
        "binlog_retention_days": 9,
    }


def test_large_service_needs_physical():
    """over 50 GB: a physical copy so the restore fits the RTO"""
    needs = {"data_gb": 400, "rpo_minutes": 2, "rto_minutes": 90, "pitr_window_days": 14}
    assert plan_backup(needs) == {
        "method": "physical",
        "full_backup": "daily",
        "ship_binlog_seconds": 120,
        "binlog_retention_days": 16,
    }


def test_tight_rto_chooses_physical_even_when_small():
    """small data but a sub-30-minute RTO still picks the faster physical restore"""
    needs = {"data_gb": 10, "rpo_minutes": 1, "rto_minutes": 20, "pitr_window_days": 3}
    assert plan_backup(needs) == {
        "method": "physical",
        "full_backup": "daily",
        "ship_binlog_seconds": 60,
        "binlog_retention_days": 5,
    }


def test_zero_rpo_ships_continuously():
    """an RPO of 0 means ship the binlog continuously, never slower than every second"""
    needs = {"data_gb": 30, "rpo_minutes": 0, "rto_minutes": 45, "pitr_window_days": 1}
    assert plan_backup(needs) == {
        "method": "logical",
        "full_backup": "daily",
        "ship_binlog_seconds": 1,
        "binlog_retention_days": 3,
    }


def test_values_at_the_limits_stay_logical():
    """exactly 50 GB is not over 50 and exactly 30 minutes is not under 30, so the plan stays logical"""
    needs = {"data_gb": 50, "rpo_minutes": 10, "rto_minutes": 30, "pitr_window_days": 2}
    assert plan_backup(needs) == {
        "method": "logical",
        "full_backup": "daily",
        "ship_binlog_seconds": 600,
        "binlog_retention_days": 4,
    }

Then test yourself on the four ideas above, RPO and RTO, the three kinds of backup, a consistent copy and the drill:

Interview questions

Warm-up, for freshers: why can a daily full backup alone never meet a one-minute RPO?

A full backup captures the database only at the instant it runs. With one full a night, everything committed between that run and a failure the next afternoon lives nowhere else, so a crash can cost many hours of data: the RPO is the age of the last full, not one minute. To promise a one-minute RPO you also need the change stream off the server at least that often, the binary log shipped continuously to another host, or a replica kept nearly in step. The full backup bounds how far back a restore starts; the copied logs bound how little it loses.

Two questions of this module start where the drill above stops. Work out a first move for each, then open it:

Key takeaways

• A backup plan is judged by two numbers: the RPO, how much data a failure may cost, and the RTO, how long the service may be down, both measured from the moment of failure.
• The common baseline is a daily full backup plus binary logs copied away continuously; the full sets where a restore starts and the shipped logs set how little it loses.
• Logical dumps are the portable kind but the slowest to take and to restore; physical copies and file-system snapshots copy and restore fast, but only onto a server of a similar build.
• A consistent copy needs a consistent read for InnoDB logical dumps, with at most a moment’s global read lock to note the binary-log position or GTID set, or a backup lock for a physical tool such as XtraBackup, never a server-wide read lock held for the whole copy.
• binlog_expire_logs_seconds defaults to 2,592,000 (30 days) and must exceed the gap between fulls plus the point-in-time window, or the logs a restore needs are deleted before it runs.
• A restore drill that reloads into a throwaway server and compares CHECKSUM TABLE ... EXTENDED is the only proof that a backup restores, and its restore time is the honest starting point for the RTO.