Logical vs Physical Backups, Online/Offline Strategies, and Using mysqldump for MySQL

Logical Backup and Physical Backup

Logical Backup

Logical backup captures database schema (CREATE DATABASE, CREATE TABLE) and data (INSERT) and is suitable for small data volumes, cross‑SQL‑server migrations, or when data needs to be modified. The mysqldump command generates a logical backup file containing CREATE TABLE and INSERT statements that can recreate tables and data.

Advantages

High portability – SQL statements can be applied to other SQL servers. Data can be added or modified before restoration. Granular recovery at server, database, or table level. Human‑readable text format.

Disadvantages

Backup requires access to the MySQL server, affecting other clients. Data must be converted to logical formats (SQL, CSV). If the command runs on the client, the server must transmit data to the client. Text files are larger than raw files.

Physical Backup

Physical backup copies the database files and directories directly, making it ideal for large, critical databases that need fast recovery.

Advantages

Complete MySQL files and directories are copied; no conversion needed, so it is faster than logical backup. Configuration and log files can be backed up together. Backup can be performed without running the MySQL server. Simple tools such as cp , scp , or tar suffice.

Disadvantages

Low portability – restoration works only on similar machines. Typically requires a shutdown (cold backup) to keep files consistent. Recovery granularity cannot be at table or user level.

Online Backup and Offline Backup

Online backup (hot backup) runs while the MySQL server is active, minimizing impact on clients but requiring locks and possibly affecting performance. Offline backup (cold backup) stops the server, simplifying the process and avoiding client interference.

Key Characteristics of Online Backup

Does not require server downtime; other clients can continue read operations. Requires locking to prevent data changes during the backup.

Key Characteristics of Offline Backup

The server is unavailable during the backup. The process is simpler and not affected by client activity.

Logical Backup (Using mysqldump)

The mysqldump command is convenient and flexible; the output can be edited or imported into other SQL servers. However, for large data sets the backup and especially the restore can be slow because it replays SQL statements.

shell> mysqldump [options] db_name [tbl_name ...]
shell> mysqldump [options] --databases db_name ...
shell> mysqldump [options] --all-databases

When using mysqldump , ensure the database remains in a consistent state. Two common methods are:

Set the MySQL server to read‑only.

Use a transaction with isolation level REPEATABLE READ .

Example command with repeatable‑read transaction:

mysqldump --master-data=2 \
  --flush-logs \
  --single-transaction \
  --all-databases > /backup/`date +%F-%H`-mysql-all.sql

Parameter explanations:

--master-data : backs up binary log file name and position. --flush-logs : flushes MySQL logs before starting the backup. --single-transaction : sets isolation level to REPEATABLE READ and issues START TRANSACTION before dumping. --all-databases : backs up all databases.

Physical Backup (Copying Raw Files)

For integrity, stop the MySQL server before copying raw files:

Stop MySQL server $ mysqladmin shutdown Copy data files, e.g., with tar $ tar cf /tmp/dbbackup.tar ./data Restart MySQL server $ mysqld_safe

Using Master‑Slave Backup Mode

Combining mysqldump (logical) and tar (physical) can impact production. A master‑slave setup mitigates this: backup the slave while the master continues serving traffic. The slave can be stopped or locked without affecting business operations.

In a single‑machine environment, add a slave that replicates data from the master, then perform backups on the slave.

— End of article —