MariaDB / Intermediate: concurrency and reusable SQL / Lesson 12

Backup design and logical exports

Choose backup artifacts from recovery requirements. Understand logical consistency, physical preparation and why a successful backup job is not a restore test.

About 4 min readContent reviewed 2026-10-23

On this page

Lesson goals

Map RPO and RTO to backup and log retention.
Explain consistent logical exports and physical backup preparation.

Start with the recovery question

Recovery point objective is the acceptable data-loss window; recovery time objective is the acceptable restoration duration. A nightly dump alone cannot promise five-minute data loss. Logical exports reconstruct objects and rows through SQL, while physical backups preserve engine files and require compatible tools and recovery processing. Decide whether the incident is accidental deletion, server loss or regional loss. That decision determines log retention, off-host copies, encryption-key availability and the isolation needed for a safe restore rehearsal.

Inventory the engines and objects

A consistent InnoDB snapshot does not make concurrently changing nontransactional tables consistent. Before choosing a logical export method, inventory engines, views, routines, triggers and events, and note which privileges and definers will be needed on restore. The query below inventories visible base tables in the training schema. Engine metadata is evidence about the method required, not evidence that a backup already exists or that it can be restored on another server version.

sql Illustrative SQL; expected results are reasoned, not measured.

SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'harbor_lab' AND TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME;
SELECT ROUTINE_NAME, ROUTINE_TYPE FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = 'harbor_lab' ORDER BY ROUTINE_NAME;

Actual inventories depend on privileges. Preserve an object manifest alongside backup artifacts.

Understand a single-transaction export

mariadb-dump can use --single-transaction for a consistent snapshot of suitable transactional tables and --quick to stream rows. This does not freeze all server activity, and concurrent ALTER, DROP or other DDL can break the assumptions. Mixed engines need a separately designed consistency strategy. Verify inclusion of routines, events and triggers using the exact tool options; do not assume a row-only file is a complete service backup. Capture tool version and exit status without exposing credentials.

Read a consistent audit sample

The following read-only transaction illustrates the idea of a coordinated InnoDB snapshot for a training bookings table. Its count and sum refer to one snapshot rather than unrelated moments. An empty table yields count zero and SUM NULL; COALESCE explicitly chooses zero for presentation here. This SQL is not a replacement for a backup utility and does not save data to durable off-host storage. It teaches why a recovery manifest should record the consistency boundary behind its validation totals.

sql Illustrative SQL; expected results are reasoned, not measured.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION WITH CONSISTENT SNAPSHOT, READ ONLY;
SELECT COUNT(*) AS bookings, COALESCE(SUM(total), 0) AS total
FROM bookings;
COMMIT;

With synthetic totals 10 and 15 only, the expected audit is bookings = 2 and total = 25.

A backup needs preparation and proof

mariadb-backup takes physical backups of supported MariaDB storage and requires a prepare phase before normal restoration; raw copied pages are not a ready-to-start data directory. Select the backup tool release according to documented server compatibility, not merely a shared executable name. A restore rehearsal should verify object presence, representative business totals and application behavior, and measure elapsed recovery time. Keep at least one protected independent copy so a compromised or failed source cannot erase every recovery path.

Common errors and fixes

The dump finished but restored totals differ

Why it happens: The export may have mixed consistency boundaries or missed objects.
How to fix it: Inspect engines, DDL activity and export options, then rebuild a validated artifact.
Prevent it next time: Record a manifest and compare logical invariants in isolated restore rehearsals.

A physical backup cannot start as a server

Why it happens: It may be unprepared or incompatible with the chosen server/tool release.
How to fix it: Use the documented prepare and restore sequence on a copy in isolation.
Prevent it next time: Retain matching tool information and test recovery before relying on a backup.

A backup exists only on the database disk

Why it happens: Job success was confused with independent recoverability.
How to fix it: Create protected off-host copies and verify access to encryption keys.
Prevent it next time: Test source-loss and credential-loss scenarios against RPO/RTO.

Check your understanding

1. The business accepts ten minutes of data loss but backups run nightly. What additional recovery chain is needed?

Show worked solution

Retain and transfer binary logs frequently enough to meet the ten-minute target, tied to a known consistent base-backup position. Verify there are no gaps and protect the chain off-host. A restore rehearsal must apply the base and required logs within RTO. Faster backup scheduling alone is another option only if its measured cost and recoverability meet the same requirement.

2. A schema has InnoDB orders and frequently updated Aria counters. Does --single-transaction alone prove one consistent export?