Introduction to Percona XtraBackup

Percona XtraBackup is an open-source tool for performing physical backups of MySQL databases. It supports hot backups, meaning data can be backed up without interrupting active transactions. This makes it iddeal for large databases where traditional logical backup tools like mysqldump may be too slow.

Key Feeatures

• Non-blocking backup for InnoDB and XtraDB tables
• Support for incremental backups
• Streaming backups to remote servers
• Compressed backup options
• Integrated backup verification

Installation and Basic Usage

XtraBackup can be installed via package managers or downloaded directly from Percona's website. Below is an example of a full backup command:

xtrabackup --defaults-file=/etc/mysql/my.cnf \
--user=backup_user --password=secure_password \
--backup --target-dir=/backup/full

Incremental Backups

Incremental backups only save changes since the last backup. Here's how to create one:

xtrabackup --user=backup_user --password=secure_password \
--backup --target-dir=/backup/inc1 \
--incremental-basedir=/backup/full

Preparing and Restoring Backups

Before restoring, apply the transaction logs to ensure data consistency:

xtrabackup --prepare --apply-log-only --target-dir=/backup/full
xtrabackup --prepare --target-dir=/backup/full --incremental-dir=/backup/inc1

To restore the backup:

xtrabackup --copy-back --target-dir=/backup/full \
--datadir=/var/lib/mysql

Streaming and Compression

For efficient storage and transfer, backups can be compressed and streamed:

xtrabackup --backup --stream=xbstream \
--target-dir=/tmp | gzip > /backup/compressed_backup.xbstream.gz

MySQL 8.0 Considerations

In MySQL 8.0, the innobackupex functionality has been merged into xtrabackup. Use xtrabackup directly for all backup operations.

Common Parameters

• --parallel: Number of threads for paralel processing
• --compress: Enable compression during backup
• --encrypt: Enable encryption for backup files
• --slave-info: Record replication information for slave setup