How to create a MySQL or MariaDB database backup

A logical backup makes schema changes, accidental deletes, and failed upgrades recoverable without rebuilding data by hand. A recent dump file is often the fastest path to a clean restore point for testing, rollback, or disaster recovery.

Current MySQL releases use mysqldump, while current MariaDB releases use mariadb-dump for the same job. Both clients connect to the server and write SQL statements for tables, views, triggers, routines, and events, and piping the output through gzip produces one compressed archive that is easier to move, retain, and verify.

Consistency and permissions still matter. --single-transaction creates a consistent snapshot for transactional tables such as InnoDB, but non-transactional tables such as MyISAM or MEMORY can still change during the dump. A restrictive umask keeps the archive private, --no-tablespaces avoids extra tablespace metadata and the extra MySQL privilege it can require, and full-instance dumps should keep --routines and --events explicit because current MySQL releases no longer include them automatically with --all-databases.

1. Create a private backup directory.

   $ sudo install --directory --owner=root --group=root --mode=0700 /var/backups/mysql

2. Select the dump client name available on the host.

   $ DUMP_CMD="$(command -v mariadb-dump >/dev/null 2>&1 && echo mariadb-dump || echo mysqldump)"
   $ printf '%s\n' "$DUMP_CMD"
   mariadb-dump

   Current MariaDB releases use mariadb-dump, and current MySQL releases still use mysqldump. MariaDB 11 removed the mysqldump symlink from the official image, so choosing the binary explicitly avoids a stale command name.

3. Set a restrictive file-creation mask for the current shell before writing the archive.

   $ umask 077

   Files created after this command default to mode 600 in the current shell session, which keeps the dump readable only by the current user unless permissions are changed later.

4. Generate a timestamp string for the backup filename.

   $ date +%F_%H%M%S
   2026-04-09_170422

   Reuse the timestamp output in the filenames below to keep backups sortable.

5. Create a compressed dump of a single database.

   $ "$DUMP_CMD" --user=dbbackup --password --single-transaction --quick --routines --events --triggers --no-tablespaces --databases appdb | gzip --stdout > /var/backups/mysql/appdb-2026-04-09_170422.sql.gz
   Enter password:

   Avoid putting passwords on the command line with --password=secret or -psecret, since process listings and shell history can expose them.

   --databases adds CREATE DATABASE and USE statements so the dump can recreate the named schema during restore.

   If the backup includes MyISAM or other non-transactional tables, plan a quiet write window or use a lock-based dump instead of relying on --single-transaction alone.

6. Create a compressed dump of a single table when only a narrow object set is needed.

   $ "$DUMP_CMD" --user=dbbackup --password --single-transaction --quick --triggers --no-tablespaces appdb orders | gzip --stdout > /var/backups/mysql/appdb.orders-2026-04-09_170422.sql.gz
   Enter password:

   Table-level dumps usually do not contain CREATE DATABASE or USE statements, so restore them into an existing target database.

7. Create one compressed archive containing several named databases.

   $ "$DUMP_CMD" --user=dbbackup --password --single-transaction --quick --routines --events --triggers --no-tablespaces --databases appdb inventory | gzip --stdout > /var/backups/mysql/multiple-databases-2026-04-09_170422.sql.gz
   Enter password:

8. Create a full instance backup when the server itself needs to be captured.

   $ "$DUMP_CMD" --user=dbbackup --password --single-transaction --quick --routines --events --triggers --no-tablespaces --all-databases | gzip --stdout > /var/backups/mysql/all-databases-2026-04-09_170422.sql.gz
   Enter password:

   Keep --routines and --events explicit here. Current MySQL releases do not include them automatically with --all-databases.

   --all-databases includes system schemas and account metadata, so store the resulting archive with the same care as a credential backup.

9. Confirm the archive exists and that the restrictive umask was applied.

   $ ls -lh /var/backups/mysql/appdb-2026-04-09_170422.sql.gz
   -rw------- 1 root root 1.8K Apr  9 17:04 /var/backups/mysql/appdb-2026-04-09_170422.sql.gz

   If several dump commands were run, list the whole directory with ls -lh /var/backups/mysql to review every archive written in the session.

10. Test the compressed archive for corruption before moving or rotating it.

    $ gzip --test /var/backups/mysql/appdb-2026-04-09_170422.sql.gz

    No output indicates the gzip stream is readable. A non-zero exit status indicates a truncated or corrupt archive.

11. Preview the start of the dump to confirm the expected database and header.

    $ gzip --decompress --stdout /var/backups/mysql/appdb-2026-04-09_170422.sql.gz | head -n 18
    /*M!999999\- enable the sandbox mode */ 
    -- MariaDB dump 10.19-12.2.2-MariaDB, for osx10.21 (arm64)
    --
    -- Host: 127.0.0.1    Database: appdb
    -- ------------------------------------------------------
    -- Server version	11.8.6-MariaDB-ubu2404
    
    /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
    /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
    /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
    /*!40101 SET NAMES utf8mb4 */;

    Recent MariaDB dumps begin with a sandbox-mode comment. Restore those dumps with mariadb or another MariaDB-compatible import path, not an older MySQL client that does not understand the header.

    MySQL dumps begin with a -- MySQL dump header instead, but the same validation pattern still works.

12. Check that non-table objects are present when the database uses them.

    $ gzip --decompress --stdout /var/backups/mysql/appdb-2026-04-09_170422.sql.gz | grep -nE 'TRIGGER|EVENT|PROCEDURE'
    67:/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`%`*/ /*!50003 TRIGGER orders_before_insert
    96:/*!50106 DROP EVENT IF EXISTS `order_cleanup_demo` */;
    122:/*!50003 DROP PROCEDURE IF EXISTS `order_count` */;

    No matches can mean the database does not use those object types, or that the dump command omitted options such as --routines or --events.