5

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.

Alexis Dufrenoy
  • 235
  • 1
  • 3
  • 11
  • This needs more info. What kind of garbage is in the table - extra rows, or physically broken data? What exactly happened? What kind of recovery do you need to happen? This *may* be better off on serverfault.com, I'll vote to migrate. – Pekka Jan 26 '11 at 20:20
  • 3
    Did you have binary logging enabled? It'll have every query executed from the start of the log to "now". You can extract/replay the log up to when the bad update was done and rebuild from that. – Marc B Jan 26 '11 at 20:20
  • 3
    Before you do anything else, stop `MySQL` and copy the whole database folder somewhere else, especially `ibdata*` and `ib_logfile*`. – Quassnoi Jan 26 '11 at 20:22

1 Answers1

4

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.

Mark Henderson
  • 68,823
  • 31
  • 180
  • 259
  • 2
    I think we were all waiting for a SQL hero to save the day. Other than that we are all biting our tongue about hiring a competent sysadmin to prevent business-killing disasters like this... copying data and auditing backups is not rocket science, it's just tedious and takes time :-/ – DutchUncle Jan 26 '11 at 23:49
  • 4
    ... and some of us are trying really really hard not to point out the insanity of not having recent (and tested) backups, especially as this isn't the first disaster. – John Gardeniers Jan 27 '11 at 02:28
  • Yeah... I deliberately chose not to mention that as it's just kinda rubbing salt into the wound – Mark Henderson Jan 27 '11 at 02:29
  • I know, right? Problem is: way too short schedule, too much work for too few people. A backup system is normally running, but it was down without anybody noticing since like forever! On the good side, we will be able to recover a good slice of the data, thanks to the binary log. Things will probably turn out right, with some work. And, yes, this will be used as a major argument to improve things here. I'm repeating this since months, now. The boss will probably understand what I mean, now... :-) Thank you to all of you, guys! – Alexis Dufrenoy Jan 27 '11 at 11:34