How to undo DROP Table?

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

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.

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

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.

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.

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.