Backup for MySQL/MariaDB: LVM snapshots, Percona XtraBackup and point-in-time RESTore

A backup for MySQL/MariaDB is often “somehow available” in everyday operations — until the first real RESTore. Then it becomes apparent whether you only copied files or whether you have a recoverable database backup. In production environments the concern is rarely individual tables; it is about reliable RPO (Recovery Point Objective: maximum tolerable data loss) and RTO (Recovery Time Objective: maximum tolerable downtime), reproducible processes, and backups that work under load, with large InnoDB data sets, and with ongoing write activity.

This article places three central building blocks into a practical context: LVM snapshots (block-device-level snapshots for fast, local “freeze points”), Percona XtraBackup (hot backup for InnoDB without shutdown) and the point-in-time RESTore (PITR) via binary logs (binlogs: the log of data changes). The focus is on prerequisites, risks, typical pitfalls, verification steps, implementation and a fallback strategy that does not have to be improvised during an incident.

Backup for MySQL/MariaDB: What “consistent” really means for MySQL/MariaDB

With MySQL/MariaDB “consistency” is multi-layered. It is important to distinguish between crash-consistent and transaction-consistent:

• Crash-consistent: The backup corresponds to a sudden power failure. InnoDB can roll back to a consistent state at startup via crash recovery (redo/undo logs). That is often sufficient, but not always predictably fast.
• Transaction-consistent: The backup represents a clean cut point at which all transactions are complete. That reduces surprises during recovery and is better for tight RTOs.

InnoDB (the default storage engine) supports transactions and can perform crash recovery. Other engines (e.g. MyISAM) have different characteristics; in mixed environments the risks increase. In practice you should therefore first clarify: Which engines and features are in use? These include replication, at-REST encryption, table compression, large BLOBs and, importantly, binary logging.

Building block 1: LVM snapshots for MySQL/MariaDB — fast, but not magical

LVM (Logical Volume Manager) enables snapshots at block-device level. A snapshot captures the state of a logical volume at a point in time. Technically this works via copy-on-write: from the snapshot point onward changed blocks are redirected to the snapshot area. This is attractive because a snapshot can be created in seconds — but it is not a complete data backup, rather a temporary “freeze point” that you must subsequently back up (e.g. via rsync or backup software).

Prerequisites and typical architecture

The most important prerequisite: The MySQL/MariaDB datadir (typically /var/lib/mysql) must reside on an LVM logical volume. A common pitfall: binlogs, relay logs, tmpdir or separate partitions are not on that volume. For a consistent RESTore you must consider all related data paths: datadir, binlogs, configuration files, possibly keyring files (for at-REST encryption), certificates and scripts/units.

Why LVM snapshots can fail for write-heavy databases

• Snapshot fills up: Under high write load the snapshot area grows quickly. If it fills, the snapshot becomes invalid — and your backup run is worthless.
• Performance drop: Copy-on-write generates additional I/O. Under load this can be clearly noticeable.
• Disjoint data paths: If binlogs or keyring are located outside, you may have files but not a reproducible state.

Rule of thumb: LVM snapshots are useful if you keep them short-lived and immediately back up from the snapshot. They are less suitable if the snapshot exists for a long time or if write load is unpredictable.

Implementation: create snapshot, back up, then remove

A practical procedure combines LVM with a short database „freeze.“ For InnoDB it is common to use FLUSH TABLES WITH READ LOCK (FTWRL) to force a consistent point for non-transactional parts; for pure InnoDB workloads this is often unnecessary, but in mixed environments it is a safety net. Important: a global read lock blocks writes and can slow down applications. Plan for it consciously and keep it brief.

#!/usr/bin/env bash
set -euo pipefail

MYSQL_SOCK="/var/run/mysqld/mysqld.sock"
MYSQL_USER="backup"
MYSQL_PWD_FILE="/etc/mysql/backup.pwd"
LV="/dev/vg0/mariadb"
SNAP_NAME="mariadb_snap"
SNAP_SIZE="30G"   # must match the write load
MOUNTPOINT="/mnt/mariadb_snap"

mysql_exec() {
  mysql --protocol=socket --socket="$MYSQL_SOCK" -u"$MYSQL_USER" \
    --password="$(cat "$MYSQL_PWD_FILE")" -e "$1"
}

# 1) optional: short read lock to create a consistent point-in-time
mysql_exec "FLUSH TABLES WITH READ LOCK;"
# note positions for PITR/diagnostics (vary by MySQL/MariaDB version)
mysql_exec "SHOW MASTER STATUS;"

# 2) create snapshot
lvcreate -s -n "$SNAP_NAME" -L "$SNAP_SIZE" "$LV"

# 3) release lock immediately
mysql_exec "UNLOCK TABLES;"

# 4) mount and back up snapshot
mkdir -p "$MOUNTPOINT"
mount -o ro "/dev/vg0/$SNAP_NAME" "$MOUNTPOINT"

# example: tar to backup target (placeholder)
BACKUP_DIR="/backup/mariadb"
mkdir -p "$BACKUP_DIR"
DATE="$(date +%F_%H%M%S)"

tar -C "$MOUNTPOINT" -cpf "$BACKUP_DIR/datadir_${DATE}.tar" var/lib/mysql

# 5) cleanup
umount "$MOUNTPOINT"
lvremove -f "/dev/vg0/$SNAP_NAME"

Important: This example deliberately shows the process, not all hardening details. In production you should release locks cleanly in trap handlers, plan snapshot sizes dynamically and verify backup integrity (see below). Also: a tar of the datadir only makes sense if you also back up the other relevant paths (binlogs, config, keyring) or clearly document that they are stored separately.

Checks and monitoring for LVM snapshot backups

• Snapshot utilization: add lvs -a -o+seg_monitor,lv_size,data_percent,metadata_percent to monitoring. Alert before data_percent becomes critical.
• Duration of the snapshot: Measure and limit snapshot lifetime. The longer it is, the greater the risk of “filling up” and the higher the I/O overhead.
• RESTore probe: Regularly unpack on a test system, start the database, observe crash recovery, then run CHECK TABLE selectively or perform application health checks.

Component 2: Percona XtraBackup – Hot backups for InnoDB with improved predictability

Percona XtraBackup is a tool that can back up InnoDB data “while running” (hot backup). It reads the InnoDB data files and uses log information to create a consistent state. For admin teams XtraBackup is often the most practical approach when downtime must be avoided and LVM snapshots are too risky (snapshot filling, separate paths, storage layout).

Important for MariaDB: Depending on version and distribution there are differences between XtraBackup variants and compatibility. Verify in advance whether the XtraBackup version you use supports your MariaDB version and whether features like encryption, Galera or certain table formats are adequately covered. In heterogeneous environments a standardized “backup toolchain” matrix document is worthwhile.

What XtraBackup backs up — and what you additionally need

XtraBackup typically produces a physical backup (file/page level) including metadata. For a complete RESTart you additionally need:

• Configuration (e.g. my.cnf, includes, systemd overrides), because parameters like innodb_buffer_pool_size, datadir, log_bin or server_id influence startup behavior.
• Binary Logs for PITR, when the RPO is smaller than your backup interval.
• Keyring/encryption artifacts (depending on the setup); otherwise the data may exist but be undecryptable.

Base runbook: Full backup and prepare phase

The typical process consists of “backup” and “prepare”. In the prepare phase the necessary log replays are performed so that the backup is bootable. Without prepare, a RESTore is often incomplete or fails to start cleanly.

#!/usr/bin/env bash
set -euo pipefail

BACKUP_BASE="/backup/mariadb/xtrabackup"
DATE="$(date +%F_%H%M%S)"
TARGET="$BACKUP_BASE/full_$DATE"

mkdir -p "$TARGET"

# Full backup (example parameters; manage credentials securely via file/secret store)
xtrabackup 
  --backup 
  --target-dir="$TARGET" 
  --user=backup 
  --password-file=/etc/mysql/backup.pwd 
  --socket=/var/run/mysqld/mysqld.sock

# Prepare: makes the backup consistent/bootable
xtrabackup --prepare --target-dir="$TARGET"

Typical pitfall: the backup is created, but ‚prepare‘ is forgotten or executed on another host with an incompatible version. Schedule the prepare phase as a fixed job step and record the version states (database and tool) in the backup metadata.

Incremental backups: save storage, but RESTores are more complex

Incremental XtraBackup backups reduce data volume and runtime, but increase RESTore complexity: you must keep the chain (full + all increments) intact and apply „apply-log“/prepare in the correct order. For tight RTOs the RESTore time of the chain is decisive — not the backup time.

Operational recommendation: use increments only if you regularly exercise the RESTore chain in a RESTore probe. Otherwise you save time during normal operation and lose it multiple times during an incident.

Troubleshooting: common XtraBackup issues

• „permission denied“ oder SELinux/AppArmor: XtraBackup needs read access to the datadir and logs. With SELinux/AppArmor the profiles must be correct, otherwise the backup will abort halfway through.
• Disk full on the target: Physical backups are large. Without a pre-check for free space and without a retention strategy you will end up with partial backups.
• Incompatible combinations: MariaDB/MySQL versions and XtraBackup versions must match. Symptoms are Prepare errors or startup failures after RESTore.
• Binlog positions missing: For PITR you need defined start points (file/position or GTID, if used). Ensure you store this information per backup.

Baustein 3: Point-in-Time-RESTore (PITR) mit Binary Logs – der Weg zu engem RPO

A PITR allows you to RESTore a database not only to the time of the last backup but to any point in time thereafter — typically ‚up to just before the error‘. This works by applying a base backup (e.g. XtraBackup or an LVM-based snapshot) and then replaying the Binary Logs (Binlogs) up to a stop point. Binlogs are the change journal of the database (DML/DDL events), also used for replication.

Prerequisites: enable and retain Binlogs correctly

Without Binlogs there is no PITR. Check in the configuration (my.cnf) whether log_bin is enabled and how retention is regulated (e.g. binlog_expire_logs_seconds or older parameters). Pay attention to storage locations: if Binlogs reside on the same volume and a storage failure occurs, the base backup and Binlogs are simultaneously at risk. For reliable PITR capability Binlogs should be replicated/secured to a separate target promptly.

[mysqld]
log_bin=/var/log/mysql/mysql-bin
binlog_format=ROW
# Aufbewahrung (Beispiel): 7 Tage
binlog_expire_logs_seconds=604800
sync_binlog=1

Note: binlog_format=ROW (row-based replication) is in many production environments the more robust choice because changes are logged as row events. That helps replication consistency but can increase binlog size. sync_binlog=1 increases safety (the binlog is flushed to disk more frequently) but can affect write performance. These parameters are trade-offs; document them as part of your backup/recovery policy.

PITR runbook in steps (operational)

1. Define stop time: „Until when“ should the system be RESTored? Often this is shortly before a failed deploy, a faulty job, or a deletion action.
2. RESTore base backup: XtraBackup RESTore or LVM backup back onto a fresh datadir.
3. Determine starting point of the binlogs: From backup metadata (binlog file/position or GTID). Without this starting point, PITR is error-prone.
4. Apply binlogs: Use mysqlbinlog to extract and apply the relevant binlogs up to the target time.
5. Validation: Application checks, data integrity (spot checks), replication status (if relevant), performance parameters.

Example: applying binlogs up to a target time

The following example demonstrates the principle. It assumes you have the binlogs available and can determine the target time precisely (pay attention to time zones!).

#!/usr/bin/env bash
set -euo pipefail

# Zielzeit (lokal/UTC bewusst wählen und dokumentieren)
STOP_TIME="2026-07-28 10:15:00"

# Beispiel: Binlogs aus zentralem Archiv in lokales Verzeichnis
BINLOG_DIR="/RESTore/binlogs"

# Verbindung zur wiederhergestellten Instanz
MYSQL_HOST="127.0.0.1"
MYSQL_PORT="3306"
MYSQL_USER="root"
MYSQL_PWD_FILE="/etc/mysql/root.pwd"

apply_binlogs() {
  local binlogs=("$@")
  mysqlbinlog --stop-datetime="$STOP_TIME" "${binlogs[@]}" 
    | mysql -h"$MYSQL_HOST" -P"$MYSQL_PORT" -u"$MYSQL_USER" --password="$(cat "$MYSQL_PWD_FILE")"
}

# Beispiel: Alle Binlogs in zeitlicher Reihenfolge anwenden
mapfile -t FILES < <(ls -1 "$BINLOG_DIR"/mysql-bin.* | sort)
apply_binlogs "${FILES[@]}"

Typical pitfalls in PITR:

• Time zone / clock drift: Event timestamps in binlogs and your „incident time“ must align. NTP (time synchronization) is essential here.
• Incomplete binlog chain: If a segment is missing, there are gaps. Therefore: back up binlogs continuously and regularly verify completeness.
• GTID vs. file/position: If GTID (Global Transaction ID) is used, PITR/failover is often easier—but only if configured and understood consistently. Without GTID, file/position remains the basis.
• Row events and large transactions: Very large transactions can prolong the apply time. That directly impacts your RTO.

Which combination is „right“? Decision guidance based on operational goals

In practice the building blocks are combined, not played off against each other. A pragmatic decision guide:

• LVM snapshot + file backup: Good for very fast local recovery points when the storage layout is clean and write load is predictable. Risks: snapshot space exhaustion and inconsistent paths.
• XtraBackup (full/incremental): Good as the standard backup for large InnoDB databases without downtime. Risks: version/compatibility management and RESTore chains with incrementals.
• PITR using binlogs: Mandatory when you require tight RPOs or frequently need to catch “logical errors” (deletes, wrong job, deploy). Risk: binlog retention and clean documentation of the start point.

A robust standard architecture for many environments is: XtraBackup as the base (e.g. daily) plus binlog backup (continuous/tightly scheduled) for PITR. LVM snapshots can be a useful complement, for example as a fast local “pre-change” snapshot before maintenance, provided the snapshot risks are actively managed.

Checklist: Before the first “real” production run

1) Inventory and paths

• Datadir, binlog directory, relay logs (for replication), tmpdir, config-includes recorded
• Encryption/keyring files and certificates identified and included in the backup plan
• Storage layout documented (LVM, RAID, SAN, cloud volumes), including dependencies

2) Backup jobs and retention

• Backup windows, I/O limits and priorities defined (so production does not fail)
• Retention for full/incremental and binlogs aligned with RPO/RTO and compliance
• Storage-space guards: pre-checks, alert thresholds, clean cleanup of old backups

3) Validation and RESTore drills

• Automated RESTore probe (e.g. weekly) on an isolated system
• Success criteria: DB starts, application can read/write, critical queries return plausible results
• Metrics: duration of RESTore and binlog replay (measure RTO realistically, don’t estimate)

Fallback strategy: If the RESTore does not go as planned

Even good backups sometimes fail in practice because of the environment: wrong version, missing binlogs, insufficient space, undocumented parameters. A fallback strategy does not mean “giving up”, but preparing alternative paths:

• Parallel RESTore instead of in-place: RESTore to a separate host/VM, then perform a controlled cutover. This reduces risk and simplifies analysis.
• Last backup guaranteed to be startable: If PITR fails, define which backup is guaranteed to start (Prepared XtraBackup, verified LVM backup).
• Binlog replay in stages: If the replay fails at a point (e.g. due to inconsistent DDL), work with clear stop points, logs and a reproducible procedure, instead of trial and error.
• Runbook and decision points: Define when to switch from Plan A (PITR) to Plan B (last full) to avoid missing the RTO.

Security and operational aspects that are often overlooked

Access and secrets

Backup users need minimal privileges. Passwords must not be in scripts. Use file permissions (root-only), secret stores or dedicated credential mechanisms. Do not accidentally log passwords (e.g. via set -x).

Ransomware resilience

Backups are a target. Separate backup targets logically and, where possible, organizationally (separate credentials, write-once/immutable options, offline copies). A PITR is of little use if binlogs and backups were encrypted together.

Versioning and migration capability

Plan how a RESTore works on newer hardware or in a new environment: paths, systemd units, package versions, kernel/filesystem. Especially with MariaDB/MySQL, default parameters differ across versions. Document the exact versions of the DB and the backup tool for each backup run.

Conclusion: Backups are only „good“ once PITR and a RESTore probe have completed

A reliable backup for MySQL/MariaDB consists of more than a nightly job. LVM snapshots provide very fast, local recovery points, but must be actively managed because of snapshot filling, I/O overhead and path consistency. Percona XtraBackup is the stable standard for hot backups in many InnoDB-heavy environments — provided the prepare phase, versions and permissions are properly controlled. The decisive improvement in RPO comes from the Point-in-Time-RESTore via binlogs: it catches the typical „logical errors“ that in practice are more common than storage failures.

If you can only prioritize one thing: automate a regular RESTore probe (including binlog replay up to a test point) and measure RTO/RPO with real run times. Only then does „we have backups“ become a reliable RESTart path.

For this topic, LVM snapshots, MySQL and Percona XtraBackup/MariaDB are also important. The article places these aspects in context and shows what matters in everyday operations.