Automated Percona Backups with XtraBackup – Simple Script Guide

DATABASEPERCONA

ITQuickFix

4/19/20252 min read

In this guide, we’ll walk through the process of automating hot, full backups from a production Percona database and restoring them onto a test server. The goal is to regularly back up data from production and replicate it in testing environments for development or validation purposes.

Both servers run:

Operating System: Rocky Linux 9
Database Engine: Percona Server 8.4
Backup Tool: Percona XtraBackup 8.4

Environment Setup

Servers

Production Server: Percona 8.4 on Rocky Linux 9
Test Server: Percona 8.4 on Rocky Linux 9

Required Variables

# Access credentials

HOST="your_production_db_ip_or_hostname"

PORT="3306"

USER="your_username"

PASS="your_password"

# Directories

BACKUP_DIR="/backup/xtrabck/"

DATA_DIR="/var/lib/mysql"

# Timestamp

START=$(date +"%m-%d-%Y %H:%M:%S")

END=$(date +"%m-%d-%Y %H:%M:%S")

In our case:

We have a dedicated 200 GB disk mounted at /backup.
The Percona data directory is located at /var/lib/mysql.

Clean the Backup Directory

Before starting a new backup, ensure that the backup directory is clean to avoid consuming unnecessary disk space:

CLEAN=$(du -sh /backup/xtrabck | cut -f1)

if [ ! -d $BACKUP_DIR ]; then

mkdir -p $BACKUP_DIR

rm -rf $BACKUP_DIR/*

echo "$(date '+%Y-%m-%d %H:%M:%S:%s') : Cleanup the backup folder is done! Starting backup" >> $BACKUP_DIR/xtrabackup.log

Performing the Hot Backup

Now, perform the actual hot backup using LZ4 compression and parallel threads to speed up the process:

xtrabackup --backup \

--datadir=$DATA_DIR \

--target-dir=$BACKUP_DIR \

--user="$USER" \

--password="$PASS" \

--no-timestamp \

--compress=LZ4 \

--compress-threads=4

echo "$(date '+%Y-%m-%d %H:%M:%S:%s') : Backup Done!" >> $BACKUP_DIR/xtrabackup.log

Hiding Credentials (Optional)

To avoid hardcoding credentials in the script, you can use the MySQL configuration file /etc/my.cnf:

[xtrabackup]

user=your_username

password=your_password

Once the credentials are stored there, you can remove --user and --password from the xtrabackup command.

Make sure to secure the file:

chmod 600 /etc/my.cnf

Complete Backup Script

Here’s the full script, combining all of the above:

#!/bin/bash

# Database access

HOST='your_production_db_ip_or_hostname'

PORT='3306'

USER='your_username'

PASS='your_password'