A mistaken table deletion can expose the gap between having a backup file and having a working recovery plan. A nightly copy alone may leave hours of valid writes missing. MySQL and MariaDB recovery depends on a consistent base, the objects and credentials needed to restore it, and a tested way to replay later changes when point-in-time recovery is required.
This guide compares logical exports with mysqldump, physical backups with XtraBackup or MariaDB Backup, and binary-log-based Point-in-Time Recovery (PITR). It explains consistency limits, incremental chains, supported versions and restore rehearsals. Choose a method against measured recovery objectives; no tool guarantees a brief interruption or a fixed recovery time.
The Oh‑No Moment: Why Backups Fail When You Need Them Most
Consider a scheduled backup that produces an empty file after a permission change. The scheduler can still appear active while no usable copy exists. Monitor the exit status of every pipeline stage, output age, expected objects and transfers. Alerts catch many failures; a successful restore rehearsal is still needed to show that the captured data can be used.
Here’s the thing about databases: they’re always moving. While you’re backing up, new rows are being written, transactions are mid-flight, and caches are reshuffling. If your method doesn’t capture a consistent picture, you end up with something that looks like a photo but has pieces from two different moments stitched together. For related transactional data, that inconsistency can break application relationships. Establish which storage engines and operations your chosen snapshot method actually covers.
Evaluate three things separately: consistency of the captured data, production impact while making the copy, and the complete recovery workflow. Recovery Point Objective (RPO) defines acceptable data loss; Recovery Time Objective (RTO) defines the targeted time to restore service. Include log-archive delay in RPO, and transfer, decryption, preparation, replay and application checks in RTO.
mysqldump: The Trusty Swiss Army Knife That’s Better Than You Think
mysqldump is like that dependable friend who shows up on moving day with a pickup truck. Not fancy, not the fastest, but you can count on it. It creates a logical export—basically, a set of SQL statements that can rebuild your schema and data from scratch. It’s portable, human‑readable, and great for migrations, small to medium databases, or when you need a quick copy to poke at locally.
For InnoDB, --single-transaction uses a consistent transaction snapshot without holding table locks for the whole dump. It does not make MyISAM or MEMORY tables consistent. Prevent concurrent ALTER, CREATE, DROP, RENAME and TRUNCATE operations on affected tables during the dump. Include required routines, triggers and events, and verify views and DEFINER dependencies. Capturing binary-log coordinates can still require a brief initial global read lock. Large logical restores may be slow even with appropriate flags.
Logical dumps often compress well with gzip or zstd, but compression and encryption add CPU work. Monitor each pipeline stage so a successful compressor does not conceal a failed dump. Restore time includes decompression, executing SQL and rebuilding indexes; measure it on a realistic target. Do not treat the duration of the backup job as the expected recovery duration.
Large data volumes, heavy writes and nontransactional tables require extra planning. MyISAM consistency may need locks or a maintenance window; an InnoDB snapshot flag does not solve it. A replica can reduce work on the primary, but its lag and recorded coordinate context must be understood. Splitting related tables into independent dumps does not automatically preserve a shared snapshot. For PITR, capture reliable binary-log start coordinates with the consistent base.
Use a logical export when its measured backup and restore windows meet your objectives and portability is valuable. On MariaDB, use the supported mariadb-dump client and options for that release. Cross-version import still requires testing SQL features, character sets and collations; a readable file is not an assurance that every target accepts it.
XtraBackup (and Mariabackup)
Percona XtraBackup and MariaDB Backup (mariadb-backup, formerly Mariabackup) copy physical data and recovery information while supported database workloads remain online. Hot backup does not mean zero load or universally non-blocking operation. Reads, compression, DDL coordination and nontransactional-table phases can affect latency or require locks. Measure that impact and consult the exact engine/version support rules.
Because files are copied at different times, the captured recovery information must be applied in a prepare phase before restoration. Restore a prepared copy to an isolated target with the database process stopped and its destination data directory empty. Confirm compatible versions, settings, ownership and required encryption keys before starting it. Never copy backup files over a running production data directory. Physical restore can help with large datasets, but the complete recovery time must be measured.
Incremental backups depend on the correct base and every required intermediate increment. Retain the matching LSN chain and apply changes in order. For XtraBackup, use the documented apply-log-only preparation for the base and intermediate stages so rollback is deferred until the final stage; prematurely finalizing prevents further increments. Follow MariaDB Backup’s own version-specific preparation procedure. A weekly full and daily increments is an example schedule, not a universal rule.
Check the backup-tool/server compatibility matrix, page size, paths, encryption and restore settings. Do not bypass version checks or assume MySQL and MariaDB physical files are interchangeable. Backup reads and CPU work compete with the application; throttling or a suitable replica may help, but both require measurement and a verified recovery plan.
The Percona XtraBackup documentation explains supported versions and restore steps. Current streaming backups use --stream=xbstream; do not copy obsolete tar examples into a current workflow. A stream may be compressed or encrypted for transfer, but the extracted files still require the correct prepare stage. Verify both the sender and receiving pipeline before accepting the result.
Point-in-Time Recovery: Restore to a Verified Transaction Boundary
PITR restores a consistent base and replays the retained binary-log chain from its recorded start point to a chosen boundary. It can reduce data loss compared with using the base alone, but recovery time depends on the data and replay workload. An approximate timestamp helps locate the incident; inspect events and transaction boundaries before deciding where to stop.
Enable binary logging before the incident and retain all required logs from the base backup through the target point. Archive them away from the source and monitor gaps and transfer delay. On MySQL 8.0.26 and later, --source-data=2 records coordinates in a comment; older releases use --master-data=2. Omitting the value defaults to an active CHANGE statement, not a comment. On MariaDB, use that release’s documented dump options. Physical tools save coordinate metadata alongside the backup; retain and verify it.
Restore to an isolated target, verify the base coordinates, then replay with the appropriate mysqlbinlog or MariaDB binlog utility. The MySQL binary-log documentation explains logging. MariaDB’s legacy PITR address now redirects to its documentation portal; use the current MariaDB Backup PITR procedure for concrete metadata and replay steps.
For example, a faulty migration might delete rows while legitimate orders continue arriving. Recovering to the boundary before that migration also excludes later good writes. Preserve incident evidence and reconcile those orders with payment providers, queues and other systems before cutover. PITR does not automatically undo only the bad operation while retaining every unrelated later change.
Plan whether replay should be logged on the target; the correct choice depends on replication and the intended cutover. Choosing a privileged account does not disable logging. Where disabling is appropriate, MySQL mysqlbinlog --disable-log-bin emits SET sql_log_bin=0 into the replay stream and needs the relevant session-variable privileges. A SET in a separate client connection does not affect the replay connection. Record the event boundary and timezone, and rehearse any replication consequences.
Choosing a Method for the Workload
Choose from the workload, supported versions, recovery targets and available operational skills. The examples below are planning scenarios, not reports of measured customer outcomes. The final choice should follow a restore test using representative data and storage.
For a modest database that fits the required restore window, a compressed, encrypted logical dump can offer useful portability. Include schema objects, coordinates where PITR is needed, and a separate users/grants recovery plan. The single-transaction option only covers its documented transactional scope. Keep the output offsite and validate the intended target version rather than assuming cross-version imports always work.
For a busy store with a large dataset, evaluate physical backups and an appropriate full/incremental schedule. Measure production latency during the backup and the complete prepare-and-restore path. Validate product, stock, order and payment relationships after replay, and include application files and external dependencies in the runbook. Rehearsal results, rather than a generic promise of hot backup, determine whether the design meets the store’s needs.
For MariaDB, choose a mariadb-backup release compatible with the source and preparation procedure. XtraBackup compatibility is not a substitute for that matrix. Restore to a clean supported target, validate ownership and encryption keys, and plan any later upgrade separately. Buffer-pool warming may improve early requests after restart, but it is not evidence that the restored data is correct.
For analytics workloads, physical recovery plus archived logs may be useful when the measured restore time and acceptable data lag fit. Protect the base and complete log chain, rehearse the target boundary, and check downstream data jobs. Neither read-heavy traffic nor a small write rate removes the need for consistency checks.
Setting It Up So Future‑You Says Thanks
Scheduling, monitoring, offsite storage and rehearsal support every backup method. The related 3-2-1 backup and hosting automation guide covers copy placement and routine operations. Separate failure domains and access permissions matter as much as the number of files.
Use cron, systemd timers or a managed scheduler with clear ownership and actionable failure notifications. Track exit codes for every stage, backup age, size changes, transfer completion and log-chain continuity. A checksum detects changed bytes during transfer; it does not prove database consistency or restore success. Run actual restores on a schedule suited to the service and after significant changes.
Encrypt backups in transit and at rest, and keep recovery keys accessible independently of the source server. XtraBackup’s supported native options differ from MariaDB Backup’s external streaming encryption workflow; do not assume identical flags. Protect secrets from command history, logs and public files. When rotating keys, retain the old keys needed to decrypt backups that are still within retention, and test that access.
Compression can save storage and bandwidth at a CPU cost. Measure backup and decompression time; streaming to another trusted host can move some processing away from production. Secure the transfer and check both ends of the pipeline. Capacity planning must include space for extraction, prepared copies, log replay and any temporary recovery files.
Restore onto an isolated instance and connect a staging application with outgoing payments, emails and jobs disabled or safely redirected. Check objects, counts and business invariants that match the actual workflow: unpaid pending orders may be valid, so “every order has a payment” is not a universal test. Verify the critical paths, not only a few successful page loads. Record the complete recovery duration and any manual dependencies.
Your First PITR Run: A Calm Walkthrough
Consider a hypothetical accidental deletion reported at 10:42:15. A nightly base and continuous archived logs exist. First confirm that the base is usable, its coordinate metadata is present, and every required log through the target point is available. The reported time narrows the investigation; it is not yet a verified replay boundary.
Restore the last suitable base on a separate instance. For a logical dump, confirm schema-selection behavior, target privileges and compatible objects before import. For a physical backup, prepare the complete matching chain, stop the target server, restore into its empty destination directory, correct ownership/settings and provide required keys before startup. Preserve the original backup copy and keep the target isolated from application writes.
Read the backup’s recorded binlog filename and position from its dump comment or physical metadata. For MySQL use the correct source-data=2 or older master-data=2 option when making the dump. GTIDs describe transaction identity and are not interchangeable with byte offsets; GTID filtering and replay require topology-specific handling. Inspect the bad event using approximate time filters, accounting for mysqlbinlog’s local timezone, then select the correct event/transaction boundary and ordered log files.
Apply the documented replay logging policy in the actual replay session. Validate the restored database, application queries and external-state reconciliation before promotion. Cutover, replication reconfiguration, identity/GTID handling and rollback need a tested plan of their own; do not simply point production or a replica at the recovered instance. Record both data validation and the total elapsed time.
Common Backup Pitfalls (And How to Dodge Them)
One classic trap is turning on binary logs but letting them purge before your backup window completes. If you keep seven days of full backups but only three days of logs, you lose the ability to do PITR from older snapshots. Match your log retention to your worst‑case restore scenario, not your best intentions.
Include the objects and dependencies the application requires: views, routines, triggers, events, DEFINER accounts, users/grants and external configuration or encryption keys. An application-database dump is not an automatic full-server backup. Inventory these before backup and verify their presence and permissions after restoration.
Validate backup and restore compatibility after database or backup-tool upgrades. Even nearby versions may have restrictions; follow the vendor’s exact support and upgrade rules. Restoring to an older compatible version before a supported migration can be an option, but direct file copy or forward replication is not a guaranteed fallback for every version pair.
Count transfer time as part of recovery. A three-hour download is only one stage before decryption, extraction, prepare, replay and application validation. A nearby copy may help meet RTO while a separately protected offsite copy covers source-site loss. Test the recovery path that would still be available in the actual failure scenario.
What About Security, Compliance, and Those Boring But Important Boxes?
Backups can contain the complete sensitive dataset. Restrict access, encrypt storage and transfers, record their contents and set retention according to documented business and legal requirements. Deletion and retention controls must cover restored copies too; a restore must not silently reactivate data previously removed from production. The precise treatment of retained backup data depends on the applicable requirements and should be documented rather than assumed.
Use dedicated service accounts with the privileges required by the selected backup method. Store credentials in protected configuration or a secret manager, and keep them out of public scripts and command-line output. Rotate access credentials and encryption keys with a recovery test; retained backups must remain decryptable for their intended lifetime.
Bringing It All Together Without Drama
A useful strategy combines a supported backup method with evidence that it can be restored. Logical exports offer readability and portability; physical tools offer another performance tradeoff for larger datasets. PITR depends on a consistent base, reliable coordinates and a continuous retained log chain. Restoring to a past boundary still requires decisions about later valid writes and external systems.
Start with explicit RPO/RTO targets and a complete rehearsal. Tune the full/incremental schedule, offsite log archival, capacity and alerts from the results. Keep the runbook clear enough for another operator to use, including versions, keys, object validation, replay boundaries and cutover. Repeat it after material changes and at a frequency appropriate to the service.
The objective is a recovery process whose dependencies and duration are known. A file, a green scheduler status or a successful checksum is only part of that evidence. Measure the entire restore, validate the application and retain the records needed to improve the next rehearsal.
