(轉)使用Xtrabackup備份MySQL資料庫

作者：NinGoo | 【轉載須以超連結形式標明文章原始出處和作者資訊】

原文：

MySQL資料庫的備份，一直是個比較頭痛的問題。各種工具雖然不少，但一個真正好用易用的，卻又非常難找。Mysqldump做為資料的邏輯備份 工具還行，但是無法進行線上熱備，而沒有物理備份工具，在資料量比較大的時候，恢復的時間也會長得無法接受。InnoDB倒是有個商業的，可以對InnoDB引擎的表實現線上熱備。最近發現了一個工具，出品的，是InnoDB Hotbackup的一個開源替代品，可以線上對InnoDB/XtraDB引擎的表進行物理備份，試用了一下，非常的不錯，值得向MySQL DBA們推薦。

下面是一個實際備份的例子，採用了gzip將備份流進行壓縮，約20GB的資料庫，壓縮後大小為340MB，當然，壓縮後的大小跟資料庫中實際使用的空間是相關的。備份時間約6分44秒。

[@more@]

innobackupex是參考了InnoDB Hotbackup的innoback指令碼修改而來的，主要是為了方便的同時備份InnoDB和MyISAM引擎的表，並且加入了一些使用的選項。如 –slave-info可以記錄備份恢復後，作為slave需要的一些資訊，根據這些資訊，可以很方便的利用備份來重做slave。

注意，從備份後的tar包解包的時候，需要使用-i引數。最新發布的是0.7版，

MySQL DBA們，你是如何做備份的呢？歡迎到。

$innobackupex-1.5.1 --user=root --stream=tar /bak/ --slave-info | gzip > /bak/bak_mysql.tar.gz

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy.
All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackup
           prints "innobackup completed OK!".

innobackupex: Using mysql  Ver 14.12 Distrib 5.0.67, for redhat-linux-gnu (x86_64)
 using  EditLine wrapper
innobackupex: Using mysql server version 5.0.67-log

innobackupex: Created backup directory /bak
090625 15:23:00  innobackupex: Starting mysql with options: --unbuffered --user=root
090625 15:23:00  innobackupex: Connected to database with mysql child process (pid=3431)
090625 15:23:04  innobackupex: Connection to database server closed

090625 15:23:04  innobackupex: Starting ibbackup with command:
xtrabackup --backup --suspend-at-end --log-stream --target-dir=./
innobackupex: Waiting for ibbackup (pid=3565) to suspend
innobackupex: Suspend file '/opt/mysqldata/xtrabackup_suspended'

xtrabackup: suspend-at-end is enabled.
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /opt/mysqldata
xtrabackup: Target instance is assumed as followings.
xtrabackup:   innodb_data_home_dir = /opt/mysqldata
xtrabackup:   innodb_data_file_path = ibdata1:10G;ibdata2:10G
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 4
xtrabackup:   innodb_log_file_size = 104857600
xtrabackup: use O_DIRECT
xtrabackup: Stream mode.
>> log scanned up to (0 3053406941)

090625 15:23:06  innobackupex: Continuing after ibbackup has suspended

innobackupex: Starting to backup InnoDB tables and indexes
innobackupex: from original InnoDB data directory '/opt/mysqldata'
innobackupex: Backing up as tar stream 'ibdata1'
>> log scanned up to (0 3053416714)
...這裡省略若干行
>> log scanned up to (0 3054123851)
tar: ibdata1: file changed as we read it
innobackupex: Backing up as tar stream 'ibdata2'
>> log scanned up to (0 3054142116)
...這裡省略若干行
>> log scanned up to (0 3054618483)
innobackupex: Backing up file '/opt/mysqldata/test/sp.ibd'
innobackupex: Backing up file '/opt/mysqldata/test/tmp_dy.ibd'
innobackupex: Backing up files '/opt/mysqldata/testdb/*.ibd' (206 files)
>> log scanned up to (0 3054638401)
>> log scanned up to (0 3054668860)
tar: testdb/group_group_thread_0027.ibd: file changed as we read it
>> log scanned up to (0 3054695015)
>> log scanned up to (0 3054928216)
tar: testdb/group_thread_reply_0007.ibd: file changed as we read it
>> log scanned up to (0 3054952588)
>> log scanned up to (0 3055005439)
tar: testdb/group_user_0001.ibd: file changed as we read it
>> log scanned up to (0 3055028610)
>> log scanned up to (0 3055044650)
tar: testdb/group_user_0006.ibd: file changed as we read it
>> log scanned up to (0 3055060461)
innobackupex: Backing up file '/opt/mysqldata/testdb/comments.ibd'
innobackupex: Backing up file '/opt/mysqldata/testdb/testdb.ibd'
innobackupex: Backing up file '/opt/mysqldata/testdb/testdb_content.ibd'
innobackupex: Backing up file '/opt/mysqldata/testdb/testdb_ids.ibd'
090625 15:29:17  innobackupex: Starting mysql with options: --unbuffered --user=root
090625 15:29:17  innobackupex: Connected to database with mysql child process (pid=5458)
>> log scanned up to (0 3055072495)
090625 15:29:21  innobackupex: Starting to lock all tables...
>> log scanned up to (0 3055087148)
>> log scanned up to (0 3055119993)
090625 15:29:39  innobackupex: All tables locked and flushed to disk

090625 15:29:39  innobackupex: Starting to backup .frm, .MRG, .MYD, .MYI,
innobackupex: .TRG, .TRN, and .opt files in
innobackupex: subdirectories of '/opt/mysqldata'
innobackupex: Backing up file '/opt/mysqldata/test/sp.frm'
innobackupex: Backing up file '/opt/mysqldata/test/tmp_dy.frm'
innobackupex: Backing up files '/opt/mysqldata/testdb/*.{frm,MYD,MYI,MRG,TRG,TRN,opt}' (207 files)
innobackupex: Backing up file '/opt/mysqldata/testdb/comments.frm'
innobackupex: Backing up file '/opt/mysqldata/testdb/testdb.frm'
innobackupex: Backing up file '/opt/mysqldata/testdb/testdb_content.frm'
innobackupex: Backing up file '/opt/mysqldata/testdb/testdb_ids.frm'
innobackupex: Backing up file '/opt/mysqldata/testdb/db.opt'
innobackupex: Backing up files '/opt/mysqldata/mysql/*.{frm,MYD,MYI,MRG,TRG,TRN,opt}' (52 files)
090625 15:29:40  innobackupex: Finished backing up .frm, .MRG, .MYD, .MYI, .TRG, .TRN, and .opt files

innobackupex: Resuming ibbackup

xtrabackup: The latest check point (for incremental): '0:3054881736'
>> log scanned up to (0 3055120013)
xtrabackup: Transaction log of lsn (0 3053102766) to (0 3055120013) was copied.
090625 15:29:44  innobackupex: All tables unlocked
090625 15:29:44  innobackupex: Connection to database server closed

innobackupex: Backup created in directory '/bak/'
innobackupex: MySQL binlog position: filename '', position
innobackupex: MySQL slave binlog position: master host '127.0.0.1',
filename 'mysql-bin.000006', position 227207755
090625 15:29:44  innobackupex: innobackup completed OK!
innobackupex: You must use -i (--ignore-zeros) option for extraction of the tar stream.

本文網址：