Difference between revisions of "Percona XtraBackup"
From DevOps Notebook
| (3 intermediate revisions by the same user not shown) | |||
| Line 1: | Line 1: | ||
== Backup and restore with XtraBackup 8.0 == | == Backup and restore with XtraBackup 8.0 == | ||
''Note that credentials need to be in .my.cnf in home folder of running user'' | ''Note that credentials need to be in .my.cnf in home folder of running user'' | ||
| − | Backup | + | === Backup with Percona XtraBackup 8.0 === |
<pre> | <pre> | ||
$ xtrabackup --backup --target-dir=/path/to/backups/ | $ xtrabackup --backup --target-dir=/path/to/backups/ | ||
| Line 7: | Line 7: | ||
</pre> | </pre> | ||
| − | Restore | + | === 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 19: | Line 20: | ||
$ chown -R mysql:mysql /var/lib/mysql | $ chown -R mysql:mysql /var/lib/mysql | ||
</pre> | </pre> | ||
| + | |||
| + | === Sample script with backup with percona XtraBackup 8.0 over nfs mount === | ||
| + | <syntaxhighlight lang="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/ | ||
| + | </syntaxhighlight> | ||
Latest revision as of 16:22, 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/
