MySQL / Concurrency, performance, and operations / Lesson 17

Backup, restore, and point-in-time recovery

Design recovery around explicit loss and downtime objectives, then verify a restored database rather than trusting a file.

About 4 min readContent reviewed 2026-10-23

On this page

Lesson goals

Distinguish logical backups, physical backups, and binary logs.
Plan consistent InnoDB capture, restore rehearsal, and transaction-boundary recovery.

State the acceptable loss and downtime

Use only the synthetic shop database for examples. Recovery point objective describes acceptable data loss; recovery time objective describes acceptable restoration time. A nightly dump cannot alone promise recovery to one minute before an incident. A backup strategy must include capture frequency, retained history, encryption, access control, off-host storage, and regular restores. A replica is not an independent backup because accidental deletes can replicate immediately.

Match the capture method to the engine

A logical dump records schema and rows as portable statements; a physical backup captures storage structures using a supported consistency method. Copying a live data directory casually is not such a method. mysqldump --single-transaction obtains a consistent InnoDB view but does not make nontransactional tables consistent, and concurrent DDL can invalidate or interrupt the dump. Coordinate schema changes and inspect engines before selecting that option.

sql Inspect the four base-table engines

SELECT TABLE_NAME,ENGINE FROM information_schema.TABLES
WHERE TABLE_SCHEMA='mysql_course_lab' AND TABLE_TYPE='BASE TABLE'
ORDER BY TABLE_NAME;

Expected output

TABLE_NAME | ENGINE
customers | InnoDB
order_items | InnoDB
orders | InnoDB
products | InnoDB

In the declared fixture, customers, order_items, orders, and products all use InnoDB. A view is excluded by TABLE_TYPE. Real results must be read from the authorized target; a single unexpected nontransactional table changes the consistency argument and requires a revised capture strategy.

Make an illustrative dump reviewable

The shell example is a plan for a separately provisioned lab, not a command executed by this lesson. The password option prompts rather than embedding a secret. source-data=2 records commented replication coordinates and can require additional privileges and brief locking. set-gtid-purged=OFF suits this isolated teaching export, not every production replication recovery. Include routines, events, and triggers deliberately and verify object coverage against the intended restore contract.

bash Review-only lab dump command

mysqldump --host=127.0.0.1 --user=lab_backup --password \
  --single-transaction --quick --source-data=2 \
  --routines --events --triggers --set-gtid-purged=OFF \
  --databases mysql_course_lab > /tmp/mysql-course-lab.sql

Do not run against an unidentified endpoint. Check exit status and stderr, protect the destination file, record server/client versions and capture coordinates, and copy the artifact to controlled durable storage. A nonempty file alone is not evidence that the dump completed successfully.

Restore to an isolated target first

A restore can create and overwrite objects, so select an empty isolated target with reviewed compatibility and sufficient space. Restore schema, data, and stored objects according to their dependencies; keep the application disconnected until validation completes. Then check representative row counts, relationships, totals, object definitions, and application reads. Measure elapsed time and document missing privileges or environment assumptions rather than declaring success from the import exit code alone.

sql Validate a restored unchanged fixture

SELECT (SELECT COUNT(*) FROM customers) AS customers,
       (SELECT COUNT(*) FROM orders) AS orders,
       (SELECT COUNT(*) FROM order_items) AS lines;
SELECT SUM(total) AS ordered_value FROM orders;

Expected output

customers | orders | lines
3 | 3 | 4
ordered_value
65.00

The expected counts are 3, 3, and 4, with ordered value 65.00. These are useful checks but not a complete integrity proof; a wrong row could preserve both count and sum. Also reconcile line totals and inspect selected identities and foreign keys before treating the restore as usable.

Replay only a verified log interval

Point-in-time recovery starts with a consistent base backup and the uninterrupted binary-log chain after its recorded position or GTID boundary. Identify the unwanted transaction and stop before its boundary, not halfway through a multi-event transaction. Timestamps help investigation but time zones and transaction boundaries need review. Rehearse replay on a clone, compare business invariants, and retain original artifacts. Never improvise log purging during an incident.

Common errors and fixes

A successful dump file cannot be restored.

Why it happens: Exit errors, omitted objects, privileges, or version incompatibilities were not checked.
How to fix it: Repeat a controlled capture and restore rehearsal with recorded diagnostics.
Prevent it next time: Make scheduled restore validation part of the backup policy.

A supposedly consistent dump has mismatched data.

Why it happens: Nontransactional tables or concurrent DDL invalidated single-transaction assumptions.
How to fix it: Use a capture method that covers all engines and coordinates schema changes.
Prevent it next time: Inventory engines and freeze incompatible DDL for the capture window.

Point-in-time recovery stops before the required time.

Why it happens: A missing or purged binary log breaks the replay chain.
How to fix it: Assess the last recoverable boundary and restore from an intact verified chain.
Prevent it next time: Align binary-log retention and off-host archival with recovery objectives.

Check your understanding

1. A backup is 24 hours old and all later binary logs are missing. Can it promise five-minute data loss?

Show worked solution

No. The recoverable point is limited by the available backup and log chain. A five-minute objective requires a suitable capture/archive strategy and restore evidence. State the actual recoverable boundary honestly rather than equating a desired objective with an existing guarantee.

2. Why do counts 3/3/4 and total 65.00 not prove a perfect restore?