2

Is there any methods to retrieve deleted records from a mysql database?

Perception
  • 79,279
  • 19
  • 185
  • 195
Harsha
  • 3,548
  • 20
  • 52
  • 75
  • 1
    Open up your backup. You do keep backups, right? – Ben Lee Mar 13 '12 at 08:18
  • Have you committed? If not just do rollback. – Chetter Hummin Mar 13 '12 at 08:18
  • Depends on what storage engine you're using. – Bobby Mar 13 '12 at 08:19
  • There also seems to be this: http://mysqlbugs.blogspot.com/2007/08/how-to-undelete-rows-from-fixed-length.html, I haven't really looked at it – Ben Lee Mar 13 '12 at 08:20
  • http://stackoverflow.com/questions/6924823/how-to-recover-just-deleted-rows-in-mysql – Ghostman Mar 13 '12 at 08:21
  • If it's hugely important, contact Oracle support and they may be able to help you recover parts of the data that has not been overwritten. Count on it costing a hefty sum though. – Joachim Isaksson Mar 13 '12 at 08:21
  • Use mysqlbinlog `mysqlbinlog binary_log_file > query_log.sql` – Naveen Kumar Mar 13 '12 at 10:19
  • hope this will help u [link](http://stackoverflow.com/a/6924888/552116) backing data up is the best method. However, it maybe possible to extract the lost data partially depending on the situation or DB server used. For most part, you are out of luck if you don't have any backup. – Ghostman Mar 13 '12 at 08:22

2 Answers2

1

No.

Deleted records are gone (or munged so badly you can't recover them). If you have autocommit turned on, the system commits each statement as you complete it (if you have auto commit turned off, then do a rollback NOW - phew, you're saved -- but you are running with autocommit, aren't you?).

One other approach is to reply the activity that created the missing records - can you do that? You can either re-run whatever programs did the updates, or replay them from a binary log (if you still have the binary log). That may not be possible, of course.

So you need to recover the data from somewhere - either a backup of your db (made using mysqldump) or of your file system (the data files of MyISAM tables are all simply structured and on the disk - recovering InnoDB tables are complicated by the shared use of ibdata files).

D Mac
  • 3,727
  • 1
  • 25
  • 32
  • Never say never - http://www.mysqlperformanceblog.com/2012/02/20/how-to-recover-deleted-rows-from-an-innodb-tablespace/ – akuzminsky Feb 14 '14 at 01:49
0

There is a possible way to retrieve deleted records (depending upon your situation). Please check here:

https://stackoverflow.com/a/72303235/2546381

VanagaS
  • 3,130
  • 3
  • 27
  • 41