Is there a way how to recover deleted rows in Mysql deleted over a month ago

Question

I am using Mysql and by mistake deleted very important details.Is there a way how to recover deleted records in Mysql?

Answer 1

I know this is not really what you are asking but I find it important that you know this.

The system I suggest is called soft delete. It works as follow:

1. Make a new column in your table (for ex the table people):

   ID	Name	Deleted
   1	Bob	0
   2	Frank	1
   3	Alice	0

   With 0 as undeleted and 1 as deleted in last column with the type BOOLEAN. Name and ID are respectively a NVARCHAR and an INT.

2. If you want delete a record, you don't delete it with a DELETE statement but update it like this:

   UPDATE people SET Deleted = 1 WHERE ID = 1
   

   Result:

   ID	Name	Deleted
   1	Bob	1
   2	Frank	1
   3	Alice	0

   You know ID's 1 and 2 are deleted because the boolean in column Deleted is on 1. Alice with ID 3 is not deleted because the boolean is on 0.

---

The pro of this system is that you can recover data with only one statement!

UPDATE people SET Deleted = 0 WHERE Deleted = 1

Result:

ID	Name	Deleted
1	Bob	0
2	Frank	0
3	Alice	0

Now all record are recovered because the boolean in column Deleted is on 0.

If you use DELETE statement, it is impossible to recover all records! They are deleted for always and you can never recover it if you are using the DELETE statements. You can only use a backup file for recover it but has also contra's like:

• It's an old backup file,
• I've forget to make one,
• I don't know how you can make it,
• I've lost it,
• ...

By soft delete you change only one column and you have your data back.

---

Edit:
The contra of the system is that (like you have said) the data is not exactly remove from your database. It's only a column you change from 0 to 1. But if you know that you can make money from data... is this another story.

If you want to delete it exactly you can use a DELETE statement.

Answer 2

Do you have binlog files from the date where you insert these records ?

Then you can get the inserts and put it back

Answer 3

mysql> use  employees;
Database changed

mysql> show create table employees;
 
| employees | CREATE TABLE `employees` (
  `emp_no` int(11) NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) NOT NULL,
  `last_name` varchar(16) NOT NULL,
  `gender` enum('M','F') NOT NULL,
  `hire_date` date NOT NULL,
  PRIMARY KEY (`emp_no`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
 



mysql> select count(*) from employees;
+----------+
| count(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.00 sec)


mysql> delete from employees;


mysql> flush table employees with read lock;
Query OK, 0 rows affected (0.01 sec)

we can directly scan the table .ibd file ,and find the deleted records:

select add .ibd from menu start

input the deleted table's ibd location

right click the ibd file , scan it

click the first page node , and input create table sql

choose the right mysql version , and page format (dynamic in most version)

select the Deleted Data Tab , then you can check deleted records and export them into MYSQLDUMP format SQL file.

You can also use conventional recovery mode in this case.

https://youtu.be/dVMO0YYcJoo

Answer 4

For InnoDB tables, if binary logs are enabled, one could use:

mysqlbinlog path_to_binary_log_file > query_log.sql

If binary logs are not enabled, there is a recovery tool provided by Percona which may help.

Created a script to automate the steps from the Percona tool which recovers the deleted rows (if exist) and provides SQL queries to load the data back into database.

Please note:

The time between the deletion of rows and the database stop is crucial. If pages are reused you can’t recover the data.

PS: the DBRecover Tool by @ParnassusData is cool and much easier IMO