Master MySQL Backups with Percona XtraBackup 2.4: Full, Incremental & Compressed Guides
This step‑by‑step tutorial shows how to install Percona XtraBackup 2.4, create full, incremental and compressed backups of MySQL 5.1‑5.7, prepare the backups, and restore data using Docker or native Linux packages.
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.debsudo dpkg -i percona-release_latest.$(lsb_release -sc)_all.debpercona-release enable-only tools releasesudo apt install percona-xtrabackup-24sudo apt install qpressRed Hat/CentOS
yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpmwget https://repo.percona.com/yum/percona-release-latest.noarch.rpm
rpm -ivH percona-release-latest.noarch.rpmpercona-release enable-only tools releaseyum install percona-xtrabackup-24yum install qpressNotes
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/baseDelete 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 wattIncremental 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/baseFurther incremental backup based on the first incremental:
xtrabackup --backup --host=mysql --password=12345678 --target-dir=/data/backups/inc2 --incremental-basedir=/data/backups/inc1Delete 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/inc1Restore 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 EinsteinCompressed 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.
Signed-in readers can open the original source through BestHub's protected redirect.
This article has been distilled and summarized from source material, then republished for learning and reference. If you believe it infringes your rights, please contactand we will review it promptly.
How this landed with the community
Was this worth your time?
0 Comments
Thoughtful readers leave field notes, pushback, and hard-won operational detail here.
