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

Although it may already be late, but also check out Phil's answer regarding binary logging. — Bahram Ardalan, Nov 12 '19 at 09:23

score 107 · Answer 1 · edited Dec 29 '18 at 09:04

107

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

edited Dec 29 '18 at 09:04

Peter Mortensen

30,738
21
105
131

answered Mar 01 '10 at 14:50

Omry Yadan

31,280
18
64
87

5

So there is no way to undo that .... Oops i did it wrong.. I should be using the select query to list what i should be deleting before actually executing the query.. A nice lesson leant to be more cautious – Vijay Mar 01 '10 at 15:15
3

sadly that's the case. and yes, it's always a good idea to verify with select before using delete or update. – Omry Yadan Mar 01 '10 at 15:49
5

This is one of the flaws of standard databases: They have no history, just a huge memory of "now". – Aaron Digulla Mar 01 '10 at 15:58
41

I would argue that it's by design. keeping a history is very expensive. you want your database to be fast, not forgiving. – Omry Yadan Mar 01 '10 at 16:25
@OmryYadan but that's how you undo all queries since the last `commit`, right? `rollback`? – Ky - Apr 01 '14 at 15:57
@Supuhstar, rollback is a mechanism to achieve consistency, not undo. yes, not committing will "undo", but no on is deleting a table in a transaction, just in case he will want to undo before he commits. the downside is that you can't really test what you have done before the commit (changes will not be visible). in addition, some actions will autocommit (http://dev.mysql.com/doc/refman/5.6/en/drop-table.html) – Omry Yadan Apr 09 '14 at 04:03
There is a bug in my program. I'm currently don't know where is it. Somewhere around in the application will wipe off 50% columns' of data in specific table. OMG, I should have scheduled a daily backup. – mjb Oct 13 '14 at 04:02
Or you could just use the binlog and restore to just before that delete. – Phil Lello Feb 16 '15 at 16:54
@PhilLello, that would only work if you actually kept all your binlogs ever. this is highly unlikely to be true in production systems. – Omry Yadan Apr 21 '15 at 22:43
If all else fails, check the ~/.mysql_history file and you may be able to recreate your SQL statements from there. – Michael Singer Apr 06 '17 at 17:48

Aaron Digulla · Accepted Answer · 2021-04-26T12:27:59.270

38

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

edited Apr 26 '21 at 12:27

answered Mar 01 '10 at 14:52

Aaron Digulla

321,842
108
597
820

by default the command line client uses auto commit after each command. – stmax Mar 01 '10 at 15:06
2

@Vijay: Just type "rollback;" + ENTER – Aaron Digulla Mar 01 '10 at 15:57
1

this would indeed work.. but if and only if he explicitly used transactions while executing his delete query, otherwise he cannot 'rollback'. – dominicbri7 Aug 05 '13 at 15:25
3

This should be accepted answer and not the current accepted answer. The current is basically saying "you're screwed". – Sticky Sep 22 '16 at 19:23
@AaronDigulla Your answer need to be updated. The rollback won't work if autocommit is enabled and enabled autocommit is the default option. If auto commit disabled ('SET autocommit=0' ) before an accident occurs then the 'rollback will work'. Please updated your answer as your answer is an accepted answer and people may get confussed. – Shamsul Arefin Apr 09 '21 at 14:54

score 13 · Answer 3 · edited Feb 07 '12 at 12:00

13

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.

edited Feb 07 '12 at 12:00

jbatista

2,747
8
30
48

answered Mar 01 '10 at 14:54

Select0r

12,234
11
45
68

score 9 · Answer 4 · edited Dec 29 '18 at 09:12

9

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

edited Dec 29 '18 at 09:12

Peter Mortensen

30,738
21
105
131

answered Aug 20 '16 at 12:39

Praveen Patel G

342
4
13

3

In my MySQL Shell, it is START TRANSACTION rather than BEGIN TRANSACTION – LotiLotiLoti Jan 19 '19 at 10:42

score 4 · Answer 5 · edited Dec 29 '18 at 09:11

4

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.

edited Dec 29 '18 at 09:11

Peter Mortensen

30,738
21
105
131

answered Feb 16 '15 at 16:57

Phil Lello

8,377
2
25
34

I've a doubt. if binary logging is enabled, i think it will have cover databases under a mysql server. So the things we have to consider, 1. if we use this option to recover for one db, we end up recovering for all 2. And I'm right, even though we use binary log for point-in-time recovery, it helps when we have a backup of the server/db to start restore process.. – Vijay Jun 24 '16 at 04:46

score 4 · Answer 6 · edited Dec 29 '18 at 09:15

4

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;

edited Dec 29 '18 at 09:15

Peter Mortensen

30,738
21
105
131

answered Mar 18 '18 at 19:58

silly

887
9
9

score 0 · Answer 7 · edited Dec 29 '18 at 09:12

0

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

edited Dec 29 '18 at 09:12

Peter Mortensen

30,738
21
105
131

answered Aug 04 '16 at 05:53

Kishore Kishore

1

score 0 · Answer 8 · edited Dec 29 '18 at 09:14

0

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.

edited Dec 29 '18 at 09:14

Peter Mortensen

30,738
21
105
131

answered Oct 18 '17 at 13:30

Nikki

409
1
5
15

score -1 · Answer 9 · edited Dec 29 '18 at 09:26

-1

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.

edited Dec 29 '18 at 09:26

Peter Mortensen

30,738
21
105
131

answered Aug 21 '13 at 11:30

ram

149
1
4
10

score -1 · Answer 10 · edited Dec 29 '18 at 09:14

-1

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

edited Dec 29 '18 at 09:14

Peter Mortensen

30,738
21
105
131

answered Mar 01 '18 at 16:53

Andreas

384
1
3
9

You can do the same in any SQL database that supports transactions. It's not clear from the OP's question, but I assume they had already committed the transaction, and it's too late to rollback. – Bill Karwin Mar 01 '18 at 16:57

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

10 Answers10

Linked