Innobackupex 全備資料庫，innobackupex

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/hotbakInnoDB 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-loginnobackupex: 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 suspendinnobackupex: 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/data3606xtrabackup: open files limit requested 0, set to 1024      # Author : Leshamixtrabackup: using the following InnoDB configuration:      # Blog   : http://blog.csdn.net/leshamixtrabackup:   innodb_data_home_dir = ./xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextendxtrabackup:   innodb_log_group_home_dir = ./xtrabackup:   innodb_log_files_in_group = 2xtrabackup:   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 suspended141105 15:42:30  innobackupex: Executing FLUSH TABLES WITH READ LOCK... #緩衝寫出到資料檔案並鎖表141105 15:42:30  innobackupex: All tables locked and flushed to disk141105 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 files141105 15:42:31  innobackupex: Executing FLUSH ENGINE LOGS...    #innodb logfile寫出到磁碟141105 15:42:31  innobackupex: Waiting for log copying to finishxtrabackup: The latest check point (for incremental): '380655683' #擷取最新的lsnxtrabackup: 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 tableinnobackupex: 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 closed141105 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_infoibdata1        performance_schema  tempdb  xtrabackup_checkpoints  xtrabackup_logfile#查看備份有關的總體資訊[mysql@app ~]$ more /data/bak/hotbak/2014-11-05_15-42-02/xtrabackup_infouuid = 4d49753a-64bf-11e4-8850-8c89a5d108aename = tool_name = innobackupextool_command = --user=robin -password=xxx --port=3606 --socket=/tmp/mysql3606.sock   --defaults-file=/data/inst3606/data3606/my3606.cnf  /data/bak/hotbaktool_version = 1.5.1-xtrabackupibbackup_version = xtrabackup version 2.2.5 based on MySQL server 5.6.21 Linux (x86_64) (revision id: )server_version = 5.6.12-logstart_time = 2014-11-05 15:41:59end_time = 2014-11-05 15:42:32lock_time = 2binlog_pos = filename 'inst3606bin.000001', position 352, GTID of the last change ''innodb_from_lsn = 0innodb_to_lsn = 380655683partial = Nincremental = Nformat = filecompact = Ncompressed = Nencrypted = N#查看備份binlog有關的資訊[mysql@app ~]$ more /data/bak/hotbak/2014-11-05_15-42-02/xtrabackup_binlog_infoinst3606bin.000001      352#檢查點的相關資訊[mysql@app ~]$ more /data/bak/hotbak/2014-11-05_15-42-02/xtrabackup_checkpointsbackup_type = full-backupedfrom_lsn = 0to_lsn = 380655683last_lsn = 380655683compact = 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