Percona Xtrabackup小筆記

• Xtrabackup原理
  Xtrabackup是基於InnoDB的crash-recovery機制來實現的，它複製InnoDB文件導致內部不一致，然後再對文件進行crash-recovery，以致於讓資料一致性並且是可用的資料庫。
• 完整備份
  

  [root@mysql-course backup]# xtrabackup --user=root --password=pwd --host=127.0.0.1 \
  --port=3306 --parallel=4 --compress --compress-threads=4 --target-dir=/opt/backup/full \
  --backup

  看到下列訊息表示備份正常完成
  

  161109 20:48:54 Executing UNLOCK BINLOG
  161109 20:48:54 Executing UNLOCK TABLES
  161109 20:48:54 All tables unlocked
  161109 20:48:54 [00] Streaming ib_buffer_pool to 
  161109 20:48:54 [00]        ...done
  161109 20:48:54 Backup created in directory '/opt/percona57/data'
  MySQL binlog position: filename 'mysql-bin.000243', position '872', GTID of the last change '4bb4a153-8312-11e6-95a9-0800278d81c7:1-691'
  161109 20:48:54 [00] Streaming backup-my.cnf
  161109 20:48:54 [00]        ...done
  161109 20:48:54 [00] Streaming xtrabackup_info
  161109 20:48:54 [00]        ...done
  xtrabackup: Transaction log of lsn (8312487) to (8312512) was copied.
  161109 20:48:55 completed OK!

• 串流備份(Stream)
  本機：
  

  [root@mysql-course backup]# innobackupex --stream=tar /opt/percona57/data > bk.tar
  [root@mysql-course backup]# ls
  bak.tar

  
  #加gzip壓縮
  

  [root@mysql-course backup]# innobackupex --stream=tar /opt/percona57/data \
  |gzip > bk.tar.gz
  [root@mysql-course backup]# ls
  bak.tar.gz

  
  #也可用xtrabackup的--compress參數來壓縮，但就需要把stream的格式由tar改為xbstream
  

  [root@mysql-course backup]# innobackupex --compress --stream=xbstream /opt/percona57/data > bak.xbstream
  [root@mysql-course backup]# ls
  bak.xbstream

  
  遠端：
  

  [root@mysql-course backup]# innobackupex --stream=tar /opt/percona57/data \
  | ssh root@192.168.56.101 "cat - > /opt/bak.tar"

  要備份到遠端前請先設定讓Client端不需輸入密碼登入，否則會一直這個訊息：
  

  161109 20:42:54 >> log scanned up to (8312496)
  xtrabackup: Generating a list of tablespaces
  InnoDB: Allocated tablespace ID 2 for mysql/plugin, old maximum was 0
  161109 20:42:55 >> log scanned up to (8312496)
  161109 20:42:56 >> log scanned up to (8312496)
  161109 20:42:57 >> log scanned up to (8312496)
  161109 20:42:58 >> log scanned up to (8312496)

  
  解串流備份的檔案：
  如果是用tar格式的檔案，當然就是用tar來解開就可以了，但如果是用xbstream格式的，則需要透過xbstream這支程式來解開：
  

  [root@mysql-course backup]# xbstream -x < bak.xbstream -C /opt/backup/full/
  [root@mysql-course backup]# ls -alh full/
  total 1.5M
  drwxr-xr-x 8 root  root  4.0K Nov  9 21:00 .
  drwxr-xr-x 3 mysql mysql   50 Nov  9 20:58 ..
  -rw-r----- 1 root  root   418 Nov  9 20:58 backup-my.cnf.qp
  -rw-r----- 1 root  root   422 Nov  9 20:58 ib_buffer_pool.qp
  -rw-r----- 1 root  root  1.4M Nov  9 20:56 ibdata1.qp
  drwxr-x--- 2 root  root  4.0K Nov  9 20:57 mysql
  drwxr-x--- 2 root  root    70 Nov  9 20:58 mytest
  drwxr-x--- 2 root  root  8.0K Nov  9 20:57 performance_schema
  drwxr-x--- 2 root  root  8.0K Nov  9 20:58 report
  drwxr-x--- 2 root  root  8.0K Nov  9 20:57 sys
  -rw-r----- 1 root  root   154 Nov  9 20:58 xtrabackup_binlog_info.qp
  -rw-r----- 1 root  root   113 Nov  9 20:58 xtrabackup_checkpoints
  -rw-r----- 1 root  root   537 Nov  9 20:58 xtrabackup_info.qp
  -rw-r----- 1 root  root   545 Nov  9 20:58 xtrabackup_logfile.qp
  drwxr-x--- 2 root  root  4.0K Nov  9 20:58 xx_report
  

  
  
• 增量備份
  

  #第一次增量備份
  [root@mysql-course backup]# xtrabackup --host=127.0.0.1 --port=3306 --parallel=4 \
  --datadir=/opt/percona57/data --incremental-basedir=/opt/backup/full \
  --target-dir=/opt/backup/inc --backup
  #第二次增量備份
  [root@mysql-course backup]# xtrabackup --host=127.0.0.1 --port=3306 --parallel=4 \
  --datadir=/opt/percona57/data --incremental-basedir=/opt/backup/inc \
  --target-dir=/opt/backup/inc_2 --backup

• 部份備份
  

  [root@mysql-course backup]# xtrabackup --databases="mytest.t1 mysql.user" --parallel=4 \
  --target-dir=/opt/backup/part --backup
  [root@mysql-course backup]# ls part/mysql/
  user.frm  user.MYD  user.MYI
  [root@mysql-course backup]# ls part/mytest/
  t1.frm  t1.ibd

• 還原
  完整還原的順序為：--decompress(如果有壓縮的話) => --prepare => --copy-back
  增量備份還原的順序為：--decompress(完整備份或增量備份如果有壓縮的話) => --prepare --apply-log-only(完整備份及有多份增量備份) => --prepare(只在最後一份增量備份執行) => --copy-back
  

  [root@mysql-course backup]# xtrabackup --decompress --target-dir=full
  [root@mysql-course backup]# xtrabackup --prepare --apply-log-only \
  --target-dir=/opt/backup/full
  [root@mysql-course backup]# xtrabackup --prepare --apply-log-only \
  --target-dir=/opt/backup/full --incremental-dir=/opt/backup/inc
  [root@mysql-course backup]# xtrabackup --prepare --target-dir=/opt/backup/full \
  --incremental-dir=/opt/backup/inc_2
  [root@mysql-course backup]# xtrabackup --target-dir=/opt/backup/full \
  --datadir=/opt/backup/new_data --copy-back
  

  註：
  [1] 解壓縮的時候會用到qpgress這支程式，如果沒安裝的話記得先去下載，否則會報錯
  [2] 還原的時候若沒有指定--datadir這個參數，它預設會抓/etc/my.cnf下的datadir來進行還原，但他也不會直接覆蓋，而是會報錯提醒您此目錄下已有檔案，建議是還原到新的目錄下，再用搬移的方式把還原的資料夾取代原本的資料夾