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

Solution 1

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

Solution 2

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

Solution 3

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.

Solution 4

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

Solution 5

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.

View more solutions