Database crash rescue – How we re-built an InnoDB MySQL database when ibdata1 file was corrupted
For a website owner or a web host, a hard disk crash is one of the most upsetting things that can happen to their business. It means service downtime, loss of business and valuable time spent on data restoration.
To minimize such catastrophe, many server owners keep regular backups, and even use RAID servers. But even with these precautions, sometimes things can go for a toss when the backups also get lost in a hard disk crash.
Yes, it seems unlikely, but it does happen. In our years of experience trouble-shooting server issues, we’ve been contacted many times by customers who faced an unexpected hard disk crash.
Database crash – A nightmare!
One such case was an emergency support request we received recently from a web host. The server hard disk had crashed, and to make matters worse, the backups were stored in the same hard disk because of an error in backup settings.
So, left with no backup, the web host was looking for a way to restore the websites in the server. Flooded with customer complaints, he was in a panicky situation.
That was when he came across the database crash recovery services we offer as a part of our Emergency Server Administration. In emergency request handling, we know that each minute counts.
Our aim was to restore the database in minimum time to reduce the business impact. As soon as we received the request, we mounted the crashed hard disk on a new server and assessed the damage.
We found that the partition in which the website files were stored was intact, but the partitions that contained databases and backups were partially damaged. There were two kinds of databases in the server – MYISAM and InnoDB.
The MYISAM databases used a decentralized storage structure where all database information were stored in separate directories, but the InnoDB databases stored all their table data and index information (aka meta data) in a central file called ibdata1.
While almost all the MyISAM databases were recoverable, the InnoDB databases were lost beyond recovery, as the ‘ibdata1’ file had got corrupted and was non-recoverable.
[ No need to panic over lost data anymore! Our expert technicians are here 24/7 to rescue your valuable data in no time. ]
Database rescue – A solution in sight!
While going through the database partition, we noticed that each InnoDB table had an associated “ibd” file. This is a configuration in some servers where InnoDB uses “.ibd” files to store individual table data, instead of default setting where all table data in stored in a central file called “ibdata1” (which was lost in the crash).
This was a lucky break because the customer data was not lost. It was safely stored in “.ibd” files. It meant that the only real damage was limited to the central indexing data – the information that said “Table X is at location ABC”.
All other information such as table structures and actual table data were recoverable. We just needed to find a way to let MySQL know where each table was.
To resolve the missing index issue, we used the below solution:
- In the new HDD, create a new InnoDB system table (with new meta data).
- Create new InnoDB tables and link it to the new system table.
- Import old data to the new InnoDB tables.
- Edit the old “.ibd” data files to sync the index number with the new InnoDB meta data.
Creating a new InnoDB system table (ibdata1)
As the first step, we installed a new MySQL server in the new hard disk. This created a new InnoDB system table, and an associated “ibdata1” file.