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