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]" 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. : 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.
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.
You can't "undo" a
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.
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)