Make a Safe MariaDB Dump with mysqldump

You will finish with a logical SQL backup that you can inspect and load into a MariaDB database. The examples use the installed MariaDB 10.11.14 client package, where mysqldump is the compatibility name for mariadb-dump. Allow 15 minutes for a small database, plus the time needed to read and verify a large dump.

You need the mariadb-client package, a database account with permission to read the objects being dumped, and enough free disk space for the output. These commands do not need sudo. Use elevated privileges only if your local authentication policy explicitly requires them, and keep the database password out of the shell history.

1. Confirm the client and its defaults

Check the binary before relying on an option copied from another server:

$ mysqldump --version
mysqldump  Ver 10.19 Distrib 10.11.14-MariaDB, for debian-linux-gnu (x86_64)
$ mysqldump --help | sed -n '1,35p'

The client reads option files including /etc/my.cnf, /etc/mysql/my.cnf and ~/.my.cnf. It reads the [mysqldump] and client groups. An unexpected option in one of those files can change a command that looks correct, so use --print-defaults when a dump behaves surprisingly. The installed help is the authority for this machine's exact option set.

Checkpoint: the version should identify MariaDB 10.11.14, or you should record the version actually installed on the host you are backing up.

2. Make a consistent InnoDB dump

For a database whose tables are InnoDB, take the dump with a transaction snapshot and stream rows rather than buffering whole tables:

$ mysqldump --user=backup_user --password \
    --single-transaction --quick \
    --routines --events --triggers \
    --databases example_app > example_app-2026-09-25.sql

--password with no value prompts on the terminal. Do not write --password=secret in a command, because command-line passwords can be exposed through shell history or process inspection. For unattended jobs, use a locked-down MariaDB option file managed by your secrets process and test its permissions separately.

--single-transaction asks the server for a consistent snapshot and automatically turns off --lock-tables. It is useful for transactional engines, currently InnoDB in this client documentation, and normally avoids blocking applications. During the dump, do not allow another connection to run ALTER TABLE, DROP TABLE, RENAME TABLE or TRUNCATE TABLE on the dumped tables if you require a valid snapshot.

The option --quick streams rows and is already enabled by the default --opt group. Keeping it explicit makes the memory behaviour visible in a runbook. Routines, events and triggers are separate objects, so include the switches when the application depends on them. Check the required privileges before treating a successful table dump as a complete application backup.

Checkpoint: confirm that the command returned status zero and that the file is non-empty:

$ test -s example_app-2026-09-25.sql && echo 'dump file exists and is non-empty'
dump file exists and is non-empty
$ head -n 12 example_app-2026-09-25.sql

The header should contain SQL comments and session setup statements. Do not judge success from a file's existence alone: a connection or privilege error can leave a partial file behind.

3. Cover the right data set

With one database name and no table names, the client dumps the whole database. Add table names when you deliberately want a subset:

$ mysqldump --user=backup_user --password \
    --single-transaction example_app invoices invoice_items \
    > invoices.sql

Use --databases when several database names follow. It adds CREATE DATABASE and USE statements. Use --all-databases only when the account and the destination policy really cover every database. The INFORMATION_SCHEMA and performance_schema databases are not dumped by default.

For structure-only testing, use --no-data. To omit a known table, repeat --ignore-table=database.table, always including both names. Treat exclusions as part of the backup specification: write them in the job's record so a future restore does not quietly miss application data.

4. Treat mixed engines and replication options as hazards

A single transaction does not make MyISAM or MEMORY tables consistent. If a database mixes engines, either arrange a maintenance window for the non-transactional tables or document that their rows can reflect different moments. --lock-tables locks tables per database and does not guarantee a logical cross-database snapshot. --lock-all-tables takes a global read lock for the dump and can stop writes, so do not add it casually.

Options such as --master-data are for replication and point-in-time recovery. They write binary log coordinates into the dump, require the RELOAD privilege, and can change locking behaviour. Use --master-data=2 when you need coordinates as an SQL comment for later planning. Do not use --delete-master-logs as a routine cleanup step: it deletes binary logs after the dump and can destroy recovery material.

5. Restore only into a deliberate target

Restoring executes SQL. The default --opt group includes --add-drop-table, so an ordinary dump can drop an existing table before recreating it. This is destructive and is not undoable by reversing the shell command. First restore into an empty or disposable database, compare the result, and retain the original dump until the application has been checked.

$ mariadb --user=restore_user --password example_app_restore \
    < example_app-2026-09-25.sql
$ mariadb --user=restore_user --password \
    -e "SHOW TABLES" example_app_restore

The installed package provides both mysql and mariadb client names. Use whichever your operational documentation standardises. If the restore is interrupted, do not assume the target is usable: inspect its tables and either remove the disposable database or restore again from a known-good dump. Removing a database is irreversible unless another backup exists, so get an explicit maintenance decision before doing it.