Tutorial :delete rows from table in mysql



Question:

if i write delete * from tablename in mysql ,all rows get delete it there any way to get the data back in mysql in that table


Solution:1

No, there isn't.

And the correct query is DELETE FROM tablename. (without *)


Solution:2

Restore from backup.


Solution:3

Depends, depends, depends.

I assume you don't have backups. If you do it's better to restore the table from the backup as it was pointed out before.

If the table was MyISAM , then the answer is rather NO than YES. The matter is MyISAM stores data in blocks. The normal block's header is 3 or 4 bytes. The deleted block's header is 20 bytes. So even if you extract records from MYD file the first column (which is your PK in most of cases) will be overwritten. To extract records from MYD file is almost impossible.

If InnoDB, chance is much higher. If MySQL was stopped/killed right after the accident then the chance is close too 100%. InnoDB doesn't delete right away. It marks the records as deleted and will physically purge the records when InnoDB rebuilds B+tree index (read - when you insert new records). So, you have some time. The first thing you'd do in cases like that is stop MySQL immediately, either gracefully or kill -9.

I don't know if I may insert URLs here. Google percona data recovery tool, take the latest revision from the trunk and check mysqlperformance blog (again, google it) for recipes.

Good luck.

UPDATE: Data recovery toolkit moved to GitHub


Solution:4

the easiest way would be to restore your latest backup.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »