Guide to Database Backups with Percona XtraBackup

In database management, protecting your data against loss or corruption is a top priority. 

For MySQL or MariaDB users, Percona XtraBackup stands out as a powerful open-source solution that enables live (hot) backups without interrupting operations. In addition to full backups, it also supports incremental snapshots, compression, and encryption — essential tools for any serious database administrator.

This guide walks you through the essentials of using XtraBackup, from installation to automation.

Percona XtraBackup is a free open-source tool designed for efficient backups of MySQL-compatible databases. Built to handle the demands of modern, high-volume environments, it offers a reliable way to protect your data without disrupting daily database operations. Whether you’re managing production workloads, automating recovery processes, or handling large-scale datasets, XtraBackup provides the performance and flexibility trusted by database professionals.

While tools like mysqldump are commonly used, they come with limitations such as downtime due to table locks and slower restore times.

XtraBackup overcomes these issues with:

• Real-time backups while the database remains online (hot backups),
• Support for full, incremental, and compressed backups,
• Integration with MySQL and MariaDB,
• Point-in-time recovery (PITR) capabilities,
• Faster restore speeds compared to traditional logical backups.

Now that we’ve covered what Percona XtraBackup is and why it’s useful, let’s do a few clicks (and a couple of terminal commands) to get Percona XtraBackup up and running. We’ll start with installing the tool, setting up a MySQL backup user, and preparing the environment for smooth, reliable backups.

For Debian/Ubuntu systems:

# Add Percona's official repository
wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb
dpkg -i percona-release_latest.$(lsb_release -sc)_all.deb

# Enable the repository
percona-release enable-only tools release

# Update and install
apt-get update
apt install percona-xtrabackup-24

Grant limited privileges to a dedicated backup user:

CREATE USER 'sysbee_backup'@'localhost' IDENTIFIED BY 'generate_strong_password';
GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'sysbee_backup'@'localhost';
FLUSH PRIVILEGES;

Create Test Data for the Backups:

-- Create a new database for testing
mysql> CREATE DATABASE library;

-- Create a 'books' table
mysql> CREATE TABLE library.books (
id INT NOT NULL AUTO_INCREMENT,
title VARCHAR(100),
author VARCHAR(100),
published_year INT,
genre VARCHAR(50),
PRIMARY KEY(id)
);

-- Insert some sample data
mysql> INSERT INTO library.books (title, author, published_year, genre)
VALUES
("1984", "George Orwell", 1949, "Dystopian"),
("To Kill a Mockingbird", "Harper Lee", 1960, "Classic"),
("The Hobbit", "J.R.R. Tolkien", 1937, "Fantasy");

mkdir -p /var/backups/mysql
chown mysql:mysql /var/backups/mysql
chmod 750 /var/backups/mysql

Here’s a basic script to create and prepare a full backup:

#!/bin/bash

# Configuration
BACKUP_DIR="/var/backups/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_USER="sysbee_backup"
BACKUP_PASS="generate_strong_password"

# Create backup
xtrabackup --backup \
--user=$BACKUP_USER \
--password=$BACKUP_PASS \
--target-dir=$BACKUP_DIR/full_$DATE

# Prepare for restore
xtrabackup --prepare \
--target-dir=$BACKUP_DIR/full_$DATE

To reduce space usage and speed up backups on weekdays, combine full and incremental backups:

#!/bin/bash

# Configuration
BACKUP_DIR="/var/backups/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_USER="sysbee_backup"
BACKUP_PASS="generate_strong_password"
FULL_BACKUP_DAY="Sunday"

# Determine backup type
if [[ $(date +%A) == $FULL_BACKUP_DAY ]]; then
# Full backup
xtrabackup --backup \
--user=$BACKUP_USER \
--password=$BACKUP_PASS \
--target-dir=$BACKUP_DIR/full_$DATE
# Prepare backup
xtrabackup --prepare \
--target-dir=$BACKUP_DIR/full_$DATE
# Update latest full backup link
ln -sf $BACKUP_DIR/full_$DATE $BACKUP_DIR/latest_full
else
# Incremental backup
xtrabackup --backup \
--user=$BACKUP_USER \
--password=$BACKUP_PASS \
--target-dir=$BACKUP_DIR/incr_$DATE \
--incremental-basedir=$BACKUP_DIR/latest_full
fi

To prevent disk space issues, add cleanup logic:

# Retain only 7 days of backups
cleanup_old_backups() {
# Keep 7 days of backups
find $BACKUP_DIR -type d -name "full_*" -mtime +7 -exec rm -rf {} \;
find $BACKUP_DIR -type d -name "incr_*" -mtime +7 -exec rm -rf {} \;
}

Automating with Cron

Create a daily backup schedule:

# /etc/cron.d/mysql-backup
0 1 * * * root /usr/local/bin/mysql-backup.sh >> /var/log/mysql-backup.log 2>&1

#!/bin/bash

# Monitoring Backup Success
# Function to send email notification
send_email_notification() {
local subject="$1"
local body="$2"
local to="admin@sysbee.net" 
echo "$body" | mail -s "$subject" "$to"
}

# Function to check backup status
check_backup() {
if [ $? -eq 0 ]; then
echo "Backup succeeded"
send_email_notification "Backup Succeeded" "Backup succeeded on $(hostname) at $(date)."
else
echo "Backup failed"
send_email_notification "Backup Failed" "Backup failed on $(hostname) at $(date). Please investigate."
fi
}

1. Full XtraBackup Restoration

# Stop MySQL
systemctl stop mysql

# Clear data directory
rm -rf /var/lib/mysql/*

# Restore backup
xtrabackup --copy-back --target-dir=/var/backups/mysql/full_20250723_220000

# Set correct permissions
chown -R mysql:mysql /var/lib/mysql

# Start MySQL
systemctl start mysql

2. Point-in-Time Recovery

Incremental backups with Percona XtraBackup allow you to back up only the data that has changed since the last backup—either a full or another incremental one.
This approach saves both time and disk space, especially in environments with large databases and minimal daily changes.
To restore from incremental backups, the process starts with a full backup, followed by applying each incremental backup in the exact order they were taken.
This rebuilds the database to its most recent consistent state while offering the ability to restore to specific points in time.

Each incremental backup depends on the previous one. If you want to restore your data as it was on Wednesday, you must apply:

The full backup (Sunday)
Monday’s incremental
Tuesday’s incremental
Wednesday’s incremental

You cannot skip any in between — they are cumulative.

# Stop MySQL
systemctl stop mysql

# Clear data directory
rm -rf /var/lib/mysql/*

# Restore the base full backup to the MySQL data directory.
# This copies all the backup files from the full backup location back to /var/lib/mysql.
xtrabackup --copy-back --target-dir=/var/backups/mysql/full_base

# Apply incremental changes on top of the full backup.
# This merges the changes from the incremental backup (incr_1) into the restored base backup.
# This step is essential for point-in-time recovery and must be repeated for each incremental in order.
xtrabackup --prepare --target-dir=/var/backups/mysql/full_base \
--incremental-dir=/var/backups/mysql/incr_1

# Apply incr_2
xtrabackup --prepare --target-dir=/var/backups/mysql/full_base \
--incremental-dir=/var/backups/mysql/incr_2

# Apply incr_3 (if exists)
xtrabackup --prepare --target-dir=/var/backups/mysql/full_base \
--incremental-dir=/var/backups/mysql/incr_3

Percona XtraBackup is a dependable, high-performance solution for MySQL-based database backups.
With support for live backups, PITR, and automation-friendly design, it fits perfectly into modern infrastructure.
By following the setup and scripting examples above, you can ensure your data is protected — with minimal performance overhead.

If you’re looking to implement a robust backup strategy but don’t have the time or in-house expertise, Sysbee can help. Our team can design, automate, and monitor a backup solution tailored to your environment — ensuring your data is safe, recoverable, and compliant with best practices.