Restoring lost InnoDB MySQL databases after all data and hope was lost with DROP TABLES

Tonight we had a DROP TABLES command run on a live production database. It happened about four hours before I was scheduled to turn on database backups, and about three days before I was scheduled to move the site to a server that Linode backs up daily. If you're not familiar with this, it basically means we lost every tiny little piece of data on a live server. Everything.

Needless to say, we all freaked. It's a product in alpha, with just a few very early testers, which is why it was on a crappy server. But this was still terrible for us; we want our customers to be able to rely on us to keep their data safe, even in an early alpha. We had one or two users who weren't just kicking the tires but were actually using this for their day-to-day work. I hadn't gotten the backups turned on because I had been traveling and I had a reminder set at 9:30pm to turn them on.

The short story

I may expand this blog post later, but in short: I've worked with my team since 6pm eastern (it's 2:35am eastern right now), I'm exhausted and need to sleep, but we went from every database table is not just truncated but entirely deleted to we have all of our data back in one piece.

It's all due to the magician that is Aleks, the man beind Twindb. Through his articles, and a bit of Twitter and email help, I was able to extract our old data from the deepest depths of InnoDB's operating archives.

Synopsis of the solution

In short, we used the tool sys_parser to recover our SQL schema ([https://twindb.com/recover-table-structure-from-innodb-dictionary/](Recover table structure from InnoDB dictionary)). Then we installed Twindb's InnoDB recovery kit using the instructions on the first half of this post: How to Recover InnoDB Dictionary. Finally, we used the instructions at Recover InnoDB Table After DROP to extract the pages of the InnoDB storage file into individual files using stream_parser, and with a combination of grep and c_parser, we identified which page each table was stored in, and then used c_parser and the schema SQL files we generated with sys_parser to create files we could import into SQL.

@todo: A longer walkthrough

If I have enough energy to talk about this later, I'll write it up further. But it needs to be said: We wouldn't've gotten anywhere without the help of the man behind Twindb, Aleksandr Kuzminsky. Just look at this Twitter thread. He also emailed with me in the midst of traveling to a camp site. I can't thank him enough for his help on this.

Also, many, many friends responded to my request for help on Twitter. Thank you so much.

So, know: it is possible to recover data even after a full drop/truncate. It's a lot of work, and you're much better off just backing up your data regularly. But if you're already in that spot... it can be done.