how to recover deleted rows from MYSQL database

Question

Today morning I surprisingly found that some data is deleted form my MYSQL database, I am unable to find that data in any table.

Please suggest how to recover data from MySQL database, DOES MYSQL keeps any Log if yes where it is located? Please suggest any query to get all records.

I am using MYSQL Workbench 5.

happy to inform you i have replaced mytable.ibd files , i found those by recovery tool , i found my data but unfortunately MuSql is not starting , please suggest — Rohit Jape, Jun 30 '16 at 04:49

Pritam Banerjee · Answer 1 · 2016-06-29T16:30:22.367

5

If you have binary logs active on your server then you can use mysqlbinlog

You can use the following:

mysqlbinlog binary_log_file > query_log.sql

If you don't have this, then you have probably lost it.

You can look here for more information on how to convert the binary logs to sql.

You can check if binary logging is enabled or not by running the following command:

SHOW VARIABLES LIKE 'log_bin';

edited Jun 29 '16 at 16:30

answered Jun 29 '16 at 07:38

Pritam Banerjee

17,953
10
93
108

2

more info here: http://dev.mysql.com/doc/refman/5.7/en/mysqlbinlog.html – Adi Azarya Jun 29 '16 at 07:41
does mysql bydefault activates Binary log at the time of installation .? – Rohit Jape Jun 29 '16 at 08:53
@RohitJape Yes, you can. You can do it by typing `SHOW VARIABLES LIKE 'log_bin';` – Pritam Banerjee Jun 29 '16 at 16:29
happy to inform you i have replaced mytable.ibd files , i found those by recovery tool , i found my data but unfortunately MySql is not starting , please suggest – Rohit Jape Jun 30 '16 at 04:50
@RohitJape Check this answer – Pritam Banerjee Jun 30 '16 at 16:16
pl upload link again – Rohit Jape Jul 01 '16 at 09:51

score 0 · Answer 2 · answered May 19 '22 at 10:36

For InnoDB tables, 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 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.

how to recover deleted rows from MYSQL database

2 Answers2