We just got a major disaster: somebody made an uncontrolled update on the production database, and obviously, the backup process is not working since a long time, so we got a major data loss. A 40 millions rows table is now full of garbage.
Does anybody has an idea to restore the data? For example, a tool using filesystem recovery?
Facts:
- ext3 fs (on Debian)
- InnoDB engine (on Mysql 5.0)
Honestly, it's not the first major disaster in our company, but this one could easily be the last one. We usually come up with some idea to save the day, but this time, I'm really out of ideas. Clusterf*ck...
Edit: the problem occurred after an update statement without where. Problem is, th problem happened between monday afternoon and tuersday morning (France time), and was only discovered today, for various reasons (the application offers a synchronization tool, so new data is inserted with the now missing data, but a foreign key in another table is now completly broken). So actually, almost all rows in the table (except the newly inserted) contains the same data (except the id column).
About ibdata* and ib_logfile*, I stopped the replicated server, so they stay as they are now. I can't stop the database on the main server to copy the files.
2 hours and no answers? I think that could just be because nobody is willing to break the news to you.
Do you have a good copy of the data anywhere at all (even one that's a month or two old)? If no, then you're SOL I'm afraid. You've made some allusions to a synchronised copy of the database, so if the synchronisation was taken down before the bad UPDATE statement was executed, you will need to create a statement to update Source A with Source B's data for that table.
(I had this once, with MSSQL and log shipping, and thankfully was able to stop the log shipping before the bad data was restored on Site B, and just did an UPDATE statement between the servers to undo my mistake).
As Marc B mentioned, if you have binary logging enabled (and the log hasn't been truncated) you might be able to recover some of the data by getting that log to be replayed (even if you have a reallllly old copy of the database, if your log is intact you'll be OK), but that can be a bit hit-and-miss.