*IMPORTANT* database rollback

Please post any ideas / changes / or help requests regarding the forum here. Announcements relating to improvements etc will also be posted here.
Post Reply
User avatar
BuZz
Site Admin
Posts: 1444
Joined: Fri Mar 04, 2011 1:15 am

*IMPORTANT* database rollback

Post by BuZz » Sat Jan 04, 2014 6:18 am

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.

jogglerhase
Posts: 140
Joined: Wed Aug 08, 2012 3:02 pm

Re: *IMPORTANT* database rollback

Post by jogglerhase » Sat Jan 04, 2014 10:41 am

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

User avatar
BuZz
Site Admin
Posts: 1444
Joined: Fri Mar 04, 2011 1:15 am

Re: *IMPORTANT* database rollback

Post by BuZz » Sat Jan 04, 2014 12:40 pm

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

jogglerhase
Posts: 140
Joined: Wed Aug 08, 2012 3:02 pm

Re: *IMPORTANT* database rollback

Post by jogglerhase » Sat Jan 04, 2014 3:34 pm

Yes - its there again!
Thank you, BuZz

Error
Posts: 243
Joined: Wed Sep 18, 2013 2:13 pm

Re: *IMPORTANT* database rollback

Post by Error » Sun Jan 05, 2014 3:23 pm

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

User avatar
BuZz
Site Admin
Posts: 1444
Joined: Fri Mar 04, 2011 1:15 am

Re: *IMPORTANT* database rollback

Post by BuZz » Sun Jan 05, 2014 3:37 pm

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.

User avatar
BuZz
Site Admin
Posts: 1444
Joined: Fri Mar 04, 2011 1:15 am

Re: *IMPORTANT* database rollback

Post by BuZz » Sun Jan 05, 2014 4:16 pm

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.

User avatar
BuZz
Site Admin
Posts: 1444
Joined: Fri Mar 04, 2011 1:15 am

Re: *IMPORTANT* database rollback

Post by BuZz » Sun Jan 05, 2014 4:30 pm

All attachments have now also been restored.

User avatar
pete
Posts: 2654
Joined: Mon Aug 01, 2011 6:33 am
Location: Time Traveler

Re: *IMPORTANT* database rollback

Post by pete » Thu Jan 09, 2014 5:32 pm

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).
- Pete

Auto mater

User avatar
BuZz
Site Admin
Posts: 1444
Joined: Fri Mar 04, 2011 1:15 am

Re: *IMPORTANT* database rollback

Post by BuZz » Thu Jan 09, 2014 5:42 pm

No worries Pete. Accidents happen, and no permanent damage was done. Appreciate your help running the forum :)

Post Reply