Emergency data recovery in the mysql table with minimal time loss

Good day habrachiteli. Not long ago, an incident occurred at work that spoiled the nerves and led to lengthy arguments. Essence: when updating the record in the database, mysql forgot to set the where condition and as a result all the records in the table changed.

How this could have been avoided:

1. Always carefully re-read the request on the working server before starting (it can save ... but ... it may not save)
2. At the end of the update requests, always set limit 1, unless otherwise required (Saves, but a matter of habit, besides, all the same, then look for the correct line).
3. Use a special utility like phpmyadmin (perhaps the most rational, but not always available)
4. Always do a dump. (And this, in theory, is mandatory, even if the remaining points are observed).

If there is a dump, then we are saved and we can restore the table. This is where the main part of my article begins.

So what we have:

1. Working base with incorrect data
2. Dump the correct database, without data for a certain period (past since the creation of the dump)
3. The time during which the base continues to work, thereby creating errors and adding data that is not in the dump.

Everything is overshadowed by the fact that the database contains foreign keys, i.e. and its id is used in other databases and it uses.
And watching this situation, I begin to quietly go into hysterics, because there is no time, and the solution will take a lot of time to do everything correctly and in the end I may lose some of the new data (it was a table with orders)
But in the end I got ready, thought for a while and found a solution that can help in such situations.

Essence: Instead of reloading the table, we update its values ​​using old data from the dump.

Specifically:

1. Making a copy of the dump
2. We edit a copy of the dump, delete the data on all tables except the necessary one.
3. Replace all occurrences of the name of the old table with a new one (for example, temp_name of the old table)
4. Download the corrected copy of the dump
5. Execute the query:
   update old_table_name t1 join new_name of table t2 on (t1.id = t2.id) set t1.field_name_adjust data = t2.name_ of this_most field in the second table;
6. Delete the new table with the changed name.

Total:

The keys are not broken, the old data is restored, the new data, if damaged, then has at least some of the information, the work has not been interrupted, everything is done fairly quickly.
I hope this story will help someone in this situation.