MySQL单表ibd文件恢复方法详解

前言：
随着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