Mysql中-Xtrabackup備份和恢複應用

標籤：##   soc   info   import   memory   參考   host   develop   tput   

關於Xtrabackup(又或innobackupex)的介紹，詳細參考官方文檔

Xtrabackup安裝指南

檔案準備

[[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

開始安裝依賴檔案

[[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%]

安裝-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

馬上就安裝完成

[[email protected] percona-xtrabackup]# yum install percona-xtrabackup-24Loaded plugins: fastestmirror, securitySetting up Install ProcessLoading mirror speeds from cached hostfileResolving Dependencies--> Running transaction check---> Package percona-xtrabackup-24.x86_64 0:2.4.11-1.el6 will 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 will 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 will be installed---> Package perl-DBI.x86_64 0:1.609-4.el6 will be installed--> Finished Dependency ResolutionDependencies Resolved=============================================================================================================================================================================================== Package                                           Arch                            Version                                               Repository                                       Size===============================================================================================================================================================================================Installing: percona-xtrabackup-24                             x86_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 this ok [y/N]: yDownloading Packages:(1/4): 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 |  11 MB     00:04     warning: rpmts_HdrFromFdno: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEYRetrieving key from file:///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:postfix-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 has 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                                                                                                                                                 1/4   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  : percona-xtrabackup-24-2.4.11-1.el6.x86_64                                                                                                                                   1/4   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 Installed:  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!

檢驗是否安裝成功

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

備份思路

Xtrabackup提供了全量備份和增量備份兩種方式，全量就不解釋了，增量是指其可以只備份指定位置後的新增資料。本文介紹的增量備份方法使用到了LSN(Log Sequence Number)，可以從備份成功檔案夾的xtrabackup_checkpoints檔案中擷取，其中to_lsn就是下次增量備份的起始位置。
結合兩者，我們可以得到的備份思路。

解釋如下：

1. 由於全量備份和增量備份的命令參數不同，所以首先要判斷是否需要全量備份。
2. 如果是全量備份，那麼便執行命令，備份成功後，記錄xtrabackup_checkpoints檔案中的to_lsn，以便後面進行增量備份
3. 如果是增量備份，那麼首先讀取上次備份時記錄下的to_lsn，如果讀取失敗，報錯並結束。否則執行相應命令，備份成功後記錄to_lsn。

編寫一個簡單的指令碼：db-backup.sh，如下所示：

#!/bin/sh# xtrabackup的相關配置INNOBACKUPEX="innobackupex "MY_CNF="/home/config/mysql/3307.backup.cnf"MY_USER="xtrabackup"MY_PASSWORD="xtrabackup"MY_SOCKET="/home/socket/mysql/3307.sock"# 遠程備份機 檔案名稱配置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"# 輸出協助資訊function usage(){    echo "Usage:"    echo "-f db will be backuped fully with this parameter. If not , incrementally. "}#防止同時執行兩個備份命令，發生衝突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)  # 輸出協助資訊            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    # 全量備份    echo ‘1‘ > $LOCK_FILE    $INNOBACKUPEX --defaults-file=$MY_CNF --user=$MY_USER --password=$MY_PASSWORD --socket=$MY_SOCKET ./ --stream=tar | gzip | ssh $REMOTE_HOST "cat - > $REMOTE_DIR/FULL-$REMOTE_FILE"    ssh $REMOTE_HOST "cd $REMOTE_DIR;rm -f xtrabackup_checkpoints;tar zxfi $REMOTE_DIR/FULL-$REMOTE_FILE xtrabackup_checkpoints "    toLSN=$( ssh $REMOTE_HOST "cat $REMOTE_DIR/xtrabackup_checkpoints|grep to_lsn|awk -F= ‘{gsub(/ /,\"\",\$2);print \$2}‘" )    if [ $toLSN ] ;then        echo $toLSN > $LOCAL_LSN_FILE    else        echo ‘no lsn from remote host!please check!‘    fielse    # 增量備份    if [ -f $LOCAL_LSN_FILE ] ;then        toLSN=`cat $LOCAL_LSN_FILE`    fi    if [ ! $toLSN ] ;then        echo ‘last LSN is 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/*; cat xtrabackup_checkpoints|grep to_lsn|awk -F= ‘{gsub(/ /,"",$2);print $2}‘ )    echo "new to lsn is "$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`

將上述指令碼配置好後，放到mysql主機上，執行 sh db-backup.sh -f進行全量備份，之後將sh backup.sh添加到crontab中每小時增量備份一次就可以了。

注意--parallel=6使用是有條件的，參考文檔

恢複思路

一旦線上資料庫發生宕機之類的災難性事故，備份資料就要立馬發揮作用了。開啟備份檔案夾一看，如上面的圖，每小時一個壓縮檔，時間久了，茫茫多，上百個壓縮檔，簡直是一定的，這必須得來個指令碼自動化恢複資料才成。思路很簡單，見：

解釋如下：

1. 先解壓，沒啥說的。
2. 找到全量備份的資料，因為增量備份也是以此為基準進行的。上面備份的時候，全量備份檔案命名以FULL開頭便是為了此處便於尋找。
3. 先恢複全量備份資料，然後按照時間順序恢複增量備份的資料。
4. 將資料恢複到mysql中
5. 啟動mysql，連上看看有沒問題。

簡單的實現指令碼，我們叫它restore.sh，如下所示

#!/bin/shBACK_DIR="/home/backup/mysql/test"RESTORE_DIR="/home/tmpback/test"# xtrabackup恢複時使用的設定檔可能與資料庫設定檔不同RESTORE_MY_CNF="/home/config/mysql/3307.backup.cnf"# 啟動mysql需要的設定檔，按需設定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=${file%.tar.gz}    full=${file%%-*}    if [ "${full}"x = "FULL"x ] ;then        fullFileName=$fileName    fi    DEST_DIR="$RESTORE_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 "**start to repare full backup dir"# 恢複全量資料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"# 恢複增量資料for 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 $file"doneecho "**start to copy back data to mysql"rm -rf $MY_DATA_DIRmkdir $MY_DATA_DIRmkdir $MY_SOCK_DIRmkdir $MY_LOG_DIR#將資料恢複到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 mysql:mysql $MY_DATA_DIRchown -R mysql:mysql $MY_SOCK_DIRchown -R mysql:mysql $MY_LOG_DIRecho "All data has been restored! Try to start mysql now"echo "mysqld_multi --defaults-file=$MY_CNF start 1"

使用方法很簡單，配置好後，直接運行sh restore.sh就可以了。

Mysql中-Xtrabackup備份和恢複應用