Innobackupex 全備資料庫

Innobackupex 全備資料庫

對於MySQL資料庫的熱備，xtrabackup是除了MySQL enterprise backup之外的不二之選。該工具提供了基於innodb儲存引擎的熱備，支援全量，增量備份，部份備份，時間點復原以及使用xtrabackup備份來重做slave等。xtrabackup工具包包含一個innobackupex命令列工具，同時支援InnoDB引擎以及MyISAM引擎。本文主要描述的是trabackup的備份原理並給出了相關示範。

1、安裝部署Xtrabackup
  ：http://www.percona.com/software/percona-xtrabackup
  # 本次安裝的版本為2.2.5，下載後解壓為rpm檔案，直接安裝即可
 
  # tar -xvf Percona-XtraBackup-2.2.5-r5027-el5-x86_64-bundle.tar
  # rpm -Uvh percona-xtrabackup-2.2.5-5027.el5.x86_64.rpm
  # rpm -Uvh percona-xtrabackup-debuginfo-2.2.5-5027.el5.x86_64.rpm
  # rpm -Uvh percona-xtrabackup-test-2.2.5-5027.el5.x86_64.rpm
  [root@app ~]# ls /usr/bin/xtraba* /usr/bin/inno*
    /usr/bin/innobackupex  /usr/bin/innochecksum  /usr/bin/xtrabackup
   
    Xtrabackup有兩個主要的工具：xtrabackup、innobackupex
　　a、xtrabackup只能備份InnoDB和XtraDB兩種資料表，而不能備份MyISAM資料表
　　b、innobackupex是一個perl指令碼封裝了xtrabackup。支援同時備份InnoDB和MyISAM引擎的表。
    註：本文描述的xtrabackup與innobackupex可以看做是同一概念。

2、xtrabackup工作原理
    InnoDB引擎很大程度上與Oracle類似，使用redo，undo機制，因此在熱備期間需要考慮對於日誌緩衝區線上事物日誌及時寫出到檔案的問題。如果log buffer沒有及時寫出將被日誌的迴圈寫特性覆蓋。xtrabackup在啟動時會記住log sequence number（LSN），然後一頁一頁地複製InnoDB的資料。與此同時，監控log buffer中的日誌情況，一旦log buffer發生變化，即資料發生了不一致，該過程會立即被捕獲並把變化的頁面複製到xtrabckup log，直到全部innoDB資料檔案複製完成之後，停止監控log buffer及日誌複製。
    xtrabackup在恢複期間對提交的事務前滾，未提交或失敗的交易進行復原，從而保證資料的一致性。因此對於InnoDB表在備份期間不會鎖表。由於XtraBackup其內建的InnoDB庫開啟檔案的時候是rw的，所以運行XtraBackup的使用者，必須對InnoDB的資料檔案具有讀寫權限。

3、extrabackup的備份步驟

4、示範全備

#當前環境
robin@localhost[(none)]> show variables like 'version';
+---------------+------------+
| Variable_name | Value      |
+---------------+------------+
| version      | 5.6.12-log |
+---------------+------------+

robin@localhost[(none)]> show variables like '%default_storage%';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+

#全備資料庫
[mysql@app ~]$ innobackupex --user=robin -password=xxx --port=3606 --socket=/tmp/mysql3606.sock \
> --defaults-file=/data/inst3606/data3606/my3606.cnf /data/bak/hotbak

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
              ............
141105 15:41:59  innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;
                                  .............
innobackupex: Using mysql server version 5.6.12-log

innobackupex: Created backup directory /data/bak/hotbak/2014-11-05_15-42-02 #在指定備份目錄下建立一個基於時間戳記的檔案夾

141105 15:42:02  innobackupex: Starting ibbackup with command: xtrabackup  --defaults-file="/data/inst3606/data3606/my3606.cnf"
--defaults-group="mysqld" --backup --suspend-at-end --target-dir=/data/bak/hotbak/2014-11-05_15-42-02
--tmpdir=/tmp --extra-lsndir='/tmp'
innobackupex: Waiting for ibbackup (pid=27441) to suspend
innobackupex: Suspend file '/data/bak/hotbak/2014-11-05_15-42-02/xtrabackup_suspended_2'

#下面從設定檔擷取有關innodb的配置資訊
xtrabackup version 2.2.5 based on MySQL server 5.6.21 Linux (x86_64) (revision id: )
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /data/inst3606/data3606
xtrabackup: open files limit requested 0, set to 1024      # Author : Leshami
xtrabackup: using the following InnoDB configuration:      # Blog  : http://blog.csdn.net/leshami
xtrabackup:  innodb_data_home_dir = ./
xtrabackup:  innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup:  innodb_log_group_home_dir = ./
xtrabackup:  innodb_log_files_in_group = 2
xtrabackup:  innodb_log_file_size = 50331648
>> log scanned up to (380655683)  #掃描innodb日誌lsn並複製inndodb系統資料表空間
[01] Copying ./ibdata1 to /data/bak/hotbak/2014-11-05_15-42-02/ibdata1
>> log scanned up to (380655683)
>> log scanned up to (380655683)
[01]        ...done
[01] Copying ./mysql/slave_relay_log_info.ibd to /data/bak/hotbak/2014-11-05_15-42-02/mysql/slave_relay_log_info.ibd
            #類似部分省略，全部是基於innodb引擎的ibd檔案...
[01] Copying ./tempdb/tb_user.ibd to /data/bak/hotbak/2014-11-05_15-42-02/tempdb/tb_user.ibd
[01]        ...done
>> log scanned up to (380655683)
xtrabackup: Creating suspend file '/data/bak/hotbak/2014-11-05_15-42-02/xtrabackup_suspended_2' with pid '27441'

141105 15:42:30  innobackupex: Continuing after ibbackup has suspended
141105 15:42:30  innobackupex: Executing FLUSH TABLES WITH READ LOCK... #緩衝寫出到資料檔案並鎖表
141105 15:42:30  innobackupex: All tables locked and flushed to disk

141105 15:42:30  innobackupex: Starting to backup non-InnoDB tables and files #複製非innodb表及相關檔案
innobackupex: in subdirectories of '/data/inst3606/data3606'
innobackupex: Backing up files '/data/inst3606/data3606/mysql/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (74 files)
>> log scanned up to (380655683)
innobackupex: Backing up files '/data/inst3606/data3606/salary/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (99 files)
innobackupex: Backing up files '/data/inst3606/data3606/performance_schema/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (53 files)
innobackupex: Backing up file '/data/inst3606/data3606/tempdb/tb_user.frm'
innobackupex: Backing up file '/data/inst3606/data3606/tempdb/db.opt'
141105 15:42:31  innobackupex: Finished backing up non-InnoDB tables and files

141105 15:42:31  innobackupex: Executing FLUSH ENGINE LOGS...    #innodb logfile寫出到磁碟
141105 15:42:31  innobackupex: Waiting for log copying to finish

xtrabackup: The latest check point (for incremental): '380655683' #擷取最新的lsn
xtrabackup: Stopping log copying thread.
.>> log scanned up to (380655683)

xtrabackup: Creating suspend file '/data/bak/hotbak/2014-11-05_15-42-02/xtrabackup_log_copied' with pid '27441'
xtrabackup: Transaction log of lsn (380655683) to (380655683) was copied.
141105 15:42:32  innobackupex: All tables unlocked                #unlock table

innobackupex: Backup created in directory '/data/bak/hotbak/2014-11-05_15-42-02'  #列出binlog的位置
innobackupex: MySQL binlog position: filename 'inst3606bin.000001', position 352, GTID of the last change ''
141105 15:42:32  innobackupex: Connection to database server closed
141105 15:42:32  innobackupex: completed OK! 

5、備份的相關資訊

除了從輸入的日誌可以看到備份的相關資訊之外，也可以從備份位置擷取備份期間產生的相關資訊
#查看備份期間產生的檔案
[mysql@app ~]$ ls /data/bak/hotbak/2014-11-05_15-42-02/
backup-my.cnf  mysql              salary  xtrabackup_binlog_info  xtrabackup_info
ibdata1        performance_schema  tempdb  xtrabackup_checkpoints  xtrabackup_logfile

#查看備份有關的總體資訊
[mysql@app ~]$ more /data/bak/hotbak/2014-11-05_15-42-02/xtrabackup_info
uuid = 4d49753a-64bf-11e4-8850-8c89a5d108ae
name =
tool_name = innobackupex
tool_command = --user=robin -password=xxx --port=3606 --socket=/tmp/mysql3606.sock
  --defaults-file=/data/inst3606/data3606/my3606.cnf  /data/bak/hotbak
tool_version = 1.5.1-xtrabackup
ibbackup_version = xtrabackup version 2.2.5 based on MySQL server 5.6.21 Linux (x86_64) (revision id: )
server_version = 5.6.12-log
start_time = 2014-11-05 15:41:59
end_time = 2014-11-05 15:42:32
lock_time = 2
binlog_pos = filename 'inst3606bin.000001', position 352, GTID of the last change ''
innodb_from_lsn = 0
innodb_to_lsn = 380655683
partial = N
incremental = N
format = file
compact = N
compressed = N
encrypted = N

#查看備份binlog有關的資訊
[mysql@app ~]$ more /data/bak/hotbak/2014-11-05_15-42-02/xtrabackup_binlog_info
inst3606bin.000001      352

#檢查點的相關資訊
[mysql@app ~]$ more /data/bak/hotbak/2014-11-05_15-42-02/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 380655683
last_lsn = 380655683
compact = 0 

6、innobackupexe備份產生的相關檔案
  backup-my.cnf
  MySQL files +
  xtrabackup_binlog_info
  xtrabackup_binlog_pos_innodb
  xtrabackup_slave_info（When –slave-info is used）
  xtrabackup_checkpoints
  Only after –apply-log
  xtrabackup_logfile
  xtrabackup_binary

本文永久更新連結地址：