CONCEPT Cited by 1 source
Logical vs physical backup¶
Definition¶
Database backups fall into two architectural families:
- Physical backup copies the on-disk data files (MySQL's
ibdata/ InnoDB tablespaces / binlogs) — typically via a consistent-snapshot tool (xtrabackup, LVM snapshot). Fast to take and fast to restore because it reproduces the engine's on-disk state byte-for-byte. Tied to the engine version + configuration — restore target must match (or be a compatible upgrade path from) the source binary layout. - Logical backup extracts data as SQL statements (
mysqldump) or a structured logical dump (mysqlshelldump utilities) that can be replayed against a fresh engine via SQL commands. Slower to take (row-by-row extraction) and slower to restore (row-by-row insert + index rebuild), but portable across engine versions, portable across engine implementations that speak the same SQL dialect, and selectively restorable (per-table / per-schema / subset-row). Supports point-in-time recovery by composing a logical dump + subsequent binlog replay.
The two are complementary — operators typically keep physical backups for fast disaster recovery and logical backups for schema-version-portable archival / cross-engine migration / selective restore. Modern backup subsystems treat the backup engine as a pluggable interface so both can coexist in one cluster, configured per-workflow per-use-case.
Seen in¶
- sources/2026-04-21-planetscale-faster-backups-with-sharding — canonical production datum on the physical-backup-engine choice. PlanetScale uses Vitess's
builtinbackup engine (physical) as its production default for all MySQL clusters, including the 256-shard / ~230 TB example. Quote: "Internally, PlanetScale uses thebuiltinbackup engine since it works well for our backup procedure." The engine is composed with the dedicated-backup-instance pattern on VTBackup ephemeral compute, writing to S3/GCS. Frames Slack'smysqlshelllogical-backup engine contribution (canonicalised above) as a pluggable alternative — physical is PlanetScale's production choice; logical is the new-in-v21 experimental option for use cases that need PITR / portability / selective restore. - — Vitess 21 introduces an experimental
mysqlshelllogical-backup engine contributed by Slack Engineering, co-existing with the prior-default physical-backup engine: "With this engine, it is possible to run logical backups and restores. The mysqlshell engine can be used to create full backups, incremental backups, and point-in-time recoveries. It is also available to use with the Vitess Kubernetes Operator." First canonical wiki instance of logical-backup coexistence with physical backup inside a Vitess cluster, formalised via the patterns/logical-backup-engine-plug-in pattern. Canonicalises the wiki datum that backup engines are pluggable in Vitess.