MySQL single table ibd File restore _ MySQL

MySQL single table ibd file recovery bitsCN.com

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 testibd

Output result

Initializing table definitions...Processing table: SYS_TABLES - total fields: 10 - nullable fields: 6 - minimum header size: 5 - minimum rec size: 21 - maximum rec size: 555Processing table: SYS_INDEXES - total fields: 9 - nullable fields: 5 - minimum header size: 5 - minimum rec size: 29 - maximum rec size: 165Setting SPACE=1 in SYS_TABLE for `test`.`testibd`Check if space id 1 is already usedPage_id: 8, next page_id: 4294967295Record position: 65Checking field lengths for a row (SYS_TABLES): OFFSETS: 16 8 50 3 2 0 0 0 0 0 Db/table: infimumSpace id: 1768842857 (0x696E6669)Next record at offset: 8DRecord position: 8DChecking field lengths for a row (SYS_TABLES): OFFSETS: 16 11 17 24 32 36 40 48 52 52 Db/table: SYS_FOREIGNSpace id: 0 (0x0)Next record at offset: D5Record position: D5Checking field lengths for a row (SYS_TABLES): OFFSETS: 16 16 22 29 37 41 45 53 57 57 Db/table: SYS_FOREIGN_COLSSpace id: 0 (0x0)Next record at offset: 122Record position: 122Checking field lengths for a row (SYS_TABLES): OFFSETS: 16 12 18 25 33 37 41 49 53 53 Db/table: test/testibdSpace id: 2 (0x2)Next record at offset: 74Space id 1 is not used in any of the records in SYS_TABLESPage_id: 8, next page_id: 4294967295Record position: 65Checking field lengths for a row (SYS_TABLES): OFFSETS: 16 8 50 3 2 0 0 0 0 0 Db/table: infimumSpace id: 1768842857 (0x696E6669)Next record at offset: 8DRecord position: 8DChecking field lengths for a row (SYS_TABLES): OFFSETS: 16 11 17 24 32 36 40 48 52 52 Db/table: SYS_FOREIGNSpace id: 0 (0x0)Next record at offset: D5Record position: D5Checking field lengths for a row (SYS_TABLES): OFFSETS: 16 16 22 29 37 41 45 53 57 57 Db/table: SYS_FOREIGN_COLSSpace id: 0 (0x0)Next record at offset: 122Record position: 122Checking field lengths for a row (SYS_TABLES): OFFSETS: 16 12 18 25 33 37 41 49 53 53 Db/table: test/testibdSpace id: 2 (0x2)Updating test/testibd (table_id 17) with id 0x01000000SYS_TABLES is updated successfullyInitializing table definitions...Processing table: SYS_TABLES - total fields: 10 - nullable fields: 6 - minimum header size: 5 - minimum rec size: 21 - maximum rec size: 555Processing table: SYS_INDEXES - total fields: 9 - nullable fields: 5 - minimum header size: 5 - minimum rec size: 29 - maximum rec size: 165Setting SPACE=1 in SYS_INDEXES for TABLE_ID = 17Page_id: 11, next page_id: 4294967295Record position: 65Checking field lengths for a row (SYS_INDEXES): OFFSETS: 15 8 50 7 2 0 0 0 0 TABLE_ID: 3798561113125514496SPACE: 1768842857Next record at offset: 8CRecord position: 8CChecking field lengths for a row (SYS_INDEXES): OFFSETS: 15 8 16 22 29 35 39 43 47 TABLE_ID: 11SPACE: 0Next record at offset: CERecord position: CEChecking field lengths for a row (SYS_INDEXES): OFFSETS: 15 8 16 22 29 36 40 44 48 TABLE_ID: 11SPACE: 0Next record at offset: 111Record position: 111Checking field lengths for a row (SYS_INDEXES): OFFSETS: 15 8 16 22 29 36 40 44 48 TABLE_ID: 11SPACE: 0Next record at offset: 154Record position: 154Checking field lengths for a row (SYS_INDEXES): OFFSETS: 15 8 16 22 29 35 39 43 47 TABLE_ID: 12SPACE: 0Next record at offset: 22CRecord position: 22CChecking field lengths for a row (SYS_INDEXES): OFFSETS: 15 8 16 22 29 44 48 52 56 TABLE_ID: 17SPACE: 2Updating SPACE(0x00000001 , 0x01000000) for TABLE_ID: 17sizeof(s)=4Next record at offset: 74SYS_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/ibdata1

Output result

page 8 invalid (fails old style checksum)page 8: old style: calculated = 0xF4AD74CB; recorded = 0xEECB309Dfixing old checksum of page 8page 8 invalid (fails new style checksum)page 8: new style: calculated = 0x6F0C29B4; recorded = 0x3D02308Cfixing new checksum of page 8page 11 invalid (fails old style checksum)page 11: old style: calculated = 0x3908087C; recorded = 0xF9E8D30Cfixing old checksum of page 11page 11 invalid (fails new style checksum)page 11: new style: calculated = 0xB26CFD77; recorded = 0xDB25D39Dfixing new checksum of page 11

Step 8: Start the mysql service

Shell> service mysqld3321 start

Reference:

Http://www.chriscalender.com /? P = 28

Http://www.mysqlperformanceblog.com/2011/05/13/connecting-orphaned-ibd-files/

Http://blogs.innodb.com/wp/2012/04/innodb-transportable-tablespaces/

BitsCN.com