MariaDB backup: where hot copying breaks
A hot copy is taken without stopping the service — and it breaks not at the moment of copying, but at the moment of recovery. A typical scenario: for consistency, the dumper briefly takes a write lock on tables.
On a database of tens of gigabytes, this means minutes during which the application does not write: the connection queue grows, and some requests fail by timeout.
As soon as the lock is released to avoid taking down the service, the copy becomes inconsistent.
Hot backup of MariaDB in single-transaction mode (--single-transaction) gives a consistent snapshot only for transactional tables, while a single schema may contain non-transactional tables next to them — they are read at a different moment.
The copy ends up with orders without line items and foreign keys pointing nowhere: recovery completes formally, but the data does not reconcile.
The second trap is that the copy does not remember the point at which it was taken. For point-in-time recovery, a consistent position in the change log is required; without it, replaying events takes the database to the wrong place.
A long backup also bloats the log: disk space runs out before the copy is ready, and the production server goes down.
And most importantly: a copy that has never been restored is not a backup but an archive of unknown quality.
Backup integrity verification is done only by a full recovery run on a separate test environment: comparing checksums of key tables, consistency of rows and foreign relationships, and a clean error log after startup.
We'll break down how this is covered — from choosing the backup mode to a schedule of regular checks.
What hot backup without service downtime provides
Downtime window collapses to seconds
The copy is taken from a running server: the process reads data files and the transaction log without blocking writes — orders, payments, and reports continue as usual.
A lock is needed only to record the position in the log, meaning fractions of a second, not hours of a nightly window.
As a result, backup without stopping the database stops being a calendar event: it can be scheduled at any time, not only during the interval when everyone is asleep.
Recovery in tens of minutes
What is deployed is not a dump but a physical copy: files are returned to their place, and the log replays changes up to the required point in time.
The main metric here is time to bring the database into working state, and it drops from hours to tens of minutes because there is no need to replay all inserts again. Rolling back to the state before a failed release becomes a routine operation, not an emergency night shift.
Copy is consistent at the moment it is taken
The goal is not merely to save tables but to obtain a non-contradictory snapshot: data and log correspond to the same point. Without this, recovery produces broken foreign keys and discrepancies between tables that are later searched for manually.
Consistency is configured at the procedure level, not checked by the eye of the on-call engineer — so the copy is usable immediately.
Backup control without manual checks
Schedule, rotation, and recoverability checks are performed automatically: the copy is regularly deployed in an isolated environment and compared with the expected state.
This removes the class of failures "a backup exists but is unusable," which usually surfaces at the worst possible moment. Plus, storage costs stop growing uncontrollably — old sets are evicted by policy while fresh restore points remain available.
Comparison of approaches: logical dump, volume snapshot, replica
The three approaches cover different tasks, and the choice is dictated not by team habit but by database size, acceptable window, and required recovery point.
A logical database dump is readable, can be moved between versions and platforms, but on large volumes it stretches over hours and noticeably heats the disk.
A storage volume snapshot is taken in seconds; however, without a preliminary consistent write pause, it produces an inconsistent set of pages that may simply fail to start.
A replica for backup moves load off the primary database: the copy is taken from a lagging node, and the production server does not notice it. Incremental backup complements any of the schemes — a daily full set of tens of gigabytes fits neither in the window nor in storage.
| Option | When it fits | Limitations |
|---|---|---|
| Logical database dump | Small and medium databases, migrations, selective export of individual tables | Time grows linearly with volume; disk and CPU load; recovery is equally long |
| Storage volume snapshot | Large databases on a dedicated volume, narrow window, need a full image in minutes | Requires a consistent point (brief write lock); volume is tied to the server and storage |
| Replica for backup | Production with high load, when the primary database cannot be slowed down | Requires a separate server and space; replica lag is monitored separately, otherwise the copy will be outdated |
In practice, schemes are combined: a full set once a week, a daily increment, and continuous binary log writing provides point-in-time recovery — rollback not to the nightly copy but to the minute before the failure.
Which combination suits you depends on volume and acceptable downtime window — this is what we analyze at the start.
Implementation order: from audit to handover to operations
The implementation route for MariaDB backup is the same for a standalone server and for a scheme with replicas: first we record what must be recovered and how often, then we configure backup and storage, and only after a successful practice recovery do we put the environment into operation.
- Backup audit. We review current jobs and their actual results, database growth rate, free space, and read permissions on replicas. We record risks: taking backups from the primary interferes with writes, integrity is not checked, the only copy is on the same volume as the data.
- Database backup scheme. We select a combination: a full mariabackup snapshot, increments by changed pages, and binary logs for point-in-time recovery. We agree on target points and recovery time, retention depth, and off-server export.
- Configuring the backup schedule. We distribute jobs into low-load windows and take copies from a replica so as not to compete with the primary for disk operations. We set rotation, job exit code monitoring, and failure alerts.
- Test recovery. We deploy copies on a separate test environment and measure real recovery time — for entire databases and for an individual table. We compare checksums and run checks on the recovered data: schema errors surface here, not during an incident.
- Handover to operations. We provide the runbooks: what is backed up and when, where it is stored, who recovers it and how, and how to read monitoring alerts. We conduct a practice recovery with your on-call shift and remain on support according to schedule.
Deliverables: configurations, schemes, procedures
The package is assembled so that the on-call shift can act on it without us: configurations that already work on test environments and in production, plus documents explaining every decision made. Below is what exactly remains with your team after handover.
- Working backup configurations for each database: schedule, set of objects, mode of taking copies without stopping writes, compression and encryption parameters — the same set works across test environments and production.
- Backup environment diagram: which nodes participate, where copies are located, how primary and external sites are separated, and what happens if one of the storages fails.
- Backup retention policy: depth by type — daily, weekly, monthly; renewal and deletion rules; binding to maintenance windows so the registry size does not grow uncontrollably.
- Database recovery runbook: step-by-step order with full and partial recovery points, expected time to return to service, and mandatory data verification after startup.
- Access roles for backups and operations log: who takes a backup, who reads it, who restores it; access to production data is moved to a separate environment.
- Backup documentation: descriptions, diagrams, and checklists are stored in the repository next to the configurations and versioned together with them.
- Recovery check logs: dates, what was restored, discrepancies, actual time — proof that the backup is alive and not just a file in storage.
Case study: database recovery after node failure
What it was like before the changes
The customer has an online store with intensive writes to MariaDB. The nightly dump was taken from the same node that served the load, landed on an adjacent partition, and was never restored.
About half of the copies passed suitability checks: some failed during export, some did not match by checksums — and this was discovered only during an incident.
When the node failed, they recovered to the previous night's copy: lost about a day of orders and catalog edits, and recovery took more than six hours.
What was changed and what became
Backups were moved to separate storage and became hot — the service is not stopped while they are taken. A full copy once a week, incremental snapshots more often, plus a continuous change log: rollback is possible to any point within 24 hours.
We automatically deploy every copy on a separate test environment and compare it by table checksums, so usability is confirmed by the fact of recovery, not by a checkbox in the job log.
All archives pass the check — unusable ones are rejected and retaken, without entering the valid history.
Recovery time decreased from six hours to tens of minutes: the latest full copy is restored, and the log is applied on top. Node failure stopped being a class of incidents with data loss — the discrepancy with the last confirmed point is measured in minutes.
The backup environment became fault-tolerant: failure of a storage or node does not interrupt backup creation, and integrity on the recovered test environment is checked before switching production traffic.
How does MariaDB backup differ from replication?
A replica and a backup solve different tasks. Replication is a continuous stream of changes: it offloads reads and allows switching to another node in minutes. But it does not keep history — every change, including an erroneous one, is applied on the replica almost instantly.
DELETE without a condition or UPDATE with a forgotten WHERE will propagate to all replicas in seconds.
A delayed replica with several hours of lag gives a rescue window, but only if the error was noticed within that window, and it does not protect against corruption that started earlier.
Point-in-time recovery is possible only from a backup. A MariaDB snapshot taken by mariadb-backup on a running server, plus binary logs, provides the full history of changes.
We deploy the snapshot and replay the logs up to the required position or timestamp — up to the query that broke everything. This is how rollback of deleted data works: the database returns to the state one second before the failure.
There is a class of failures that a replica does not cover: dropped tables and logical corruption on the primary node, storage failure, an audit requirement to show data as of a specific date.
Moreover, a replica often lives in the same rack and under the same account — an incident takes it down too. We move backups to a separate environment.
Bottom line: replication and backup complement each other — the first is responsible for availability, the second for recovery. We test both environments on a test bench, not during an incident.
Common technical questions about backup
A hot backup is taken from a running node: the snapshot reads data files and the pre-transaction log; consistency is provided by multiversioning — readers do not block writes.
A write prohibition is needed only to record the position in the replication log, and the load comes down to reading from disk.
Will backup slow down the production database?
The main cost is reading pages and re-reading changed ones in a loop; the load falls on disk and cache. We limit parallelism and I/O priority below the application. The backup runs for hours, and query response time does not change.
Do we need to stop the application?
No. Hot mode works on a live node, and in replication schemes the copy is taken from a replica — the primary is not affected. Downtime is needed only for an inconsistent logical dump.
How much space will backups take?
A full copy is comparable to the data volume; an increment contains only changed pages. We collapse the chain into a new full copy, and old versions are evicted according to retention — size growth is predictable.
How to verify that a backup is usable?
A backup is valid only after deployment: an automated test starts a separate instance, applies the log, compares checksums, and runs queries against key tables. We check point-in-time recovery using the transaction log.
We already have backup configured — what should change?
We look at what is done now: a scheduled dump, a snapshot via virtualization tools, or a replica without logs. We answer from which backup the database will recover in acceptable time; if there is no clarity — we add usability checks and managed storage.
Let's discuss the task: what is needed to assess the backup environment
A discussion with an engineer starts with facts about the environment, not with wishes — otherwise the estimate turns into a range. Below are inputs for calculating the work that remove most questions at the start. If something is missing, we'll suggest which command to use to check it on a running system.
- Topology: standalone server, primary with replicas, or cluster — this determines whether we take a logical dump or a physical copy.
- Database size and write profile: data size, daily change rate, windows of minimal activity.
- Recovery objectives: how much data may be lost at which point in time and how quickly the database must return to service.
- Consistency requirements: whether a consistent snapshot of all tables is needed or rollback via log to the desired second is enough.
- Where to store backups: local disk, network storage, or a separate environment, with retention policy and rotation depth.
- Access and encryption: where keys are stored, who gets access to backups, whether operations audit is required.
- What already exists: current procedures, scripts, schedules, and a test environment where recovery can be practiced.
After that, we return with an estimate for backup work and a backup implementation plan: task list, order, checkpoints, and what will be required from your side.
We will separately discuss backup environment support — monitoring, backup rotation, and regular recovery drills, so backups do not remain "on paper."