Databases & Data / Backup & Recovery

Create and restore a physical backup with mariadb-backup

Install the matching utility, take a streamed or directory backup, prepare redo logs, encrypt and copy it, then restore on an isolated compatible server.

100 min8 stepsChanges system stateRevision 2

Save or explore

#mariadb #mariadb-backup #physical-backup #restore #rto

0 of 8 steps completed

Goal

Prove a low-RTO physical recovery path for a large MariaDB instance.

Supported environments

MariaDB 10.11 LTS, 11.4 LTS

Prerequisites

Recovery access and maintenance window Keep console access and schedule write-affecting work so an interrupted package, grant, backup, or replication change can be recovered.
Verified backup Create a fresh backup and prove that its format, encryption key, and restore procedure are available before changing production data.
Capacity and compatibility Check database version, storage headroom, character sets, application driver compatibility, and expected downtime.df -h; df -i; free -m

Operating boundary

OneLiners never runs these steps or stores secrets. Review placeholders, versions, current state, and change-control requirements before using a command.

Full guide

What you will build

System

A production-ready MariaDB implementation of “Create and restore a physical backup with mariadb-backup” with explicit identities, configuration ownership, observable checkpoints and a tested reversal path.
A before-state, native validation sequence and concrete evidence set covering the database service, client behavior, data or administrative surface affected by this procedure.
A least-privilege operational runbook that keeps secrets out of commands and verifies both the required success path and an expected denial or failure boundary.

Observable outcome

Prove a low-RTO physical recovery path for a large MariaDB instance. The result is demonstrated through the guide's native verification commands and representative application or restore evidence.
Unrelated databases, accounts, listeners and services remain unchanged; operators can identify the exact revision, artifact and rollback action for this change.

Architecture

How the parts fit together

The physical backup tool coordinates file copies with the running server, records redo/binlog position, and prepares a self-consistent restore image. Encryption and off-host storage protect the artifact. Recovery uses a disposable host with the same compatible server family/version, prepares the backup, replaces only an empty controlled data directory, and validates InnoDB plus application-level checks before any production decision.

Hot-backup toolCopies files while coordinating redo and server state.

Prepared imageApplies recorded redo into a self-consistent restore directory.

Encrypted repositoryStores checksummed artifacts outside the server failure domain.

Disposable recovery hostValidates startup, tables and workload without overwriting production.

Capture effective configuration, versions, listeners, identities, grants, workload health and a recoverable before-state.
Prepare the physical backup candidate using only official MariaDB interfaces, protected secret delivery and the narrowest necessary scope.
Apply one controlled layer at a time, run the immediate checkpoint and inspect native logs/status before proceeding.
Perform end-to-end success and expected-failure tests; retain rollback evidence and schedule recurring verification.

Assumptions

The installed MariaDB family and version are supported by the official sources linked below, and package/configuration provenance is known.
A maintenance or canary window, current recoverable backup and console-level recovery path exist before state-changing database work begins.
Operator, application, backup, replication and web-administration identities are separate; secrets are supplied by protected option files, environment injection with appropriate controls, or a secret manager.
Representative schema size, workload, storage capacity, network policy and recovery objectives are documented for the target environment.

Key concepts

user@host identity: MySQL-family authorization matches both an account name and its permitted client origin, not the name alone.
effective configuration: The final runtime values after packaged defaults and ordered configuration fragments have been combined.
consistent state: Data and metadata captured at one valid transactional or recovery point rather than a mixture of moments.
recovery objective: The acceptable data-loss window and restoration time that drive backup, replication and validation design.
positive and negative verification: Proving an intended operation succeeds and an operation outside the granted/security boundary fails.

Before you copy

Values used in this guide

{{ageRecipientsFile}}

Operator-reviewed value used by this tutorial for ageRecipientsFile; derive it from the target environment and never from untrusted request data.

Example: /etc/backup/age-recipients.txt

{{backupRoot}}

Operator-reviewed value used by this tutorial for backupRoot; derive it from the target environment and never from untrusted request data.

Example: /var/backups/mariadb

{{backupUser}}

Operator-reviewed value used by this tutorial for backupUser; derive it from the target environment and never from untrusted request data.

Example: backup_agent

{{criticalTable}}

Operator-reviewed value used by this tutorial for criticalTable; derive it from the target environment and never from untrusted request data.

Example: example-criticaltable

{{database}}

Application database name; use an identifier approved by the database naming policy.

Example: billing

{{protectedSecret}}

Operator-reviewed value used by this tutorial for protectedSecret; derive it from the target environment and never from untrusted request data.

Example: example-protectedsecret

{{restoreHost}}

Operator-reviewed value used by this tutorial for restoreHost; derive it from the target environment and never from untrusted request data.

Example: db-restore.example.com

{{restorePreparedDir}}

Operator-reviewed value used by this tutorial for restorePreparedDir; derive it from the target environment and never from untrusted request data.

Example: /srv/restore/prepared

{{restoreReport}}

Operator-reviewed value used by this tutorial for restoreReport; derive it from the target environment and never from untrusted request data.

Example: example-restorereport

{{restoreUser}}

Operator-reviewed value used by this tutorial for restoreUser; derive it from the target environment and never from untrusted request data.

Example: restore_operator

{{stamp}}

Operator-reviewed value used by this tutorial for stamp; derive it from the target environment and never from untrusted request data.

Example: 20260725T020000Z

Security and production boundaries

Never place database passwords, encryption keys, recovery codes or private certificates directly in reusable shell history or repository files.
Do not use `%`, global grants, disabled TLS verification, public administrative interfaces or world-readable dumps as troubleshooting shortcuts.
Treat backups, slow logs and phpMyAdmin sessions as sensitive data; encrypt, restrict, rotate and monitor them according to the same or stronger policy as production.

Stop before continuing if

Stop if the server version/config family is ambiguous, the backup cannot be restored, secrets would be exposed, or a proposed command affects databases/accounts beyond the declared scope.
Do not continue when native validation, representative workload, expected-denial test, TLS identity, replication continuity or storage capacity evidence is incomplete for this procedure.
Immediately isolate or revert a change that broadens a listener/grant, breaks application health, introduces replication error, loses recoverability or produces unbounded sensitive logs.

instruction

Match backup utility and server versions

read-only

Record server version, data directory, storage engines, encryption, and package candidate; incompatible physical backups can be unusable.

Why this step matters

Match backup utility and server versions is a separate checkpoint because it establishes one auditable part of prove a low-rto physical recovery path for a large mariadb instance. Performing it independently keeps a failed prerequisite, syntax error, or compatibility mismatch from being hidden by later actions.

What to understand

Record server version, data directory, storage engines, encryption, and package candidate; incompatible physical backups can be unusable. Read the complete command or configuration before execution, replace the documented placeholders, and compare the target host with the supported environments listed at the beginning of this tutorial.

Capture the before-state and the exact output in the change record. If this step modifies a file or service, validate the candidate with its native checker before any reload; a successful process exit alone does not prove that the application uses the intended value.

Use a representative staging or canary host first. Repeat the stated checkpoint immediately, because proceeding with ambiguous evidence makes the later end-to-end verification and rollback materially harder.

System changes

This step may affect the files, packages, identities, services, or runtime policy named in “Match backup utility and server versions”. The scope must remain limited to the selected application and supported host.

Syntax explained

mariadb: Invokes the principal tool or configuration operation for “Match backup utility and server versions”; confirm that it resolves to the expected packaged executable or file before using elevated privileges.
|: Passes standard output to the next read-only inspection stage; review each stage separately if the combined result is surprising.

Command

sudo mariadb -NBe "SELECT VERSION(); SHOW VARIABLES LIKE 'datadir'; SHOW ENGINES;"; mariadb-backup --version 2>/dev/null || true

Example output / evidence

Server and backup utility compatibility are documented.

Checkpoint: Checkpoint: Match backup utility and server versions

Continue whenServer and backup utility compatibility are documented. Treat this concrete evidence as the minimum success signal and also confirm that unrelated services and the previous healthy path remain unchanged.

Stop whenStop before the next step when mariadb reports an error, the output differs materially from the example, an unexpected package/service/path is affected, or the required before-state and rollback artifact are missing.

If this step fails

The command for “Match backup utility and server versions” fails, produces no useful evidence, or the expected service/configuration state is absent.

Likely causeA placeholder targets the wrong environment, the installed version uses another path or unit name, permissions are insufficient, or an earlier prerequisite was not satisfied.

Safe checks

command -v mariadb 2>/dev/null || true
systemctl --failed --no-pager 2>/dev/null || true
journalctl -p warning -n 50 --no-pager 2>/dev/null || true

ResolutionDo not improvise a privileged workaround. Re-read the environment and variable table, inspect the official source for the installed version, restore the candidate file if necessary, and repeat only this checkpoint.

Security notes

Keep secrets out of shell history, process arguments, screenshots, logs, and tutorial placeholders. Elevate only the narrow command that requires it and preserve package signature and TLS verification.

Alternatives

When the host layout or software version differs, use the vendor-supported equivalent in a disposable staging environment and document the mapping before touching production.

Stop conditions

Stop if the action broadens network exposure, permissions, package sources, writable paths, or service privileges beyond the tutorial's declared architecture.

instruction

Measure source and destination capacity

read-only

Size data, changed-page rate, backup duration, free space, inodes, throughput, and offsite capacity before starting.

Why this step matters

Measure source and destination capacity is a separate checkpoint because it establishes one auditable part of prove a low-rto physical recovery path for a large mariadb instance. Performing it independently keeps a failed prerequisite, syntax error, or compatibility mismatch from being hidden by later actions.

What to understand

Size data, changed-page rate, backup duration, free space, inodes, throughput, and offsite capacity before starting. Read the complete command or configuration before execution, replace the documented placeholders, and compare the target host with the supported environments listed at the beginning of this tutorial.

System changes

This step may affect the files, packages, identities, services, or runtime policy named in “Measure source and destination capacity”. The scope must remain limited to the selected application and supported host.

Syntax explained

du: Invokes the principal tool or configuration operation for “Measure source and destination capacity”; confirm that it resolves to the expected packaged executable or file before using elevated privileges.
{{…}}: Marks an operator-supplied value. Replace every placeholder deliberately; do not paste untrusted text or leave braces in the production command.

Command

Fill variables0/1 ready

Values stay on this page and are never sent or saved.

Backup Root{{backupRoot}}Operator-reviewed value used by this tutorial for backupRoot; derive it from the target environment and never from untrusted request data.

sudo du -sx /var/lib/mysql; df -h {{backupRoot}}; df -i {{backupRoot}}; iostat -xz 1 3

Example output / evidence

Capacity and I/O baseline show sufficient headroom.

Checkpoint: Checkpoint: Measure source and destination capacity

Continue whenCapacity and I/O baseline show sufficient headroom. Treat this concrete evidence as the minimum success signal and also confirm that unrelated services and the previous healthy path remain unchanged.

Stop whenStop before the next step when du reports an error, the output differs materially from the example, an unexpected package/service/path is affected, or the required before-state and rollback artifact are missing.

If this step fails

The command for “Measure source and destination capacity” fails, produces no useful evidence, or the expected service/configuration state is absent.

Likely causeA placeholder targets the wrong environment, the installed version uses another path or unit name, permissions are insufficient, or an earlier prerequisite was not satisfied.

Safe checks

command -v du 2>/dev/null || true
systemctl --failed --no-pager 2>/dev/null || true
journalctl -p warning -n 50 --no-pager 2>/dev/null || true

Security notes

Keep secrets out of shell history, process arguments, screenshots, logs, and tutorial placeholders. Elevate only the narrow command that requires it and preserve package signature and TLS verification.

Alternatives

When the host layout or software version differs, use the vendor-supported equivalent in a disposable staging environment and document the mapping before touching production.

Stop conditions

Stop if the action broadens network exposure, permissions, package sources, writable paths, or service privileges beyond the tutorial's declared architecture.

command

Create a least-privilege backup account

caution

Grant the MariaDB Backup privileges documented for the active release and keep credentials in a protected option file.

Why this step matters

Create a least-privilege backup account is a separate checkpoint because it establishes one auditable part of prove a low-rto physical recovery path for a large mariadb instance. Performing it independently keeps a failed prerequisite, syntax error, or compatibility mismatch from being hidden by later actions.

What to understand

Grant the MariaDB Backup privileges documented for the active release and keep credentials in a protected option file. Read the complete command or configuration before execution, replace the documented placeholders, and compare the target host with the supported environments listed at the beginning of this tutorial.

System changes

This step may affect the files, packages, identities, services, or runtime policy named in “Create a least-privilege backup account”. The scope must remain limited to the selected application and supported host.

Syntax explained

mariadb: Invokes the principal tool or configuration operation for “Create a least-privilege backup account”; confirm that it resolves to the expected packaged executable or file before using elevated privileges.
{{…}}: Marks an operator-supplied value. Replace every placeholder deliberately; do not paste untrusted text or leave braces in the production command.

Command

Fill variables0/2 ready

Values stay on this page and are never sent or saved.

Backup User{{backupUser}}Operator-reviewed value used by this tutorial for backupUser; derive it from the target environment and never from untrusted request data.Protected Secret{{protectedSecret}}Operator-reviewed value used by this tutorial for protectedSecret; derive it from the target environment and never from untrusted request data.

sudo mariadb --protocol=socket -e "CREATE USER IF NOT EXISTS '{{backupUser}}'@'localhost' IDENTIFIED BY '{{protectedSecret}}'; GRANT RELOAD, PROCESS, LOCK TABLES, BINLOG MONITOR ON *.* TO '{{backupUser}}'@'localhost';"

Example output / evidence

The local backup account has the reviewed privilege set.

Checkpoint: Checkpoint: Create a least-privilege backup account

Continue whenThe local backup account has the reviewed privilege set. Treat this concrete evidence as the minimum success signal and also confirm that unrelated services and the previous healthy path remain unchanged.

If this step fails

The command for “Create a least-privilege backup account” fails, produces no useful evidence, or the expected service/configuration state is absent.

Likely causeA placeholder targets the wrong environment, the installed version uses another path or unit name, permissions are insufficient, or an earlier prerequisite was not satisfied.

Safe checks

command -v mariadb 2>/dev/null || true
systemctl --failed --no-pager 2>/dev/null || true
journalctl -p warning -n 50 --no-pager 2>/dev/null || true

Security notes

Keep secrets out of shell history, process arguments, screenshots, logs, and tutorial placeholders. Elevate only the narrow command that requires it and preserve package signature and TLS verification.

Alternatives

When the host layout or software version differs, use the vendor-supported equivalent in a disposable staging environment and document the mapping before touching production.

Stop conditions

Stop if the action broadens network exposure, permissions, package sources, writable paths, or service privileges beyond the tutorial's declared architecture.

command

Take the physical backup

caution

Write into a new timestamped directory, capture stderr and exit status, and never reuse a partially written target.

Why this step matters

Take the physical backup is a separate checkpoint because it establishes one auditable part of prove a low-rto physical recovery path for a large mariadb instance. Performing it independently keeps a failed prerequisite, syntax error, or compatibility mismatch from being hidden by later actions.

What to understand

Write into a new timestamped directory, capture stderr and exit status, and never reuse a partially written target. Read the complete command or configuration before execution, replace the documented placeholders, and compare the target host with the supported environments listed at the beginning of this tutorial.

System changes

This step may affect the files, packages, identities, services, or runtime policy named in “Take the physical backup”. The scope must remain limited to the selected application and supported host.

Syntax explained

umask: Invokes the principal tool or configuration operation for “Take the physical backup”; confirm that it resolves to the expected packaged executable or file before using elevated privileges.
{{…}}: Marks an operator-supplied value. Replace every placeholder deliberately; do not paste untrusted text or leave braces in the production command.
|: Passes standard output to the next read-only inspection stage; review each stage separately if the combined result is surprising.

Command

Fill variables0/4 ready

Values stay on this page and are never sent or saved.

Backup Root{{backupRoot}}Operator-reviewed value used by this tutorial for backupRoot; derive it from the target environment and never from untrusted request data.Stamp{{stamp}}Operator-reviewed value used by this tutorial for stamp; derive it from the target environment and never from untrusted request data.Backup User{{backupUser}}Operator-reviewed value used by this tutorial for backupUser; derive it from the target environment and never from untrusted request data.Protected Secret{{protectedSecret}}Operator-reviewed value used by this tutorial for protectedSecret; derive it from the target environment and never from untrusted request data.

umask 077; sudo mariadb-backup --backup --target-dir={{backupRoot}}/{{stamp}} --user={{backupUser}} --password='{{protectedSecret}}' 2>&1 | sudo tee {{backupRoot}}/{{stamp}}.log

Example output / evidence

The command exits zero and xtrabackup_checkpoints exists.

Checkpoint: Checkpoint: Take the physical backup

Continue whenThe command exits zero and xtrabackup_checkpoints exists. Treat this concrete evidence as the minimum success signal and also confirm that unrelated services and the previous healthy path remain unchanged.

Stop whenStop before the next step when umask reports an error, the output differs materially from the example, an unexpected package/service/path is affected, or the required before-state and rollback artifact are missing.

If this step fails

The command for “Take the physical backup” fails, produces no useful evidence, or the expected service/configuration state is absent.

Likely causeA placeholder targets the wrong environment, the installed version uses another path or unit name, permissions are insufficient, or an earlier prerequisite was not satisfied.

Safe checks

command -v umask 2>/dev/null || true
systemctl --failed --no-pager 2>/dev/null || true
journalctl -p warning -n 50 --no-pager 2>/dev/null || true

Security notes

Keep secrets out of shell history, process arguments, screenshots, logs, and tutorial placeholders. Elevate only the narrow command that requires it and preserve package signature and TLS verification.

Alternatives

When the host layout or software version differs, use the vendor-supported equivalent in a disposable staging environment and document the mapping before touching production.

Stop conditions

Stop if the action broadens network exposure, permissions, package sources, writable paths, or service privileges beyond the tutorial's declared architecture.

command

Prepare the backup

caution

Apply redo to make the copied files consistent; preserve the original backup if policy requires an immutable raw copy.

Why this step matters

Prepare the backup is a separate checkpoint because it establishes one auditable part of prove a low-rto physical recovery path for a large mariadb instance. Performing it independently keeps a failed prerequisite, syntax error, or compatibility mismatch from being hidden by later actions.

What to understand

Apply redo to make the copied files consistent; preserve the original backup if policy requires an immutable raw copy. Read the complete command or configuration before execution, replace the documented placeholders, and compare the target host with the supported environments listed at the beginning of this tutorial.

System changes

This step may affect the files, packages, identities, services, or runtime policy named in “Prepare the backup”. The scope must remain limited to the selected application and supported host.

Syntax explained

mariadb-backup: Invokes the principal tool or configuration operation for “Prepare the backup”; confirm that it resolves to the expected packaged executable or file before using elevated privileges.
{{…}}: Marks an operator-supplied value. Replace every placeholder deliberately; do not paste untrusted text or leave braces in the production command.
|: Passes standard output to the next read-only inspection stage; review each stage separately if the combined result is surprising.

Command

Fill variables0/2 ready

Values stay on this page and are never sent or saved.

sudo mariadb-backup --prepare --target-dir={{backupRoot}}/{{stamp}}; sudo grep -E 'backup_type|from_lsn|to_lsn' {{backupRoot}}/{{stamp}}/xtrabackup_checkpoints

Example output / evidence

Prepare completes successfully and checkpoint metadata is readable.

Checkpoint: Checkpoint: Prepare the backup

Continue whenPrepare completes successfully and checkpoint metadata is readable. Treat this concrete evidence as the minimum success signal and also confirm that unrelated services and the previous healthy path remain unchanged.

Stop whenStop before the next step when mariadb-backup reports an error, the output differs materially from the example, an unexpected package/service/path is affected, or the required before-state and rollback artifact are missing.

If this step fails

The command for “Prepare the backup” fails, produces no useful evidence, or the expected service/configuration state is absent.

Likely causeA placeholder targets the wrong environment, the installed version uses another path or unit name, permissions are insufficient, or an earlier prerequisite was not satisfied.

Safe checks

command -v mariadb-backup 2>/dev/null || true
systemctl --failed --no-pager 2>/dev/null || true
journalctl -p warning -n 50 --no-pager 2>/dev/null || true

Security notes

Keep secrets out of shell history, process arguments, screenshots, logs, and tutorial placeholders. Elevate only the narrow command that requires it and preserve package signature and TLS verification.

Alternatives

When the host layout or software version differs, use the vendor-supported equivalent in a disposable staging environment and document the mapping before touching production.

Stop conditions

Stop if the action broadens network exposure, permissions, package sources, writable paths, or service privileges beyond the tutorial's declared architecture.

command

Archive, encrypt, checksum, and copy off host

caution

Create a deterministic archive or supported stream, encrypt it, calculate a checksum, and transfer it independently of the database host.

Why this step matters

Archive, encrypt, checksum, and copy off host is a separate checkpoint because it establishes one auditable part of prove a low-rto physical recovery path for a large mariadb instance. Performing it independently keeps a failed prerequisite, syntax error, or compatibility mismatch from being hidden by later actions.

What to understand

Create a deterministic archive or supported stream, encrypt it, calculate a checksum, and transfer it independently of the database host. Read the complete command or configuration before execution, replace the documented placeholders, and compare the target host with the supported environments listed at the beginning of this tutorial.

System changes

This step may affect the files, packages, identities, services, or runtime policy named in “Archive, encrypt, checksum, and copy off host”. The scope must remain limited to the selected application and supported host.

Syntax explained

set: Invokes the principal tool or configuration operation for “Archive, encrypt, checksum, and copy off host”; confirm that it resolves to the expected packaged executable or file before using elevated privileges.
{{…}}: Marks an operator-supplied value. Replace every placeholder deliberately; do not paste untrusted text or leave braces in the production command.
|: Passes standard output to the next read-only inspection stage; review each stage separately if the combined result is surprising.

Command

Fill variables0/3 ready

Values stay on this page and are never sent or saved.

Backup Root{{backupRoot}}Operator-reviewed value used by this tutorial for backupRoot; derive it from the target environment and never from untrusted request data.Stamp{{stamp}}Operator-reviewed value used by this tutorial for stamp; derive it from the target environment and never from untrusted request data.Age Recipients File{{ageRecipientsFile}}Operator-reviewed value used by this tutorial for ageRecipientsFile; derive it from the target environment and never from untrusted request data.

set -o pipefail; sudo tar -C {{backupRoot}} -cpf - {{stamp}} | zstd -T0 | age -R {{ageRecipientsFile}} -o {{backupRoot}}/{{stamp}}.tar.zst.age; sha256sum {{backupRoot}}/{{stamp}}.tar.zst.age > {{backupRoot}}/{{stamp}}.sha256

Example output / evidence

Encrypted archive and checksum exist.

Checkpoint: Checkpoint: Archive, encrypt, checksum, and copy off host

Continue whenEncrypted archive and checksum exist. Treat this concrete evidence as the minimum success signal and also confirm that unrelated services and the previous healthy path remain unchanged.

Stop whenStop before the next step when set reports an error, the output differs materially from the example, an unexpected package/service/path is affected, or the required before-state and rollback artifact are missing.

If this step fails

The command for “Archive, encrypt, checksum, and copy off host” fails, produces no useful evidence, or the expected service/configuration state is absent.

Likely causeA placeholder targets the wrong environment, the installed version uses another path or unit name, permissions are insufficient, or an earlier prerequisite was not satisfied.

Safe checks

command -v set 2>/dev/null || true
systemctl --failed --no-pager 2>/dev/null || true
journalctl -p warning -n 50 --no-pager 2>/dev/null || true

Security notes

Keep secrets out of shell history, process arguments, screenshots, logs, and tutorial placeholders. Elevate only the narrow command that requires it and preserve package signature and TLS verification.

Alternatives

When the host layout or software version differs, use the vendor-supported equivalent in a disposable staging environment and document the mapping before touching production.

Stop conditions

Stop if the action broadens network exposure, permissions, package sources, writable paths, or service privileges beyond the tutorial's declared architecture.

command

Restore on an isolated compatible host

caution

Stop the disposable instance, move its empty data directory aside, copy back the prepared files, fix ownership, and start only after configuration review.

Why this step matters

Restore on an isolated compatible host is a separate checkpoint because it establishes one auditable part of prove a low-rto physical recovery path for a large mariadb instance. Performing it independently keeps a failed prerequisite, syntax error, or compatibility mismatch from being hidden by later actions.

What to understand

Stop the disposable instance, move its empty data directory aside, copy back the prepared files, fix ownership, and start only after configuration review. Read the complete command or configuration before execution, replace the documented placeholders, and compare the target host with the supported environments listed at the beginning of this tutorial.

System changes

This step may affect the files, packages, identities, services, or runtime policy named in “Restore on an isolated compatible host”. The scope must remain limited to the selected application and supported host.

Syntax explained

systemctl: Invokes the principal tool or configuration operation for “Restore on an isolated compatible host”; confirm that it resolves to the expected packaged executable or file before using elevated privileges.
{{…}}: Marks an operator-supplied value. Replace every placeholder deliberately; do not paste untrusted text or leave braces in the production command.

Command

Fill variables0/1 ready

Values stay on this page and are never sent or saved.

Restore Prepared Dir{{restorePreparedDir}}Operator-reviewed value used by this tutorial for restorePreparedDir; derive it from the target environment and never from untrusted request data.

sudo systemctl stop mariadb; sudo mv /var/lib/mysql /var/lib/mysql.empty; sudo mkdir /var/lib/mysql; sudo mariadb-backup --copy-back --target-dir={{restorePreparedDir}}; sudo chown -R mysql:mysql /var/lib/mysql; sudo systemctl start mariadb

Example output / evidence

The isolated MariaDB instance starts with restored data.

Checkpoint: Checkpoint: Restore on an isolated compatible host

Continue whenThe isolated MariaDB instance starts with restored data. Treat this concrete evidence as the minimum success signal and also confirm that unrelated services and the previous healthy path remain unchanged.

Stop whenStop before the next step when systemctl reports an error, the output differs materially from the example, an unexpected package/service/path is affected, or the required before-state and rollback artifact are missing.

If this step fails

The command for “Restore on an isolated compatible host” fails, produces no useful evidence, or the expected service/configuration state is absent.

Likely causeA placeholder targets the wrong environment, the installed version uses another path or unit name, permissions are insufficient, or an earlier prerequisite was not satisfied.

Safe checks

command -v systemctl 2>/dev/null || true
systemctl --failed --no-pager 2>/dev/null || true
journalctl -p warning -n 50 --no-pager 2>/dev/null || true

Security notes

Keep secrets out of shell history, process arguments, screenshots, logs, and tutorial placeholders. Elevate only the narrow command that requires it and preserve package signature and TLS verification.

Alternatives

When the host layout or software version differs, use the vendor-supported equivalent in a disposable staging environment and document the mapping before touching production.

Stop conditions

Stop if the action broadens network exposure, permissions, package sources, writable paths, or service privileges beyond the tutorial's declared architecture.

verification

Prove data and recovery time

read-only

Run CHECK TABLE on representative critical tables, application read tests, object counts, and record backup age plus total recovery duration.

Why this step matters

Prove data and recovery time is a separate checkpoint because it establishes one auditable part of prove a low-rto physical recovery path for a large mariadb instance. Performing it independently keeps a failed prerequisite, syntax error, or compatibility mismatch from being hidden by later actions.

What to understand

Run CHECK TABLE on representative critical tables, application read tests, object counts, and record backup age plus total recovery duration. Read the complete command or configuration before execution, replace the documented placeholders, and compare the target host with the supported environments listed at the beginning of this tutorial.

System changes

This step may affect the files, packages, identities, services, or runtime policy named in “Prove data and recovery time”. The scope must remain limited to the selected application and supported host.

Syntax explained

mariadb-check: Invokes the principal tool or configuration operation for “Prove data and recovery time”; confirm that it resolves to the expected packaged executable or file before using elevated privileges.
{{…}}: Marks an operator-supplied value. Replace every placeholder deliberately; do not paste untrusted text or leave braces in the production command.

Command

Fill variables0/2 ready

Values stay on this page and are never sent or saved.

Database{{database}}Application database name; use an identifier approved by the database naming policy.Critical Table{{criticalTable}}Operator-reviewed value used by this tutorial for criticalTable; derive it from the target environment and never from untrusted request data.

sudo mariadb-check --all-databases --check; sudo mariadb -NBe "SELECT COUNT(*) FROM {{database}}.{{criticalTable}}; SHOW MASTER STATUS;"

Example output / evidence

Checks pass and the restore report meets RPO/RTO.

Checkpoint: Checkpoint: Prove data and recovery time

Continue whenChecks pass and the restore report meets RPO/RTO. Treat this concrete evidence as the minimum success signal and also confirm that unrelated services and the previous healthy path remain unchanged.

Stop whenStop before the next step when mariadb-check reports an error, the output differs materially from the example, an unexpected package/service/path is affected, or the required before-state and rollback artifact are missing.

If this step fails

The command for “Prove data and recovery time” fails, produces no useful evidence, or the expected service/configuration state is absent.

Likely causeA placeholder targets the wrong environment, the installed version uses another path or unit name, permissions are insufficient, or an earlier prerequisite was not satisfied.

Safe checks

command -v mariadb-check 2>/dev/null || true
systemctl --failed --no-pager 2>/dev/null || true
journalctl -p warning -n 50 --no-pager 2>/dev/null || true

Security notes

Keep secrets out of shell history, process arguments, screenshots, logs, and tutorial placeholders. Elevate only the narrow command that requires it and preserve package signature and TLS verification.

Alternatives

When the host layout or software version differs, use the vendor-supported equivalent in a disposable staging environment and document the mapping before touching production.

Stop conditions

Stop if the action broadens network exposure, permissions, package sources, writable paths, or service privileges beyond the tutorial's declared architecture.

Finish line

Verification checklist

Backup metadatatest -s {{backupRoot}}/{{stamp}}/xtrabackup_checkpoints && test -s {{backupRoot}}/{{stamp}}/xtrabackup_infoPrepared backup metadata files are present.

Independent restoremariadb --host={{restoreHost}} --user={{restoreUser}} --password -e 'SELECT VERSION();' && cat {{restoreReport}}The isolated restored server answers and the report records successful validation.

Recovery guidance

Common problems and safe checks

The native client reports access denied even though a similarly named account has grants.

Likely causeDatabase identities include a host component and authentication plugin; the connection matched another user@host row or transport.

Safe checks

mysql -e 'SELECT USER(), CURRENT_USER();'
mysql -e "SELECT User,Host,plugin FROM mysql.user ORDER BY User,Host;"
mysql -e 'SHOW GRANTS FOR CURRENT_USER;'

ResolutionIdentify the exact matched account and create or modify only the intended user@host identity; do not broaden it to '%' as a shortcut.

TLS is enabled but the client still connects with an unexpected identity or without hostname verification.

Likely causeThe client uses encryption-only mode, connects by an address absent from the SAN, trusts a broad system CA set, or another option file overrides policy.

Safe checks

mysql --print-defaults
openssl s_client -starttls mysql -connect db.example.com:3306 -servername db.example.com </dev/null
mysql -e "SHOW STATUS LIKE 'Ssl_%';"

ResolutionUse the documented hostname, intended CA and VERIFY_IDENTITY-equivalent mode; remove conflicting client options and retest expected failure with a wrong hostname.

A change passes syntax checks but application errors, lag, latency or disk use increases.

Likely causeThe candidate is syntactically valid but workload, lock, cache, rotation, privilege or version assumptions are wrong.

Safe checks

mysqladmin ping
mysql -e 'SHOW PROCESSLIST;'
journalctl -u mariadb -u mysql -p warning --since '-10 min' --no-pager

ResolutionStop promotion, preserve evidence, revert the smallest changed configuration/account/routing layer and reproduce with a safe representative workload.

After the procedure

Alternatives and next steps

Consider these alternatives

Use a managed database only after private connectivity, identity, encryption, monitoring, backup restoration and responsibility boundaries are proven.
Use immutable configuration automation and a disposable recovery environment when it produces stronger review, repeatability and rollback than manual host edits.

Operate it safely

Automate health, capacity, certificate, replication, backup and restore-test alerts with an owner and response runbook.
Review accounts, grants, package support, encryption material and recovery evidence after every major release and at least every 90–180 days.
Practice the rollback or restore from recorded artifacts on an isolated host so the documented time and dependencies remain credible.

Reference

Frequently asked questions

Does a successful command mean the procedure is complete?

No. Database tools may return success before application compatibility, replica replay, restore integrity, TLS identity or expected denials are proven. Use every checkpoint and final verification.

Can an administrator use the application account for convenience?

No. Separate identities make least privilege, rotation, audit and incident containment possible; the application should not hold administrative capabilities.

Why test restoration or negative access separately?

A backup that cannot restore and a grant that was never tested for denial are assumptions, not controls. Isolation makes those tests safe and repeatable.

Recovery

Rollback

Disable the new physical-backup schedule and restore the prior job; never treat copy-back as an in-place rollback.

On the production source, disable only the new timer and preserve completed backup artifacts.
On a failed isolated restore, stop MariaDB and move the failed data directory aside before restoring its original disposable directory.
Revoke the backup account after confirming no retained schedule uses it.

Evidence

Sources and review

Verified 2026-07-25Review due 2026-10-23

MariaDB Backup overviewofficial MariaDB security guidanceofficial MariaDB Server installation guideofficial