MySQL Forums
Forum List  »  Backup

Re: Help! - Innodb and MyISAM accidental DROP DATABASE - 112 tables gone forever?
Posted by: Greg Lehey
Date: June 28, 2006 08:09PM

> INNODB DATA: ~1GB in /var/lib/mysql (~12 tables)
> -rw-rw---- 1 mysql mysql 25088 Jun 15 2005 ib_arch_log_0000000000
> -rw-rw---- 1 mysql mysql 991952896 Jun 28 05:21 ibdata1
> -rw-rw---- 1 mysql mysql 5242880 Jun 28 05:22 ib_logfile0
> -rw-rw---- 1 mysql mysql 5242880 Jun 27 21:32 ib_logfile1
> -rw-rw---- 1 mysql mysql 2635 Jul 6 2005 ls1336.err
> -rw-rw---- 1 mysql mysql 5 Jun 15 2005 ls1336.pid
>
> BACKUP SITUATION:
>
> The backup server was supposed to be backing up the data every night,
> but it seems that the data in the backup is about a month old. I
> thought our tech was backing up this data every day, but it seems that
> he was doing a rsync backup of the database directory itself, thus
> completely missing the ~12 VERY important innodb tables (I think he
> missed them).

It's not clear what you mean by this. Are you saying that you only
backed up the contents of the database subdirectory of /var/lib/mysql?
Unfortunately, that didn't contain the InnoDB tables. The InnoDB
tables used to be in /var/lib/mysql/ibdata1, but I think you know this
already. It all depends on whether you have a backup of this file.

> So, our only recourse seems to be to recoved the dropped database
> somehow.. but how?

I can't think of a MySQL-level solution to this problem. About the
only thing you can do is to try to recover at the file level (find the
data in the InnoDB table space) or at the file system level (recover
the deleted files). I don't know how to do either, in particular no
way to recover the contents of an InnoDB table space, nor even if it's
possible. I'm guessing that the table space will be updated to show
that the blocks that contained your tables will be marked "free", but
not physically removed.

> MySQL.com recommends purchasing their $3,000 Gold tech support to
> handle this... but when I asked them if this sort of situation is
> recoverable, they couldn't say. They said that I'd better ask the
> forums and get an idea if purchasing gold support to recover these
> files is worthwhile investment.

I'm sure that the Gold tech support is a worthwhile investment, but
support was correct in not promising to recover the data. My guess is
that you've lost the data for good unless you have some kind of
backup. Sorry.

Greg Lehey, Senior Software Engineer, Online Backup
MySQL AB, http://www.mysql.com/
Echunga, South Australia
Diary: http://www.lemis.com/grog/diary.html

Are you MySQL certified? http://www.mysql.com/certification/

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.