[DEV] Partial DB Backup

A place for MOD Authors to post and receive feedback on MODs still in development. No MODs within this forum should be used within a live environment! No new topics are allowed in this forum.
Forum rules
READ: phpBB.com Board-Wide Rules and Regulations

IMPORTANT: MOD Development Forum rules

On February 1, 2009 this forum will be set to read only as part of retiring of phpBB2.
Post Reply
User avatar
Nux
Registered User
Posts: 280
Joined: Tue May 18, 2004 3:14 pm
Location: 3cities, Poland
Contact:

[DEV] Partial DB Backup

Post by Nux »

This is an idea for a MOD that will be very selective what to backup from the database. I'm especially thinking to backup posts, PMs, topics and such (not sure if there is anything else), to backup from some date till now.

This is meant to be a backup utility for big forums that have big DB backup files, but still want to keep it safe or even very safe.

Maybe some cron-like (or just cron) utility will be given to backup daily or hourly things that change frequently - like posts, topics and PMs.

I will think of integrating some existing MODs, but I want to keep it working for all users that can use the DB backup already. To give the ability to be DB unrelaint I'm thinking of integrating this somehow with EasyMOD (or to be more exact with SQL parser) so it can retrieve any important table changes.

Users (admin only of course) should also have the possibility to add tables that they want to backup by default and those to backup daily and to backup partially (a field name containing the date in PHP format should be given here).

First version (planned) features:
  • choose to backup from date posts, PMs, topics
    (last edited time should be taken into account)
    (problem: admin/mod edits to posts do not evaluate last edit?)
  • choose to omit search and session tables (as in lightbackup)
    (not really important tables: 'search_results', 'search_wordlist', 'search_wordmatch', 'sessions' )
  • add the date to file name in nicely sortable order like: db_backup_2006_01_31.sql
    (this format should be changeable in the future)
First version will be realeased in no time soon - sorry about that.

I want to change as less as possible so (as written above) it will work for any databases that it works now.

If there will be a feature to save the generated file on server, then it will add some random (+ md5) codes to the file name. This way you can load through FTP at any time without fear of it being downloaded by someone else.

And now... Give me some feature requests :).
Last edited by Nux on Tue Jun 27, 2006 6:35 pm, edited 1 time in total.
My page: DOM, Javascript, SQL - tips&scripts (mostly in Polish, sorry ;))
User avatar
jeevan25
Registered User
Posts: 388
Joined: Thu Dec 30, 2004 2:45 am

Post by jeevan25 »

seems nice.
User avatar
Ramon Fincken
Registered User
Posts: 4835
Joined: Thu Oct 14, 2004 1:04 am
Location: NL, The Netherlands Amsterdam area @GMT +1
Contact:

Post by Ramon Fincken »

request..

a backup of the config table WITHOUT the VALUES of server path and cookie settings
to assure better transfer to other host
Make the config table sql so that it will only UPDATE the existing values, so no drop table

Rfn
Dutch quality fully managed WordPress hosting - ManagedWPHosting.nl

Before changing a file, some code or installing a MOD >> Make a backup first!

Do you like my mods? paypal me $1 :) forumsoftware[AT}creativepulses[DOT}nl [/size]
PhpBBantispam.com || Instant find your mod here
User avatar
Nux
Registered User
Posts: 280
Joined: Tue May 18, 2004 3:14 pm
Location: 3cities, Poland
Contact:

Post by Nux »

  1. A backup of the config table WITHOUT the VALUES of server path and cookie settings - to assure better transfer to other host.
    Not sure if that would be a good idea...
    - If someone would restore on an empty board then he would get errors that he probably wouldn't be able to solve.
    - If someone would have the full backup and some test board, then he would only need to use the Auto cookies MOD.
    But I'm not saying no here.
  2. Make the config table sql so that it will only UPDATE the existing values, so no drop table.
    Updates instead of dropping would be interesting... I think I could make insert that would fail quietly while restoring an existing field (will still have to check DB specific queries).
    Would that be OK :?:
My page: DOM, Javascript, SQL - tips&scripts (mostly in Polish, sorry ;))
User avatar
Ramon Fincken
Registered User
Posts: 4835
Joined: Thu Oct 14, 2004 1:04 am
Location: NL, The Netherlands Amsterdam area @GMT +1
Contact:

Post by Ramon Fincken »

Nux wrote:
  1. A backup of the config table WITHOUT the VALUES of server path and cookie settings - to assure better transfer to other host.
    Not sure if that would be a good idea...
    - If someone would restore on an empty board then he would get errors that he probably wouldn't be able to solve.
    - If someone would have the full backup and some test board, then he would only need to use the Auto cookies MOD.
    But I'm not saying no here.
  2. Make the config table sql so that it will only UPDATE the existing values, so no drop table.
    Updates instead of dropping would be interesting... I think I could make insert that would fail quietly while restoring an existing field (will still have to check DB specific queries).
    Would that be OK :?:



1 yes that's absolutely true.
2 is needed to forfill 1


Let me explain a bit further..
This option would come in handy if you have 2 boards..
One live board and a test/dev board.

When you want to update the live board with test board data or vice versa on normal circumstances ( when you have a sql dump for the config table ) all values will be overwritten by the drop down.

And worst is ... this means the server/path for the files and cookie settings are ruined in most cases..

This option would be a 'transfer essential data' for the config table.
Not for only backup-usage because ... it misses the paths of board and cookies...


I hope I explained this a bit more then in my previous post :)


You know where to find me ;)

Rfn
Dutch quality fully managed WordPress hosting - ManagedWPHosting.nl

Before changing a file, some code or installing a MOD >> Make a backup first!

Do you like my mods? paypal me $1 :) forumsoftware[AT}creativepulses[DOT}nl [/size]
PhpBBantispam.com || Instant find your mod here
Mercuree
Registered User
Posts: 49
Joined: Tue Apr 08, 2003 5:37 am

Post by Mercuree »

great idea.
i suggest to have option to backup selective forums.
Last edited by Mercuree on Mon Mar 27, 2006 8:10 am, edited 1 time in total.
User avatar
Ramon Fincken
Registered User
Posts: 4835
Joined: Thu Oct 14, 2004 1:04 am
Location: NL, The Netherlands Amsterdam area @GMT +1
Contact:

Post by Ramon Fincken »

Mercuree wrote: great idea.
i suggest to have option to backup selective forums.


selective forums?
so you want to backup some forums and leave some 'unbackupped' ?

This can be a problem with the search index, but per haps Nux can help you out :)

Rfn
Dutch quality fully managed WordPress hosting - ManagedWPHosting.nl

Before changing a file, some code or installing a MOD >> Make a backup first!

Do you like my mods? paypal me $1 :) forumsoftware[AT}creativepulses[DOT}nl [/size]
PhpBBantispam.com || Instant find your mod here
User avatar
Nux
Registered User
Posts: 280
Joined: Tue May 18, 2004 3:14 pm
Location: 3cities, Poland
Contact:

Post by Nux »

Mercuree wrote: great idea.
i suggest to have option to backup selective forums.

I'll think about that, but why do you want this ability? I'm asking because I might be able to achieve this in some other way.


For others interested in this MOD: I just noticed that I'll have to include users to the partial backup as there might be quite few of them that will join the forum. Unfortunately there is no way to tell if someone changed his profile...

There are more problems like that so generally full backup (optionally disregarding session tables and such) will still be advised. That is unless I (or someone else) will think of some easy way to watch other tables for any changes. There would be a possibility to collect some info on that, but I don't see any other way then change all files that change phpBB tables in any way and that is something I would like to avoid.

EDIT: OK. I'll just skip that problem and on first version backup will include most tables plus topics, posts and PMs will just be included from some date.
My page: DOM, Javascript, SQL - tips&scripts (mostly in Polish, sorry ;))
User avatar
Nux
Registered User
Posts: 280
Joined: Tue May 18, 2004 3:14 pm
Location: 3cities, Poland
Contact:

Post by Nux »

After few more thoughts on that...

There will be four suggested ways to backups:
1. Full backup - ment to be done yearly (or monthly on big forums)
2. Partial-important backup - ment to be done monthly (or weekly on big forums)
3. Partial-dated+ backup - ment to be done weekly (or daily on big forums)
4. Partial-dated only backup - ment to be done daily (or hourly on big forums)

"Full backup" - the same as is done now.
"Partial-important backup" - full backup of important tables (not easily recreateable)
"Partial-dated+ backup" - backup of some important tables and the rest of them will be backed up only if changed (that is not a stric rule, because moderator changes are not registered, though this can be easily changed)
"Partial-dated only backup" - this wil save all tables that are considered to change daily, this a little bigger set then the above mentioned tables.

Details (please confirm if you agree):

Code: Select all

			$tables_unimportant = array('search_results', 'search_wordlist', 'search_wordmatch', 'sessions', 'sessions_keys', 'confirm');
			// used for light backup
			$tables_important = array('auth_access', 'banlist', 'categories', 'config', 'disallow', 'forums', 'forum_prune', 'groups', 'posts', 'posts_text', 'privmsgs', 'privmsgs_text', 'ranks', 'smilies', 'themes', 'themes_name', 'topics', 'topics_watch', 'user_group', 'users', 'vote_desc', 'vote_results', 'vote_voters', 'words');
			// for full backup
			$tables = array_merge($tables_unimportant, $tables_important);
			
			// for dated plus (dated part)
			$tables_dated_plus = array(
				'users', 'user_group', 'groups',// considered values: user_regdate, user_lastvisit, user_session_time
				'posts', 'posts_text',			// considered values: post_time, post_edit_time
				'privmsgs', 'privmsgs_text',	// considered value - privmsgs_date
				'topics',						// considered value - topics_time
			);
			// for dated plus
			$tables_dated_plus = array_merge($tables_dated_plus, $tables_important);
			
			// for dated only
			$tables_dated_only = array(
				'vote_desc', 'vote_results', 'vote_voters',	// all (no easy way to find out what was changed)
				'users', 'user_group', 'groups',// considered values: user_regdate, user_lastvisit, user_session_time
				'posts', 'posts_text',			// considered values: post_time, post_edit_time
				'privmsgs', 'privmsgs_text',	// considered value - privmsgs_date
				'topics',						// considered value - topics_time
			);
I'll explain my notation on the example of users:

Code: Select all

'users', 'user_group', 'groups',// considered values: user_regdate, user_lastvisit, user_session_time
Considered values means that if a value of time in that field is bigger then the last backup time then this will be saved.

So fields from tables: 'users', 'user_group', 'groups' will be backed up if user_regdate or user_lastvisit or user_session_time will be greater (or equal) then given last backup time.
My page: DOM, Javascript, SQL - tips&scripts (mostly in Polish, sorry ;))
Alexander the Great
Registered User
Posts: 109
Joined: Sun Sep 25, 2005 2:35 am
Location: Lee's Summit, MO, USA

Post by Alexander the Great »

How about a backup that only does the users and their information? Oh, and how about one that does everything but topics and posts?
User avatar
Nux
Registered User
Posts: 280
Joined: Tue May 18, 2004 3:14 pm
Location: 3cities, Poland
Contact:

Post by Nux »

Alexander the Great wrote: How about a backup that only does the users and their information? Oh, and how about one that does everything but topics and posts?
In advanced optionts maybe.

Currently I'm trying to figure out how to support Postgre with the lack of REPLACE statement... Anyone have any ideas?
My page: DOM, Javascript, SQL - tips&scripts (mostly in Polish, sorry ;))
User avatar
Nux
Registered User
Posts: 280
Joined: Tue May 18, 2004 3:14 pm
Location: 3cities, Poland
Contact:

Post by Nux »

I think I'll be releasing BETA in April, but it will probably not be fully functionall for PostgreSQL. That's because I wanted the updates to work one by one, so that all previous data in the new backup most be overwritten... I'm thinking of just a simple solution:
DELETE row
INSERT row
but it would make files a lot bigger...

What do you think about it?
My page: DOM, Javascript, SQL - tips&scripts (mostly in Polish, sorry ;))
User avatar
Nux
Registered User
Posts: 280
Joined: Tue May 18, 2004 3:14 pm
Location: 3cities, Poland
Contact:

Post by Nux »

Haven't been here for a while... April wasn't the best of months I guess. And then I've been working on other projects and on my studies and some other stuff...

Anyway since the day still gets only 24 hours from father time ;), and the interest on this MOD is not what I would expect from the first posts. I just won't be able to finish this in any time soon, sorry.

The trivia part is done from some time (file naming, and choosing to omit some tables). The main problem of this MOD for me is how to make it safe for users not knowing much about databases. The second one - how to make it work with PostgreSQL the same way it would for MySQL. If anyone has any ideas about those issues then you may send me a PM or write about this here.

Vacations are slowly coming, but it looks like they will be pretty busy too, so I can't promise any progress with this MOD.

Oh, I someone want to takeover the idea then I don't have nothing against it.
My page: DOM, Javascript, SQL - tips&scripts (mostly in Polish, sorry ;))
Post Reply

Return to “[2.0.x] MODs in Development”