咨询微信： dbservice1234 7 x 24 在线支持！

首页

下载 DBRECOVER

What are odds MySQL table can be recovered?

What are odds MySQL table can be recovered?

Posted by PDSERVICE on 11月 07, 2019 In
This is the most asked question. Every single customer asks if their MySQL table can be recovered. Although it’s not possible to answer that with 100% confidence there are ways to estimate recovery chances. I will describe few tricks.
 
Generally speaking, if data is on media there are high odds TwinDB data recovery toolkit can fetch it. Where to look for depends on accident type.
 
Online MySQL data recovery toolkit
On our Data Recovery portal you can upload an .ibd file and check if the InnoDB tablespace contains any good records. The table space may be corrupt. The tool should handle that.
 
MySQL data recovery portal
 
DROP TABLE or DATABASE with innodb_file_per_table=OFF
If innodb_file_per_table is OFF InnoDB stores all tables in one file ibdata1. When a table or database is dropped pages with data are marked as free. InnoDB may reuse the pages for new data. It’s important to stop writes to ibdata1 as soon as possible, but if MySQL was running a while InnoDB might overwrite some data.
 
Let’s take table actor from sakila database as an example:
 
CREATE TABLE `actor` (
`actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`first_name` varchar(45) NOT NULL,
`last_name` varchar(45) NOT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`actor_id`),
KEY `idx_actor_last_name` (`last_name`)
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8
Fields first_name and last_name are string and they come next to each other. In InnoDB page these fields also located next to each other. InnoDB does’t terminated strings with ‘\0’, so if first_name is WOODY and last_name is HOFFMAN in the InnoDB page you will find WOODYHOFFMAN string. So, take grep and try to find that string:
 
# grep WOODYHOFFMAN ibdata1
Binary file ibdata1 matches
So it’s likely the record is still in ibdata1 and not overwritten. However the string may be a remains of system buffers. To be sure the string comes from a good index page I use bvi. It stands for binary vi and works pretty much similar to vi. Particularly search works the same way. I can scroll down ibdata1 and see in what context WOODYHOFFMAN shows up. Here’s how InnoDB index page looks like.
 
0013C44C  00 00 00 00 07 0E 94 00 00 01 3D 02 ..........=.
0013C458  14 4A 55 4C 49 41 4D 43 51 55 45 45 .JULIAMCQUEE
0013C464  4E 43 F2 AF 59 07 05 04 00 E8 00 26 NC..Y......&
0013C470  00 1C 00 00 00 00 07 0E 94 00 00 01 ............
0013C47C  3D 02 1E 57 4F 4F 44 59 48 4F 46 46 =..WOODYHOFF
0013C488  4D 41 4E 43 F2 AF 59 05 04 00 00 F0 MANC..Y.....
0013C494  00 23 00 1D 00 00 00 00 07 0E 94 00 .#..........
0013C4A0  00 01 3D 02 28 41 4C 45 43 57 41 59 ..=.(ALECWAY
0013C4AC  4E 45 43 F2 AF 59 04 06 00 00 F8 00 NEC..Y......
0013C4B8  24 00 1E 00 00 00 00 07 0E 94 00 00 $...........
0013C4C4  01 3D 02 32 53 41 4E 44 52 41 50 45 .=.2SANDRAPE
0013C4D0  43 4B 43 F2 AF 59 08 05 00 01 00 00 CKC..Y......
0013C4DC  27 00 1F 00 00 00 00 07 0E 94 00 00 '...........
If you go up you’ll see the infimum and supremum records – those index page starts with:
 
0013C038  00 00 00 00 00 00 00 00 00 00 00 00 ............
0013C044  00 00 00 00 00 7D 00 00 00 00 00 00 .....}......
0013C050  01 EE 03 32 00 00 00 00 00 00 01 EE ...2........
0013C05C  02 72 01 00 02 00 1C 69 6E 66 69 6D .r.....infim
0013C068  75 6D 00 05 00 0B 00 00 73 75 70 72 um......supr
0013C074  65 6D 75 6D 07 08 00 00 10 00 29 00 emum......).
0013C080  01 00 00 00 00 07 0E 94 00 00 01 3D ...........=
0013C08C  01 10 50 45 4E 45 4C 4F 50 45 47 55 ..PENELOPEGU
0013C098  49 4E 45 53 53 43 F2 AF 59 08 04 00 INESSC..Y...
0013C0A4  00 18 00 26 00 02 00 00 00 00 07 0E ...&........
DROP TABLE or DATABASE with innodb_file_per_table=ON
The same principle applies if innodb_file_per_table is OFF.
 
The difference however is InnoDB deletes *.ibd file with data from file system when you DROP TABLE or DATABASE. That means the data maybe anywhere in free space of the file system. In this case I recommend to remount disk partition with MySQL data read-only as soon as possible. Otherwise not only MySQL but any process may overwrite the data.
 
To find the original records you can use grep:
 
# grep NICKWAHLBERG /dev/sda1
Binary file /dev/sda1 matches
bvi on large files works as bad as vi, so I use hexdump -C and less. Search however is less reliable because strings may be wrapped.
 
Corrupted InnoDB table
Depending on innodb_file_per_table you can look for the data in ibdata1 or respective *.ibd file. If records look good then the table is recoverable. Often corruption touches headers. For InnoDB it’s critical but data recover toolkit can ignore the corrupted bits and fetch what looks like good records.