MySQL logical vs physical backups

The mechanism comparison behind MySQL backup strategies. Logical = data expressed as SQL/CSV text (mysqldump, MySQL Shell dump, mydumper) — rebuild from statements. Physical (block-level) = the actual data files: InnoDB tablespaces, redo, copied in place (XtraBackup, clone plugin, cold tar of the datadir).

The one-line trade: logical is portable and slow; physical is fast and picky — restore of a physical backup is a file copy plus a crash-recovery replay, orders of magnitude faster than re-parsing SQL and rebuilding every secondary index.

The USER take, validated (2026-10-20)

Four claimed downsides of physical backups, submitted in Polish (verbatim):

1. blokowy wymaga tej samej wersji mysql na źródłowym serwerze i docelowym serwerze więc się nie nadaje do rolling upgrade
2. jak chcesz odzyskać pojedynczą tabelę z bazy, a nie cały serwer mysql, to trzeba pokombinować i oskryptować podmianę plików tabel
3. plus i minus — kopie przyrostowe: da się, w odróżnieniu od logicznych mysql dumpów, ale trzeba dobrze skonfigurować i przygotować skrypty
4. kopia fizyczna zajmuje więcej miejsca na dysku niż logiczna

Point 1 — version coupling: partially correct

Right idea, wrong precision. Physical restore requires the same major version family (8.0 backup → 8.0 server), and the safe rule is same-or-very-close minor. But:

Correction: it is not “the same version on source and target” — it is same major, same or newer minor. Restoring an 8.0.36 backup onto 8.0.40 binaries is a supported path: that is the in-place upgrade mechanism (8.0.16+ runs the upgrade automatically at startup). What physical genuinely cannot do: downgrades (8.0.36 backup → 8.0.30 server: no) and major crossings (5.7↔8.0, 8.0↔9.x: no). So “not suitable for rolling upgrade” is correct for major-version migrations (logical is the vehicle), but minor-version rolling upgrades inside 8.0 can use physical SST/restore — which is exactly how PXC rolling restarts work between minors.

Point 2 — single-table restore: correct, but easier than “pokombinować” implies

Correction: there is a native mechanism — transportable tablespaces (5.6+): create the empty table with identical DDL on the target, ALTER TABLE ... DISCARD TABLESPACE; copy the .ibd from the backup, ALTER TABLE ... IMPORT TABLESPACE;. Documented, supported, scriptable — not a hack. The real friction is elsewhere: the DDL must match exactly (foreign keys must be handled, partitioned tables import per-partition), and for mysqldump-style flexibility nothing beats logical — a single table from a Shell dump/mydumper is just one file. Verdict stands (physical = ceremony for partial restores), the mechanism is just official rather than improvised.

Point 3 — incrementals: correct, and the caveat deserves emphasis

True both ways: mysqldump has no native incrementals; XtraBackup does (LSN-based: full + chain of increments).

Corrections (two): (a) the chain is the risk — every increment back to the last full must survive and be prepared in order with --apply-log-only; one broken link = restore fails. “Trzeba dobrze skonfigurować i przygotować skrypty” is exactly right, and the scripts must also verify the chain, not just take backups. (b) The nuance on the logical side: logical dumps have no incrementals, but binlog archiving gives logical PITR — you replay to any second from the last dump. So the honest comparison is XtraBackup-incrementals (faster, chain-risk) vs dump+binlog (simpler, slower recovery, larger replay window).

Point 4 — disk usage: mostly wrong

Correction: not universally true, and typically false once compression is on. An uncompressed physical backup carries data + secondary indexes + undo → usually bigger than a logical dump’s raw data-as-text for index-heavy schemas (a dump stores each row once as SQL; indexes are rebuilt at restore). But text SQL is bloated too, and the standard practice — XtraBackup --compress + xbstream, or gzip on a dump — flips it: compressed physical is usually the smallest artifact of all, because binary page compression beats SQL-text compression. The real asymmetry is time, not space: physical restore ≈ copy + crash-recovery (minutes); logical restore = parse SQL + rebuild every index (often 10–50× slower). Space depends on schema and compression; time overwhelmingly favors physical.

Scorecard

#	Claim	Verdict
1	Same-version requirement blocks rolling upgrades	Partially — same-major, newer-minor OK; downgrades/major-crossings no
2	Single-table restore needs scripted file swaps	Correct in friction, but transportable tablespaces are the native supported path
3	Incrementals possible but config/script heavy	Correct — plus chain-verification duty and the binlog-PITR alternative
4	Physical takes more disk than logical	Mostly wrong — depends on indexes/compression; compressed physical usually smallest; physical’s real win is restore time

Kin

• MySQL backup strategies — the umbrella (tools, RPO/RTO patterns)
• Betting on Percona — XtraBackup as the PS/PXC-native lane
• jsystems Kurs administracji PostgreSQL — the same logical/physical split on the PG side (pg_dump vs pgBackRest/WAL), where the same four points translate almost 1:1