Vbulletin Database Backup Pro (Cron Support)

VBulletin Database Backup Pro

NOTICE: 02/03/2006
This system is being "ReBorn" into a new product "Vbulletin Backup Pro" working from a New Codebase and hopefully implementing all the features and fixing all the bugs that were present in the previous releases.
This will be developed specifically for the vbulletin 3.5 system but will probably maintain compatabilty with the pre-existing 3.0 versions.



Summary:

Vbulletin Database Backup Pro is just what it says; a professional tool for your Vbulletin installation. This script will handle the automated backup of your forums in a secure, data-conscious and optimized way.


Introduction:

I want to interest all of you in the ultimate solution for automated database backup from within Vbulletin crontab system.
And potentially more so than a standard crontab.

It's been said, backing up without direct shell access is futile. Wrong. This script is optimized to work just fine with even the largest of databases. (Note: If you have an extremely large database, make sure to use the mysqldump binary option that is selected by default.)

The reason for the creation of this script was due to needing a better system of automated backup. I think me and a lot of people have used either a standard cron system, or brad.loo's small database cron; while they can be somewhat effective, they do not provide the functionality or a total solution without having to extensively mod it.

This system has automatic failovers for MySQLdump, as well as the compression programs used where applicable. All in all, this script is a one stop shop for handling your backup needs on a automatic basis or from the control panel without having to use shell and do a dump manually.


Features:

• Absolutely NO Vbulletin File Modifications
• Fully automized backup using the Vbulletin Cron system.
• Remotely store backups on another srever via builtin FTP libraries.
• Keep Remote FTP Server directory SYNC'd with Local Filesystem.
• Dump using shell based, MySQLdump
• Dump using an optimized PHP based Dump
• System will Failover from MySQLDump binary straight Into a PHP Dump
• Various other Failovers for file/dir creations & deletion.
• Automatic pruning & control of a Specified max backups at any one time.
• Do a Full database dump OR just the tables you want. Such as post, forums, threads & users.
• Combine individual tables from a dump into 1 SQL File.
• Some optimizations for InnoDb tables AND MySQL 4
• System LOCKS tables while dumping, to prevent database corruption.
• Can Shutdown forum while backup is in progress. And then REOPEN it automatically.
• Customized Errors are Logged back into the Vbulletin Cron Log
• Log path for MySQLDump Errors are customizable as well as Dump Path
• Customize the Full Paths to binarys that will be used if needed.
• Customize which execution command to use from PHP if using MySQLdump & certain compressors.
• Specifiy the Dump Switches MySQLDump will use if selected.
• Choose the option to Compress your Backups or not.
• Choose from gzip, bzip2, tar, tar gz, tar bz2, zip for compressions.
• And Many Many More...

Change log:

• 3/30/2005 - Patch v1.9
• Added configuration directives to control the PHP Dump optimizations for MySQL4, InnoDB, and Locking Tables. Some people were having some reported problems with restoring dumps; I believe this may have been the cause, I was using optimizations hardcoded directly for MySQL 4+ support, they are now MySQL 3/4+.

• 3/25/2005 - Patch v1.8
• Lots of bug fixes regarding directory paths. If dump path was null, the script would translate the path as starting at / root.
• Incorporated Full Remote FTP Support.
  This includes remote offloading of dumps, keeping them rotated in sync with local backups, as well as removing Local backups after transfer to Remote Server.

• 3/23/2005 - Patch v1.7
• Added ability to Repair & Optimize tables before backup.

• 3/23/2005 - Patch v1.6
• A few configuration changes, I didn't save in the mysqlbackupconfig.php

• 3/22/2005 - Patch v1.5
• Made the LOCK Tables mechanism optional.
  Note: Use the shutdown Forums option, if you disable the LOCK Tables.
• Made some adjustments to the cronbackup.php might help with the array_merge errors, and it may not..
• Took the eval out for the parsing the configuration, they are referenced in hardcode now.
• Modified the date feature for backups. Remember just use 'mdy' or 'm_d_y'.

• 3/22/2005 - Patch v1.4
• Added Backup Rotating (Pruning) support to limit max backups at any one time.
• Make sure to replace all files, even the mysqlbackupconfig.php as it contains 3 new configuration options.

• 3/21/2005 - Patch v1.3
• Added some optimization code for InnoDB. Will help when you reload the database.
• Reason: Thought about Erwin, I believe he mentioned he used InnoDB.

• 3/21/2005 - Patch v1.2
• Modified previous sql/directory cleanup. Better code.

• 3/21/2005 - Patch v1.1
• Fix for table prefixes, updated config with info about it.
• Bug located by: Nexialys

• 3/21/2005 - Initial Patch v1.0
• Forgot to add unbuffered streams for fwrite(), this will speed up the compression of the database backup.

• 3/21/2005 - Initial release. v1.0
• Script was just released today, waiting on bug reports or any features that may need added to the script that I have missed.

To-Do:

• AdminCP Based Dump Control Panel to Test & Run Customized Configurations
• Create a Database Restore Tool, that works through Shell or PHP.
• Redesign the error system for better debugging, with severity levels.
• Finish implementing shell based support of Secure SSH SCP File Transfer Support
• Full Backup of Directory Tree for all of your Files
• Fix the bug regarding serializing/unserializing on some PHP Setups

Known Issues:

• Need to work on some of the Raw Windows Commands.
• Some of the stuff isn't reporting the errors as correctly as they should.

Instructions:

1. Download the zip file.
2. Place mysqlbackupconfig.php, mysqlbackup.php, ftp.lib.php, pclzip.lib.php in the forums 'includes' folder.
3. Place cronbackup.php in your 'includes/cron' folder.
4. Add a New Cron Task in your Scheduled Task Section. Refer to the screenshot if you need help.
5. Read and Modify the configuration data in the mysqlbackupconfig.php
6. Go back to the Scheduled Task section, and run the task. See what messages you get.
7. Note: pclzip.lib.php is an Modified Version of this open source GPL Php Zip Library.http://www.phpconcept.net
8. CHMOD 0777 the Directory you will be saving Dumps/Logs to !!!
9. IF POSSIBLE. YOU OR YOUR HOST SHOULD TURN OFF SAFEMODE/OPENBASEDIR

Help Debugging:

This may come as a surprise to some people.. but I do all my code in notepad, thus it may not look formatted right for people that use editors. If you want to view the code in "my" style, how its supposed to look.
Open my files in Notepad, with Font size 10-Regular-Lucida Console.

As for An errors you may get, when you post;
Post your PHP Version, MySQL Version, and the Error as is Shown Please.


Further Info:

I would like a lot of people to try this script and give me feedback on what you would like changed, what Needs changed and so on and so forth. With the size of this script, it more than likely not gonna work perfect on every system.. so just tell me whats wrong and I will try to get it fixed.

[Trigunflame]

Originally Posted by jdsinclair

Just bumping this, if you can point me in the right direction of where I am going wrong here that would be great.

Thanks,

jd.

I've noticed problems with this particular code on a couple systems yesterday, will be updated soon.

1. open mysqlbackup.php
2. find $this->OPTIONS = unserialize(stripslashes($this->OPTIONS['data']));
3. replace it with :
$this->OPTIONS = unserialize($this->OPTIONS['data']);

See if u still get the fetch database options problem anymore.

[Trigunflame]

Originally Posted by The Realist

What does this mean "An error occured during input of the source file for Compression"

Also if I turn compression off and run the cron I get this straight away?

"MySQL Backup was completed successfully with no errors"

And no files or error docs show in the backup dir?
Laters

It means it couldn't open the sql dump file for compression.

[Trigunflame]

Originally Posted by Sportbikeworld

Great script! Running fine on 3.03

I have 2 ideas for future options...

#1 How about an option to backup our httpdocs / DOCUMENT_ROOT directory as well? Would be nice to be able to backup all of our forums php pages and attachments for those of us who keep them in the file system and not the database. The backup file can be placed below the DOCUMENT_ROOT so it will not keep making copies of itself.

#2 Since a lot of VB forums also have ad-ons (Gallery etc...) how about being able to include a 2nd, 3rd or even 4th database for backup as well?

Thanks again for a great script!

Installed and voted for HOTM.

Noted,

[Greebo]

My backup file is getting to be over 100 MB, and unfortunately I can't use the shell option, I have to use the php option.

Last night, for the first time, the backup failed to return the forums to their online state. It did so again this morning when I ran it manually.

I suspect the size is an issue - first task on my part is probably going to be moving attachments out of the database (finally) and into the file system.

But - is there any way to guarantee that if the process fails, it doesn't keep my users locked out?

[Trigunflame]

Originally Posted by Greebo

My backup file is getting to be over 100 MB, and unfortunately I can't use the shell option, I have to use the php option.

Last night, for the first time, the backup failed to return the forums to their online state. It did so again this morning when I ran it manually.

I suspect the size is an issue - first task on my part is probably going to be moving attachments out of the database (finally) and into the file system.

But - is there any way to guarantee that if the process fails, it doesn't keep my users locked out?

It's more of an issue on browser time, after helping a guy yesterday that had backup files of "1.1gb" timeouts were occuring during the backup, the problem being that for some reason you need to send data to the browser at some intervals to keep it from disconnecting.

Even though I have code that "supposedly" is supposed to ignore browser disconnection, I will need to make mods in version 2 that send updates to the browser..

possibly . . . . . etc..

May want to turn off forum shutdown for now, until the new version.

[Greebo]

Perhaps - but I moved my attachments out of the DB (something I've been meaning to do for a while now) and now the backup is 30mb instead of 130, and it doesn't time out anymore.

By the time we get big enough to hit that limit again, we'll have moved to new servers and I'll be able to have it go via shell.

[ncangler]

Trigunflame, This looks very impressive and may be very timely for me, so in advance...thank you!

My serverhost had indicated that they would do complete backups every night. Last night my site went down and they fessed up that they had not backed up for a week. I lost all posts and new users for the past week. Fortunately it is still a relatively new and small board (200+). My question is regarding optimum backup strategy. Since the server box and drives went down last night isn't it better to have a backup on another physical harddrive somewhere in addition to or in lieu of being on the same box that your site is on? Is there a data backup service that can be purchased where your script would ftp a complete site backup to another secure (hopefully mirrored) backup server?

I may be working through moving to another host soon (fear and dread) but want to choose the optimum backup strategy now. Any input is most appreciated. And I will install this on the first of three sites and let you know how it goes.

I continue to be amazed at the collaborative spirit of incredibly talented and gifted people like yourself. Thanks.

[Robert Basil]

We use 1and1 Internet (Dedicated servers).

They include nightly backups for all shared accounts and an FTP server account for backups for dedicated servers.

http://www.1and1.com

[Trigunflame]

Originally Posted by Sportbikeworld

We use 1and1 Internet (Dedicated servers).

They include nightly backups for all shared accounts and an FTP server account for backups for dedicated servers.

http://www.1and1.com

Never mention 1&1 again, they are close to being the worst webhost that exist in modern times.

I know, ive had em. :disappointed:

[Trigunflame]

Originally Posted by ncangler

Trigunflame, This looks very impressive and may be very timely for me, so in advance...thank you!

My serverhost had indicated that they would do complete backups every night. Last night my site went down and they fessed up that they had not backed up for a week. I lost all posts and new users for the past week. Fortunately it is still a relatively new and small board (200+). My question is regarding optimum backup strategy. Since the server box and drives went down last night isn't it better to have a backup on another physical harddrive somewhere in addition to or in lieu of being on the same box that your site is on? Is there a data backup service that can be purchased where your script would ftp a complete site backup to another secure (hopefully mirrored) backup server?

I may be working through moving to another host soon (fear and dread) but want to choose the optimum backup strategy now. Any input is most appreciated. And I will install this on the first of three sites and let you know how it goes.

I continue to be amazed at the collaborative spirit of incredibly talented and gifted people like yourself. Thanks.

FTP Support is in the current script, but just for transferring the backup.

In version 2, I am transforming the script into a "Forum Backup Script"; so it's reach will include that of backing up the entire forum, not just the SQL File itself.