[DEV] Auto 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!
Ideas Centre
User avatar
Nelsaidi
Registered User
Posts: 525
Joined: Mon Feb 11, 2008 1:59 pm
Location: London, UK
Contact:

[DEV] Auto DB Backup

Post by Nelsaidi » Mon Mar 23, 2009 10:00 pm

I'm working on a mod which will automatically backup the database every few days. The frequecny of backups will be between 1 and 7 days, and can be defined from the ACP.

So far what I have working is the whole part to backup the database, just integrate it into the ACP and it should be done.

I have to say there are some issues right now which I'm trying to solve (just frequency), thought cant see whats wrong, I'll investigate tomorrow :)

The mod will use a file based cron system, and will have to be activated in common.php by including the class, and from there calling it (ie $adbb->run();). Backups get stored with a 64 character hash (currently 32, but just for the extra bit of security going to make it 64 :P) in the regular store folder, allowing it to be recovered from the ACP. Will be zipped using Gzip.

Currently, only options to be avaiable will enable or disable it, and the backup frequency.

Thanks

Nelsaidi

Ps
Please post any requests or whatever here :)
(will replace this text with the proper layout once i have the beta avaiable for download and move all my notes to the post after this).

EDIT
(lol already) - This will only work with PHP5+, as a)dont have a php4 tesbed (and cba to test it), all classes i right make use of the php5 class/oop system.
Last edited by Nelsaidi on Mon Mar 23, 2009 10:02 pm, edited 1 time in total.
Image
Click here to find out what eRepublik is.

User avatar
Nelsaidi
Registered User
Posts: 525
Joined: Mon Feb 11, 2008 1:59 pm
Location: London, UK
Contact:

Re: [DEV] Auto DB Backup

Post by Nelsaidi » Mon Mar 23, 2009 10:01 pm

// reserved for notes once beta avaiable.
Image
Click here to find out what eRepublik is.

User avatar
Nelsaidi
Registered User
Posts: 525
Joined: Mon Feb 11, 2008 1:59 pm
Location: London, UK
Contact:

Re: [DEV] Auto DB Backup

Post by Nelsaidi » Tue Mar 24, 2009 7:18 pm

Ok, I have pretty much finished a beta which I will be testing, so far all working :)
Just sorting out the modx

Some statistics :

Before: -0.099917

After: -0.100461 (-0.000544)

Common.php run time, on average it takes ~0.0006 seconds to run the cron, which is pretty good :)


EDIT
To actually backup the database would take considerably longer, the above is just to check whether to backup or not. I'm trying to think how I could maybe run it, as if it takes a minute or so to run (ie my 200mb db for forum does) some users may leave.
Last edited by Nelsaidi on Tue Mar 24, 2009 7:44 pm, edited 1 time in total.
Image
Click here to find out what eRepublik is.

Jume
Registered User
Posts: 198
Joined: Tue Jun 22, 2004 9:29 am

Re: [DEV] Auto DB Backup

Post by Jume » Tue Mar 24, 2009 7:24 pm

Sounds good, I'm interested :)

User avatar
Nelsaidi
Registered User
Posts: 525
Joined: Mon Feb 11, 2008 1:59 pm
Location: London, UK
Contact:

Re: [DEV] Auto DB Backup

Post by Nelsaidi » Tue Mar 24, 2009 8:15 pm

Thanks :)
Just sorting out some issues with modX not validating grrr :/ - then will test it with automod and ensure it works.

Need to find a way to not force a user to leave by it taking ~2/3 minutes to load a page when backing up a large db.
Image
Click here to find out what eRepublik is.

opalelement
Registered User
Posts: 315
Joined: Wed Dec 27, 2006 4:05 am

Re: [DEV] Auto DB Backup

Post by opalelement » Wed Mar 25, 2009 8:14 pm

On my board I made a custom user named BackupBot, and created a cURL script that logs him into the board, makes his session an admin session, and then $_POSTs a list of phpBB tables to the database backup module... I set the script to run as a cron job in cPanel, here is the output I make it give if I have it email it to me:

Code: Select all

% Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                Dload  Upload   Total   Spent    Left  Speed

 0     0    0     0    0     0      0      0 --:--:--  0:00:02 --:--:--     0
 0     0    0     0    0     0      0      0 --:--:--  0:00:03 --:--:--     0
 0     0    0     0    0     0      0      0 --:--:--  0:00:04 --:--:--     0
 0     0    0     0    0     0      0      0 --:--:--  0:00:05 --:--:--     0
100  1947    0  1947    0     0    373      0 --:--:--  0:00:05 --:--:--   612
Connected to MySQL
Connected to Database

cURL connection established

SID: e3ca7c43158e6d307bd06fe8bae42ba1
Session associated with BackupBot

Query string being created...
i=database
sid=e3ca7c43158e6d307bd06fe8bae42ba1

mode=backup
action=download
type=data
method=text
where=store
table[]=phpbb_acl_groups
table[]=phpbb_acl_options
table[]=phpbb_acl_roles
table[]=phpbb_acl_roles_data
table[]=phpbb_acl_users
table[]=phpbb_announcement_centre
table[]=phpbb_attachments
table[]=phpbb_banlist
table[]=phpbb_bbcodes
table[]=phpbb_bookmarks
table[]=phpbb_bots
table[]=phpbb_config
table[]=phpbb_confirm
table[]=phpbb_disallow
table[]=phpbb_drafts
table[]=phpbb_extension_groups
table[]=phpbb_extensions
table[]=phpbb_forums
table[]=phpbb_forums_access
table[]=phpbb_forums_track
table[]=phpbb_forums_watch
table[]=phpbb_groups
table[]=phpbb_icons
table[]=phpbb_lang
table[]=phpbb_log
table[]=phpbb_moderator_cache
table[]=phpbb_modules
table[]=phpbb_poll_options
table[]=phpbb_poll_votes
table[]=phpbb_posts
table[]=phpbb_privmsgs
table[]=phpbb_privmsgs_folder
table[]=phpbb_privmsgs_rules
table[]=phpbb_privmsgs_to
table[]=phpbb_profile_fields
table[]=phpbb_profile_fields_data
table[]=phpbb_profile_fields_lang
table[]=phpbb_profile_lang
table[]=phpbb_ranks
table[]=phpbb_reports
table[]=phpbb_reports_reasons
table[]=phpbb_search_results
table[]=phpbb_search_wordlist
table[]=phpbb_search_wordmatch
table[]=phpbb_sessions
table[]=phpbb_sessions_keys
table[]=phpbb_sitelist
table[]=phpbb_smilies
table[]=phpbb_styles
table[]=phpbb_styles_imageset
table[]=phpbb_styles_imageset_data
table[]=phpbb_styles_template
table[]=phpbb_styles_template_data
table[]=phpbb_styles_theme
table[]=phpbb_topics
table[]=phpbb_topics_posted
table[]=phpbb_topics_track
table[]=phpbb_topics_watch
table[]=phpbb_user_group
table[]=phpbb_users
table[]=phpbb_warnings
table[]=phpbb_words
table[]=phpbb_zebra
submit=Submit

cURL connection established

Backup complete.
Not quite sure what the top is, the cron job sticks that in lol

User avatar
Nelsaidi
Registered User
Posts: 525
Joined: Mon Feb 11, 2008 1:59 pm
Location: London, UK
Contact:

Re: [DEV] Auto DB Backup

Post by Nelsaidi » Wed Mar 25, 2009 10:06 pm

Mmmmh, thanks for that opalelement, havent really done cURL, but will look at that :)

Thanks alot :) - would seem the better option thoug tbh, or something, because of the way my functions work, shouldnt be too difucult to sort that if i can do it :)

Also, I'm going to add in an auto purge (purges with database backup, deletes any auto backup and so on)

Reason for the file base cron, not all have access to a cron, thought is better, me for example have my hosting donated my a member of the community as part of a share of his dedi, I dont have remote access and stuff, and my regular nagging makes me feel bad to ask for crons and stuff set up :P .

Rewally appreciate your feedback though, and hmm, email, wonder i it could email the backup itself as a second storage, although this may excede limits, ie my backups are ~30mb zipped, so that could be too much. Another idea is maybe remote backup, ie have it save to an ftp server/external site/something automatically with each backup.

EDIT
Bare in mind, not many hosts offer cURL, for a private mod, I would have used what i have know and cronjobbed it server side and stuff. As I am hoping to release it (but not necesrily use it as it is myself, maybe a 'modded' version of it and cron it.
Image
Click here to find out what eRepublik is.

opalelement
Registered User
Posts: 315
Joined: Wed Dec 27, 2006 4:05 am

Re: [DEV] Auto DB Backup

Post by opalelement » Wed Mar 25, 2009 10:47 pm

cURL is actually installed much more than you would think, typically installed with PHP.

User avatar
Nelsaidi
Registered User
Posts: 525
Joined: Mon Feb 11, 2008 1:59 pm
Location: London, UK
Contact:

Re: [DEV] Auto DB Backup

Post by Nelsaidi » Wed Mar 25, 2009 11:01 pm

Really?
As far as I was aware the majority of hosts offer JUST php, and as cURL has to be compiled with php (ie no longer simply php) not. Better hosts will usually go as far as recompiling PHP to include this sort of stuff under request.

What I want to do is get the server to run it, just figuring out how (ie client visits, tells server to run it, it will be seperate, so no slowdown).

For myself, I'm cronning it up tbh properly.

Im still thinking of the emailing idea, 20mb limits for gmail and stuff, so split up the file and mail it seperatley, hopefully that will be done.
Image
Click here to find out what eRepublik is.

User avatar
Johan-NL
Registered User
Posts: 92
Joined: Thu Aug 07, 2008 8:37 am
Location: Netherlands

Re: [DEV] Auto DB Backup

Post by Johan-NL » Thu Mar 26, 2009 6:42 pm

Is it possible to get it automaticly e-mailed to max. 3 or 5 e-mail addresses? :) If yes, is it also possible to edit the mail in the ACP/in the files? :) Like

Code: Select all

Subject: {SITENAME}'s DB Backup at {DATE}
Message: A new backup is made of your forum. The backup is sent in the attachment. The backup is made at {DATE}. The attachment contains a full backup of the database of {SITENAME}. You can go to the ACP to enable/disable ZIP compression.

This backup was also sent to: {DB_MAIL_LIST}


Something like that :)

User avatar
Nelsaidi
Registered User
Posts: 525
Joined: Mon Feb 11, 2008 1:59 pm
Location: London, UK
Contact:

Re: [DEV] Auto DB Backup

Post by Nelsaidi » Thu Mar 26, 2009 8:06 pm

Problem is I need to split up the database backup, and mail it as multiple emails or something, >10mb will mean it wont be recieved. Writing my own class (well trying to) once i figure out how to offset by bytes and stuff.

Just doing an auto purge now and integrating it into the backup function :) (the backing up itself is done, now adding more stuff to it)

Going to have to make an extra page to the ACP to fit all this stuff tbh, too many stuff ! :O
Image
Click here to find out what eRepublik is.

simvader
Registered User
Posts: 144
Joined: Thu Sep 06, 2007 5:54 pm
Location: Netherlands
Contact:

Re: [DEV] Auto DB Backup

Post by simvader » Thu Mar 26, 2009 8:33 pm

lol, can you give us a sneak preview of the stuff thats coming? :D

opalelement
Registered User
Posts: 315
Joined: Wed Dec 27, 2006 4:05 am

Re: [DEV] Auto DB Backup

Post by opalelement » Thu Mar 26, 2009 8:53 pm

Well what exactly are you emailing to them?

There are only 3 options I can think of:

1. Send them the giant SQL query in an email (big and annoying, would not recommend ever)
2. Send it as an attachment (link) (still a big file but not as annoying to read)
3. (My suggestion) Select the database table into an outfile (link) and just email the link to this file.

Of course, using the last one you would need to make a string of the tables to put into the FROM statement... If you choose that, I can give you the chunk to do that, I had to make one for my cron:)

User avatar
Nelsaidi
Registered User
Posts: 525
Joined: Mon Feb 11, 2008 1:59 pm
Location: London, UK
Contact:

Re: [DEV] Auto DB Backup

Post by Nelsaidi » Thu Mar 26, 2009 8:59 pm

No, email providers have a total message size of 10mb, this is shared by text and attachments. So 1 and 2 wont happen.

Point of this is to store the backup else where, as just locally isnt too much use. What I have in mind is, split the sql dump, then be able to merge it should you ever need the copy, and then restore from it.

No guarantees on the emailing or offsite hosting aswell, just sorting out a purge now and need to add a page to the ACP. Then will have to test that.

At the moment all i have is a working beta of just backing up ,but would rather release a backup with the extra features. I've theoratically finished purging, just need to test it (ie to me the code looks as if it should work). Problem with testing these sort of things is you have to manually edit everything and make it as if your for example a month in the future.
Image
Click here to find out what eRepublik is.

opalelement
Registered User
Posts: 315
Joined: Wed Dec 27, 2006 4:05 am

Re: [DEV] Auto DB Backup

Post by opalelement » Thu Mar 26, 2009 9:48 pm

With my cron it adds it to the store area in the same format that phpBB backups are since it comes straight from the ACP and then it shows up in the ACP maintenance restore list

Locked

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