Skip navigation links

MySQL Forums :: Backup :: Help! - Innodb and MyISAM accidental DROP DATABASE - 112 tables gone forever?


Advanced Search

Help! - Innodb and MyISAM accidental DROP DATABASE - 112 tables gone forever?
Posted by: Casey Fenton ()
Date: June 28, 2006 12:47PM

Hello Experts!

I think that this is a tough one.

We are in the middle of a huge catstrophe. An accidental 'DROP DATABASE xxx" was issued last night and now we are trying to recover form this loss. My question to you experts is, "IS this data recoverable???"

SERVER CONFIGURATION:
###########################

Linux Fedora Core 4 - RAID-10 (120GB x 4) - 200GB /home partition
Ext3 filesystem

Filesystem 1K-blocks Used Available Use% Mounted on
/dev/sda5 4127076 3493156 424276 90% /
/dev/sda1 101086 12799 83068 14% /boot
/dev/sda7 1035660 115572 867480 12% /tmp
/dev/sda8 ~200000000 58% /home
/dev/sda3 12389352 1580396 10179612 14% /usr
/dev/sda2 12389352 3044020 8715988 26% /var
/tmp 1035660 115572 867480 12% /var/tmp


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

/etc/my.cnf -- some file settings:
# InnoDB settings
innodb_data_home_dir = /var/lib/mysql/
innodb_data_file_path = ibdata1:100M:autoextend
set-variable = innodb_buffer_pool_size=300M
set-variable = innodb_additional_mem_pool_size=30M
innodb_flush_log_at_trx_commit=0


MYISAM DATA: ~46 GB (~100 tables)
in /home/mydbbkp/database_data
There is a symbolic link from /var/lib/mysql/database_data to /home/mydbbkp/database_data

##########################

WHAT HAPPENED:
A 'DROP TABLE database_data' was accidentally issued. A minute later, when we figured out what happened, we umounted the /home partition to stop any writes to the MyISAM data. Then, a few minutes later, we stopped mysqld and made a copy of the /var/lib/mysql innodb files.


WHAT WE ARE TRYING:
We are trying to recoved the tables that mysql dropped on the dev/sda8 (/home/mydbbkp/database_data) folder to recover the most recent MyISAM data/tables. This isn't working very well as the file system is ext3 and the related inodes got destroyed when the table files were dropped.

MOST IMPORTANT DATA - innodb tables:
The most important data was in the ~12 innodb tables. Am I correct in assuming that this data lives in /var/lib/mysql/? Or does innodb data live next to the myisam tables? If that is where this data resides, I don't know what happens to the innodb files when a drop database command is issued. I'm assuming that the 1GB ibdata1 file still holds all of the innodb data (~12 tables) but it is just 'unlinked' within the innodb file structure, not actually deleted/wiped... right?

#########################

THE QUESTIONS:
How do we recover the innodb tables that were dropped?
How do we recover the myisam tables that were dropped?

#########################

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). We have had to learn a hard lesson that even when the techs say that things are backed up and safe, they may not be!

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

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.

Since we are a non-profit social network, $3,000 is a LOT of money for us. We would consider trying to take out a loan to do this though if recovery is feasable.

Help!

Thank you all so very much!

Casey

Options: ReplyQuote


Subject Views Written By Posted
Help! - Innodb and MyISAM accidental DROP DATABASE - 112 tables gone forever? 28927 Casey Fenton 06/28/2006 12:47PM
Re: Help! - Innodb and MyISAM accidental DROP DATABASE - 112 tables gone forever? 12625 Greg Lehey 06/28/2006 08:09PM
Re: Help! - Innodb and MyISAM accidental DROP DATABASE - 112 tables gone forever? 10356 Oli Sennhauser 06/29/2006 04:17AM
Re: Help! - Innodb and MyISAM accidental DROP DATABASE - 112 tables gone forever? 8469 Oscar Rylin 07/03/2006 12:42PM
Re: Help! - Innodb and MyISAM accidental DROP DATABASE - 112 tables gone forever? 7266 Jeff Wooshed 07/09/2006 07:14AM
Re: Help! - Innodb and MyISAM accidental DROP DATABASE - 112 tables gone forever? 7205 Greg Lehey 07/09/2006 10:32PM


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.