CODEWITHSUNDEEP
mysqlsqldatabasemysql-workbench
Rohit Jape
Rohit Jape

Reputation: 79

how to recover deleted rows from MYSQL database

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.

Upvotes: 3

Views: 16461

Answers (2)

VanagaS
VanagaS

Reputation: 3698

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.

Upvotes: 0

Pritam Banerjee
Pritam Banerjee

Reputation: 18923

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';

Upvotes: 6

Related Questions