Xtrabackup 流備份與恢複，xtrabackup備份

Xtrabackup 流備份與恢複，xtrabackup備份

        Xtrabackup是MySQL資料庫的備份不可多得的工具之一。提供了全備，增備，資料庫層級，表層級備份等等。最牛X的還有不落盤的備份，即流備份方式。對於伺服器上空間不足，或是搭建主從，直接使用流式備份大大簡化了備份後的壓縮複製所帶來的更多開銷。Xtrabackup支援tar格式以及xbstream格式的流備份。本文即是對此展開的相關描述。

 

1、基於tar格式備份
a、備份到本地
# innobackupex --stream=tar  /tmp >/backup/bak.tar           ###非壓縮方式
# innobackupex --stream=tar /tmp |gzip >/backup/bakz.tar.gz  ###壓縮方式
# ls -hltr
total 42M
-rw-r--r-- 1 root root  39M Apr 15 17:23 bak.tar
-rw-r--r-- 1 root root 3.3M Apr 15 17:24 bakz.tar.gz

###解壓備份
# mkdir bak bakz
# tar -xivf bak.tar -C /backup/bak
# tar -xizvf bakz.tar.gz -C /backup/bakz
# du -sh *
38M bak
39M bak.tar
38M bakz
3.3M   bakz.tar.gz

b、備份到遠程
# innobackupex --stream=tar /tmp | ssh root@192.168.1.7 \ "cat - > /backup/bak.tar"  ###非壓縮方式
# innobackupex --stream=tar /tmp | ssh root@192.168.1.7 \ "gzip >/backup/bak.tar.gz" ###壓縮方式

 

2、使用xbstream格式備份
a、備份到本地
# innobackupex --stream=xbstream /tmp >/backup/bak.xbstream                       ###非壓縮方式 
# innobackupex --stream=xbstream --compress /tmp >/backup/bak_compress.xbstream   ###壓縮方式
# ls -hltr
total 43M
-rw-r--r-- 1 root root  37M Apr 15 17:41 bak.xbstream
-rw-r--r-- 1 root root 6.0M Apr 15 17:41 bak_compress.xbstream

###解壓備份
# mkdir bk bk_compress
# xbstream -x < bak.xbstream -C /backup/bk       ###解壓xbstream格式

###解壓xbstream格式，compress參數的備份                 
# xbstream -x < bak_compress.xbstream -C /backup/bk_compress                         ###首先解壓xbstream
# for bf in `find . -iname "*\.qp"`; do qpress -d $bf $(dirname $bf) && rm $bf; done ###再解壓qp壓縮格式
# innobackupex --decompress /backup/bk_compress     ###如果xtrabackup版本大於2.1.4，可以直接通過該方式解壓

b、備份到遠程
###使用壓縮備份到遠程並解壓
# innobackupex --stream=xbstream --compress /tmp | ssh root@192.168.1.7 "xbstream -x -C /backup/stream"

 

3、流備份的全備與增備
###全備資料庫，使用--extra-lsndir參數生產checkpoints檔案
# innobackupex --stream=xbstream --compress --extra-lsndir=/backup/chkpoint /tmp >/backup/bak_compress.xbstream
# more /backup/chkpoint/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 8408290
last_lsn = 8408290
compact = 0

### Author : Leshami
### Blog   : http://blog.csdn.net/leshami
###增備資料庫，如果後續還需要再次增備，則可以再次指定--extra-lsndir，如果與上次備份指定相同的位置，該檔案被覆蓋
# innobackupex --compress --incremental --extra-lsndir=/backup/chkpoint --incremental-basedir=/backup/chkpoint \
>  --stream=xbstream /tmp >/backup/bak_compress_inc.xbstream

 

4、流備份的異機恢複
###備份到異機
# innobackupex --stream=xbstream --extra-lsndir=/backup/chkpoint /tmp | ssh root@192.168.1.7 "xbstream -x -C /backup/stream"
# innobackupex --incremental --extra-lsndir=/backup/chkpoint --incremental-basedir=/backup/chkpoint --stream=xbstream \
> /tmp | ssh root@192.168.1.7 "xbstream -x -C /backup/stream_inc"

###異機恢複，copy-back及後續步驟省略
# innobackupex --apply-log --redo-only /backup/stream
# innobackupex --apply-log /backup/stream --incremental-dir=/backup/stream_inc

 

5、其它注意事項
a、如果使用xbstream格式異機備份時，異機未安裝xbstream(封裝在xtrabackup中)則出現如下錯誤提示。
  bash: xbstream: command not found
  xtrabackup: Error writing file 'UNOPENED' (Errcode: 32 - Broken pipe)
  xb_stream_write_data() failed.
  compress: write to the destination stream failed.
  xtrabackup: Error writing file 'UNOPENED' (Errcode: 32 - Broken pipe)
  xb_stream_write_data() failed.
  xtrabackup: Error writing file 'UNOPENED' (Errcode: 32 - Broken pipe)
  [01] xtrabackup: Error: xtrabackup_copy_datafile() failed.
  [01] xtrabackup: Error: failed to copy datafile.
  innobackupex: Error: The xtrabackup child process has died at /usr/bin/innobackupex line 2681.

b、異機備份時需要建立等效性，如下樣本
  # ssh-keygen
  Generating public/private rsa key pair.
  Enter file in which to save the key (/root/.ssh/id_rsa):
  Enter passphrase (empty for no passphrase):
  Enter same passphrase again:
  Your identification has been saved in /root/.ssh/id_rsa.
  Your public key has been saved in /root/.ssh/id_rsa.pub.
  The key fingerprint is:
  29:45:ee:8d:b3:55:f1:5f:2f:da:2a:88:0c:0d:37:9f root@vdbsrv1
  
  ###copy 公開金鑰到遠程主機
  # ssh-copy-id -i ~/.ssh/id_rsa.pub root@192.168.1.7
  21
  The authenticity of host '192.168.1.7 (192.168.1.7)' can't be established.
  RSA key fingerprint is 1d:7c:40:98:ef:de:6f:b8:8c:b2:87:72:0e:79:db:0a.
  Are you sure you want to continue connecting (yes/no)? yes
  Warning: Permanently added '192.168.1.7' (RSA) to the list of known hosts.
  root@192.168.1.7's password:
  Now try logging into the machine, with "ssh 'root@192.168.1.7'", and check in:
  
    .ssh/authorized_keys
  
  to make sure we haven't added extra keys that you weren't expecting.
  
  ###驗證等效性是否成功
  # ssh 192.168.1.7 date;
  Wed Apr 15 17:55:31 CST 2015

c、使用tar格式遠程增量備份時收到如下提示，即只支援xbstream
  xtrabackup: error: streaming incremental backups are incompatible with the
  'tar' streaming format. Use --stream=xbstream instead.

d、用流備份，預設的臨時目錄都是系統的/tmp目錄，需要保證該目錄有足夠的空間，或指定--tmpdir選項

e、流備份日誌輸出
  innobackupex --stream=xbstream /tmp 2>>"$backupLog" | gzip > "$backup_file" 2>>"$backupLog"

 

6、更多參考
      基於innobakcupex跨執行個體不完全恢複步驟
      基於Innobackupex的MySQL備份指令碼
      基於Innobackupex的不完全恢複
      基於Innobackupex的完全恢複
      基於Innobackupex的增備及恢複
      基於Innobackupex的全備恢複
      Innobackupex 全備資料庫