How to recover data from corrupted innoDB table from a crashed mySQL server?

Question

Some background:

I had a MySQL server running on Ubuntu Server. I don't remember which versions they were running but it was likely a Ubuntu 12.04 with MySQL 5.5. During the course of a day, connections from the client application to the server kept dropping. On the next day, the server crashed and mysql wouldn't start again. I was also getting lots of disk errors so I decided to attempt to recover the data on a new machine.

What I tried:

I tried to recover the data with a Ubuntu 12.04 running MySQL 5.5 but I couldn't get the service running. Before troubleshooting it, I tried the same steps on a Ubuntu Desktop running mySQL 5.1 that I had available, and they worked. Here's what I did:

1. I copied /var/lib/mysql from the crashed server, to the new one
2. I ran chown -R mysql:mysql /var/lib/mysql
3. I ran service mysql start

The service started and everything seemed to work, except on one table. This is a table with BLOB data.

After some trial and error, I narrowed the problematic queries to record ids above a certain number (roughly, the last 100 records of a total of 7000 records).

Sample of some queries and outputs:

> select field1 from table1 where field1 = 6900
> (completes successfully)
> select field1 from table1 where field1 <= 6900
> (completes successfully)
> select field1 from table1 where field1 = 6901
> Error 2013 (HY000) at line 1: Lost connection to mySQL server during query

Then, I started trying to dump the data in an attempt to recover it:

> mysqldump -u root -p database1 table1 > table1.sql
> Lost connection to mySQL server during query when dumping table 'table1' at row 6642

I looked into mysqldump's options and decided to try to add -f and the output was different:

> Couldn't execute 'UNLOCK TABLES': MySQL server has gone away (2006)

To make sure the problem's in this table, I tried:

> mysqldump -u root -p --ignore-table=database1.table1 database1 > database1.sql

... and the command completed successfully.

I tried to delete one of the offending records and the delete command completed successfully. I guess I could delete them all, from the first problematic one and minimize losses but is there any hope of recovering them?

What would you suggest I do from here?

Sidenote: I was using Navicat to manage the server and make backups, and strangely enough, restoring that particular table with a recent backup also fails with the connection lost error, which leads me to believe that this table has been having problems for a while now, maybe due to disk errors and not specifically due to the crash.

Answer 1

You could try to use MySQL's CHECK TABLE and REPAIR TABLE functions (but make sure you grab a copy of the table first):

mysql -uroot -p database1
CHECK TABLE table1

If that doesn't work, try using:

CHECK TABLE table1 EXTENDED

According to the CHECK TABLE documentation:

EXTENDED is to be used only after you have run a normal check but still get strange errors from a table when MySQL tries to update a row or find a row by key. This is very unlikely if a normal check has succeeded.

If any errors are reported by either of the above, try:

REPAIR TABLE table1

If that doesn't work, you can try:

REPAIR TABLE table1 EXTENDED

Finally, if that still doesn't work, you may be able to use myisamchk while MySQL is offline - according to the documentation:

REPAIR TABLE does not implement all the options of myisamchk