[kwlug-disc] how to recover corrupt mysql database from .frm files?

[Khalid Baheyeldin]

On Wed, Feb 24, 2010 at 5:44 AM, Robert P. J. Day <rpjday at crashcourse.ca>wrote:

>
>  a colleague asks me if there's a way to recover a mysql database
> that was being hosted by someone else and was apparently corrupted in
> ways that are not at all obvious.
>
>  all the hosting company is providing him with is a tar file of the
> /var/lib/mysql/<db> directory, which consists of nothing but .frm
> files (so it's in innodb format, correct?).


Apparently, yes.

The .frm files are the table definitions only. No data in them.


> they won't give him the
> ibdata1 file or ib log files, for security reasons.
>

Because they did not set the innodb-file-per-table parameter, and hence
the ibdata file contains the data of every database on that machine, so
it would be inappropriate to give it to someone.

If they did have innodb-file-per-table, there would be data as .ibd for each
table in the same directory as the .frm file.

Recovering from this is not easy still. InnoDB still writes stuff to the
ibdata
files.

>
>  as a trivial test, i simply untarred the db .frm files under *my*
> /var/lib/mysql directory and fired up mysql.  "show databases;" sees
> the alleged database, i can switch to it, "show tables;" also lists
> all the tables, but if i try to describe a table:
>
> mysql> desc <table name>;
> ERROR 1146 (42S02): Table '<db name>.<table name>' doesn't exist
> mysql>
>
>  not at all surprising since there is no record of this database on
> my system in the ibdata1 file.  that's all he's got, and he'd really
> like to recover the data in that database, but his hosting company
> won't provide him with any further information or data, and they
> apparently don't have backups with which to restore.
>

That is the sad part. He has to run dumps daily to have backups he could
recover to, or move his data to another host if the need arises.


>
>  so ... i'm by no means a mysql expert, is there anything that can be
> done with nothing but the directory of .frm files?
>

No. .frm are only table definitions. The only thing you can do with it is
run TRUNCATE tablename and it will recreate an empty table.

This is really inexcusable, because of the lack of backups.


> rday
> --
>
> ========================================================================
> Robert P. J. Day                               Waterloo, Ontario, CANADA
>
>            Linux Consulting, Training and Kernel Pedantry.
>
> Web page:                                          http://crashcourse.ca
> Twitter:                                       http://twitter.com/rpjday
> ========================================================================
>
> _______________________________________________
> kwlug-disc_kwlug.org mailing list
> kwlug-disc_kwlug.org at kwlug.org
> http://astoria.ccjclearline.com/mailman/listinfo/kwlug-disc_kwlug.org
>



-- 
Khalid M. Baheyeldin
2bits.com, Inc.
http://2bits.com
Drupal optimization, development, customization and consulting.
Simplicity is prerequisite for reliability. --  Edsger W.Dijkstra
Simplicity is the ultimate sophistication. --   Leonardo da Vinci
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://kwlug.org/pipermail/kwlug-disc_kwlug.org/attachments/20100224/c959ab34/attachment.htm>