Percona XtraBackup

From DevOps Notebook

Backup and restore with XtraBackup 8.0

Note that credentials need to be in .my.cnf in home folder of running user

Backup with Percona XtraBackup 8.0

$ xtrabackup --backup --target-dir=/path/to/backups/
$ xtrabackup --prepare --target-dir=/path/to/backups/

Restore with Percona XtraBackup 8.0


Backup needs to be prepared before it can be restored
Method 1:

$ xtrabackup --copy-back --target-dir=/path/to/backups/

Method 2:

$ rsync -avrP /path/to/backup/ /var/lib/mysql/
$ chown -R mysql:mysql /var/lib/mysql

Sample script with backup with percona XtraBackup 8.0 over nfs mount

#!/bin/bash

HOSTNAME=`/bin/hostname | awk -F "." '{print $1}'`
MONTH=$(date +"%m")
DAY=$(date +"%d")
YEAR=$(date +"%Y")
HOUR=$(date +"%H")
MINUTE=$(date +"%M")
TIMESTAMP="$YEAR/$MONTH/$DAY/"
BKP_NAME="$HOSTNAME"_"$HOUR"_"$MINUTE"
BACKUP_DIR="/mnt/bkp/$TIMESTAMP"
NFS_STORAGE="172.20.0.254:/backup/"

/bin/mkdir -p /mnt/bkp/
/bin/mount $NFS_STORAGE /mnt/bkp/
/bin/sleep 10
/bin/mkdir -p $BACKUP_DIR
/usr/bin/nice -n 19 /usr/bin/ionice -c 3 xtrabackup --backup --no-timestamp --slave-info --target-dir=$BACKUP_DIR$BKP_NAME
/bin/sleep 10
/usr/bin/nice -n 19 /usr/bin/ionice -c 3 xtrabackup --prepare --target-dir=$BACKUP_DIR$BKP_NAME
/bin/cp /etc/mysql/my.cnf $BACKUP_DIR$BKP_NAME/my.cnf.bkp
/bin/sleep 10
/bin/umount /mnt/bkp/