mysqldump¶
Writes a backup of MySQL and MariaDB databases as SQL; on MariaDB the same tool is also called mariadb-dump.

Syntax¶
mysqldump [OPTIONS] DATABASE [TABLE...]
mysqldump [OPTIONS] --all-databases
Options¶
| Option | Description |
|---|---|
--single-transaction |
Consistent dump of InnoDB tables without locking them |
--all-databases |
All databases, including the user tables in mysql |
--routines, --events, --triggers |
Also stored procedures, scheduled events and triggers |
--no-data |
Only the table structure |
-u USER, -p, -h HOST |
Connection; better put credentials in ~/.my.cnf |
--result-file=FILE |
Write to a file |
Restore with mysql DATABASE < dump.sql (on MariaDB also mariadb).
Examples¶
Dump one database, compressed:
sudo mysqldump --single-transaction --routines shop | gzip > /var/backups/db/shop-$(date +%F).sql.gz
ls -lh /var/backups/db/shop-*
-rw-r--r-- 1 root root 1.8M Oct 26 02:31 /var/backups/db/shop-2026-10-26.sql.gz
The beginning of a dump:
zcat /var/backups/db/shop-2026-10-26.sql.gz | head -6
/*M!999999\- enable the sandbox mode */
-- MariaDB dump 10.19-11.8.3-MariaDB, for debian-linux-gnu (x86_64)
--
-- Host: localhost Database: shop
-- ------------------------------------------------------
-- Server version 11.8.3-MariaDB-0+deb13u1 from Debian
All databases with users and routines:
sudo mysqldump --all-databases --single-transaction --routines --events | gzip > /var/backups/db/all-$(date +%F).sql.gz
ls -lh /var/backups/db/all-*
-rw-r--r-- 1 root root 2.4M Oct 26 02:32 /var/backups/db/all-2026-10-26.sql.gz
Restore into a test database:
sudo mysql -e 'CREATE DATABASE shop_test'
zcat /var/backups/db/shop-2026-10-26.sql.gz | sudo mysql shop_test
sudo mysql -e 'SELECT count(*) FROM shop_test.orders'
count(*)
2
When do I need it?¶
- Every night before the file backup, so the dump is part of it
- Before updates of the database server or a web application such as WordPress or Nextcloud
- To move a database to another server
No password on the command line
With -pSECRET, the password shows up in ps and the shell history. Put it into
/root/.my.cnf with mode 600 ([client], user=, password=), then mysqldump and
mysql need no options.
Without --single-transaction
Without this option, a dump of a busy database can contain tables from different points
in time, or lock them for the duration of the dump. For InnoDB, always use
--single-transaction.
Distribution differences¶
| Debian | Ubuntu | RHEL | Arch | |
|---|---|---|---|---|
| Database server | MariaDB | MySQL or MariaDB | MySQL or MariaDB | MariaDB |
| Package with the tool | mariadb-client |
mysql-client or mariadb-client |
mysql or mariadb |
mariadb-clients |