What Are Logical Backups?

Logical backups export your database as SQL statements - CREATE TABLE commands followed by INSERT statements containing your data. The most common tool for logical backups is mysqldump, included with every MySQL installation.

How mysqldump Works

When you run mysqldump, it connects to MySQL as a client and queries each table, converting the results into SQL statements. The output is a text file that can recreate your database from scratch on any MySQL server.

# Basic mysqldump command
mysqldump -u root -p mydatabase > backup.sql

# With compression
mysqldump -u root -p mydatabase | gzip > backup.sql.gz

Advantages of Logical Backups

• Portable across MySQL versions and even different database systems
• Human-readable output - you can inspect and edit the backup file
• Can backup specific tables or filter data with WHERE clauses
• Works across different operating systems and architectures
• Smaller file sizes for databases with lots of free space or deleted rows
• No special tools required beyond standard MySQL installation

Disadvantages of Logical Backups

• Slow for large databases - must read every row through SQL layer
• Restore is even slower - must execute millions of INSERT statements
• Table locks can impact production performance during backup
• Cannot capture binary logs for point-in-time recovery directly
• InnoDB tables may have inconsistent state without proper locking
• Memory intensive for tables with large TEXT or BLOB columns

What Are Physical Backups?

Physical backups copy the actual database files from the filesystem - the InnoDB tablespace files, redo logs, and other internal MySQL data structures. Tools like Mariabackup and Percona XtraBackup perform physical backups.

How Physical Backup Tools Work

Physical backup tools copy the raw InnoDB data files while the database is running. They track changes made during the copy process using the InnoDB redo log, then apply those changes to create a consistent backup. This 'hot backup' capability means no downtime or table locks.

# Mariabackup full backup
mariabackup --backup --target-dir=/backup/full

# Prepare the backup for restore
mariabackup --prepare --target-dir=/backup/full

Advantages of Physical Backups

• Much faster backup - copies files at disk speed, not SQL query speed
• Much faster restore - copy files back instead of executing SQL
• No table locks - hot backup while database serves production traffic
• Supports incremental backups - only copy changed data
• Captures exact database state for point-in-time recovery
• More efficient for large databases (100GB+)

Disadvantages of Physical Backups

• Must restore to same or compatible MySQL/MariaDB version
• Cannot selectively restore individual tables easily
• Requires same architecture (cannot restore 32-bit backup to 64-bit)
• Backup files are not human-readable
• Requires additional tools beyond standard MySQL installation
• InnoDB-only - MyISAM tables require brief lock

Performance Comparison

The performance difference between physical and logical backups becomes dramatic as database size increases. Here's what to expect:

The restore time difference is often more important than backup time. When your production database is down, every minute counts. A physical restore can get you back online 5-10x faster than importing a SQL dump.

When to Use Each Approach

Use Logical Backups (mysqldump) When:

• Database is small (under 5-10 GB)
• You need to migrate between MySQL versions or forks
• You need to backup specific tables or filtered data
• You want human-readable backups you can inspect
• Cross-platform portability is required
• You're creating development/test database copies with subset of data

Use Physical Backups (Mariabackup/XtraBackup) When:

• Database is large (over 10 GB)
• Minimize backup window impact on production
• Fast restore time is critical for disaster recovery
• You need incremental backups to reduce storage and backup time
• Point-in-time recovery capability is required
• Running continuous backup systems with tight RPO requirements

Consider Using Both When:

Many organizations use both approaches: physical backups for fast disaster recovery and logical backups for long-term archival, cross-version migration, or creating development copies. This provides flexibility while optimizing for different scenarios.

Incremental Capabilities

One of the biggest advantages of physical backups is incremental backup support. Instead of copying the entire database every time, incremental backups only copy data that changed since the last backup.

Physical Incremental Backups

Mariabackup and XtraBackup track changes at the page level using InnoDB's Log Sequence Number (LSN). An incremental backup only copies pages modified since the last backup's LSN.

# Full backup (base for incrementals)
mariabackup --backup --target-dir=/backup/full

# First incremental (changes since full)
mariabackup --backup --target-dir=/backup/inc1 \
  --incremental-basedir=/backup/full

# Second incremental (changes since inc1)
mariabackup --backup --target-dir=/backup/inc2 \
  --incremental-basedir=/backup/inc1

For a 100GB database with 1GB of daily changes, incremental backups reduce daily backup size from 100GB to ~1GB - a 99% reduction in storage and backup time.

Logical Backup Limitations

mysqldump has no built-in incremental capability. Every backup is a full export of all data. Some workarounds exist (binary log replay, trigger-based change tracking) but they add complexity and aren't true incremental backups.

How DBCalm Uses Physical Backups

DBCalm leverages physical backup technology (Mariabackup/XtraBackup) to provide 15-minute incremental backups without impacting your production database performance.

The DBCalm Approach

• Full physical backup as the base, then incremental backups every 15 minutes
• Hot backups with zero table locks - no impact on running applications
• Automated backup verification by restoring and validating each backup
• AES-256 encryption before data leaves your server
• Compressed storage for efficient retention of backup history

This approach combines the speed advantages of physical backups with automation, verification, and encryption - solving the operational challenges of managing physical backup infrastructure yourself.

Frequently Asked Questions

Related Guides

• Incremental Backup Explained
• Point-in-Time Recovery Guide
• MySQL Backup Best Practices