有時(shí)候我們從一個(gè)完整的數(shù)據(jù)庫備份中不需要整庫恢復(fù),只需要恢復(fù)其中的一個(gè)或者幾個(gè)表,如果使用Percona的XtraBackup備份在恢復(fù)的時(shí)候如何只恢復(fù)部分?jǐn)?shù)據(jù)呢?

轉(zhuǎn)載請尊重原創(chuàng)、保留相關(guān)鏈接本文來自多寶平臺 ：http://www.mbodb.com

下面用例子來演示

首先為了能恢復(fù)表需要做以下操作:
1、InnoDB_FAST_SHUTDOWN = 0 ? ? --此參數(shù)MySQL在關(guān)閉的時(shí)候,需要完成所有的full purge和merge insert buffer操作.
2、InnoDB_File_Per_Table = ON ? --此參數(shù)修改InnoDB為獨(dú)立表空間模式，每個(gè)數(shù)據(jù)庫的每個(gè)表都會(huì)生成一個(gè)數(shù)據(jù)空間.

開始使用XtraBackup備份,這里用到--export參數(shù).
[mysql@localhost mysql]$ innobackupex-1.5.1 --defaults-file=/etc/my.cnf --export /mysql/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

140312 16:06:45 ?innobackupex-1.5.1: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_file=/etc/my.cnf;mysql_read_default_group=xtrabackup' (using password: NO).
140312 16:06:45 ?innobackupex-1.5.1: Connected to MySQL server
140312 16:06:45 ?innobackupex-1.5.1: Executing a version check against the server...
140312 16:06:51 ?innobackupex-1.5.1: Done.
IMPORTANT: Please check that the backup run completes successfully.
......
......
......

備份完成之后應(yīng)用日志使備份多寶保持一致性
[mysql@localhost backup]$ innobackupex-1.5.1 --defaults-file=/etc/my.cnf --apply-log --export /mysql/backup/2014-03-12_16-06-52/

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

140312 16:11:47 ?innobackupex-1.5.1: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_file=/etc/my.cnf;mysql_read_default_group=xtrabackup' (using password: NO).
140312 16:11:47 ?innobackupex-1.5.1: Connected to MySQL server
Connected successfully
140312 16:11:47 ?innobackupex-1.5.1: Connection to database server closed
IMPORTANT: Please check that the apply-log run completes successfully.
? ? ? ? ? ?At the end of a successful apply-log run innobackupex-1.5.1
? ? ? ? ? ?prints "completed OK!".
......
......
......

現(xiàn)在我們登錄MySQL刪除一些t表的數(shù)據(jù)
mysql> select * from t;
+----+
| id |
+----+
| ?1 |
| ?2 |
| ?3 |
| ?4 |
| ?5 |
| ?6 |
| ?7 |
| ?8 |
| ?9 |
| 10 |
| 11 |
+----+
11 rows in set (0.06 sec)

mysql> delete from t where id between 7 and 10;
Query OK, 4 rows affected (0.18 sec)

mysql> select * from t;
+----+
| id |
+----+
| ?1 |
| ?2 |
| ?3 |
| ?4 |
| ?5 |
| ?6 |
| 11 |
+----+
7 rows in set (0.00 sec)

這時(shí)我們Discard表
mysql> ALTER TABLE t DISCARD TABLESPACE;
Query OK, 0 rows affected (0.12 sec)

從備份目錄復(fù)制文件到數(shù)據(jù)庫目錄
[mysql@localhost test]$ cp -p -r t.cfg t.ibd ../../../data/test/

我們進(jìn)入到MySQL之后Import表
mysql> ALTER TABLE t import TABLESPACE;
Query OK, 0 rows affected (0.07 sec)

查看表t
mysql> select * from t;
+----+
| id |
+----+
| ?1 |
| ?2 |
| ?3 |
| ?4 |
| ?5 |
| ?6 |
| ?7 |
| ?8 |
| ?9 |
| 10 |
| 11 |
+----+
11 rows in set (0.00 sec)

注意:
在復(fù)制備份文件的時(shí)候一定要復(fù)制后綴cfg文件,否則在Import的時(shí)候就會(huì)報(bào)Warning.例如如下的信息:
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1810 | InnoDB: IO Read error: (2, No such file or directory) Error opening './test/t.cfg', will attempt to import without schema verification ? ?|
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------+
cfg文件的用處主要是在MySQL5.6執(zhí)行"Flush Table xxx Export;"之后使.ibd文件保持一致性,同時(shí)這個(gè)文件會(huì)生成一個(gè).cfg文件,在做Import的時(shí)候會(huì)對導(dǎo)入過程進(jìn)行校驗(yàn),但是在MySQL5.6.8版本之后也不是必須要有.cfg文件.如果真沒有,在導(dǎo)入的時(shí)候有可能就會(huì)報(bào)出上面的錯(cuò)誤,所以為了安全還是復(fù)制它.

如果表有外鍵在Discard的時(shí)候執(zhí)行如下命令:
set FOREIGN_KEY_CHECKS=0;

在Import表之后執(zhí)行以下命令恢復(fù)外鍵檢查
set FOREIGN_KEY_CHECKS=1;

參數(shù)--export的英文解釋如下:

? ? ? ? This option is passed directly to xtrabackup's --export option. It
? ? ? ? enables exporting individual tables for import into another server.

總結(jié)

以上是生活随笔為你收集整理的如何从一个XtraBackup完整备份中恢复一个InnoDB表的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。