MySQL single table ibd file recovery method details
- 2020-05-13 03:41:59
- OfStack
Preface:
With the popularity of innodb, innobackup has become the mainstream backup method. Physical backup for new slave, the need to restore the entire library can be handled gracefully.
But in the case of single-table data deletion, or single-table drop error, how to use physical full recovery?
A detailed analysis will follow.
Need to use tools, in the process of recovery percona data recover tool: https: / / launchpad net/percona - innodb recovery -- tool
Case 1: delete part of data by mistake and need to be overwritten with the last backup
ibd restore coverage from the same machine, and table was not used by recreate after backup.
This is the simplest case when backing up space id and index id in the ibd file (later known as the old ibd) and space id and index id1 in the new ibd space id and index id1.
And to space id and index id1 in ibdata documents. Therefore, the physical file can be overwritten directly 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: backup current ibd files (optional)
cp -a testibd.ibd testibd.bak
Step 2: discard the current ibd file
mysql > alter table testibd discard tablespace
Step 3: copy and 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 the ibd file
mysql > alter table testibd import tablespace
Case 2: table was deleted by mistake. The table structure has been drop
This situation is a little more complicated, but the recovery process is relatively easy. Since table is left empty by space id after drop, space id of the backup file will not be occupied.
We only need to rebuild the table structure, and then restore space id of the table in ibdata. The physical files can be overwritten directly for recovery.
Step 1: rebuild the table
mysql > create table testibd (UserID int);
Step 2: close mysql service (required)
shell > service mysqld3321 stop
Step 3: prepare ibd documents apply log
shell > innobackupex --apply-log --defaults-file=/usr/local/mysql3321/my.cnf /xfs/backup/2012-10-17_11-29-20/
Step 4: backup current ibd files (optional)
cp -a testibd.ibd testibd.bak
Step 5: copy and 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: modify ibdata using percona recovery tool
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
Step 7: percona recovery tool checksum ibdata again using percona recovery tool
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
Step 8: start the mysql service
shell > service mysqld3321 start
With the popularity of innodb, innobackup has become the mainstream backup method. Physical backup for new slave, the need to restore the entire library can be handled gracefully.
But in the case of single-table data deletion, or single-table drop error, how to use physical full recovery?
A detailed analysis will follow.
Need to use tools, in the process of recovery percona data recover tool: https: / / launchpad net/percona - innodb recovery -- tool
Case 1: delete part of data by mistake and need to be overwritten with the last backup
ibd restore coverage from the same machine, and table was not used by recreate after backup.
This is the simplest case when backing up space id and index id in the ibd file (later known as the old ibd) and space id and index id1 in the new ibd space id and index id1.
And to space id and index id1 in ibdata documents. Therefore, the physical file can be overwritten directly 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: backup current ibd files (optional)
cp -a testibd.ibd testibd.bak
Step 2: discard the current ibd file
mysql > alter table testibd discard tablespace
Step 3: copy and 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 the ibd file
mysql > alter table testibd import tablespace
Case 2: table was deleted by mistake. The table structure has been drop
This situation is a little more complicated, but the recovery process is relatively easy. Since table is left empty by space id after drop, space id of the backup file will not be occupied.
We only need to rebuild the table structure, and then restore space id of the table in ibdata. The physical files can be overwritten directly for recovery.
Step 1: rebuild the table
mysql > create table testibd (UserID int);
Step 2: close mysql service (required)
shell > service mysqld3321 stop
Step 3: prepare ibd documents apply log
shell > innobackupex --apply-log --defaults-file=/usr/local/mysql3321/my.cnf /xfs/backup/2012-10-17_11-29-20/
Step 4: backup current ibd files (optional)
cp -a testibd.ibd testibd.bak
Step 5: copy and 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: modify ibdata using percona recovery tool
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 again using percona recovery tool
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
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