Configuring MySQL Hot Backup with Percona XtraBackup

MySQL Backup with Percona XtraBackup: What Breaks

Backups formally exist: the nightly dump runs, the file goes to storage, the checkbox in the report is ticked.

The problem is that a logical dump reads the entire database, and as the database grows, the backup window grows with it — first an hour at night, then four, and then the process overlaps with morning traffic and adds up with production load.

Hot MySQL backup via Percona XtraBackup removes most of this pain: physical data pages are copied, and writes are not stopped for the entire duration of the dump.

But locks during copying do not disappear completely — a short capture of a consistent point is needed, and if a long transaction is hanging in the database at that moment, the point cannot be taken: writes stall and stay stalled for exactly as long as the transaction lives.

A separate headache is the bloat of change logs on disk when reading the backup falls behind the write rate.

The second half of the story is recovery. A full backup restores predictably, but without a chain of incremental backups and processed logs, rollback turns into manual work for the on-call engineer: find the right set of files, restore, catch up changes, verify consistency. At night, under pressure, with the risk of making a mistake in the order of steps.

We look at how this is solved: what is configured in Percona XtraBackup itself, how the schedule is built so the window does not grow, and how to verify in advance that the backup actually restores.

What Hot Backup Delivers: Measurable Effect

A hot backup is taken from a running server: Percona XtraBackup reads data pages physically, so there is no need to stop writes, and the service continues serving clients.

We measure the effect in three values — backup window, recovery time, and the class of failures that stop being outages.

The Backup Window Shrinks Several Times Over

The backup reads data files directly, without a logical dump or intermediate tables. Then incremental backups do the work: one full backup, followed by only changed pages.

On a typical database, the backup window drops from hours to minutes, and the cycle fits into a low-load period.

Recovery Time: Tens of Minutes Instead of Hours

The backup does not need to be imported row by row: files are unpacked and attached to a prepared instance. Preparation reconciles unfinished transactions, so after startup the server serves a consistent snapshot as of the moment the backup completed.

For the service, this is a measurable reduction in downtime: recovery fits into a window known to the on-call shift in advance.

The Class of Failures "The Backup Exists but Won't Come Up" Goes Away

Recovery testing in a separate environment is part of the runbook, not a one-off event. We record preparation and startup time, verify data consistency, and only then consider the backup fit for use.

The consistency point provides a rollback point when a replica desynchronizes: the service returns to the state as of the backup, not to a random snapshot.

Backup Modes and Compression: Comparing Options

The choice of mode is determined not by database size, but by how long recovery will take and how often data changes between backups. Below are the options that are actually chosen between when configuring Percona XtraBackup, and their limitations.

Compression and encryption are layered on top of any mode. The backup is compressed on the fly, but pays for it with CPU load, and the encryption key is stored separately from the backup itself — otherwise the protection loses its meaning.

In practice, a full backup is set as the base layer, incremental backups between them, and streaming is enabled when there is no disk space for the intermediate layer.

We verify not only backup creation but also the full recovery cycle in a separate environment — otherwise the mode is considered untested.

Implementing Percona XtraBackup: Stages of Engineering Work

Backup implementation begins not with installing the utility, but with understanding how the database lives now: volume, growth rate, minimum-load window, acceptable downtime during recovery.

We rely on Percona XtraBackup — hot backup without service interruption or long table locks — but the result rests on the schedule and storage, not on the utility itself.

1. Backup scheme audit. We analyze what is backed up now and where, whether there is enough space, and when someone last tested recovery. The outcome is a risk map: backups on the same disk, jobs without return-code checks, windows with peak load.
2. Design. A full backup once a day plus incremental backups between them, retention depth and rotation, offloading outside the database server. The output is a schedule and a calculation of how much data is lost during a failure.
3. Configuration. A separate user with the minimum required privileges, compression and encryption, scheduled execution with a run log and notification if a job fails.
4. Recovery testing. We restore the backup in a separate environment, measure the time to a working state, and verify the integrity of key tables. A backup that has not been restored does not count as a backup.
5. Handover to operations. Monitoring of the age of the last successful backup, size, duration, and free space — the alert arrives before space runs out or the job fails.
6. Handover. A recovery runbook with step-by-step commands, responsible people, and periodic drills in a test environment so the procedure does not remain a verbal agreement.

Configurations, Schemes, and Database Recovery Runbooks

The result of the work is not a "configured server" but a set of artifacts by which backup can be reproduced from scratch and handed over to another engineer.

Everything lies next to the database: configurations in version control, runbooks in documentation, access in accounts with a log. Below is what exactly remains with your team after implementation.

• Backup configuration: Percona XtraBackup settings files, schedule of full and incremental backups, a separate node for preparing the snapshot before deployment.
• Backup storage scheme: storage tiers, retention by generation, off-site backup removal, rotation while preserving a rollback point to the previous release.
• Backup access policies: separate accounts for write and read, archive encryption, keys stored outside the database server, access log for the storage.
• Recovery runbook: step-by-step restore procedure, target recovery time and acceptable data loss, integrity verification, actions for an incomplete set of logs.
• Automated backup verification: regular restoration of the snapshot in an isolated environment, checksum comparison of key tables, report with the result.
• Status notifications: signals for a missed run, increased duration, preparation error, and storage filling up — before the backup is needed.
• On-call instructions: first actions during a failure, notification procedure, incident recording.
• Responsibility matrix: who owns the schedule, who reviews verification results, who rotates keys and accounts.

Case Study: Database Recovery in Minutes Instead of Hours

The client came after an incident: the database server died in the middle of the workday, and it was only brought back up by evening.

Backups were made with a nightly full dump via mysqldump — the backup window stretched to 4-5 hours, the database responded noticeably slower during the dump, and the backups themselves were stored on the same disk as the data.

Backup verification amounted to reviewing the log: recovery in a separate environment had never been run, and about a third of the archives did not restore.

We moved backups to Percona XtraBackup — hot physical copying without stopping the server.

The scheme: a weekly full backup, daily incrementals on top of it for changed pages, and preparation of the backup with a separate command on a standby node so it is ready to restore rather than requiring rework during an incident.

We automatically restore every backup in a test environment and compare checksums of key tables — backup verification became part of the schedule, not a manual operation for special occasions.

Metrics before and after: the backup window dropped from 4-5 hours to 12 minutes, full recovery from 6 hours to 9 minutes, and the share of successfully restored backups grew from 70% to 100%.

An entire class of failures disappeared — "the archive exists but won't come up": if a backup fails verification, the on-call engineer learns about it that same night, not at the moment of an incident.

What remains in operations is the schedule, alerts for a missed or failed backup, and a short recovery runbook with exact commands — so no one has to recall the order of actions at night.

How Hot Backup Differs from Logical: What to Choose

The difference is in what exactly is read from disk. A hot physical backup transfers tablespace pages and the transaction log on a running server; a logical one turns data into a stream of SQL statements. From this, load, recovery time, and granularity diverge.

Percona XtraBackup reads data files and the log in parallel without stopping writes: the server continues serving requests, and the backup remains consistent as of the start time.

Recovery is unpacking and applying the accumulated log; it takes minutes even on a database of hundreds of gigabytes.

A logical dump runs queries and writes text. It is portable between versions and lets you extract a single table, but the dump time grows linearly with volume, and recovery takes hours: rows are inserted again, indexes are rebuilt after loading.

The selection rule is simple: as long as the database fits into a short window and selective extraction is needed, a logical dump is enough.

When the volume is measured in hundreds of gigabytes or the window is compressed to minutes, hot physical backup becomes the foundation, and logical remains a supplement for targeted tasks.

Technical Doubts: Consistency, Load, Verification

Backing up a live database raises three questions: will the snapshot fall out of sync, will the service sag under reads, and can you extract a single table instead of the whole database.

Below is what we clarify before launch: the operating mode of Percona XtraBackup and the backup retention policy are built on these answers.

How much load does backup put on the production server?

The main load is reading data and the log; application writes are almost untouched, so the service's peak throughput does not collapse. We limit the number of threads and read speed: on a slow disk the mode is gentler, on a fast one we take more.

Can you recover a single table instead of the whole database?

Yes. The required table is extracted from the prepared backup and attached alongside the working data. Investigating an accidental deletion takes minutes, and a full database rollback is not required.

How many backups should be kept, and where?

A full backup once a week plus daily incrementals and mandatory off-site transfer to independent storage. Rotation is set by age and number of backups: old ones are removed automatically, and storage volume stops growing uncontrollably.

How do you make sure a backup is recoverable?

The backup is regularly restored on a separate node, the database starts and passes a verification set of queries with checksum comparison. Until the run succeeds, the backup is considered unfit and does not enter the recovery set.

Let's Discuss the Task: What to Prepare for the Conversation

So that the estimate and work plan are based on facts rather than assumptions, it is enough to bring a few inputs to the conversation.

They determine the scope and how Percona XtraBackup will fit your scheme — after that we can avoid a series of clarifying emails and repeated calls.

• Service scheme: where the databases live, whether there are replicas, who writes to the primary, and how the application survives a node failure.
• Current backups: what they are made with, how often, where they are stored, and when recovery was last tested.
• Recovery time requirements: what downtime is acceptable and to what point in time you need to roll back.
• Volumes and dynamics: data size, daily growth, low-load windows, available bandwidth to storage.
• Infrastructure: where we place backups, whether there is a separate environment, how access and encryption are set up.
• Constraints: retention periods, access runbook, what has already happened and how long recovery took.

If some data is not at hand, we will reconstruct the picture on a call. Describe your backup task in your own words: we will come back with an estimate, a work plan, and a list of recovery checks that close the risk of silent data loss.