How can I roll back my last delete command in MySQL

Question

I accidentally deleted some huge number of rows from a table     How can I roll it back   I executed the query using PuTTY   I ll be grateful if any of you can guide me safely out of this

User · Answer

The accepted answer is not always correct. If you configure binary logging on MySQL, you can rollback the database to any previous point you still have a snapshot and binlog for.

7.5 Point-in-Time (Incremental) Recovery Using the Binary Log is a good starting point for learning about this facility.

User · Answer

A  rollback  only works if you used transactions  That way you can group queries together and undo all queries if only one of them fails   But if you already committed the transaction  or used a regular DELETE-query   the only way of getting your data back is to recover it from a previously made backup

User · Answer

Use the BEGIN TRANSACTION command before starting queries  So that you can ROLLBACK things at any point of time   FOR EXAMPLE    begin transaction select   from Student delete from Student where Id 2 select   from Student rollback select   from Student

User · Answer

In MySQL   start transaction   savepoint sp1   delete from customer where ID 1   savepoint sp2   delete from customer where ID 2   rollback to sp2   rollback to sp1

User · Answer

In Oracle this would be a non issue   SQL gt  delete from Employee where id    01    1 row deleted   SQL gt  select id  last name from Employee where id    01    no rows selected  SQL gt  rollback   Rollback complete   SQL gt  select   from Employee  where id    01    ID   FIRST NAME LAST NAME  START DAT END DATE      SALARY CITY       DESCRIPTION ---- ---------- ---------- --------- --------- ---------- ---------- --------------- 01   Jason      Martin     25-JUL-96 25-JUL-06    1234 56 Toronto    Programmer

User · Answer

If you didn t commit the transaction yet  try rollback  If you have already committed the transaction  by commit or by exiting the command line client   you must restore the data from your last backup

User · Answer

If you want rollback data  firstly you need to execute autocommit  0 and then execute query delete  insert  or update   After executing the query then execute rollback

User · Answer

If you haven t made a backup  you are pretty much fudged

User · Answer

I also had deleted some values from my development database  but I had the same copy in QA database  so I did a generate script and selected option  type of data to script  to  data only  and selected my table   Then I got the insert statements with same data  and then I run the script on my development database

User · Answer

Rollback normally won t work on these delete functions and surely a backup only can save you   If there is no backup then there is no way to restore it as delete queries ran on PuTTY Derby using  sql files are auto committed once you fire the delete query

[mysql] How can I roll back my last delete command in MySQL?

Examples related to mysql

Examples related to sql

Examples related to sql-delete

Examples related to rollback