Monday, September 10, 2012

Recovering from Deleted Row Disaster in MySQL

This post is dedicated to accidents that every so often resulting in deleted rows of data.
Of course everyone should have backups. But things do happen when the backups nowhere to be found, or not being done often enough, or we're storing data in a forgotten database server.
In the event of such incident.. deactivate your apps. Prevent unnecessary writes to db that could make your data overwritten. In case of oracle database, you could try flashback query that assume the transaction is still fresh in the redo log. In case of mysql database, copy the whole data directory into a safe location, where we would try to dump deleted rows using Percona Innodb recovery tool.
But such methods failed miserably when we found out about the accident much too late. I think every person should be responsible for their actions, even if the action is about deleting data. But the consequence of mass-delete actions is very different from single-row deletes. That is why mass delete function must be avoided at all cost.. just kidding. There are few legitimate reasons to provide mass delete functionality, and we should always try to help our users to do their tasks. But if there is such functionality, we must : a. ensure undo mass delete functionality is implemented as well, or b. ensure informations deleted is saved into some other tables ( folders) upon mass delete executions, or c. ensure the informations deleted is still remained in the system in another form (another table stores similar data and not affected by delete command in the first table). The functionality to actually recover the mass deleted data could be left unimplemented until the event of the accident, but you must be certain that the information stored during mass delete are correct and sufficient for recovery.
The similar rules apply to mass update functionality. The safeguards must be implemented, snapshot of changed data should be serialized and stored in a different database table. 
I hope if there ever such an accident to your app, recovery could be done and no work would be lost.

1 comment:

Nandkishor Wagh said...

too good piece of information, I had come to know about your site from my friend sajid, bangalore,i have read atleast 11 posts of yours by now, and let me tell you, your web-page gives the best and the most interesting information. This is just the kind of information that i had been looking for, i'm already your rss reader now and i would regularly watch out for the new post, once again hats off to you! Thanks a lot once again, Regards, Single Row Function in sql