Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I accidentally dropped all tables. Can I restore back? I don't have the backup copy.

share|improve this question

6 Answers 6

If you have literally no backup then I'm 99% sure you're out of luck.

If you do have any form of backup, however old, then do you have binary logging turned on via the log-bin option into the MySQL config file (my.ini)? If so they you might be able to recover since last backup.

Bad way to start a week dude, sorry.

share|improve this answer
2  
Presumably because you're inexperienced, we've all done this kind of thing, still do sometimes (locked myself out of my own VCenter not a week ago in fact) - all that matters is that you learn from it so you're less likely to repeat. –  Chopper3 Jan 18 '10 at 12:09
    
What can i do now?? I cant just sit and wait!!!! –  deadman Jan 18 '10 at 13:08
8  
Tell your manager –  Chopper3 Jan 18 '10 at 13:18
3  
It doesn't fix the problem, but perhaps one of your developers has a copy that's not too distant from the last good copy? –  Tom O'Connor Jan 18 '10 at 13:52
2  
Tom raises a very good point: if your developers are in the habit of regularly taking snapshots of the live data for testing/development purposes then you might be in luck and fine one of them has a recent enough unspoilt copy to downgrade your situation from "complete disaster" to only "major inconvenience". –  David Spillett Jan 18 '10 at 14:33

There is very little you can do unfortunately, other than take away a very valuable less about the need for a good backup plan.

Depending on the table type you might be able to find an expert who can piece the data back together from what it left on disc but such forensic analysis would be very very very expensive (as it would require relatively uncommon skills) and not at all guaranteed to be truly useful.

share|improve this answer
    
Is there any other option? –  deadman Jan 18 '10 at 13:15
1  
As FractalizeR suggests, if the tables were simple MyISAM tables then you might be able to undelete the files associated with them. If you are going to try then then you need to shut down the server now because the longer the system is active the more chance there is that the space used by the files will be reused making undelete impossible (or making the undeleted files have corrupted content). How to undelete depends on the filesystem used. ntfsundelete.com is the first useful looking link in a Google search for undelete on NTFS. –  David Spillett Jan 18 '10 at 13:37

Here's what I did. In the mysql directory (for Ubuntu this is /var/lib/mysql, for Mac using Homebrew this is /usr/local/var/mysql), I found some files. First I copied the myapp_development/ directory containing the particular schema into my local mysql directory. Then I backed up my local ibdata1 and copied the server's ibdata1 into the mysql directory. Killed mysqld. (ps aux to find the PID, then kill PID). Restarted mysql, it started in crash recovery mode. Then fired up my local mysql client and generated a full dump of the tables I needed.

And, 15,000 rows representing weeks of work entering metadata we thought were gone forever, are saved!!

Hope this helps someone.

share|improve this answer
    
That's a nice effort but I sure wouldn't be pinning too many hopes on this being a reliable technique. Nevertheless, +1 for some creative thinking. –  John Gardeniers Feb 27 '11 at 21:10
    
Yes, you're correct. It ended up working only because we had accidentally removed all permissions from the mysql user, so the database was showing up as empty to that user. –  Duke Mar 24 '11 at 8:54

The question is rather old, but there is no single positive answer, so I'll add one.

After MySQL drops a table the data is still on the media for a while. So you can fetch records and rebuild a table. Later on I'll blog about it, but for now quick sketch.

You would need to have structure of your table(CREATE TABLE statement).

If innodb_file_per_table is ON the dropped table are on disk partition. Stop MySQL and re-mount it as read-only ASAP. If MySQL was on a root partition (which is not good idea btw) then take an image or take the disk out and plug into another server. Stop all writes in other words.

If innodb_file_per_table OFF then just stop MySQL.

Then download and compile the un-drop tool for InnoDB from https://code.launchpad.net/undrop-for-innodb. Check "Compiling TwinDB recovery toolkit" post for details.

Then parse either disk partition or ibdata1 (depending on innodb_file_per_table setting) with stream_parser :

./stream_parser -f /path/to/diskimage_or_ibdata1

Then recover InnoDB dictionary to know in which index_id the dropped table was.

Then take the table structure and fetch the records

./c_parser -f pages-diskimage_or_ibdata1/FIL_PAGE_INDEX/00000<index_id>.page

It will output records to stdout and the LOAD DATA command to stderr.

share|improve this answer

If this was MyISAM table, you need just to undelete table files in /var/log/mysql or whatever your data dir is. You can use ext3grep utility for that for example.

share|improve this answer
    
WHAT IT IS? CAN U EXPLAIN MORE? HOW TO USE? STEP BY STEP, I BEG YOU PLEASE, IREALLY NEED YOUR HELP... I AM GOING CRAZY BEFORE I DIE –  deadman Jan 18 '10 at 13:02
    
I cant found the folder. I am using windows server 2003. Help me find please, please pleaseeeeeeeeeeeeee –  deadman Jan 18 '10 at 13:03
    
Are you there? please response –  deadman Jan 18 '10 at 13:07
    
ext3grep is in beta stage, even the creator claims there are bugs, I cant risk my server again. I cant risk my other databases. ANY OTHERS SOLUTIONS?? PLEASE HELP!!!!! –  deadman Jan 18 '10 at 13:14
    
ext3grep is for ext3 filesystems. If you are using Windows then you are unlikely to be using an ext3 filesystem (chances are you are using NTFS, though it may be FAT32). If you are going to try any recovery of deleted files you need to shut down the server as soon as possible, no matter what other services are running on it, as the longer the server is active the more chance there is that undelete can not help you at all. –  David Spillett Jan 18 '10 at 13:41

You can't "undo" a DROP TABLE.

You can look and see if that MySQL had binary logging enabled, maybe you can extract some data from there.

Other than that, you can forget about MySQL and are in the same class of problems of "I accidentally deleted some files from my filesystem". There are some tools out there that try to recover files, and there are also companies who do that on a professional basis.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.