MySQL in-xtrabackup backup and recovery applications

For an introduction to Xtrabackup (or Innobackupex), refer to the official documentation for details

Xtrabackup Installation Guide

File preparation

[[email protected] ~]# cd percona-xtrabackup/[[email protected] percona-xtrabackup]# lslibev-4.15-1.el6.rf.x86_64.rpm  percona-release-0.1-4.noarch.rpm  percona-xtrabackup-24-2.4.4-1.el7.x86_64.rpm

Start installing dependent files

[[email protected] percona-xtrabackup]# rpm -ivh libev-4.15-1.el6.rf.x86_64.rpm warning: libev-4.15-1.el6.rf.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 6b8d79e6: NOKEYPreparing...                ########################################### [100%]   1:libev                  ########################################### [100%]

Installation-01

[[email protected] percona-xtrabackup]# rpm -ivH percona-release-0.1-4.noarch.rpmPreparing packages for installation...percona-release-0.1-4

[[email protected] percona-xtrabackup]# yum list | grep percona

The installation is complete immediately.

[[email protected] percona-xtrabackup]# yum install percona-xtrabackup-24loaded plugins:fastestmirror, Securitysetting up Install processloading mirror speeds from cached hostfileresolving dependencies--> Running transact ION Check---> Package percona-xtrabackup-24.x86_64 0:2.4.11-1.el6 'll be installed--> processing Dependency:perl (dbd::mysql) for package:percona-xtrabackup-24-2.4.11-1.el6.x86_64--> Running transaction Check---> Package Perl-dbd-mysql.x86_64 0:4.013-3.el6 'll be installed--> processing Dependency:perl (Dbi::const::getinfotype) for package:perl-dbd-mysql-4.013-3.el6.x86_64--> processing Dependency:perl (DBI) for package: perl-dbd-mysql-4.013-3.el6.x86_64--> processing dependency:libmysqlclient.so.16 (libmysqlclient_16) (64bit) for Package:perl-dbd-mysql-4.013-3.el6.x86_64--> processing dependency:libmysqlclient.so.16 () (64bit) for package: perl-dbd-mysql-4.013-3.el6.x86_64--> Running Transaction Check---> Package percona-serveR-shared-51.x86_64 0:5.1.73-rel14.12.625.rhel6 'll be installed---> Package perl-dbi.x86_64 0:1.609-4.el6 'll be Installed--> finished Dependency resolutiondependencies resolved=============================================== =============================================================================================================== ================================= Package Arch Versio N Repository size===================== =============================================================================================================== ===========================================================installing:percona-xtrabackup-24 x                          86_64 2.4.11-1.el6 percona-release-x86_64 8.1 Minstalling for Dependencies:percona-server-shared-51 x86_64 5.1.73-rel14.12.625.rhel6                          Percona-release-x86_64 2.1 M Perl-dbd-mysql x86_64  4.013-3.el6 centos6.7_64 134 k                                           PERL-DBI x86_64 1.609-4.el6 centos6.7_64 705 Ktransaction summary=================================== =============================================================================================================== =============================================install 4 Package (s) total download size:11 minstalled size:12 MIs Thi                                                       s OK [y/n]: ydownloading Packages: (quarter): percona-server-shared-51-5.1.73-rel14.12.625.rhel6.x86_64.rpm                                             |                                                                                                                    2.1 MB 00:02 (2/4): percona-xtrabackup-24-2.4.11-1.el6.x86_64.rpm |                                                                                                                            8.1 MB 00:01 (3/4): perl-dbd-mysql-4.013-3.el6.x86_64.rpm |                                                                                                                                  134 KB 00:00 (4/4): perl-dbi-1.609-4.el6.x86_64.rpm | 705 KB 00:00----------------------------------------------------------------------------------------------------                                                                                                                                                    -------------------------------------------------------------------------------------------Total 2.5 MB/s| One MB 00:04 warning:rpmts_HdrFromFdno:Header V4 dsa/sha1 Signature, key ID cd2efd2a:nokeyretrieving key from fi Le:///etc/pki/rpm-gpg/rpm-gpg-key-perconaimporting gpg KEY 0xcd2efd2a:userid:percona MySQL Development Team <[ Email protected]> Package:percona-release-0.1-4.noarch (installed) from:/etc/pki/rpm-gpg/ Rpm-gpg-key-perconais this OK [y/n]: yrunning rpm_check_debugrunning Transaction testtransaction Test succeededrunning Transactionwarning:rpmdb altered outside of yum.** Found 3 pre-existing Rpmdb problem (s), ' yum check ' output Follows:2:po Stfix-2.6.6-6.el6_5.x86_64 has missing requires of libmysqlclient.so.16 () (64bit) 2:postfix-2.6.6-6.el6_5.x86_64 have Missing requires of libmysqlclient.so.16 (LIBMYSQLCLIENT_16) (64bit) 2:postfix-2.6.6-6.el6_5.x86_64 has missing                                                                                                                                Requires of Mysql-libs installing:perl-dbi-1.609-4.el6.x86_64                 Installing:percona-server-shared-51-5.1.73-rel14.12.625.rhel6.x86_64 2/4 installing:perl-dbd-mysql-4.                                                                                                                                           013-3.el6.x86_64                                                                                                                                   3/4 installing:percona-xtrabackup-24-2.4.11-1.el6.x86_64 4/4 Verifying:p                                                                                                                                   Ercona-xtrabackup-24-2.4.11-1.el6.x86_64                                                                                                                                           Verifying:perl-dbd-mysql-4.013-3.el6.x86_64   2/4 Verifying:percona-sErver-shared-51-5.1.73-rel14.12.625.rhel6.x86_64                                                                                                                                                 3/4 verifying:perl-dbi-1.609-4.el6.x86_64 4/4 Installe                                                                                                                                                  D:percona-xtrabackup-24.x86_64 0:2.4.11-1.el6 Dependency installed:percona-server-shared-51.x86 _64 0:5.1.73-rel14.12.625.rhel6 perl-dbd-mysql.x86_64 0:4.013-3.el6 perl-dbi.x86 _64 0:1.609-4.el6 complete!

Verify that the installation is successful

[[email protected] percona-xtrabackup]# rpm -qa|grep xtrabackuppercona-xtrabackup-24-2.4.11-1.el6.x86_64[[email protected] percona-xtrabackup]#

Backup ideas

The xtrabackup provides both full and incremental backups, and the full amount is not explained, and the increment means that it can only back up the new data after the specified location. The incremental backup method described in this article uses the LSN (Log Sequence number), which can be obtained from the xtrabackup_checkpoints file of the Backup Success folder, where To_lsn is the starting point for the next incremental backup.
Combining both, we can get the backup idea.

The explanations are as follows:

1. Because of the different command parameters for full and incremental backups, it is first necessary to determine whether a full-scale backup is required.
2. If this is a full-scale backup, then execute the command, and after the backup succeeds, log the TO_LSN in the xtrabackup_checkpoints file for incremental backup later
3. In the case of an incremental backup, the TO_LSN that was recorded at the time of the last backup is read first, and if the read fails, the error is ended. Otherwise execute the corresponding command, log TO_LSN after the backup is successful.

Write a simple script: db-backup.sh, as follows:

#!/bin/sh# xtrabackup Related configuration innobackupex= "Innobackupex" my_cnf= "/home/config/mysql/3307.backup.cnf" MY_USER= " Xtrabackup "my_password=" Xtrabackup "my_socket="/home/socket/mysql/3307.sock "# Remote backup machine file name configuration remote_host=" Dbbackup " Remote_dir= "/home/backup/mysql/test" local_lsn_file= "~/.to_lsn_important" Date_name= ' DATE +%Y-%m-%d-%H-%M-%S ' remote_file= $DATE _name.tar.gzlock_file= "~/.mysql.backup.lock" local_backup_dir= "/home/backup/mysql/test/$DATE _ NAME "# Output Help information function usage () {echo" Usage: "echo"-F DB would be backuped fully with this parameter. If not, incrementally.    "} #防止同时执行两个备份命令, a conflict occurs if [-f $LOCK _file]; then echo ' Mysql backup lockfile is locked! '        Exit 0fifull=0while getopts "FH" Arg #选项后面的冒号表示该选项需要参数do case $arg in F) full=1;;    h) # Output Help information usage exit 0;; Esacdoneecho "Backup dir is $REMOTE _dir/$REMOTE _file" # Backup up db to REMOTE Hostecho ' start to backup db! ' ' Date +%y-%m-%d-%h-%m-%s ' if ["$full" x = "1" x] ; then # full backup echo ' 1 ' > $LOCK _file $INNOBACKUPEX--defaults-file= $MY _cnf--user= $MY _user--password= $MY _PASSW ORD--socket= $MY _socket./--stream=tar | gzip | SSH $REMOTE _host "cat-> $REMOTE _dir/full-$REMOTE _file" ssh $REMOTE _host "CD $REMOTE _dir;rm-f Xtrabackup_checkpoin Ts;tar zxfi $REMOTE _dir/full-$REMOTE _file xtrabackup_checkpoints "tolsn=$ (ssh $REMOTE _host" Cat $REMOTE _dir/xtrabacku P_checkpoints|grep to_lsn|awk-f= ' {gsub (//,\ "\", \$2);p rint \$2} ') if [$toLSN]; then echo $toLSN > $LOCA    L_lsn_file else Echo ' no LSN from remote host!please check! '        Fielse # Incremental backup If [-f $LOCAL _lsn_file]; then tolsn= ' Cat $LOCAL _lsn_file ' fi if [! $toLSN], then        Echo ' Last LSN was not set!please check! ' Exit 0 fi echo ' 1 ' > $LOCK _file mkdir-p $LOCAL _backup_dir echo "Last to LSN is" $toLSN $INNOBACKUPEX-- Parallel=6--defaults-file= $MY _cnf--user= $MY _user--password= $MY _password--socket= $MY _sockeT--incremental--incremental-lsn= $toLSN $LOCAL _backup_dir 2>/tmp/innobackexlog tolsn=$ (CD $LOCAL _backup_dir/*; CA    T Xtrabackup_checkpoints|grep to_lsn|awk-f= ' {gsub (//, "", $ $);p rint $) ') echo "New to LSN are" $toLSN; If [$toLSN], then echo $toLSN > $LOCAL _lsn_file cd $LOCAL _backup_dir/*;tar ZC. |    SSH $REMOTE _host "cat-> $REMOTE _dir/$REMOTE _file" echo "Save FILE to $REMOTE _host @ $REMOTE _dir/$REMOTE _file" else Echo ' no LSN from local backup file!delete remote backup file! ' $LOCAL _backup_dir/$REMOTE _file fi echo "Remove $LOCAL _backup_dir" Rm-rf $LOCAL _backup_dirfirm-f $LOCK _fileecho "End to backup db!" ' Date +%y-%m-%d-%h-%m-%s '

After configuring the above script, put it on the MySQL host, perform sh db-backup.sh-f for full backup, then add SH backup.sh to crontab hourly incremental backup once.

Note--parallel=6 use is conditional, reference documentation

Recovery ideas

Once the online database has a catastrophic outage, the backup data will be immediately functional. Open backup folder A look, such as the above figure, a compressed file per hour, a long time, the vast, hundreds of compressed files, is absolutely certain, this must be a script to automate the recovery of data. The idea is simple, see:

The explanations are as follows:

1. Unzip first, nothing to say.
2. Find the data for a full amount of backups, because incremental backups are also based on this benchmark. When you back up, the full backup file is named "Start" to make it easier to find.
3. Restore the full amount of backup data first, and then restore the incremental backup data in chronological order.
4. Restore data to MySQL
5. Start MySQL, and connect to see if there's a problem.

The simple implementation of the script, we call it restore.sh, as shown below

#!/bin/shback_dir= "/home/backup/mysql/test" restore_dir= "/home/tmpback/test" # Xtrabackup the configuration file used when restoring may be different from the database configuration file restore_my_cnf= "/home/config/mysql/3307.backup.cnf" # Start the required configuration file for MySQL, set the settings on Demand my_cnf= "/home/config/mysql/3307.cnf" my_data_dir= "/home/data/mysql/3307" my_sock_dir= "/home/socket/mysql/" MY_LOG_DIR= "/ home/logs/mysqld/3307 "#extract All tar filefullfilename=" ' for file ' in $ (ls $BACK _dir | grep ' tar.gz ') do Filename=${fi LE%.TAR.GZ} full=${file%%-*} if ["${full}" x = "full" x] and then fullfilename= $fileName fi dest_dir= "$RES    tore_dir/$fileName "If [-D $DEST _dir]; then echo" $DEST _dir exists! " else mkdir-p $DEST _dir echo "Start to extract file $file to $DEST _dir" tar zxif $BACK _dir/$file-C $ restore_dir/$fileName echo "End to extract file" $file Fidoneecho "full backup DIR is" $fullFileNameecho "**star T to repare full backup dir "# Recover all data echo" Innobackupex--apply-log--redo-only--use-memory=4g $RESTORE _dir/$fullFileName "InnobackUpex--apply-log--redo-only--use-memory=4g "$RESTORE _dir/$fullFileName" echo "**end to Repare full backup DIR" # Recover incremental Data fo R file in $ (ls $RESTORE _dir|grep-v ' full ') do echo ' **start to Repare $file ' echo ' Innobackupex--apply-log--redo -only--use-memory=4g $RESTORE _dir/$fullFileName--incremental-dir= $RESTORE _dir/$file "Innobackupex--apply-log-- Redo-only--use-memory=4g "$RESTORE _dir/$fullFileName"--incremental-dir= "$RESTORE _dir/$file" echo "**end to Repare $f Ile "Doneecho" **start to copy back data to MySQL "Rm-rf $MY _data_dirmkdir $MY _data_dirmkdir $MY _sock_dirmkdir $MY _log_dir# Restore data to MySQL echo "Innobackupex--defaults-file= $RESTORE _my_cnf--copy-back $RESTORE _dir/$fullFileName" Innobackupex --defaults-file= $RESTORE _my_cnf--copy-back $RESTORE _dir/$fullFileNameecho "**end to copy back data to MySQL" Chown-r Mys Ql:mysql $MY _data_dirchown-r mysql:mysql $MY _sock_dirchown-r mysql:mysql $MY _log_direcho "All DATA have been restored! Try to start MySQL now "echo" Mysqld_multi --defaults-file= $MY _cnf Start 1 " 

Using the method is very simple, after configuration, directly run SH restore.sh can be.

MySQL in-xtrabackup backup and recovery applications