*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: 1460
Joined: Fri Mar 04, 2011 1:15 am

*IMPORTANT* database rollback

Post 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.
jogglerhase
Posts: 174
Joined: Wed Aug 08, 2012 3:02 pm

Re: *IMPORTANT* database rollback

Post 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
User avatar
BuZz
Site Admin
Posts: 1460
Joined: Fri Mar 04, 2011 1:15 am

Re: *IMPORTANT* database rollback

Post 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
jogglerhase
Posts: 174
Joined: Wed Aug 08, 2012 3:02 pm

Re: *IMPORTANT* database rollback

Post by jogglerhase »

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 »

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: 1460
Joined: Fri Mar 04, 2011 1:15 am

Re: *IMPORTANT* database rollback

Post 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.
User avatar
BuZz
Site Admin
Posts: 1460
Joined: Fri Mar 04, 2011 1:15 am

Re: *IMPORTANT* database rollback

Post 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.
User avatar
BuZz
Site Admin
Posts: 1460
Joined: Fri Mar 04, 2011 1:15 am

Re: *IMPORTANT* database rollback

Post by BuZz »

All attachments have now also been restored.
User avatar
pete
Posts: 2950
Joined: Mon Aug 01, 2011 6:33 am
Location: Time Traveler

Re: *IMPORTANT* database rollback

Post 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).
- Pete
O2 Jogglers running EFI Ubuntu / Squeezeplayer
OpenPeak Voip Telephony / Zigbee tabletops hardware modded with Seabios / RTC / Ethernet ROM edits / SSD drives running XPe for automation screens

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

Re: *IMPORTANT* database rollback

Post by BuZz »

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