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:
- I copied
/var/lib/mysqlfrom the crashed server, to the new one
- I ran
chown -R mysql:mysql /var/lib/mysql
- 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.