12

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

Improve this question
0

7 Answers 7

Reset to default
14

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.

Improve this answer
6
  • 3
    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, 2010 at 12:09
  • What can i do now?? I cant just sit and wait!!!!
    – deadman
    Jan 18, 2010 at 13:08
  • 9
    Tell your manager
    – Chopper3
    Jan 18, 2010 at 13:18
  • 4
    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, 2010 at 13:52
  • 3
    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, 2010 at 14:33
9

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://github.com/twindb/undrop-for-innodb/. Check "[Compiling TwinDB recovery toolkit][1]" 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. [1]: https://twindb.com/how-to-recover-innodb-dictionary/

P.S. video tutorial on Undrop For InnoDB - Undrop for InnoDB overview https://youtu.be/-1LeLhGjAWM

Improve this answer
1
  • 2
    you sir saved my life
    – Buddhi741
    Apr 15, 2019 at 2:21
2

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.

Improve this answer
2
  • 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, 2011 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, 2011 at 8:54
2

There is very little you can do unfortunately, other than take away a very valuable lesson 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.

Improve this answer
2
  • Is there any other option?
    – deadman
    Jan 18, 2010 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, 2010 at 13:37
1

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.

Improve this answer
0

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.

Improve this answer
3
  • 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, 2010 at 13:41
  • If you have shadow copy turned on on the volume that stored the MyISAM tables you have a shot at getting them back that way.
    – Catherine MacInnes
    Jan 18, 2010 at 14:17
  • To restore deleted database file you can search google for "ntfs undelete". I don't know where data dir is located on your PC. You need to check your my.ini for that or search for files with MYD extension for example.
    – Vladislav Rastrusny
    Jan 18, 2010 at 22:04
-1

If you have binary logging turned on then you can just recreate a table first if you have schema. Make sure you create schema while you have binlogs turned off. Or you can just skip for the session. Then you can replay the binlogs up until the last statement which was drop table itself.

If not then you can restore using a backup dump if you have any. If you have csv files then you can do load data infile method to recover data. If you are recovering from mysqldump then you may consider restoring a single table from the dump file rather than restoring full database. If data size is too large then you may consider disabling keys before loading this will significantly increase restore process.

For future you may like to have a delayed slave something like 10-24 hours behind. You can create delayed slave using percona toolkit(pt-slave-delay)

Improve this answer

You must log in to answer this question.