默認(rèn)情況下谬泌，InnoDB表不能通過直接復(fù)制表文件的方式在mysql服務(wù)器之間進(jìn)行移植，即便使用了innodb_file_per_table選項(xiàng)逻谦。而使用Xtrabackup工具可以實(shí)現(xiàn)此種功能掌实，不過，此時(shí)需要“導(dǎo)出”表的mysql服務(wù)器啟用了innodb_file_per_table選項(xiàng)（嚴(yán)格來說跨跨，是要“導(dǎo)出”的表在其創(chuàng)建之前潮峦，mysql服務(wù)器就啟用了innodb_file_per_table選項(xiàng)）囱皿，并且“導(dǎo)入”表的服務(wù)器同時(shí)啟用了innodb_file_per_table和innodb_expand_import選項(xiàng)。

(1)“導(dǎo)出”表
導(dǎo)出表是在備份的prepare階段進(jìn)行的忱嘹，因此嘱腥，一旦完全備份完成，就可以在prepare過程中通過–export選項(xiàng)將某表導(dǎo)出了：

# innobackupex --apply-log --export /path/to/backup

此命令會(huì)為每個(gè)innodb表的表空間創(chuàng)建一個(gè)以.exp結(jié)尾的文件拘悦，這些以.exp結(jié)尾的文件則可以用于導(dǎo)入至其它服務(wù)器齿兔。
(2)“導(dǎo)入”表
要在mysql服務(wù)器上導(dǎo)入來自于其它服務(wù)器的某innodb表，需要先在當(dāng)前服務(wù)器上創(chuàng)建一個(gè)跟原表表結(jié)構(gòu)一致的表础米，而后才能實(shí)現(xiàn)將表導(dǎo)入：

mysql> CREATE TABLE mytable (...) ENGINE=InnoDB;

然后將此表的表空間刪除：

mysql> ALTER TABLE mydatabase.mytable DISCARD TABLESPACE;

接下來分苇，將來自于“導(dǎo)出”表的服務(wù)器的mytable表的mytable.ibd和mytable.exp文件復(fù)制到當(dāng)前服務(wù)器的數(shù)據(jù)目錄，然后使用如下命令將其“導(dǎo)入”：
mysql> ALTER TABLE mydatabase.mytable IMPORT TABLESPACE;
7屁桑、使用Xtrabackup對(duì)數(shù)據(jù)庫進(jìn)行部分備份

Xtrabackup也可以實(shí)現(xiàn)部分備份医寿，即只備份某個(gè)或某些指定的數(shù)據(jù)庫或某數(shù)據(jù)庫中的某個(gè)或某些表。但要使用此功能蘑斧，必須啟用innodb_file_per_table選項(xiàng)靖秩，即每張表保存為一個(gè)獨(dú)立的文件。同時(shí)竖瘾，其也不支持–stream選項(xiàng)，即不支持將數(shù)據(jù)通過管道傳輸給其它程序進(jìn)行處理捕传。

此外惠拭，還原部分備份跟還原全部數(shù)據(jù)的備份也有所不同，即你不能通過簡單地將prepared的部分備份使用–copy-back選項(xiàng)直接復(fù)制回?cái)?shù)據(jù)目錄庸论，而是要通過導(dǎo)入表的方向來實(shí)現(xiàn)還原职辅。當(dāng)然，有些情況下葡公，部分備份也可以直接通過–copy-back進(jìn)行還原罐农，但這種方式還原而來的數(shù)據(jù)多數(shù)會(huì)產(chǎn)生數(shù)據(jù)不一致的問題，因此催什，無論如何不推薦使用這種方式。

(1)創(chuàng)建部分備份

創(chuàng)建部分備份的方式有三種：正則表達(dá)式(–include), 枚舉表文件(–tables-file)和列出要備份的數(shù)據(jù)庫(–databases)宰睡。

(a)使用–include
使用–include時(shí)蒲凶，要求為其指定要備份的表的完整名稱，即形如databasename.tablename拆内，如：
# innobackupex --include='^feiyu[.]tb1' /path/to/backup

(b)使用–tables-file
此選項(xiàng)的參數(shù)需要是一個(gè)文件名旋圆，此文件中每行包含一個(gè)要備份的表的完整名稱；如：
`# echo -e 'feiyu.tb1\nmageedu.tb2' > /tmp/tables.txt

innobackupex --tables-file=/tmp/tables.txt /path/to/backup

`

(c)使用–databases
此選項(xiàng)接受的參數(shù)為數(shù)據(jù)名麸恍，如果要指定多個(gè)數(shù)據(jù)庫灵巧，彼此間需要以空格隔開搀矫；同時(shí)，在指定某數(shù)據(jù)庫時(shí)刻肄，也可以只指定其中的某張表瓤球。此外，此選項(xiàng)也可以接受一個(gè)文件為參數(shù)敏弃，文件中每一行為一個(gè)要備份的對(duì)象卦羡。如：

# innobackupex --databases="feiyu testdb" /path/to/backup

(2)整理(preparing)部分備份
prepare部分備份的過程類似于導(dǎo)出表的過程，要使用–export選項(xiàng)進(jìn)行：
# innobackupex --apply-log --export /pat/to/partial/backup

此命令執(zhí)行過程中麦到，innobackupex會(huì)調(diào)用xtrabackup命令從數(shù)據(jù)字典中移除缺失的表绿饵，因此，會(huì)顯示出許多關(guān)于“表不存在”類的警告信息瓶颠。同時(shí)拟赊，也會(huì)顯示出為備份文件中存在的表創(chuàng)建.exp文件的相關(guān)信息。

(3)還原部分備份
還原部分備份的過程跟導(dǎo)入表的過程相同粹淋。當(dāng)然吸祟，也可以通過直接復(fù)制prepared狀態(tài)的備份直接至數(shù)據(jù)目錄中實(shí)現(xiàn)還原，不要此時(shí)要求數(shù)據(jù)目錄處于一致狀態(tài)廓啊。

下面實(shí)際演示其完整的備份流程：

#innobackupex --user=root /tmp/full-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.
欢搜。。谴轮。炒瘟。。第步。疮装。。粘都。廓推。

xtrabackup: The latest check point (for incremental): '2987626'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (2987626)

xtrabackup: Creating suspend file '/tmp/full-backup/2015-06-25_05-58-26/xtrabackup_log_copied' with pid '7858'
xtrabackup: Transaction log of lsn (2987626) to (2987626) was copied.
150625 05:58:30  innobackupex: All tables unlocked

innobackupex: Backup created in directory '/tmp/full-backup/2015-06-25_05-58-26'
innobackupex: MySQL binlog position: filename 'mysql-bin.000001', position 2383
150625 05:58:30  innobackupex: Connection to database server closed
150625 05:58:30  innobackupex: completed OK!

mysql> insert into  tutors(tname) values('stu00011');#在數(shù)據(jù)庫中插入數(shù)據(jù)
Query OK, 1 row affected (0.03 sec)

mysql> insert into  tutors(tname) values('stu00012');
Query OK, 1 row affected (0.00 sec)

innobackupex --incremental /tmp/full-backup/ --incremental-basedir=/tmp/full-backup/2015-06-25_05-58-26/ #做增量備份

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013\.  All Rights Reserved.
。翩隧。樊展。。堆生。专缠。。淑仆。涝婉。。蔗怠。
xtrabackup: Creating suspend file '/tmp/full-backup/2015-06-25_06-00-48/xtrabackup_log_copied' with pid '8663'
xtrabackup: Transaction log of lsn (2988209) to (2988209) was copied.
150625 06:00:53  innobackupex: All tables unlocked

innobackupex: Backup created in directory '/tmp/full-backup/2015-06-25_06-00-48'
innobackupex: MySQL binlog position: filename 'mysql-bin.000001', position 2924
150625 06:00:53  innobackupex: Connection to database server closed
150625 06:00:53  innobackupex: completed OK!

mysql> insert into  tutors(tname) values('stu00014');  #再次插入數(shù)據(jù)
Query OK, 1 row affected (0.02 sec)

mysql> insert into  tutors(tname) values('stu00015');
Query OK, 1 row affected (0.00 sec)

innobackupex --incremental /tmp/full-backup/ --incremental-basedir=/tmp/full-backup/2015-06-25_06-00-48  #再次做增量備份

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013\.  All Rights Reserved
墩弯。吩跋。。渔工。锌钮。。涨缚。轧粟。。
xtrabackup: Creating suspend file '/tmp/full-backup/2015-06-25_06-02-41/xtrabackup_log_copied' with pid '9259'
xtrabackup: Transaction log of lsn (2988781) to (2988781) was copied.
150625 06:02:45  innobackupex: All tables unlocked

innobackupex: Backup created in directory '/tmp/full-backup/2015-06-25_06-02-41'
innobackupex: MySQL binlog position: filename 'mysql-bin.000001', position 3465
150625 06:02:46  innobackupex: Connection to database server closed
150625 06:02:46  innobackupex: completed OK!

cat  xtrabackup_checkpoints  #查看日志序列號(hào)是否一致
backup_type = log-applied
from_lsn = 0
to_lsn = 2987626
last_lsn = 2987626
compact = 0
cd ../2015-06-25_06-00-48/
cat  xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 2987626
to_lsn = 2988209
last_lsn = 2988209
compact = 0
cd ../2015-06-25_06-02-41/
cat  xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 2988209
to_lsn = 2988781
last_lsn = 2988781
compact = 0

innobackupex --apply-log --redo-only /tmp/full-backu2015-06-25_05-58-26/  #做準(zhǔn)備
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013\.  All Rights Reserved.

[notice (again)]
  If you use binary log and don't use any hack of group commit,
  the binary log position seems to be:
InnoDB: Last MySQL binlog file position 0 2241, file name ./mysql-bin.000001

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 2987626
150625 06:04:03  innobackupex: completed OK!

innobackupex --apply-log --redo-only /tmp/full-backu2015-06-25_05-58-26/ --incremental-dir=/tmp/full-backup/2015-06-25_06-00-48/ #合并第一次增量備份文件

脓魏。兰吟。。茂翔。混蔼。。珊燎。惭嚣。。悔政。
innobackupex: Copying '/tmp/full-backup/2015-06-25_06-00-48/management/admin.frm' to '/tmp/full-backup/2015-06-25_05-58-26/management/admin.frm'
150625 06:05:28  innobackupex: completed OK!

innobackupex --apply-log --redo-only /tmp/full-backu2015-06-25_05-58-26/ --incremental-dir=/tmp/full-backup/2015-06-25_06-02-41/  #合并第二次增量備份文件

晚吞。。谋国。槽地。。芦瘾。捌蚊。。近弟。缅糟。。祷愉。窗宦。。二鳄。迫摔。
innobackupex: Copying '/tmp/full-backup/2015-06-25_06-02-41/management/classinfo.frm' to '/tmp/full-backup/2015-06-25_05-58-26/management/classinfo.frm'
innobackupex: Copying '/tmp/full-backup/2015-06-25_06-02-41/management/admin.frm' to '/tmp/full-backup/2015-06-25_05-58-26/management/admin.frm'
150625 06:07:10  innobackupex: completed OK!

rm -rf  /mydata/data1/*  #刪除數(shù)據(jù)文件目錄
innobackupex --copy-back  /tmp/full-backup/2015-06-25_05-58-26/  #恢復(fù)

。泥从。。沪摄。躯嫉。纱烘。。祈餐。擂啥。。帆阳。哺壶。。蜒谤。山宾。。
innobackupex: Starting to copy InnoDB log files
innobackupex: in '/tmp/full-backup/2015-06-25_05-58-26'
innobackupex: back to original InnoDB log directory '/mydata/data1'
innobackupex: Finished copying back files.

150625 06:12:29  innobackupex: completed OK!

chown -R mysql.mysql ./*  #修改屬主和屬組