Logical Backup with mariadb-dump: Consistency, Objects, Routines, and Large Databases

Learning outcomes

ServiceHub can recreate application binaries from Git, but a recent staging incident exposed a harder question: could the team rebuild the database state after an operator deletes a customer queue? A developer answers, “we have a nightly SQL file.” That is not yet a recovery capability. Nobody has proved whether the file is transactionally consistent, whether stored routines and scheduled events are inside it, whether a schema change can race the dump, or whether the file can actually be imported into a clean MariaDB instance.

A logical backup records database objects as logical SQL or delimited data rather than copying MariaDB's physical data files. mariadb-dump is MariaDB's standard logical-dump client. Logical backups are portable and inspectable, but their correctness depends on storage-engine semantics, the chosen dump options, concurrent Data Definition Language (DDL), object coverage, privileges, character sets and—most importantly—successful restore testing.

1. Start with the recoverability question, not the dump command

A backup should correspond to a defined recovery promise. For a logical backup, ask: which schemas, objects and data must be reconstructed; what consistency point is required; how much concurrent write activity is allowed; how long may export and import take; and what will prove the result is usable?

2. Build a disposable ServiceHub logical-backup lab

Run the following only on a disposable local MariaDB Community instance. The lab uses InnoDB so the mandatory consistency experiment does not depend on optional storage engines. A trigger, stored procedure and event make object coverage observable.

DROP DATABASE IF EXISTS servicehub_recovery_lab;CREATE DATABASE servicehub_recovery_lab  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;USE servicehub_recovery_lab;CREATE TABLE tickets (  ticket_id BIGINT PRIMARY KEY,  customer_name VARCHAR(120) NOT NULL,  status ENUM('open','assigned','closed') NOT NULL,  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP             ON UPDATE CURRENT_TIMESTAMP) ENGINE=InnoDB;CREATE TABLE ticket_audit (  audit_id BIGINT AUTO_INCREMENT PRIMARY KEY,  ticket_id BIGINT NOT NULL,  old_status VARCHAR(20),  new_status VARCHAR(20),  changed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB;DELIMITER //CREATE TRIGGER tickets_auAFTER UPDATE ON ticketsFOR EACH ROWBEGIN  IF NOT (OLD.status <=> NEW.status) THEN    INSERT INTO ticket_audit(ticket_id, old_status, new_status)    VALUES(NEW.ticket_id, OLD.status, NEW.status);  END IF;END//CREATE PROCEDURE close_ticket(IN p_ticket_id BIGINT)BEGIN  UPDATE tickets SET status='closed' WHERE ticket_id=p_ticket_id;END//DELIMITER ;CREATE EVENT IF NOT EXISTS ev_recovery_markerON SCHEDULE EVERY 1 DAYDO INSERT INTO ticket_audit(ticket_id, old_status, new_status)   VALUES(0, 'event', 'marker');INSERT INTO tickets(ticket_id,customer_name,status) VALUES(3001,'Northwind Clinic','open'),(3002,'Alpine Support','assigned'),(3003,'Contoso Field Ops','open');CALL close_ticket(3003);

Inspect what exists before backup. This pre-backup inventory becomes the restore acceptance baseline.

SELECT COUNT(*) AS ticket_rows FROM servicehub_recovery_lab.tickets;SELECT COUNT(*) AS audit_rows FROM servicehub_recovery_lab.ticket_audit;SHOW TRIGGERS FROM servicehub_recovery_lab;SHOW PROCEDURE STATUS WHERE Db='servicehub_recovery_lab';SHOW EVENTS FROM servicehub_recovery_lab;SHOW CREATE TABLE servicehub_recovery_lab.tickets\G

Expected state: three ticket rows, at least one audit row from the procedure-driven update, one trigger, one procedure and one event. The exact timestamps and metadata formatting vary. This evidence proves the source objects existed; it does not prove the backup captured them.

3. Understand --single-transaction precisely

With InnoDB, --single-transaction starts a transaction with a consistent snapshot so rows read later in the dump can represent the same logical point even while ordinary DML continues. This is far less disruptive than locking every InnoDB table for the full export. The property comes from InnoDB multi-version concurrency control (MVCC), not from magic inside the output file.

If you want to study the mixed-engine boundary, first run SHOW ENGINES. Only if a nontransactional engine is supported in your local build, create a disposable table with that engine and observe that a transaction snapshot does not roll its changes back or version them like InnoDB. Do not make optional-engine behavior a production assumption merely because CREATE TABLE succeeds.

4. Create a deliberate, self-describing dump

Use an option file or another secret mechanism appropriate to your environment rather than placing reusable passwords in shell history. The command below assumes authentication is already configured securely. --quick retrieves rows incrementally instead of buffering an entire table in client memory; current --opt enables it by default, but spelling it out here documents intent.

mkdir -p ./servicehub-backupsmariadb-dump \  --single-transaction \  --quick \  --routines \  --events \  --triggers \  --master-data=2 \  servicehub_recovery_lab \  > ./servicehub-backups/servicehub_recovery_lab.sql

--master-data=2 records binary-log coordinates as comments when binary logging is enabled and is useful when this dump becomes the base of point-in-time recovery. Combined with --single-transaction, MariaDB can coordinate the snapshot and log position for InnoDB-oriented online backup. If binary logging is disabled, do not invent coordinates; Lesson 3 builds the PITR prerequisite deliberately.

Record tool and server versions next to the artifact:

mariadb-dump --versionmariadb --versionmariadb -Nse "SELECT VERSION();"wc -c ./servicehub-backups/servicehub_recovery_lab.sql

On Windows PowerShell, use the same MariaDB client executable but redirect explicitly:

New-Item -ItemType Directory -Force .\servicehub-backups | Out-Nullmariadb-dump --single-transaction --quick --routines --events --triggers --master-data=2 servicehub_recovery_lab |  Set-Content -Encoding utf8 .\servicehub-backups\servicehub_recovery_lab.sqlmariadb-dump --versionmariadb --version

Compression can reduce transfer/storage bytes, but a pipeline must propagate failures from both the dump and compressor. On POSIX shells, enable a failure mode such as set -o pipefail when supported, and verify the resulting compressed stream before promotion:

set -o pipefailmariadb-dump --single-transaction --quick --routines --events --triggers servicehub_recovery_lab   | gzip -c > ./servicehub-backups/servicehub_recovery_lab.sql.gzgzip -t ./servicehub-backups/servicehub_recovery_lab.sql.gz

Do not assume database users and roles belong inside the same application-data dump. Account definitions, authentication plugins, password hashes and grants are security configuration and can be version/platform sensitive. Inventory them separately with SHOW CREATE USER/SHOW GRANTS for the accounts you actually intend to recover, or use a current MariaDB-supported system-account export method after verifying it on the target release. Avoid copying raw mysql.* system tables across major versions as a generic migration technique.

SHOW CREATE USER 'mariadb_backup'@'localhost';SHOW GRANTS FOR 'mariadb_backup'@'localhost';-- Repeat only for explicitly managed accounts/roles that belong in the recovery plan.

A safe restore order is deliberate: create/verify the target server baseline; restore required account/role/definer identities or intentionally rewrite definers; restore schemas/tables/data; recreate routines/triggers/events/views in dependency order; then enable schedules/application traffic only after validation. A single dump file may automate much of that order, but the runbook must still account for external identities, secrets, plugins and definers.

5. Deliberately make an incomplete backup, then diagnose it

A common failure is to assume “dump database” means “dump every executable object.” Create a second dump without routine/event options:

mariadb-dump --single-transaction --quick servicehub_recovery_lab   > ./servicehub-backups/incomplete.sqlgrep -n "CREATE.*PROCEDURE\|CREATE.*EVENT\|TRIGGER"   ./servicehub-backups/incomplete.sql || true

The trigger may still be present because triggers are dumped with their tables by default, while the procedure and event are absent unless requested. That is the important lesson: backup completeness is object-type-specific. The repair is not “hope the defaults are enough”; it is an explicit command plus restore acceptance tests.

A second misleading approach is to run long schema migrations during a dump because “single transaction means frozen database.” The data snapshot and schema metadata are different concerns. Coordinate DDL through deployment controls or a defined backup window, and monitor dump errors rather than suppressing them with --force.

6. Restore into a disposable second instance and prove the result

Never test restore by overwriting the only copy of the source. Use a second local MariaDB instance of the same baseline—another service/data directory or a disposable container. The container example is optional in tooling choice but remains free/local; use port 3307 to avoid colliding with the source.

docker run --name servicehub-restore -d \  -e MARIADB_ROOT_PASSWORD='lab-only-change-me' \  -p 3307:3306 mariadb:12.3.2# Wait until the server reports ready, then create the target schema.mariadb -h 127.0.0.1 -P 3307 -u root -p   -e "CREATE DATABASE servicehub_recovery_lab;"mariadb -h 127.0.0.1 -P 3307 -u root -p   servicehub_recovery_lab   < ./servicehub-backups/servicehub_recovery_lab.sql

If Docker/Podman is unavailable, initialize a second local MariaDB data directory/service on a different port/socket following your operating-system package instructions. The critical property is isolation, not the container product.

SELECT COUNT(*) AS ticket_rows FROM servicehub_recovery_lab.tickets;SELECT COUNT(*) AS audit_rows FROM servicehub_recovery_lab.ticket_audit;SHOW TRIGGERS FROM servicehub_recovery_lab;SHOW PROCEDURE STATUS WHERE Db='servicehub_recovery_lab';SHOW EVENTS FROM servicehub_recovery_lab;CALL servicehub_recovery_lab.close_ticket(3002);SELECT ticket_id,status FROM servicehub_recovery_lab.tickets ORDER BY ticket_id;

Compare those results with the source inventory captured before backup. A successful import with missing routines is still a failed recovery if the application depends on those routines. Likewise, equal row counts do not prove semantic equality; add domain-specific checks such as sums, foreign-key integrity, known business invariants and sampled hashes where appropriate.

7. Large databases: throughput is part of recoverability

Logical dumps scan and serialize data. On large databases, export duration, restore duration, network throughput, disk contention, undo retention pressure from a long consistent snapshot, and single-threaded restore characteristics can dominate. MariaDB versions from 11.5 add newer directory/parallel dump capabilities; treat those as version-gated features and verify exact syntax with your client. Do not claim a universal throughput gain.

8. Production judgment and cleanup

Choose logical backup when inspectability, migration/portability, object-level recovery or selective transformation matters and the measured export/restore time fits objectives. Prefer physical backup for very large same-engine/server recoveries when file-level restore is materially faster; Lesson 2 teaches that path. Many production strategies use both.

Protect dump files as production data. They can contain credentials, personal data, definers and sensitive SQL. Encrypt at rest, restrict filesystem/object-store access, keep integrity metadata, and retain copies away from the database host. Do not delete source binlogs after a dump until the recovery policy proves they are no longer required.

DROP DATABASE IF EXISTS servicehub_recovery_lab;

docker rm -f servicehub-restore  # only if this lab created it

Next, move from logical SQL to physical page/file backup. The mental model changes: mariadb-backup copies a live data directory while coordinating with the server, and the raw copy must be prepared before it becomes a consistent restore candidate.