Preface:
With the popularization of InnoDB, Innobackup has become the mainstream backup way. Physical backup for the new slave, the need for a full library recovery can be easily addressed.
But when the single table data mistakenly deleted, or a single table mistakenly drop, if the use of physical fully prepared to restore it?
A detailed analysis is provided below.
Tools to be used in the recovery process, Percona data recover Tool:https://launchpad.net/percona-innodb-recovery-tool
situation one: mistakenly delete part of the data, need to overwrite with the most recent backup
IBD recovery coverage from the same machine, and the table has not been recreate after the backup.
This is the simplest case where the space ID and index ID in the IBD file (later known as Old IBD) in the backup match the Space ID and index ID of the new IBD.
and is consistent with the Space ID and index ID in the Ibdata file. As a result, physical files can be directly overwritten for recovery.
here are the detailed steps
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 files (optional)
Cp-a TESTIBD.IBD Testibd.bak
Step 2: Discard current IBD files
Mysql> ALTER TABLE TESTIBD discard Tablespace
Step 3: Copy backup 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 IBD Files
mysql> ALTER TABLE TESTIBD import tablespace
situation two: mistakenly delete table, the structure has been dropped
This is a slightly more complex situation, but the recovery process is relatively easy to manipulate. The space ID of the backup file is not occupied because the space ID of the table is left blank.
We just need to rebuild the table structure, then restore the space ID of the table in Ibdata, and the physical file can be directly overwritten for recovery.
Step 1: rebuilding the table
Mysql> CREATE TABLE TESTIBD (UserID int);
Step 2: Turn off 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 files (optional)
Cp-a TESTIBD.IBD Testibd.bak
Step 5: Copy backup 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 Modify 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
Copy Code code 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
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 isn't used in 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) 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 spaces (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 checksum ibdata again
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
Copy Code code 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 one invalid (fails old style checksum)
Page 11:old style:calculated = 0x3908087c; Recorded = 0xf9e8d30c
Fixing old checksum of page 11
Page one 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
