Database backup strategies — mysqldump, pg_dump, and beyond

Backing up a filesystem snapshot of /var/lib/mysql or /var/lib/pgsql while the database is running gives you a 50/50 shot at a usable restore. Maybe the on-disk state is consistent at the snapshot instant; maybe it's mid-transaction and the restored database refuses to start. The fix is to use the database's own dump tooling, or to coordinate the snapshot with the database's quiesce mechanism. This article covers both paths for MySQL/MariaDB and PostgreSQL on a LYLIX VPS.

MySQL / MariaDB

Logical dumps with mysqldump (simplest, works for most)

# Full dump of every database, with proper locking for consistency.
mysqldump --all-databases \
          --single-transaction \
          --routines --triggers --events \
          --hex-blob \
          | gzip > /backups/mysql-$(date +%F).sql.gz

Flags worth knowing:

• --single-transaction — uses a consistent snapshot via InnoDB's MVCC instead of locking tables. Works only for InnoDB; MyISAM tables aren't covered consistently (they're rare today but check).
• --routines --triggers --events — stored procedures, triggers, scheduled events. Skipped by default for legacy reasons; you almost always want them.
• --hex-blob — encodes BLOB columns as hex, avoids restore problems with special bytes in dump files.

Restore is straightforward:

zcat /backups/mysql-2026-06-22.sql.gz | mysql

Tradeoff: logical dumps are slow for large databases (multi-GB takes minutes), and restore is even slower. Up to ~10 GB this is still the right answer for most operators.

Physical backups with mariabackup / xtrabackup

For databases past ~20 GB or when restore time matters (e.g. a busy FreePBX® install where CDRs accumulate fast), use the physical-backup tool. MariaDB ships mariabackup; MySQL uses Percona's xtrabackup or the bundled mysqldump for logical.

# Full backup
mariabackup --backup --target-dir=/backups/mysql-full-$(date +%F) \
            --user=root --password=...

# Prepare (apply uncommitted transactions so the backup is consistent)
mariabackup --prepare --target-dir=/backups/mysql-full-2026-06-22

Restore involves shutting down the database, replacing the data directory with the prepared backup, fixing ownership, and starting back up. Faster than a logical restore for big data.

PostgreSQL

Logical dumps with pg_dump (custom format preferred)

# Per-database dump in custom binary format — supports selective restore + parallelism
pg_dump -Fc -j 4 -f /backups/mydb-$(date +%F).dump mydb

# All databases, schema + globals (roles, tablespaces)
pg_dumpall --globals-only > /backups/pg-globals-$(date +%F).sql
for db in $(psql -At -c "SELECT datname FROM pg_database WHERE datname NOT IN ('template0','template1');"); do
    pg_dump -Fc -f /backups/pg-${db}-$(date +%F).dump "$db"
done

Restore with pg_restore:

createdb mydb
pg_restore -j 4 -d mydb /backups/mydb-2026-06-22.dump

Continuous archiving with WAL (point-in-time recovery)

For workloads where losing more than a few minutes of data is unacceptable, PostgreSQL supports continuous WAL archiving plus periodic base backups. Lets you restore to any point in time, not just the moment of the last dump.

See the dedicated article in the databases category for the full WAL setup; the short version: enable archive_mode=on, point archive_command at a script that copies WAL segments to off-host storage, and run pg_basebackup nightly for a fresh base.

Schedule patterns that work

• Small (<5 GB), low churn: nightly full dump, retain 14 daily + 4 weekly + 6 monthly.
• Medium (5–50 GB), medium churn: nightly full physical backup, hourly incremental WAL/binlog, retain a week of point-in-time recovery capability.
• Large (50+ GB) or PBX/CDR-heavy: weekly full + daily incremental + continuous WAL/binlog; archive WAL/binlog to off-host storage so a host disaster doesn't take your incremental chain with it.

The off-host part is non-negotiable

Whichever method you use, the resulting dump file is data that lives on the same disk as the database. Snapshots on the LYLIX portal capture both. For real DR, pipe the dump straight to an off-host target via restic or borg, or push it to S3-compatible storage with rclone. See the related articles on off-host backup strategies and restic with Backblaze B2.

What to verify, every time

• Dump exit code is 0 (don't trust "the file got created" — partial dumps with errors still produce non-empty files).
• Dump size is in a sane range. A dump that suddenly halved means tables got dropped (intentionally or otherwise).
• Periodically restore the dump into a throwaway database and run row counts against the originals. The schemas and row counts should match.

None of this catches application-level corruption — if your application wrote bad data, the backup will faithfully preserve the bad data. But it does catch the more common "backup ran, restore doesn't work" failures.