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.

Click here to recover your lost data!

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.

InnoDB metadata corrupted due to crashed ibdata1 file.

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.

Edit the individual tables to sync the index numbers with the new 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.

Creating the databases

Now we needed to create new databases for all the InnoDB tables. This was a time consuming step if we were to make each database manually – and time was something that we didn’t have.

We need to bring the databases online ASAP to minimize downtime for websites hosted in this server. So, we collected the list of InnoDB databases into a file, and used it in a shell script to create all databases afresh in the new server.

Creating the tables

The next step was to create tables in these new databases. For that, first we needed to know the structure of each table. This structure information was available from the “.frm” file located in each database’s directory in the old server.

For example, the table structure of table1 inside database1 was available from the file /var/lib/mysql/database1/table1.frm. To get that table structure, we used a tool called mysqlfrm.

This gave us a listing of CREATE TABLE statements for all tables in the database database1. We fed these “CREATE TABLE” commands into another shell script and quickly populated all the databases we created earlier with its corresponding tables.

Now, we had the full list of databases, and all their corresponding tables in the new server, within a few minutes of time.

[ Hard disk crashed? Trying to recover valuable data? Don’t worry! Our expert technicians will restore your databases in no time. ]

Importing old data

The databases and tables in the new server now had the right structure, but no data in it. To import the data, first we asked MySQL to discard the table content it currently had for each database table, using shell script.

Now, the “.ibd” files (which contain table data) were copied over from the old hard disk to the new hard disk. At this point, the databases technically had all the data in the hard disk, but MySQL didn’t know where to look for it. We now had to link it to the InnoDB meta data.

Linking old data with new InnoDB meta data

When we created new databases and tables, MySQL assigned a unique number to each table. For example, in the new server, MySQL assigned the number 261 to the table table1 of a database named database1, but in the old server this table had the number 4722.

For the new MySQL server to recognize the old table, all we had to do was to edit table1.ibd and replace 4722 with 261. Here’s how we found out these unique numbers, and edited the table1 data file:

First we tried importing the old table in the new database.

# mysql database1 
mysql> alter table table1 import tablespace;

It gave us an error ‘Got error -1 from storage engine‘. The MySQL error log showed the below corresponding entry.

150923 16:54:48 InnoDB: Error: tablespace id and flags in file '/var/lib/mysql/database1/table1.ibd' are 4722 and 0, but in the InnoDB InnoDB: data dictionary they are 261 and 0.

So, now we knew table1 had the unique number 4722 in the old server and 261 in the new server’s InnoDB meta data. The database tables are all Hex encoded. So, we used a hex editor to replace 1272 (hex code of 4722) with 0105 (hex code of 261) in table1.ibd.

Now, we tried importing the table again and all worked well! 🙂 The above process was then repeated for all the tables using a shell script, and all web sites in the server were verified manually.

All sites were found to be working, and our customer was back in business.

How Bobcares minimizes data loss in servers

The whole issue with this server happened because it lacked a reliable backup. In the servers that we manage via Server Management service, we avoid such catastrophe with proper planning and proactive management.

Our server management task involves setting up a fault-tolerant backup system in an external safe location, and periodic audits to ensure that reliable backups are available at any point of time.

With our regular server health checks, we are able to detect majority of the hardware issues such as a disintegrating hard disk and tackle them, before it crashes and render a server downtime.

If you’d like to know how to ensure a reliable server environment for your business, feel free to contact us.