Kinda Advanced MYSQL Restoration Question re Vanilla User's Posts...

Andy K

Hey all, a funny thing happened at my community.

So, one of the users had a total flameout regequit meltdown, and instead of making like a Really Loud Angry Post and fucking off, he decided to go in and manually search for every single one of his comments he had ever posted (there were exactly 1000), edited them and erased them. He's banned and all.

Anyway, my thing is this: I've got a SQL dump of the entire Vanilla DB from about last Thursday or so. I don't want to overwrite everything, because there's been tons of interesting discussions since then.

But I was wondering if anyone happened to have any suggestions, like a way to somehow take a dump of my database now, do some sort of comparison and "SQUIGGLEDY FLABBIDY DOO!" restore that one dude's posts up until last thursday, while preserving all new discussions and comments that have happened since by all the other users.

Possible? Stupid-hard? In the end, I'm fine with letting it die if it's too difficult/time-consuming, but he did have some interesting topics and discussions way back when, before he became an Internet Tough Guy and all.

Thanks!

WallPhone

It would be a piece of cake to restore the dump to a new DB, delete everything else but his comments you want to keep, then merge that with the existing db. Since each of his posts can be identified by his user ID, you can quickly separate them out.

Andy K

Interesting. I'll take a look at doing it in my "hobby free time" (there's no rush, really).

BTW, any recommended methodology for separating/merging with the existing DB? (I pretty much do everything through textpad and phpMySQL, but have no problems with other tools as well)

Thanks-
Andy

WallPhone

First thing I would do is see if its possible to edit the dump to only restore the lum_comment table (large files tend to choke many text editors--a Unix guru could probably chop the unneeded parts of the dump right at the command line without blinking)

Then I would restore the table to a new database and run some SQL on it:DELETE FROM LUM_Comment WHERE UserID <> 456

This would delete everything from the table except for 456's comments. Then make a SQL dump of that table, and run some find and replace operations on the file to change the SQL commands from INSERT INTOs to be UPDATEs.

Probably the best way to see how that dump should be massaged would be to compare the backup of a post to the old data (CommentIDs should both match) and do a update manually in phpMySQL. Copy the command phpMySql shows you was performed, and figure out what replacements should be made to change every line in the dump to look like that one.

Next, make a current backup of your live system's comment table, restore that backup on your new database, and run your script against the backup. If that checks out OK, then cross your fingers and run the update script against your live system.

(OK, maybe it's not a piece of cake, but its not impossible)