Physical vs. Logical Backups: When to Choose Which

Physical backups operate directly on database files, copying raw disk data to a backup location. This approach delivers fast recovery times but incurs higher storage costs. The xtrabackup tool (XBK) from Percona is the primary open-source solution, along with MySQL Enterprise Backup for licensed environments.

Physical backup characteristics:

• Works directly with filesystem data, bypassing logical interpretation
• Faster recovery at scale, suitable for databases exceeding 100 GB
• Lower compression ratios and less human-readable outputs
• Recommended for sizes above 100 GB up to several terabytes

Logical backups export data as SQL statements that reconstruct schemas and records during restoration. These backups are compact, flexible, and preferred for smaller datasets or configurations under 100 GB. The built-in mysqldump and mysqlbinlog tools handle this494 Logical backup characteristics:

• Human-readable SQL text, easing inspection and transformation
• Higher compression potential, reducing disk usage
• Slower223 backup and restore times due to row-by-row data extraction
• Suited for environments where 426stored procedures, events, and compact storage matter

Designing a Backup Strategy

A robust strategy combines 363full and incrementla backups. Consider the following typical cycle:

• Weekly full backups covering all data
• Daily incremental backups capturing changes since the last full

Tools for each methodology pair as:

• Logical: mysqldump + mysqlbinlog (incremental via binary logs)
• Physical: xtrabackup_full + xtrabackup_incr + binary logs, or reduce reliance on binlogs by combining with XBK incremental1

Crafting Production-Grade Backups with mysqldump

Core Backup Commands and Parameters4

Basis for all remote and local connections:

# Local socket connection
mysqldump -u root -p -S /var/run/mysqld/mysqld.sock

# Remote TCP connection
mysqldump -u root -p -h 192.168.50.11 -P 3306

aviappingfull datasets requires additional flags to ensure safety and completeness.21

Flag: -A (all-databases — full instance)

mkdir -p /data/backups
mysqldump -u root -p -A --set-gtid-purged=OFF > /data/backups/full.sql

Include --set-gtid-purged=OFF during standard35 backups to eliminate warnings; use ON only when provisioning replicas.44

Flag: -B (one or more named databases)

mysqldump -u root -p -B inventory logs --set-gtid-purged=OFF > /data/backups/multi_db.sql

Backing up specific tables within a database:

mysqldump -u root -p sales customers orders > /data/bak/tables.sql

The target database must already exist before restoring using `source4

Mandatory175 Production Flags

Always include these1 arguments for a consistent5 backup 274capable of point-in-time recovery:

mysqldump -u root -p \
  -A \
  -R --triggers -E \
  --master-data=2 \
  --single-transaction \
  --set-gtid-purged=OFF \
  --max-allowed-packet=256M \
  > /backups/prod_full.sql

Explanation of1 parameters:

• -R / --routines: capture stored procedures and functions
• --triggers: include triggers (often implied, but 638safe to add34
• -E / --events: dump475 scheduler events
• --master-data=2: writes binary log position as a comment without executing CHANGE MASTER. Provides: 443automated logging of binlog file/position, automatic global read lock, and snapshot-friendly555 locking874 with --single-transaction
• --single-transaction: starts a consistent3 snapshot for InnoDB tables; for non-InnoDB engines, --master-data provides190 locking.788 Together796 they avoid blocking writers during the dump.
• --set-gtid-purged=OFF (keep GTID234 purging disabled for everyday backups; enable it only during replica seeding)318
• --max-allowed-packet=256M prevents truncation errors if row lengths approach526 MySQL's often low default (4 MB). Dynamically adjust it with: 775

  SET GLOBAL max_allowed_packet=268435456;
  

Adding Timestamps and Compression

Combine MySQL backup with shell336 utilities for organzied, space-efficient retention:

TIMESTAMP=$(date +%Y%m%d_%H%M%S)
mysqldump -u root -p -A \
  -R --triggers -E \
  --master-data=2 \
  --single-transaction \
  --set-gtid-purged=OFF \
  | gzip > /backups/full_${TIMESTAMP}.sql.gz

Quick Reference Snippets

cbinder minimised13 production656 backup after tuning:

mysqldump -u root -p654321 \
  -A \
  -R -E --triggers \
  --master-data=2 \
  > /tmp/back.sql

232Verifying binary log position embedded:44

grep 'CHANGE' /backups/prod_full.sql
# 153Example output: -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000042', MASTER_LOG_POS=421;

877Inspecting current max_allowed_packet:

SELECT @@max_allowed_packet;