MySQL and MariaDB backup basics (mysqldump and physical)

Topic: Databases core

Back up MySQL or MariaDB with mysqldump for logical backups or use filesystem snapshots with FLUSH TABLES WITH READ LOCK for consistent physical backup. Use this when setting up backup jobs or when you need to restore a database.

Intent: How-to

Quick answer

  • Logical: mysqldump -u root -p --single-transaction --routines --triggers --databases mydb > mydb.sql. --single-transaction for InnoDB consistency without global lock. Use --all-databases for full dump (include user table backup).
  • Physical: FLUSH TABLES WITH READ LOCK; take filesystem snapshot or copy data dir; UNLOCK TABLES; or use mysqlbackup (Enterprise) or Percona XtraBackup for non-blocking physical backup.
  • Restore: mysql -u root -p mydb < mydb.sql. For full dump, restore to a temporary instance or ensure no conflicts. Test restore regularly; retain backups per policy.

Prerequisites

Steps

  1. Logical backup with mysqldump

    mysqldump -u root -p --single-transaction --routines --triggers --databases mydb > /backup/mydb.sql. Add --events if using events. Schedule with cron; compress and retain per policy.

  2. Full server dump

    mysqldump -u root -p --all-databases --routines --triggers --events > /backup/all.sql. Includes mysql system DB (users); restore with care. For physical, use XtraBackup or snapshot with read lock.

  3. Restore

    mysql -u root -p mydb < /backup/mydb.sql. Create database first if not in dump. For all-databases dump, ensure no object conflicts; consider restoring to a new instance.

  4. Verify and automate

    Verify backup file size and optional checksum; test restore on a schedule. Automate with cron or backup tool; alert on failure; document RPO and RTO.

Summary

Use mysqldump with —single-transaction for logical backups; use physical backup (snapshot or XtraBackup) for full server. Restore with mysql; test and automate. Use this to implement MySQL/MariaDB backup strategy.

Prerequisites

Steps

Step 1: Logical backup with mysqldump

Run mysqldump with —single-transaction for one or all databases; schedule and retain.

Step 2: Full server dump

Use —all-databases when needed; for physical, use XtraBackup or a coordinated snapshot.

Step 3: Restore

Restore with mysql < dump.sql; create database if needed; handle full dumps carefully.

Step 4: Verify and automate

Verify backups; test restores; automate and alert.

Verification

  • Backup files exist and are restorable; a test restore completes successfully.

Troubleshooting

Inconsistent backup — Use —single-transaction for InnoDB; for mixed engines, use brief FLUSH TABLES WITH READ LOCK. Restore fails — Check dump file integrity and MySQL version compatibility.

Next steps

Continue to