一、Xtrabackup簡單概述：
Percona Xtrabackup是開源免費的mysql資料庫熱備份軟體，它能對InnoDB和XtraDB儲存引擎的資料庫非阻塞地備份（對於MyISAM的備份同樣需要加表鎖）。XtraBackup支援所有的Percona Server、MySQL、MariaDB和Drizzle。
XtraBackup優勢 ：
1、無需停止資料庫進行InnoDB熱備
2、增量備份MySQL
3、流壓縮到傳輸到其它伺服器
4、能比較容易地建立主從同步
5、備份MySQL時不會增大伺服器負載
二、Xtrabackup的安裝
首先列出以下我當前的伺服器環境
下載Xtrabackup
[root@localhost softs]# wget http://www.percona.com/downloads/XtraBackup/LATEST/RPM/rhel5/i386/percona-xtrabackup-2.1.6-702.rhel5.i386.rpm--2014-01-14 10:06:41-- http://www.percona.com/downloads/XtraBackup/LATEST/RPM/rhel5/i386/percona-xtrabackup-2.1.6-702.rhel5.i386.rpm
Resolving www.percona.com... 74.121.199.234
……………………此處省略部分內容輸出………………
100%[==========================>] 8,662,225 360K/s in 32s
2014-01-14 10:07:43 (1448 KB/s) - `percona-xtrabackup-2.1.6-702.rhel5.i386.rpm' saved [8662225/8662225]
rpm包直接安裝
[root@localhost softs]# rpm -ivh percona-xtrabackup-2.1.6-702.rhel5.i386.rpm
error: Failed dependencies:
perl(DBD::mysql) is needed by percona-xtrabackup-2.1.6-702.rhel5.i386
這裡需要解決依賴包的關係，我直接使用本地yum安裝
[root@localhost softs]# yum install perl-DBD-MySQL -y
[root@localhost softs]# rpm -ivh percona-xtrabackup-2.1.6-702.rhel5.i386.rpm
Preparing... ########################################### [100%]
1:percona-xtrabackup ########################################### [100%]
再次嘗試安裝後成功
安裝XtraBackup後，其實會有幾個工具：
innobackupex：
這個是其實是下面三個工具的一個perl指令碼封裝，可以備份MyISAM, InnoDB, XtraDB表。
xtrabackup：
一個由C編譯而來的二進位檔案，只能備份InnoDB和XtraDB資料。
xbcrypt：
用來加密或解密備份的資料。
xbstream：
用來解壓或壓縮xbstream格式的壓縮檔。
建議使用perl封裝的innobackupex來作Database Backup，因為比較容易使用。
三、innobackupex相關參數說明
--defaults-file：指定my.cnf參數檔案的位置
--apply-log：同xtrabackup的--prepare參數,一般情況下,在備份完成後，資料尚且不能用於恢複操作，因為備份的資料中可能會包含尚未提交的事務或已經提交但尚未同步至資料檔案中的事務。因此，此時資料 檔案仍處理不一致狀態。--apply-log的作用是通過復原未提交的事務及同步已經提交的事務至資料檔案使資料檔案處於一致性狀態。
--copy-back：做資料恢複時將備份資料檔案拷貝到MySQL伺服器的datadir
--remote-host=HOSTNAME： 通過ssh將備份資料存放區到進程伺服器上
--stream=[tar]：備份檔案輸出格式, 該檔案可在XtarBackup binary檔案中獲得. 在使用參數stream=tar備份的時候,你的xtrabackup_logfile可能會臨時放在/tmp目錄下,如果你備份的時候並發寫入較大的話,xtrabackup_logfile可能會很大(5G+),很可能會佔滿你的/tmp目錄,可以通過參數--tmpdir指定目錄來解決這個問題.
--tmpdir=DIRECTORY：當有指定--remote-host or --stream時, 交易記錄臨時儲存的目錄, 預設採用MySQL設定檔中所指定的臨時目錄tmpdir
--redo-only --apply-log：強製備份日誌時只redo,跳過rollback,這在做增量備份時非常必要
--use-memory=*：該參數在prepare的時候使用,控制prepare時innodb執行個體使用的記憶體
--databases=LIST：列出需要備份的databases,如果沒有指定該參數,所有包含MyISAM和InnoDB表的database都會被備份
--slave-info：備份從庫, 加上--slave-info備份目錄下會多產生一個xtrabackup_slave_info 檔案, 這裡會儲存主記錄檔以及位移, 檔案內容類別似於:CHANGE MASTER TO MASTER_LOG_FILE='', MASTER_LOG_POS=0
--socket=SOCKET：指定mysql.sock所在位置，以便備份進程登入mysql。
四、全量備份
[root@localhost tmp]# mkdir /backup
[root@localhost tmp]# innobackupex --user=root --password=123456 --defaults-file=/usr/local/mysql/etc/my.cnf --port=3306 /backup
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013. All Rights Reserved.
This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.
Get the latest version of Percona XtraBackup, documentation, and help resources:
http://www.percona.com/xb/p
140119 05:46:11 innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_file=/usr/local/mysql/etc/my.cnf;mysql_read_default_group=xtrabackup;port=3306' as 'root' (using password: YES).
140119 05:46:11 innobackupex: Connected to MySQL server
140119 05:46:12 innobackupex: Executing a version check against the server...
140119 05:46:17 innobackupex: Done.
IMPORTANT: Please check that the backup run completes successfully.
At the end of a successful backup run innobackupex
prints "completed OK!".
innobackupex: Using mysql server version 5.6.12-debug-log
innobackupex: Created backup directory /backup/2014-01-19_05-46-19
140119 05:46:19 innobackupex: Starting ibbackup with command: xtrabackup_56 --defaults-file="/usr/local/mysql/etc/my.cnf" --defaults-group="mysqld" --backup --suspend-at-end --target-dir=/backup/2014-01-19_05-46-19 --tmpdir=/tmp
innobackupex: Waiting for ibbackup (pid=2458) to suspend
innobackupex: Suspend file '/backup/2014-01-19_05-46-19/xtrabackup_suspended_2'
xtrabackup_56 version 2.1.6 for MySQL server 5.6.11 Linux (i686) (revision id: 702)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /database
xtrabackup: using the following InnoDB configuration:
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 (2000813)
InnoDB: Allocated tablespace 2, old maximum was 0
[01] Copying ./ibdata1 to /backup/2014-01-19_05-46-19/ibdata1
>> log scanned up to (2000813)
>> log scanned up to (2000813)
>> log scanned up to (2000813)
>> log scanned up to (2000813)
>> log scanned up to (2000813)
[01] ...done
[01] Copying ./mysql/innodb_index_stats.ibd to /backup/2014-01-19_05-46-19/mysql/innodb_index_stats.ibd
[01] ...done
[01] Copying ./mysql/slave_worker_info.ibd to /backup/2014-01-19_05-46-19/mysql/slave_worker_info.ibd
[01] ...done
[01] Copying ./mysql/innodb_table_stats.ibd to /backup/2014-01-19_05-46-19/mysql/innodb_table_stats.ibd
[01] ...done
[01] Copying ./mysql/slave_relay_log_info.ibd to /backup/2014-01-19_05-46-19/mysql/slave_relay_log_info.ibd
[01] ...done
[01] Copying ./mysql/slave_master_info.ibd to /backup/2014-01-19_05-46-19/mysql/slave_master_info.ibd
…………………………此處省略大量內容輸出…………………………
innobackupex: Backing up file '/database/supor/productnotes.MYI'
innobackupex: Backing up file '/database/supor/productnotes.frm'
innobackupex: Backing up file '/database/supor/orders.frm'
innobackupex: Backing up file '/database/supor/customers.frm'
innobackupex: Backing up file '/database/supor/orderitems.frm'
innobackupex: Backing up file '/database/supor/productnotes.MYD'
140119 05:46:26 innobackupex: Finished backing up non-InnoDB tables and files
140119 05:46:26 innobackupex: Waiting for log copying to finish
xtrabackup: The latest check point (for incremental): '2000813'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (2000813)
xtrabackup: Creating suspend file '/backup/2014-01-19_05-46-19/xtrabackup_log_copied' with pid '2458'
xtrabackup: Transaction log of lsn (2000813) to (2000813) was copied.
140119 05:46:27 innobackupex: All tables unlocked
innobackupex: Backup created in directory '/backup/2014-01-19_05-46-19'
140119 05:46:27 innobackupex: Connection to database server closed
140119 05:46:28 innobackupex: completed OK!
備份成功
五、全量備份恢複 現在我類比將資料庫的所有檔案誤刪除，然後進行資料恢複 [root@localhost database]# rm -rf *
[root@localhost database]# ls
[root@localhost database]#
[root@localhost database]# innobackupex --defaults-file=/usr/local/mysql/etc/my.cnf --user=root --password=123456 --apply-log /backup/2014-01-19_05-57-08/
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013. All Rights Reserved.
IMPORTANT: Please check that the apply-log run completes successfully.
At the end of a successful apply-log run innobackupex
prints "completed OK!".
140119 06:00:47 innobackupex: Starting ibbackup with command: xtrabackup_56 --defaults-file="/usr/local/mysql/etc/my.cnf" --defaults-group="mysqld" --prepare --target-dir=/backup/2014-01-19_05-57-08 --tmpdir=/tmp
xtrabackup_56 version 2.1.6 for MySQL server 5.6.11 Linux (i686) (revision id: 702)
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Compressed tables use zlib 1.2.3
InnoDB: Not using CPU crc32 instructions
……………………………………………………………………………………
[notice (again)]
If you use binary log and don't use any hack of group commit,
the binary log position seems to be:
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
I……………………………………………………………………………………
innobackupex: Creating directory '/database/mysql'
innobackupex: Copying '/backup/2014-01-19_05-57-08/mysql/help_keyword.frm' to '/database/mysql/help_keyword.frm'
innobackupex: Copying '/backup/2014-01-19_05-57-08/mysql/user.MYI' to '/database/mysql/user.MYI'
innobackupex: Copying '/backup/2014-01-19_05-57-08/mysql/help_relation.frm' to '/database/mysql/help_relation.frm'
innobackupex: Copying '/backup/2014-01-19_05-57-08/mysql/slow_log.CSV' to '/database/mysql/slow_log.CSV'
innobackupex: Copying '/backup/2014-01-19_05-57-08/mysql/time_zone_leap_second.frm' to '/database/mysql/time_zone_leap_second.frm'
innobackupex: Copying '/backup/2014-01-19_05-57-08/mysql/time_zone.MYI' to '/database/mysql/time_zone.MYI'
innobackupex: Copying '/backup/2014-01-19_05-57-08/mysql/time_zone_transition.MYI' to '/database/mysql/time_zone_transition.MYI'
innobackupex: Copying '/backup/2014-01-19_05-57-08/mysql/slave_master_info.frm' to '/database/mysql/slave_master_info.frm'
innobackupex: Copying '/backup/2014-01-19_05-57-08/mysql/help_category.MYI' to '/database/mysql/help_category.MYI'
innobackupex: Copying '/backup/2014-01-19_05-57-08/mysql/procs_priv.MYI' to '/database/mysql/procs_priv.MYI'
innobackupex: Copying '/backup/2014-01-19_05-57-08/mysql/help_category.MYD' to '/database/mysql/help_category.MYD'
innobackupex: Copying '/backup/2014-01-19_05-57-08/mysql/func.MYI' to '/database/mysql/func.MYI'
innobackupex: Copying '/backup/2014-01-19_05-57-08/mysql/tables_priv.frm' to '/database/mysql/tables_priv.frm'
innobackupex: Copying '/backup/2014-01-19_05-57-08/mysql/time_zone_transition.MYD' to '/database/mysql/time_zone_transition.MYD'
…………………………………………此處省略大量資料輸出………………………………
innobackupex: Copying '/backup/2014-01-19_05-57-08/performance_schema/performance_timers.frm' to '/database/performance_schema/performance_timers.frm'
innobackupex: Creating directory '/database/test'
innobackupex: Starting to copy InnoDB system tablespace
innobackupex: in '/backup/2014-01-19_05-57-08'
innobackupex: back to original InnoDB data directory '/database'
innobackupex: Copying '/backup/2014-01-19_05-57-08/ibdata1' to '/database/ibdata1'
innobackupex: Starting to copy InnoDB undo tablespaces
innobackupex: in '/backup/2014-01-19_05-57-08'
innobackupex: back to '/database'
innobackupex: Starting to copy InnoDB log files
innobackupex: in '/backup/2014-01-19_05-57-08'
innobackupex: back to original InnoDB log directory '/database'
innobackupex: Copying '/backup/2014-01-19_05-57-08/ib_logfile0' to '/database/ib_logfile0'
innobackupex: Copying '/backup/2014-01-19_05-57-08/ib_logfile1' to '/database/ib_logfile1'
innobackupex: Finished copying back files.
140119 06:01:31 innobackupex: completed OK! ###恢複完成
接下來檢查資料是否恢複
[root@localhost database]# ls
ibdata1 ib_logfile0 ib_logfile1 mysql performance_schema test
[root@localhost database]# chown -R mysql.mysql /database/
[root@localhost ~]# /etc/init.d/mysql5 restart
Shutting down MySQL.. [ OK ]
Starting MySQL............ [ OK ]
全量備份恢複成功
