Mysql – Restoring deleted MySQL database from log file

MySQL

Last Night I accidently deleted a production MySql database on a digital ocean droplet. I immediately shut down apache and MySql. I have the following ib* files:

  • ib_buffer_pool
  • ib_logfile0
  • ib_logfile1
  • ibdata1
  • ibtmp1

Is there anyway I can restore my database from these files? Does anyone have a program or know of one that can rebuild this? If not a program, a way to role back the latest changes to the server?

I have no back-up.

Best Answer

No, sorry. Those files are from the innodb storage engine, and are journals, logs, etc. While journaling does ensure data consistency, it only uses metadata to do so - so if you have no data to go with it, you're not recovering anything.

But that's maybe not as much of an issue as it might seem. Most filesystems are relatively easy to recover data from as long as you do not write to it in any way. A write may overwrite your old deleted data. After a deletion, a file is simply taken out of the "filesystem index", leaving the actual data behind (which can be overwritten with new data if you use the filesystem).

testdisk is an excellent utility for restoring (un-deleting) files. There are several others that you may use, many of which are filesystem specific. They should be run on an unmounted block device. So if you want to recover this data, you should stop using this volume immediately and open it with testdisk or similar. You should also make an image of the underlying block device and work from that, rather than operating on your original. dd is a great utility for doing that.

Maybe you have volume snapshots of some kind? That would allow you to roll back this change, potentially. You could also use that to snapshot your volume in its current state before attempting a recovery, circumventing the need for copying the entire block device.

Related Topic