Master MySQL Backups with Percona XtraBackup 2.4: Full, Incremental & Compressed Guides

Preface

Many online tutorials are outdated copies; the official recommendation now is to use xtrabackup directly, as innobackupex is deprecated.

This guide uses xtrabackup 2.4.28 , which can back up InnoDB, XtraDB, MyISAM tables on MySQL 5.1‑5.7 and Percona Server with XtraDB.

Environment Installation

The tutorial assumes MySQL 5.7 is already installed. Three installation methods for xtrabackup are provided.

Docker

Recommended Docker setup (docker‑compose.yml):

version: "3.7"
services:
    xtrabackup:
        image: percona/percona-xtrabackup:2.4.28
        container_name: xtrabackup
        restart: "always"
        command: bash -c "while true; do sleep 1; done"
        volumes:
            - "/home/docker/xtrabackup:/data"
            - "/home/docker/mysql/data/:/var/lib/mysql"

The command keeps the container running for interactive use.

Debian/Ubuntu

wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb

sudo dpkg -i percona-release_latest.$(lsb_release -sc)_all.deb

percona-release enable-only tools release

sudo apt install percona-xtrabackup-24

sudo apt install qpress

Red Hat/CentOS

yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm

wget https://repo.percona.com/yum/percona-release-latest.noarch.rpm
rpm -ivH percona-release-latest.noarch.rpm

percona-release enable-only tools release

yum install percona-xtrabackup-24

yum install qpress

Notes

xtrabackup must have access to MySQL's data directory and appropriate permissions. If MySQL and xtrabackup run on different hosts, copy the data directory to the host running xtrabackup.

Common Concepts and Parameters

Full backup – a complete copy of all data files.

Prepare backup – processes a full backup so it can be restored.

Incremental backup – backs up only changes since the last full backup.

Compressed backup – compresses backup files to save space.

Key xtrabackup command‑line options:

--backup – create a backup in --target-dir .

--prepare – prepare a backup for restore.

--target-dir – directory where backup files are stored.

--apply-log-only – apply redo logs without finalizing (used for incremental backups).

--host , --port , --user , --password – MySQL connection details.

--defaults-file – path to my.cnf (must not be a symlink).

--datadir – MySQL data directory (usually read from my.cnf).

Preparing Test Data

CREATE DATABASE test;

CREATE TABLE `user` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 CHARACTER SET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=Dynamic;

INSERT INTO `user` (name) VALUES ("oldme");
INSERT INTO `user` (name) VALUES ("newton");
INSERT INTO `user` (name) VALUES ("watt");

Full Backup

Create Backup

xtrabackup --backup --host=mysql --password=12345678 --target-dir=/data/backups/base/

Result:

List backup files:

ls -l /data/backups/base

Delete a row to demonstrate restoration:

DELETE FROM `user` WHERE id=3;

Prepare Backup

xtrabackup --prepare --target-dir=/data/backups/base/

Result:

Restore Backup

Stop MySQL, then copy files back:

// base must end with /
rsync -avrP /data/backups/base/ /var/lib/mysql/

If not using Docker, fix ownership: chown -R mysql:mysql /var/lib/mysql Restart MySQL and verify data:

SELECT * FROM `user`;

id  name
--------------
1   oldme
2   newton
3   watt

Incremental Backup

Create Backup

Start from an existing full backup:

xtrabackup --backup --host=mysql --password=12345678 --target-dir=/data/backups/base/

Add a new row: INSERT INTO `user` (name) VALUES ("Einstein"); Create incremental backup based on the full backup:

xtrabackup --backup --host=mysql --password=12345678 --target-dir=/data/backups/inc1 --incremental-basedir=/data/backups/base

Further incremental backup based on the first incremental:

xtrabackup --backup --host=mysql --password=12345678 --target-dir=/data/backups/inc2 --incremental-basedir=/data/backups/inc1

Delete rows to prepare for restore:

DELETE FROM `user` WHERE id in (3,4);

Prepare and Restore

Prepare the base backup with --apply-log-only:

xtrabackup --prepare --apply-log-only --target-dir=/data/backups/base/

Apply the incremental backup:

xtrabackup --prepare --apply-log-only --target-dir=/data/backups/base --incremental-dir=/data/backups/inc1

Restore as with a full backup:

// base must end with /
rsync -avrP /data/backups/base/ /var/lib/mysql/

Resulting data:

id  name
--------------
1   oldme
2   newton
3   watt
4   Einstein

Compressed Backup

Create Compressed Backup

xtrabackup --backup --compress --host=mysql --password=12345678 --target-dir=/data/backups/compressed/

Multi‑threaded compression example:

xtrabackup --backup --compress --host=mysql --password=12345678 --compress-threads=4 --target-dir=/data/backups/compressed/

Prepare Compressed Backup

Decompress before preparing:

xtrabackup --decompress --target-dir=/data/backups/compressed/

After decompression, restore using the same steps as a full backup.