The probable outcome of a software engineer or database administrator deleting data he didn’t mean to from production database, is immediate heart attack. Hospitals are full of programmers leaving out the ‘where’ clause by mistake.

If you’re reading this post, I assume you would like to know one of two things:

How to restore data you just deleted from your MySQL database. How to avoid deleting data you don’t mean to from production database.

So let’s tackle both of those.

How to restore data deleted from MySQL database by accident?

If you just deleted a couple (of million) records from your production database, you have few options to restore them quickly.

Well, this might be trivial, but I still have to state the obvious - if you have a backup, this is the time to use it. I recommend restoring the backup to a new server / database, and only once you have the data safely in there, you can copy it to the old server. You can decide whether you just want to revert to an old dump, or maybe just copy a few rows from the backup database / replica. If you have binary logs enabled, you can restore the relevant statements from there and re-execute them on your production database. The binary logs keep track of all changes that occur on your database, so if you still have the relevant logs, you should be able to restore the data. If you don’t know whether the binary logs are activated, use this command in the MySQL client shell: SHOW VARIABLES LIKE 'log_bin' ; . Please note that these logs are rotated after a while, so hopefully your data is still there. To extract the statements from an entire binary log file, run this command: mysqlbinlog mysql_bin.000001 | mysql -u root -ppassword database_name

If you want to extract the lost data from several binary log files, use this command: mysqlbinlog mysql_bin.000001 mysql_bin.000002 | mysql -u root -ppassword database_name

If you know when this data was added to the database, you can extract statements by using the relevant timestamps: mysqlbinlog --start-datetime="2017-04-20 10:01:00" \ --stop-datetime="2017-04-20 9:59:59" mysql_bin.000001 \ | mysql -u root -ppassword database_name

Do you want to avoid accidentally deleting production data from the MySQL database?

Preventing an issue is always easier and better than handling it after it happened, so let’s go over a few tips to make it happen: