Logical backups with SQL dumps

Last reviewed: 2026-02-11 / Plakar v1.1.0

Overview

Logical backups export database structure (CREATE DATABASE, CREATE TABLE) and content (INSERT statements) using mysqldump. These backups are machine-independent and portable across MySQL versions and architectures.

For a deeper understanding of logical backups and MySQL backup strategies, we recommend reading the official MySQL documentation on mysqldump.

Prerequisites

• Running MySQL server
• MySQL credentials with dump privileges
• mysqldump and mysql utilities installed

Configure Credentials

Set environment variables to avoid exposing credentials on command line:

export MYSQL_HOST=xxxx
export MYSQL_TCP_PORT=3306
export MYSQL_USER=xxxx
export MYSQL_PWD=xxxx

Back Up Single Database

Basic backup

mysqldump <dbname> | plakar at /var/backups backup stdin:dump.sql

InnoDB with all objects (recommended)

mysqldump --single-transaction \
  --routines \
  --triggers \
  --events \
  <dbname> | plakar at /var/backups backup stdin:dump.sql

Options:

• --single-transaction: Consistent snapshot without locking tables (InnoDB)
• --routines: Include stored procedures and functions
• --triggers: Include table triggers
• --events: Include scheduled events

Back Up All Databases

mysqldump --all-databases \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  --set-gtid-purged=OFF | \
  plakar at /var/backups backup stdin:all_databases.sql

The --set-gtid-purged=OFF option improves portability across MySQL configurations.

Restore Database

Single database

plakar at /var/backups cat <SNAPSHOT_ID>:dump.sql | mysql <dbname>

All databases

plakar at /var/backups cat <SNAPSHOT_ID>:all_databases.sql | mysql

List snapshots:

plakar at /var/backups ls

Mixed Storage Engines

For databases using both InnoDB and MyISAM, use --lock-all-tables:

mysqldump --all-databases --lock-all-tables | \
  plakar at /var/backups backup stdin:dump.sql

This blocks all write operations during the dump.

Best Practices

Credentials

• Use environment variables or ~/.my.cnf
• Never pass passwords with -p<password> on command line (exposes in process listings)

Compression

• Do not compress dumps manually
• Plakar automatically deduplicates and compresses data
• Pre-compressed dumps prevent effective deduplication

Storage Engines

• Use --single-transaction for InnoDB (default since MySQL 5.5)
• Use --lock-all-tables for mixed InnoDB/MyISAM environments