Logical vs Physical Backup: mysqldump, MySQL Shell, Snapshots, and Tradeoffs

Learning outcomes

ServiceHub has reached the point where “we copy the database sometimes” is no longer an acceptable recovery strategy. A backup is useful only if it matches a recovery objective, captures a consistent state, carries enough metadata to restore correctly, and has been restored successfully somewhere other than the source. This lesson starts by choosing the right backup family before touching a command.

Start from the recovery question, not the backup tool

Different failures demand different recovery artifacts. If one table was deleted accidentally, a portable logical dump may be enough. If a multi-terabyte server must return quickly after host loss, replaying SQL row by row may be too slow. If the requirement is “return to 14:31:07 just before an accidental update,” a full backup alone is insufficient; the binary-log chain after that backup must also survive.

No row in this table is “best.” Backup engineering is an optimization against Recovery Point Objective (RPO), the maximum tolerable data loss measured in time, and Recovery Time Objective (RTO), the target time to restore useful service. Chapter 13 measures both instead of treating them as slogans.

Why copying a live InnoDB directory is unsafe

InnoDB is not a collection of independent table files that can be copied arbitrarily while the server is writing. Data pages, redo state, undo history, dictionary metadata, and file-system write ordering participate in a crash-consistent database state. A naive copy that starts with one file and finishes minutes later may combine blocks from different moments. Storage snapshots can be valid building blocks, but only when the snapshot technology and MySQL consistency procedure are understood.

Mandatory free lab: build a logical baseline with mysqldump

-- Run on a disposable local MySQL 8.4 LTS instance.CREATE DATABASE IF NOT EXISTS servicehub_recovery_lab  CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;CREATE TABLE IF NOT EXISTS servicehub_recovery_lab.sites (  site_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,  site_code VARCHAR(20) NOT NULL UNIQUE,  site_name VARCHAR(120) NOT NULL) ENGINE=InnoDB;CREATE TABLE IF NOT EXISTS servicehub_recovery_lab.work_orders (  work_order_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,  site_id BIGINT UNSIGNED NOT NULL,  status ENUM('OPEN','IN_PROGRESS','DONE','CANCELLED') NOT NULL DEFAULT 'OPEN',  summary VARCHAR(180) NOT NULL,  opened_at TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),  closed_at TIMESTAMP(6) NULL,  CONSTRAINT fk_recovery_work_order_site    FOREIGN KEY (site_id) REFERENCES servicehub_recovery_lab.sites(site_id)) ENGINE=InnoDB;CREATE TABLE IF NOT EXISTS servicehub_recovery_lab.recovery_markers (  marker_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,  marker_name VARCHAR(80) NOT NULL UNIQUE,  marker_time TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),  note VARCHAR(255) NOT NULL) ENGINE=InnoDB;INSERT IGNORE INTO servicehub_recovery_lab.sites(site_code,site_name)VALUES ('BAKU-01','Baku Central Service Site'),       ('BAKU-02','Baku East Service Site');INSERT IGNORE INTO servicehub_recovery_lab.recovery_markers(marker_name,note)VALUES ('CH13_BASELINE','Known recovery invariant before Chapter 13 drills');SELECT COUNT(*) AS sites FROM servicehub_recovery_lab.sites;SELECT COUNT(*) AS markers FROM servicehub_recovery_lab.recovery_markers;

INSERT INTO servicehub_recovery_lab.work_orders(site_id,status,summary)SELECT site_id,'OPEN','Inspect pump vibration'FROM servicehub_recovery_lab.sites WHERE site_code='BAKU-01';INSERT INTO servicehub_recovery_lab.work_orders(site_id,status,summary)SELECT site_id,'IN_PROGRESS','Replace pressure sensor'FROM servicehub_recovery_lab.sites WHERE site_code='BAKU-02';SELECT work_order_id, site_id, status, summaryFROM servicehub_recovery_lab.work_ordersORDER BY work_order_id;

# Bash / Command Prompt. -p prompts; do not put the password after -p.mysqldump -h 127.0.0.1 -P 3306 -u root -p \  --single-transaction --routines --triggers --events \  --databases servicehub_recovery_lab \  > servicehub_ch13_baseline.sql# Record an artifact checksum.# Linux/macOS:sha256sum servicehub_ch13_baseline.sql# PowerShell:Get-FileHash .\servicehub_ch13_baseline.sql -Algorithm SHA256

--single-transaction starts a consistent transaction snapshot and is appropriate for transactional tables such as InnoDB. It does not make concurrent DDL harmless and does not give nontransactional engines the same snapshot guarantee. The lab schema is intentionally all InnoDB so the mechanism is visible without mixing engines.

Optional free path: MySQL Shell dump and load utilities

MySQL Shell provides util.dumpSchemas(), util.dumpInstance(), and util.loadDump(). The dump utilities are multithreaded, chunk table data by default, can compress output, and can create checksum metadata. Their default consistent:true procedure coordinates short locks and consistent snapshots across worker threads. This makes them useful for larger data sets, but they are not permission-free magic: the dump account still needs the privileges documented for the selected objects and consistency method.

// mysqlsh --js -u root -p -h 127.0.0.1util.dumpSchemas(  ["servicehub_recovery_lab"],  "servicehub_shell_dump",  {threads: 4, checksum: true, showProgress: true});

Do not memorize threads:4 as a tuning recommendation; it is simply a reproducible starting value. Measure source I/O, CPU, network, and foreground application latency before increasing parallelism.

Failure case: a file exists, but the restore contract is incomplete

Create a damaged copy of the dump instead of damaging the good artifact. The goal is to show that backup presence and backup integrity are different facts.

# Cross-platform Python one-liner; keep the original dump untouched.python -c "p='servicehub_ch13_baseline.sql'; d=open(p,'rb').read(); open('servicehub_ch13_truncated.sql','wb').write(d[:max(1,len(d)//3)])"# Linux/macOSsha256sum servicehub_ch13_baseline.sql servicehub_ch13_truncated.sql# PowerShellGet-FileHash .\servicehub_ch13_baseline.sql,.\servicehub_ch13_truncated.sql -Algorithm SHA256

A different hash is enough to reject the damaged artifact before attempting production recovery. Hash equality does not prove logical correctness; it only proves the bytes match the expected artifact. Recovery testing must continue through a real restore.

Restore into a disposable target and verify business invariants

-- Remove only the disposable lab database if it already exists.DROP DATABASE IF EXISTS servicehub_recovery_lab;

# Bash / Command Promptmysql -h 127.0.0.1 -P 3306 -u root -p < servicehub_ch13_baseline.sql# Windows PowerShell can delegate redirection to cmd.exe:cmd.exe /c "mysql -h 127.0.0.1 -P 3306 -u root -p < servicehub_ch13_baseline.sql"

SELECT COUNT(*) AS site_count FROM servicehub_recovery_lab.sites;SELECT COUNT(*) AS work_order_count FROM servicehub_recovery_lab.work_orders;SELECT marker_name,noteFROM servicehub_recovery_lab.recovery_markersWHERE marker_name='CH13_BASELINE';SELECT COUNT(*) AS orphan_work_ordersFROM servicehub_recovery_lab.work_orders wLEFT JOIN servicehub_recovery_lab.sites s ON s.site_id=w.site_idWHERE s.site_id IS NULL;

Expected evidence is not a magic row count copied from this page, because a learner may rerun inserts. The stable invariants are: the baseline marker exists, the schema and foreign key exist, and the orphan query returns zero. Recovery acceptance criteria should be business-aware, not only “mysql exited with code 0.”

Production judgment

Logical backups are strong when portability, selective restore, schema visibility, or smaller databases matter. MySQL Shell dumps add parallelism and metadata for larger logical workflows. Storage snapshots can reduce capture time but move critical correctness into the snapshot coordination and restore procedure. Commercial physical backup tooling may be appropriate when recovery time at large scale justifies it, but no paid product is required for this course.

Whichever method you choose, protect backup confidentiality, keep the backup identity/privilege model separate from the application, record version/tool assumptions, and test restore on a clean target. The next lesson focuses on one subtle requirement that decides whether a backup is even internally consistent while the application stays online.

Authoritative references

• MySQL 8.4 — Database Backup Methods
• MySQL 8.4 — Dumping Data in SQL Format with mysqldump
• MySQL 8.4 — Reloading SQL-Format Backups
• MySQL Shell 8.4 — Instance/Schema/Table Dump Utilities
• MySQL Shell 8.4 — Dump Loading Utility