Logical backups with SQL dumps

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.

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

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

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

$ 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

$ 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.

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

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

List snapshots:

$ plakar at /var/backups ls

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.

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

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

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