CODEWITHSUNDEEP
mysqlsqlsql-deleterollback
Vijay
Vijay

Reputation: 5433

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

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...

Upvotes: 67

Views: 313021

Answers (10)

Aaron Digulla
Aaron Digulla

Reputation: 328556

If you didn't commit the transaction yet, try rollback. If you have already committed the transaction (by manually execiting commit or by exiting the command line client or when the option autocommit is 1 which is the default), you must restore the data from your last backup.

To prevent things like that in the future, use SET autocommit=0 before any dangerous work. Any changes will be kept inside of your current transaction until you commit them. See https://dev.mysql.com/doc/refman/8.0/en/innodb-autocommit-commit-rollback.html for details

Upvotes: 43

ram
ram

Reputation: 159

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.

Upvotes: -1

silly
silly

Reputation: 947

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;

Upvotes: 6

Andreas
Andreas

Reputation: 392

In Oracle this would be a non issue:

SQL> delete from Employee where id = '01';

1 row deleted.

SQL> select id, last_name from Employee where id = '01';

no rows selected

SQL> rollback;

Rollback complete.

SQL> 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

Upvotes: -1

Nikki
Nikki

Reputation: 441

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.

Upvotes: 0

Praveen Patel G
Praveen Patel G

Reputation: 362

Use the BEGIN TRANSACTION command before starting queries. So that you can ROLLBACK things at any point of time.

FOR EXAMPLE:

  1. begin transaction
  2. select * from Student
  3. delete from Student where Id=2
  4. select * from Student
  5. rollback
  6. select * from Student

Upvotes: 11

Kishore Kishore
Kishore Kishore

Reputation: 1

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...

Upvotes: 0

Phil Lello
Phil Lello

Reputation: 8639

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.

Upvotes: 4

Omry Yadan
Omry Yadan

Reputation: 33646

If you haven't made a backup, you are pretty much fudged.

Upvotes: 110

Select0r
Select0r

Reputation: 12628

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.

Upvotes: 13

Related Questions