Page 1 of 1

*IMPORTANT* database rollback

Posted: Sat Jan 04, 2014 6:18 am
by BuZz
Due to a moderartion accident that involved three thread deletions, the forum database was rolled back to the last backup (18:04 GMT, 2014-01-03).

I have a backup from the database before the older backup was restored so I could manually restore the 2 posts and any PMs that were made between 6pm 2014-01-03 and 05:05 2014-01-04. Let me know if you want me to do this. Apologies for any inconvenience caused by this.

Re: *IMPORTANT* database rollback

Posted: Sat Jan 04, 2014 10:41 am
by jogglerhase
Hi BuZz,
I am missing at least 1 important pm from hawsey from yesterday with some parcel tracking info - could you restore that?
Thanks for your help!
Heiner

Re: *IMPORTANT* database rollback

Posted: Sat Jan 04, 2014 12:40 pm
by BuZz
I have restored PM's to how they were as there were a a handful made since the earlier backup. Please confirm you have the missing PMs. thanks

Re: *IMPORTANT* database rollback

Posted: Sat Jan 04, 2014 3:34 pm
by jogglerhase
Yes - its there again!
Thank you, BuZz

Re: *IMPORTANT* database rollback

Posted: Sun Jan 05, 2014 3:23 pm
by Error
BuZZ, I,m missing the thread about 24/9696 playing files?
could it have something to do with the forum rollback?

http://www.jogglerwiki.com/forum/viewto ... ed+digital

this link

Re: *IMPORTANT* database rollback

Posted: Sun Jan 05, 2014 3:37 pm
by BuZz
Thanks for the heads up. Yep there have been more topics deleted in december also :/

I keep database backups for 8 weeks, so I will get an older database and attempt to reconstruct manually. this is going to take some time though.

Re: *IMPORTANT* database rollback

Posted: Sun Jan 05, 2014 4:16 pm
by BuZz
Ok, so not too bad to get the stuff back. I'll detail here for reference - might be useful to someone. I take backups very seriously, so getting the old database is not a problem. I use http://www.nongnu.org/rdiff-backup/ with 8 weeks of reverse increments (and 2 offsite backups a day).

A look at the moderator logs tells me the topics were accidently removed on 2013-12-28 in the afternoon. My backups run morning and evening, so I did

rdiff-backup -r2013-12-28T07:00:00 username@backupserver::path/to/database/backup.sql.gz /root/backup.sql.gz

I then loaded a temporary database with the old backup. A quick look at the phpbb schema and documentation tells me I need to restore

Code: Select all

phpbb_topics
phpbb_posts
phpbb_attachments
(excluding things like topic subscriptions etc which are not important)

the moderator logs tell me the topic title. So for each topic I did a

Code: Select all

select topic_id from temp.phpbb_topics where topic_title="title we want";
insert into liveforum.phpbb_topics select * from temp.phpbb_topics where topic_id=topic id above;
insert into liveforum.phpbb_posts select * from temp.phpbb_posts where topic_id=topic id above;
insert into liveforum.phpbb_attachments select * from temp.phpbb_attachments where topic_id=topic id above;
I also got the name of the attachments as I will need to restore them from backup also (not yet done)

Code: Select all

select physical_filename from temp.phpbb_attachments where topic_id
phew. everything seems ok. I will go through the moderator a log a bit more and make sure i didn't miss anything.

Re: *IMPORTANT* database rollback

Posted: Sun Jan 05, 2014 4:30 pm
by BuZz
All attachments have now also been restored.

Re: *IMPORTANT* database rollback

Posted: Thu Jan 09, 2014 5:32 pm
by pete
It was me that caused the grief / issues relating to posts being deleted.

I apologize about doing this.

Over the last month or so; with the holidays et al. I would visit the forum more relating to just a quick managing look rather than posting stuff.

That said I got a bit overzealous a few times seeing much spam and tried to delete all of the spam too quickly not looking at what I was doing (er deleting whole posts).

Re: *IMPORTANT* database rollback

Posted: Thu Jan 09, 2014 5:42 pm
by BuZz
No worries Pete. Accidents happen, and no permanent damage was done. Appreciate your help running the forum :)