mysqldump vs mariadb-dump and a Restore Drill

mysqldump and mariadb-dump are cousins. On a MariaDB server the binary you want is mariadb-dump; mysqldump is often a compatibility name that points at the same tool, or a leftover from an old Oracle MySQL client. Mixing clients across major versions is how you get dumps that fail to restore, or restores that silently skip GTID and privilege statements. Name the tool, pin the client version to the server series, and treat a dump as unproven until you have restored it onto another host.

A logical dump is not a snapshot of InnoDB files. It is SQL that can rebuild tables. That is why it ports across minor versions, why it is slow on huge schemas, and why a restore drill is part of the backup, not a ceremony you schedule after a disk dies.

Prefer mariadb-dump with a consistent InnoDB read

For InnoDB-only schemas, --single-transaction takes a consistent view without locking every table for the whole dump. Add --routines --triggers --events so you do not restore a database that is missing the procedures the application expects. Add --default-character-set=utf8mb4 so the dump file cannot quietly become latin1. Compress on the fly. Write to a disk that is not the datadir.

#!/usr/bin/env bash
set -euo pipefail
STAMP=$(date +%F-%H%M)
OUT=/var/backups/mariadb
mkdir -p $OUT
mariadb-dump --single-transaction --quick --routines --triggers --events   --default-character-set=utf8mb4 --databases appdb   | gzip -9 > $OUT/appdb.$STAMP.sql.gz
find $OUT -name 'appdb.*.sql.gz' -mtime +14 -delete
ls -lh $OUT | tail

--quick streams rows instead of buffering the whole table in the client. That matters on a 4 GB VPS. Do not dump as root from the application config. Use a backup user with SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER, and PROCESS if you need --master-data later. Skip FILE.

If you still type mysqldump, check what it is:

command -v mariadb-dump mysqldump
mariadb-dump --version
mysqldump --version
readlink -f $(command -v mysqldump)

If mysqldump is an Oracle client talking to MariaDB 11.4, stop using it for backups. Restore compatibility is not guaranteed, and option names have already drifted (master-data versus dump-history, system-wide vs per-series defaults).

A restore is a second VPS, not a hope

A dump you have never restored is a file. The drill is: provision a throwaway Ubuntu host, install the same MariaDB series, restore, run a handful of application queries, then destroy the host. Do this on a schedule, not after an incident.

sudo gzip -dc /var/backups/mariadb/appdb.2026-04-19-0200.sql.gz   | mariadb --default-character-set=utf8mb4
mariadb -e "USE appdb; SHOW TABLES; SELECT COUNT(*) FROM orders;"

Watch the failure modes that actually happen:

• The dump was taken with --no-create-info and the target is empty.
• DEFINER accounts in views and routines do not exist on the target. Restore into a clean instance with matching users, or strip DEFINER in a known-good way.
• max_allowed_packet on the target is smaller than the largest row. Raise it for the restore session.
• The dump includes a CREATE DATABASE that you did not want, or omits one that you did.

Time the restore. That number is your real RTO for this method. A 12 GB gzip that takes 40 minutes to apply means your incident already has a 40 minute floor before you even debug the application. If that is too slow, logical dumps stay as portable insurance and you add a faster method for the tight RTO.

Keep at least one dump off the database host. A backup on the same High-Speed SSD as the datadir dies with the datadir. Push the gzip to another VPS, or to storage that is not Lucknow DC01's live disk.

What a drill runbook looks like on a weekday

Write the steps down so 3 a.m. you is not inventing them:

1. Freeze deploys. Record the dump filename and its checksum.
2. Boot a restore VPS with the same MariaDB series and utf8mb4 defaults.
3. Restore, run COUNT and a couple of JOINs the app actually uses.
4. If this is a real incident, point a staging app at the restore host and only then cut DNS or the proxy.
5. If this is a drill, destroy the restore host and log how long it took.

Do not restore over the production datadir to "see if it works." That is how you turn a backup test into an outage.

Pick mariadb-dump for MariaDB, take consistent InnoDB dumps, and restore onto a throwaway host until the clock and the row counts bore you. That boredom is the point. Spin up a second Ubuntu instance on Netbay in Lucknow in under 60 seconds and run the drill this week — netbayhosts.in.