Detailed description of methods for recovering ibd files from a single MySQL table

Preface:
With the popularization of innodb, innobackup has become a mainstream backup method. Physical backup can easily meet all database recovery requirements when creating a new slave.
However, when a single table is accidentally deleted or a single table is accidentally dropped, what if physical full backup is used for recovery?
The detailed analysis is as follows.
Percona data recover tool: https://launchpad.net/percona-innodb-recovery-tool

Scenario 1: accidentally delete some data and overwrite it with the last backup
Ibd recovery coverage from the same machine, and the table has not been recreate after backup.
This is the simplest case. The space id and index id in the ibd file (hereinafter referred to as the old ibd) during Backup are consistent with the space id and index id of the new ibd.
And it is consistent with the space id and index id in the ibdata file. Therefore, physical files can be directly overwritten for restoration.
The procedure is as follows:

Step-1: physical backup
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: Back up the current ibd file (optional)
Cp-a testibd. ibd testibd. bak
Step 2: discard the current ibd File
Mysql> alter table testibd discard tablespace
Step 3: Copy and back up ibd files
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: import an ibd File
Mysql> alter table testibd import tablespace

Case 2: accidentally delete the table. The table structure has been dropped.
This situation is a little complicated, but the recovery process is relatively easy to operate. Because the space id after the table is dropped will be left blank, the space id of the backup file will not be occupied.
We only need to re-create the table structure, and then restore the space id of the table in ibdata. The physical files can be overwritten and restored.

Step 1: recreate the table
Mysql> create table testibd (UserID int );
Step 2: Disable the mysql Service (required)
Shell> service mysqld3321 stop
Step 3: Prepare the ibd file apply log
Shell> innobackupex -- apply-log -- defaults-file =/usr/local/mysql3321/my. cnf/xfs/backup/2012-10-17_11-29-20/
Step 4: Back up the current ibd file (optional)
Cp-a testibd. ibd testibd. bak
Step 5: Copy and back up ibd files
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: Use percona recovery tool to modify ibdata
Shell & gt;/root/install/percona-data-recovery-tool-for-innodb-0.5/ibdconnect-o/xfs/mysql3321/ibdata1-f/xfs/mysql3321/test/testibd. ibd-d test-t testibdCopy codeThe Code is as follows: 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 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 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 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 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 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: Use percona recovery tool to re-checksum ibdata
Repeat the following command until the program has no output.
Shell>/root/install/percona-data-recovery-tool-for-innodb-0.5/innochecksum-f/xfs/mysql3321/ibdata1Copy codeThe Code is as follows: 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: Start the mysql Service
Shell> service mysqld3321 start