解析XtraBackup備份MySQL的原理和過程

　　XtraBackup對Innodb的備份之所以是熱備，無需鎖表，是基於Innodb自身的崩潰恢復機制，它首先復制所有的Innodb數據文件，這樣復制出來的文件肯定是不一致的，然後對每個文件進行崩潰恢復處理，最終達到一致。就和MySQL在啟動Innodb的時候一樣，會通過比較數據文件頭和redo log文件頭信息來檢查數據是否是一致的，如果不一致就嘗試通過前滾(把redo log中所有提交的事務寫入數據文件)和回滾(從數據文件中撤銷所有redo log中未提交的事務引起的修改)來使數據達到最終一致。

　　XtraBackup在啟動的時候會記錄一個LSN(log sequence number)，然後就把所有的Innodb數據文件復制出來，這樣復制出來的數據文件是不一致的，但是XtraBackup會在後台運行一個進程把所有對redo log file的修改記錄下來，只要有了這個數據，就能進行崩潰恢復。只所以要額外記錄下來，是因為MySQL自身的redo log file是可重用的。

　　以上的操作是由xtrabackup二進制程序(比如xtrabackup_55)完成的，如果使用innobackupex 腳本，剛才的步驟完成以後，innobackupex就會去備份MyISAM表和.frm文件，這時要保證數據的一致性就會先鎖表了，通過FLUSH TABLES WITH READ LOCK命令鎖表然後把文件復制出來，再釋放掉這個鎖。

　　在恢復數據的時候，要經過prepare(recovery)和restore兩個步驟。在prepare結束以後，Innodb的表恢復到了復制Innodb文件結束的時間點，這個時間點也就是鎖表復制MyISAM表的起點，所以最終數據是一致的。一般我們在恢復的時候執行兩次prepare，是因為第二次prepare會幫助我們生成redo log文件，從而加快MySQL數據庫啟動的速度。

　　我們再來看一下實際備份的日志來理解這個過程：

......

110701 03:29:13  innobackupex: Starting ibbackup with command: xtrabackup_55  --defaults-file="/home/MySQL/3306/my.cnf" --backup --suspend-at-end --log-stream --target-dir=./

innobackupex: Waiting

suspend

innobackupex: Suspend file '/home/MySQL/3306/data/xtrabackup_suspended'

xtrabackup: suspend

enabled.

xtrabackup: uses posix_fadvise().

xtrabackup: cd to /home/MySQL/3306/

data

xtrabackup: Target instance is assumed as

followings.

xtrabackup:   innodb_data_home_dir = /home/MySQL/3306/

data

xtrabackup:   innodb_data_file_path =

ibdata1:512M:autoextend

xtrabackup:   innodb_log_group_home_dir = /home/MySQL/3306/

redolog

xtrabackup:   innodb_log_files_in_group = 3

xtrabackup:   innodb_log_file_size

InnoDB: Using Linux native AIO

110701  3:29:13  InnoDB: Warning: allocated tablespace 268, old maximum was 0

xtrabackup: Stream mode.

)

110701 03:29:15

  innobackupex: Continuing after ibbackup has suspended

innobackupex: Starting to backup InnoDB tables and

indexes

innobackupex: from original InnoDB data directory '/home/MySQL/3306/data'

innobackupex: Backing up

)

>> log scanned up to (2371742105

)

>> log scanned up to (2371742105

)

innobackupex: Backing up file '/home/MySQL/3306/data/test/t.ibd'

)

innobackupex: Backing up files '/home/MySQL/3306/data/banping/*.ibd' (16

files)

......

110701 03:29:35  innobackupex: Connected to database with MySQL child process (pid=22630

)

>> log scanned up to (2371742526

)

110701 03:29:39  innobackupex: Starting to lock all

tables...

>> log scanned up to (2371742526

)

>> log scanned up to (2371742526

)

110701 03:29:51  innobackupex: All tables locked and flushed to disk

.frm, .MRG, .MYD, .MYI,

innobackupex: .TRG, .TRN, .ARM, .ARZ, .CSM, .CSV and .opt files in

innobackupex: subdirectorIEs

innobackupex: Backing up files

files)

innobackupex: Backing up file '/home/MySQL/3306/data/test/t.frm'

......

.opt files

innobackupex: Resuming ibbackup

xtrabackup: The latest check point (for incremental): '2371742526'

)

xtrabackup: Transaction log of lsn (2371741708) to (2371742526

) was copIEd.

110701 03:29:55  innobackupex: All

tables unlocked

110701 03:29:55  innobackupex: Connection to database

server closed

innobackupex: Backup created in directory '/home/MySQL/backup/data/3306'

innobackupex: MySQL binlog position: filename

MySQL,information_schema,performance_schema

innobackupex: MySQL slave binlog position: master host '', filename ''

, position

innobackupex: You must use -i (--ignore-zeros) option for extraction of the tar stream.