Actual combat: use xtrabackup to make incremental backups for mysql

Posted by mounika on Sun, 30 Jun 2019 01:08:48 +0200

Originality: Fat Dog and Flying Bird qq14808540

Production environment architecture: MySQL 5.6, one master and one slave. Periodic incremental backup is implemented on the slave side. The slave is configured as a 2-core 4G and hangs on the SSD data disk.

Introduction to xtrabackup

There are two tools:

xtrabackup: is a tool for hot backup of data in innodb, xtradb tables. It can not backup other types of tables, nor can it backup data table structure.
innobackupex: a perl script that encapsulates xtrabackup and provides the ability to backup myisam tables.

Installation:

yum install rsync numactl
rpm -ivh ftp://rpmfind.net/linux/dag/redhat/el6/en/x86_64/dag/RPMS/libev-4.15-1.el6.rf.x86_64.rpm

#Install xtrabackup
mkdir /home2/soft/percona-xtrabackup
cd /home2/soft
wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.5/binary/redhat/6/x86_64/Percona-XtraBackup-2.4.5-re41c0be-el6-x86_64-bundle.tar

tar xvf Percona-XtraBackup-2.4.5-re41c0be-el6-x86_64-bundle.tar -C /home2/soft/percona-xtrabackup
rpm -ivh percona-xtrabackup-24-2.4.5-1.el6.x86_64.rpm 
rpm -ivh percona-xtrabackup-24-debuginfo-2.4.5-1.el6.x86_64.rpm 
rpm -ivh percona-xtrabackup-test-24-2.4.5-1.el6.x86_64.rpm

Some github experts provide encapsulation scripts, which are more convenient to use:

cd /home2/soft
wget https://github.com/bshp/xtrabackup/archive/master.zip -O xtrabackup.zip
unzip xtrabackup.zip

Create an account for export

mysql>
CREATE USER 'backup-user'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT, SHOW VIEW, RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'backup-user'@'localhost';
FLUSH PRIVILEGES;

Full backup (long, unused)

mkdir /home2/tmp
mkdir /home2/log
#Note that you need to enter the / home2/soft/xtrabackup/bash directory for execution
cd /home2/scripts/xtrabackup-master/bash
sh ./xb-backup-fs.sh --mysql-user=backup-user --mysql-passwd=showmethemoney123456 --tmp-dir=/home2/tmp --backup-repository=/home2/fullbackup --log-file=/home2/log/xb-backup-fs.log --verbose

Incremental backup

mkdir /home2/tmp
mkdir /home2/log

#First perform the first full backup
cd /home2/scripts/xtrabackup-master/bash
sh ./xb-backup-incremental.sh --cycle-repository=/home2/cyclebackup/cycle --mysql-user=backup-user --mysql-passwd=showmethemoney123456 --tmp-dir=/home2/tmp --data-dir=/home2/cyclebackup/data-dir --log-file=/home2/log/xb-backup-fs.log --verbose

#Make a second incremental backup on a regular basis
cd /home2/scripts/xtrabackup-master/bash
sh ./xb-backup-incremental.sh --cycle-repository=/home2/cyclebackup/cycle --mysql-user=backup-user --mysql-passwd=showmethemoney123456 --tmp-dir=/home2/tmp --data-dir=/home2/cyclebackup/data-dir --log-file=/home2/log/xb-backup-fs.log --verbose --increment

Automatic backup and synchronization to backup machine

vi /home2/scripts/increase_backup.sh

cd /home2/scripts/xtrabackup-master/bash
sh ./xb-backup-incremental.sh --cycle-repository=/home2/cyclebackup/cycle --mysql-user=backup-user --mysql-passwd=password --tmp-dir=/home2/tmp --data-dir=/home2/cyclebackup/data-dir --log-file=/home2/log/xb-backup-fs.log --verbose --increment
/usr/bin/rsync -rtP /home2/cyclebackup backup@xx.xx.xx.xx::mysql/ --password-file=/home2/scripts/rsync.pw

Add the crontab entry and execute it hourly

MAILTO=""
1 * * * * /home2/scripts/increase_backup.sh > /var/log/backup.log

Restore Incremental Backup on Backup Machine

Directory: / home1/mysql
Incremental backup recovery requires three steps

  • 1 restore full backup
  • 2. Restore incremental backup to full backup (add - redo-only parameter to start recovery incremental backup and remove - redo-only to last incremental backup)
  • 3 Restore the whole complete backup and roll back the uncommitted data
#Restore base Pack and Incremental Pack 1-5
service mysqld stop
cd /home1/scripts/xtrabackup-master/bash
sh ./xb-restore-incremental.sh -b /home1/backup/mysql/cyclebackup/cycle/20170505_0959/backup_base_20170505_0959.tar.gz -i /home1/backup/mysql/cyclebackup/cycle/20170505_0959/INC/backup_inc_5_20170513_2001.tar.gz --tmp-dir=/home1/tmp --log-file=/home1/log/xb-restore-incremental.log

Improvement

Improve the shell:
The production system mysql takes up 16G of data and takes about 15 minutes to backup in full. The last step is to compress and pack the exported data, which consumes a lot of time and disk IO. The backup server needs to be decompressed when it restores. Modify xb-backup-incremental.sh and xb-restore-incremental.sh to bypass the compression and decompression of base, respectively (but when restoring backups, the data-dir backup to mysql will consume IO). Put diff directly:

Modify xb-backup-incremental.sh on slave:

diff xb-backup-incremental-old.sh xb-backup-incremental.sh 
174a175
>   _xb_base_dir="${archive_repository}/backup_base_${timestamp}"
212a214,216
> xb_base_move_dir() {
>   mv "${FLAGS_tmp_dir}/backup" "${_xb_base_dir}"
> }
239c243,248
<   xb_archive_backup || bail "An exception occured while trying to archive the backup."
---
>   #Uncompressed base, only compressed increments
>   if [[ ${FLAGS_increment} -eq ${FLAGS_true} ]]; then
>     xb_archive_backup || bail "An exception occured while trying to archive the backup."
>   else
>     xb_base_move_dir || bail "An exception occured while trying to move the base backup."
>   fi
246d254
< 

Modify xb-restore-incremental.sh on backup:

diff ./xb-restore-incremental-old.sh ./xb-restore-incremental.sh 
104c104,105
<   ${_archiver_bin} -xvpzf ${FLAGS_base_backup} -C ${FLAGS_data_dir}
---
>   #${_archiver_bin} -xvpzf ${FLAGS_base_backup} -C ${FLAGS_data_dir}
>   cp -Rf ${FLAGS_base_backup}/* ${FLAGS_data_dir}/
107c108,109
<   ${_archiver_bin} -xpzf ${FLAGS_base_backup} -C ${FLAGS_data_dir}
---
>   #${_archiver_bin} -xpzf ${FLAGS_base_backup} -C ${FLAGS_data_dir}
>   cp -Rf ${FLAGS_base_backup}/* ${FLAGS_data_dir}/*
181a184
>     #There is no need to decompress the base, because the base does not have a compressed package, but copies directly from base_dir to / home1/mysql
183,184c186
<     cmd "cp -Rf /home1/mysql-base-backup ${FLAGS_data_dir}"
<     msg_ok "Base backup uncompressed and prepared."
---
>     msg_ok "Base backup copyed and prepared."

In the recovery command, the - b parameter is set to the base directory (originally the zip path packaged by base):

sh ./xb-restore-incremental.sh -b /home1/backup/mysql/cyclebackup/cycle/20170517_1642/backup_base_20170517_1642 -i /home1/backup/mysql/cyclebackup/cycle/20170517_1642/INC/backup_inc_2_20170517_1705.tar.gz --tmp-dir=/home1/tmp --log-file=/home1/log/xb-restore-incremental.log --verbose

Topics: MySQL RPM rsync github