Extracting data from backups to restore selected rows from MySQL tables
Archive - Originally posted on "The Horse's Mouth" - 2015-05-01 21:41:41 - Graham Ellis
We take backups from our live server, using mysqldump for the databases, and tar / gzip for most of the file systems. And when we had a server failure which meant a reload (and a new underlying OS too), we were able to be back online in a couple of hours, with minimal data loss.
But our backups are not only taken as a guarantee against complete failure, but also to allow us to step back to a specific checkpoint if we need to, or indeed to do a partial restore. And with the backups being huge data files, the extraction and management of what we want to pull back, and how to merge it onto the live and continuing system, can be an interesting challenge. And it's a challenge we have very very rarely had to face. However, it came up yesterday when one of our moderators accidentally deleted a thread - some 35 posts - on a very active board that continued to roll after the trip-up and couldn't simply be wound back.
So - here's the fix / mechanism used for selective restore:
1. Take another backup before you start, and warn users the may see a service dip.
2. Create a new database and restore the latest backup into it
create database test_fgw
use test_fgw
source latestbackupfile.sql
3. locate the missing records, and copy them into new tables with a structure that mirrors the original structure
create table ref_15686 select * from smf_messages where id_topic = 15686;
create table topic_15686 select * from smf_topics where id_topic = 15686;
4. Dump the newly created tables which have the structure of the whole table, but only the missing records in them
5. Edit the dump, changing 'insert into ref_15686' into 'insert into smf_messages', and so on
6. merge in the records being restored
use fgw
source partial.sql
7. Test thoroughly, and when you are sure it's worked delete the test batabase.
This worked for us. But beware - if the tables / records being restored aren't uniquely keyed, or if your software reallocated keys from deleter records for new records, it won't work. Note also the need to ensure that joining keys, etc, may need to be restored - in my case this was the second table in my example above.
Please understand what youre doing when following a procedure like this - the opportunity to really screw up is ever-present!