Difference between revisions of "Percona XtraBackup"
From DevOps Notebook
Line 7: | Line 7: | ||
</pre> | </pre> | ||
− | === Restore with Percona XtraBackup 8.0 === <br> | + | === Restore with Percona XtraBackup 8.0 === |
+ | <br> | ||
''Backup needs to be prepared before it can be restored'' <br> | ''Backup needs to be prepared before it can be restored'' <br> | ||
Method 1: | Method 1: | ||
Line 18: | Line 19: | ||
$ rsync -avrP /path/to/backup/ /var/lib/mysql/ | $ rsync -avrP /path/to/backup/ /var/lib/mysql/ | ||
$ chown -R mysql:mysql /var/lib/mysql | $ chown -R mysql:mysql /var/lib/mysql | ||
+ | </pre> | ||
+ | |||
+ | === Sample script with backup with percona XtraBackup 8.0 over nfs mount === | ||
+ | <pre syntaxhighlight="bash"> | ||
+ | #!/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/ | ||
</pre> | </pre> |
Revision as of 16:20, 21 March 2020
Contents
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/