April 17, 2022

Percona Xtrabackup Backup & Recovery

Percona Xtrabackup (PXC Part 4)


XtraBackup is an open-source MySQL hot backup software program.
Features include hot, non-locking backups for InnoDB storage, incremental backups, streaming, parallel-compressed backups, throttling based on the number of I/O operations per second, etc.

Percona XtraBackup is an open-source hot backup utility for MySQL servers that doesn’t lock databases during the backup.


Percona Xtrabackup (8.0) installation from Repository

Percona provides repositories for yum (RPM packages for Red Hat, CentOS and Amazon Linux AMI) and apt (.deb packages for Ubuntu and Debian) for software such as Percona Server for MySQL, Percona XtraBackup, and Percona Toolkit.

yum list all | grep xtrabackup
dnf install percona-xtrabackup-80 -y

Percona XtraBackup can backup data from InnoDB, XtraDB, MyISAM tables on MySQL 8.0 servers as well as Percona Server for MySQL with XtraDB, Percona Server for MySQL 8.0, and Percona XtraDB Cluster 8.0.

Creating Backup

To create a backup, run xtrabackup with --backup option and specify --target-dir option where the backup will be stored.
xtrabackup --backup --target-dir=/data/backups/
xtrabackup --backup --user=satya --password=Secret9 --target-dir=/path/to/backupdir
xtrabackup --backup --user=satya --password=Secret9 --slave-info --target-dir=/path/to/backupdir

Preparing Backup

After making a backup with the --backup option, you need need to prepare it in order to restore it.
xtrabackup --prepare --target-dir=/data/backups/
xtrabackup --prepare --user=satya --password=Secret9 --target-dir=/path/to/backupdir

Restoring Backup

For convenience, xtrabackup binary has the --copy-back option to copy the backup to the datadir of the server. Backup needs to be prepared before it can be restored.
xtrabackup --copy-back --target-dir=/data/backups/

Incremental Backup

Percona xtrabackup supports incremental backups, which means that they can copy only the data that has changed since the last backup.
DBAs can perform many incremental backups between each full backup, to have a backup process such as a full backup once a week and an incremental backup every day, or full backups every day and incremental backups every hour.

To make an incremental backup, begin with a full backup as usual. The xtrabackup binary writes a file called xtrabackup_checkpoints into the backup’s target directory.
xtrabackup --backup --target-dir=/data/backups/full
xtrabackup --backup --target-dir=/root/backups/full -uroot --pSecret9

Now that you have a full backup, you can make an incremental backup based on it.
xtrabackup --backup --target-dir=/data/backups/incl --incremental-basedir=/data/backups/full
xtrabackup --backup --target-dir=/root/backups/incl --incremental-basedir=/root/backups/full -uroot --pSecret9

Preparing Incremental Backups

The --prepare step for incremental backups is not the same as for full backups. Beginning with the full backup, you can prepare it, and then apply the incremental differences to it.
xtrabackup --prepare --apply-log-only --target-dir=/data/backups/full
xtrabackup --prepare --apply-log-only --target-dir=/root/backups/full -uroot --pSecret9

To apply the first incremental backup to the full backup. Preparing the second/third incremental backup is a similar process.
xtrabackup --prepare --apply-log-only --target-dir=/data/backups/full --incremental-dir=/data/backups/incl
xtrabackup --prepare --apply-log-only --target-dir=/root/backups/full --incremental-dir=/root/backups/incl -uroot --pSecret9

Restoring Incremental Backups

Once prepared incremental backups are the same as the full backups and they can be restored in the same way.

.. shutdown database ..
xtrabackup --copy-back --target-dir=/root/backups/
xtrabackup --copy-back --target-dir=/data/backups/
Related MySQL Articles: Percona Monitoring and Management (PMM)   How to use tools in Percona Toolkit
Labels: , , ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)