Since MariaDB 10.4 the canonical client commands have carried the mariadb- prefix, with the old mysql* names kept as symlinks. Most hosting backup scripts still call mysqldump, and that works right up until a package update or a switch to a different distribution’s packaging drops the symlink. The 12.x series also added features that only exist under the new name. This guide updates the backup recipe, explains the options that matter on a hosting server, and covers the wildcard database dumps that make per-customer backups simpler.
Applies to MariaDB 10.4+ (wildcard dumps need 12.1+); MySQL 8.x
Table of Contents
Short answer: On MariaDB, mysqldump is only a symlink to mariadb-dump, so scripts should call mariadb-dump directly (falling back to mysqldump on MySQL) and always pass --single-transaction --routines --events --triggers --hex-blob --default-character-set=utf8mb4. MariaDB 12.1 and later add mariadb-dump -L 'prefix_%' to dump every database matching a LIKE pattern in one command, which suits per-account backups on cPanel. A dump is only a backup once its last line reads Dump completed and it has been restored into a scratch database.
Check what your server actually provides
which mariadb-dump mysqldump
ls -la $(which mysqldump)
mariadb-dump --versionOn a MariaDB server, mysqldump should be a symlink to mariadb-dump. On a MySQL server, mariadb-dump will not exist at all. Scripts that need to run on both should test for the binary and fall back:
DUMP=$(command -v mariadb-dump || command -v mysqldump)The remaining commands in this guide use mariadb-dump, but everything except the wildcard option applies to mysqldump on MySQL 8.x as well.
The options a hosting backup should always use
The defaults are tuned for a quick ad hoc export, not for a consistent backup of a busy server. A full-server dump that is safe to restore looks like this:
mariadb-dump --all-databases \
--single-transaction \
--routines --events --triggers \
--flush-privileges \
--hex-blob \
--max-allowed-packet=1G \
--default-character-set=utf8mb4 \
| gzip -1 > /backup/all-$(date +%F-%H%M).sql.gz--single-transaction opens a consistent snapshot for InnoDB tables so the dump reflects one point in time without locking; it does nothing for MyISAM or Aria, which are dumped table by table with a brief read lock. --routines, --events and --triggers are off by default and their absence is the most common reason a “complete” restore is missing stored procedures. --hex-blob avoids encoding problems with binary columns. --flush-privileges adds a statement at the end so restored grants take effect.
For a server whose tables are all InnoDB, add --skip-lock-tables explicitly to make the intent obvious to the next person. For a server with large MyISAM tables where even brief locks are a problem, dump during low traffic or move the tables to InnoDB, which is the better answer.
Per-database and per-customer dumps
Restoring one customer from a 200 GB all-databases dump is slow and error-prone. Dump per database as well:
for db in $(mariadb -N -e "SHOW DATABASES" | grep -vE '^(information_schema|performance_schema|mysql|sys)$'); do
mariadb-dump --single-transaction --routines --events --triggers --hex-blob "$db" | gzip -1 > "/backup/db/$db-$(date +%F).sql.gz"
doneOn a cPanel server database names are prefixed with the account name, so a customer’s set is username_*. That is where the 12.1 wildcard option helps.
Wildcard database selection in 12.1 and later
mariadb-dump -L (long form --databases-list in some builds; check mariadb-dump --help | grep -i wildcard for your exact version) accepts patterns rather than explicit names, so a single command dumps every database belonging to one account:
mariadb-dump -L 'shopuser_%' --single-transaction --routines --events --triggers --hex-blob | gzip -1 > /backup/accounts/shopuser-$(date +%F).sql.gzThe pattern uses SQL LIKE syntax, so % is the wildcard and _ matches a single character; escape a literal underscore as \_ if the prefix contains one and you want an exact match. The output includes CREATE DATABASE and USE statements for each matched database, the same as --databases, so the restore is a single mariadb < file. On servers below 12.1 the flag is unknown and the loop above remains the way to do it.
Speed and size
Compression is usually the bottleneck. gzip -1 is a reasonable default; zstd -3 is faster and smaller if it is installed, and pigz uses multiple cores:
mariadb-dump --all-databases --single-transaction --routines --events --triggers | zstd -3 -T4 > /backup/all-$(date +%F).sql.zstVery large single tables are better served by physical backups (mariadb-backup, the Percona XtraBackup equivalent for MariaDB) or by binary-log-based point-in-time recovery, which our PITR guide covers. A logical dump remains the right tool for portability across versions, which is why it is what you take before an upgrade.
Common pitfall
Dumping with the system default character set. Older scripts, or scripts run under a cron environment with no locale, can produce a dump in latin1 that turns every accented character into two bytes on restore. Always pass --default-character-set=utf8mb4 and check the head of the file for the SET NAMES line it produces.
Verify
A dump is not a backup until it has been restored somewhere. At minimum, check that it ends with the completion marker and that it restores into a scratch instance:
zcat /backup/all-*.sql.gz | tail -1
mariadb -e "CREATE DATABASE restore_test"
zcat /backup/db/shopuser_main-*.sql.gz | mariadb restore_test
mariadb -e "SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA='restore_test'"
mariadb -e "DROP DATABASE restore_test"The last line of the dump should be the Dump completed comment; anything else means it was cut short. Our backup verify script runs this check across a directory of dumps and reports files that are truncated, empty or older than expected, and it is worth wiring into the same cron that produces them.
Mariadb-dump vs mysqldump at a glance
Official documentation: MySQL reference manual, MariaDB documentation, AlmaLinux wiki.
Related guides: MariaDB point-in-time recovery with binary logs (and 13.0’s innodb_log_archive) · Harden SSH on AlmaLinux 9 and Rocky 9 in 15 minutes · MariaDB won’t start after an upgrade: InnoDB recovery, mariadb-upgrade and sql_mode issues.
Frequently asked questions
Is mariadb-dump the same as mysqldump?
On a MariaDB server they are the same binary; mysqldump is a compatibility symlink that a package update can drop. The options are largely identical, but features added in 12.x such as the -L wildcard exist only under the mariadb-dump name, and mariadb-dump is not present on a MySQL server at all.
Does –single-transaction lock tables during a mariadb-dump?
Not for InnoDB, where it opens a consistent snapshot and the dump reflects one point in time without blocking writes. MyISAM and Aria tables are still dumped with a brief read lock each, which is one more reason to move customer tables to InnoDB.
How do I dump all databases for one cPanel account?
On MariaDB 12.1 or later, mariadb-dump -L 'username_%' with the usual options dumps every database with that prefix, including CREATE DATABASE statements. On older versions, loop over SHOW DATABASES LIKE 'username\_%' and dump each one to its own file.
Maintenance record
This guide changes servers, data or security settings, so we re-check it against current versions on a fixed schedule. Take a backup or snapshot before you start.
- Maintained by
- srvScripts editorial team
- Supported versions
- MariaDB 10.4+ (wildcard dumps need 12.1+); MySQL 8.x
- Last full review
- Next review