前言：

随着innodb的普及，innobackup也成为了主流备份方式。物理备份对于新建slave，全库恢复的需求都能从容应对。

但当面临单表数据误删，或者单表误drop的情况，如果使用物理全备进行恢复呢？

下文将进行详细分析。

恢复过程中需要用到的工具，percona data recover tool ： https://launchpad.net/percona-innodb-recovery-tool

情况一：误删部分数据，需要用最近一次备份覆盖

来自同一台机器的ibd恢复覆盖，且备份后table没有被recreate过。

这种情况是最简单的，备份时的ibd文件（后称老ibd）中的space id和index id 与 新ibd的space id 和index id一致。

且和ibdata文件中的space id和index id一致。因此，物理文件可以直接覆盖做恢复。

以下是详细步骤

Step -1 : 物理备份

innobackupex --defaults-file=/usr/local/mysql3321/my.cnf --socket=/xfs/mysql3321/mysql.sock --user=root --password=password /xfs/backup/

Step 0 : apply log

innobackupex --apply-log --defaults-file=/usr/local/mysql3321/my.cnf /xfs/backup/2012-10-17_11-29-20/

Step 1 : 备份现在的ibd文件（可选）

cp -a testibd.ibd testibd.bak

Step 2 : 舍弃现在ibd文件

mysql> alter table testibd discard tablespace

Step 3 : 复制备份ibd文件

shell> cp /xfs/backup/2012-10-17_11-29-20/test/testibd.ibd /xfs/mysql3321/test/

shell> chown mysql:mysql /xfs/mysql3321/test/testibd.ibd

Step 4 : 导入ibd文件

mysql> alter table testibd import tablespace

情况二：误删 table，表结构已经被drop了

这种情况稍复杂，不过恢复过程还是比较容易操作的。由于table被drop后的space id会留空因此备份文件的space id不会被占用。

我们只需要重建表结构，然后把ibdata中该表的space id还原，物理文件可以直接覆盖做恢复了。

Step 1 : 重建表

mysql> create table testibd (UserID int);

Step 2 : 关闭mysql服务（必须）

shell> service mysqld3321 stop

Step 3: 准备ibd文件 apply log

shell> innobackupex --apply-log --defaults-file=/usr/local/mysql3321/my.cnf /xfs/backup/2012-10-17_11-29-20/

Step 4 : 备份现在的ibd文件（可选）

cp -a testibd.ibd testibd.bak

Step 5 : 复制备份ibd文件

shell> cp -a /xfs/backup/2012-10-17_11-29-20/test/testibd.ibd /xfs/mysql3321/test/

shell> chown mysql:mysql /xfs/mysql3321/test/testibd.ibd

Step 6 : 使用percona recovery tool 修改ibdata

shell> /root/install/percona-data-recovery-tool-for-innodb-0.5/ibdconnect -o /xfs/mysql3321/ibdata1 -f /xfs/mysql3321/test/testibd.ibd -d test -t testibd

复制代码 代码如下:

Initializing table definitions...

Processing table: SYS_TABLES

- total fields: 10

- nullable fields: 6

- minimum header size: 5

- minimum rec size: 21

- maximum rec size: 555

Processing table: SYS_INDEXES

- total fields: 9

- nullable fields: 5

- minimum header size: 5

- minimum rec size: 29

- maximum rec size: 165

Setting SPACE=1 in SYS_TABLE for `test`.`testibd`

Check if space id 1 is already used

Page_id: 8, next page_id: 4294967295

Record position: 65

Checking field lengths for a row (SYS_TABLES): OFFSETS: 16 8 50 3 2 0 0 0 0 0

Db/table: infimum

Space id: 1768842857 (0x696E6669)

Next record at offset: 8D

Record position: 8D

Checking field lengths for a row (SYS_TABLES): OFFSETS: 16 11 17 24 32 36 40 48 52 52

Db/table: SYS_FOREIGN

Space id: 0 (0x0)

Next record at offset: D5

Record position: D5

Checking field lengths for a row (SYS_TABLES): OFFSETS: 16 16 22 29 37 41 45 53 57 57

Db/table: SYS_FOREIGN_COLS

Space id: 0 (0x0)

Next record at offset: 122

Record position: 122

Checking field lengths for a row (SYS_TABLES): OFFSETS: 16 12 18 25 33 37 41 49 53 53

Db/table: test/testibd

Space id: 2 (0x2)

Next record at offset: 74

Space id 1 is not used in any of the records in SYS_TABLES

Page_id: 8, next page_id: 4294967295

Record position: 65

Checking field lengths for a row (SYS_TABLES): OFFSETS: 16 8 50 3 2 0 0 0 0 0

Db/table: infimum

Space id: 1768842857 (0x696E6669)

Next record at offset: 8D

Record position: 8D

Checking field lengths for a row (SYS_TABLES): OFFSETS: 16 11 17 24 32 36 40 48 52 52

Db/table: SYS_FOREIGN

Space id: 0 (0x0)

Next record at offset: D5

Record position: D5

Checking field lengths for a row (SYS_TABLES): OFFSETS: 16 16 22 29 37 41 45 53 57 57

Db/table: SYS_FOREIGN_COLS

Space id: 0 (0x0)

Next record at offset: 122

Record position: 122

Checking field lengths for a row (SYS_TABLES): OFFSETS: 16 12 18 25 33 37 41 49 53 53

Db/table: test/testibd

Space id: 2 (0x2)

Updating test/testibd (table_id 17) with id 0x01000000

SYS_TABLES is updated successfully

Initializing table definitions...

Processing table: SYS_TABLES

- total fields: 10

- nullable fields: 6

- minimum header size: 5

- minimum rec size: 21

- maximum rec size: 555

Processing table: SYS_INDEXES

- total fields: 9

- nullable fields: 5

- minimum header size: 5

- minimum rec size: 29

- maximum rec size: 165

Setting SPACE=1 in SYS_INDEXES for TABLE_ID = 17

Page_id: 11, next page_id: 4294967295

Record position: 65

Checking field lengths for a row (SYS_INDEXES): OFFSETS: 15 8 50 7 2 0 0 0 0

TABLE_ID: 3798561113125514496

SPACE: 1768842857

Next record at offset: 8C

Record position: 8C

Checking field lengths for a row (SYS_INDEXES): OFFSETS: 15 8 16 22 29 35 39 43 47

TABLE_ID: 11

SPACE: 0

Next record at offset: CE

Record position: CE

Checking field lengths for a row (SYS_INDEXES): OFFSETS: 15 8 16 22 29 36 40 44 48

TABLE_ID: 11

SPACE: 0

Next record at offset: 111

Record position: 111

Checking field lengths for a row (SYS_INDEXES): OFFSETS: 15 8 16 22 29 36 40 44 48

TABLE_ID: 11

SPACE: 0

Next record at offset: 154

Record position: 154

Checking field lengths for a row (SYS_INDEXES): OFFSETS: 15 8 16 22 29 35 39 43 47

TABLE_ID: 12

SPACE: 0

Next record at offset: 22C

Record position: 22C

Checking field lengths for a row (SYS_INDEXES): OFFSETS: 15 8 16 22 29 44 48 52 56

TABLE_ID: 17

SPACE: 2

Updating SPACE(0x00000001 , 0x01000000) for TABLE_ID: 17

sizeof(s)=4

Next record at offset: 74

SYS_INDEXES is updated successfully

Step 7 : 使用percona recovery tool 重新checksum ibdata

重复执行以下命令，直到程序没有输出为止。

shell> /root/install/percona-data-recovery-tool-for-innodb-0.5/innochecksum -f /xfs/mysql3321/ibdata1

复制代码 代码如下:

page 8 invalid (fails old style checksum)

page 8: old style: calculated = 0xF4AD74CB; recorded = 0xEECB309D

fixing old checksum of page 8

page 8 invalid (fails new style checksum)

page 8: new style: calculated = 0x6F0C29B4; recorded = 0x3D02308C

fixing new checksum of page 8

page 11 invalid (fails old style checksum)

page 11: old style: calculated = 0x3908087C; recorded = 0xF9E8D30C

fixing old checksum of page 11

page 11 invalid (fails new style checksum)

page 11: new style: calculated = 0xB26CFD77; recorded = 0xDB25D39D

fixing new checksum of page 11

Step 8 : 启动mysql服务

shell> service mysqld3321 start