Vbulletin Database Backup Pro (Cron Support)
 

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.

looks great! will install shortly

GJ, but do you have restore hack??

No, but I can work on it :) Until then, use phpmyadmin, or shell.

This really sounds great, will be doing some tests today if i have time.

Very Nice.

I coded a custom script myself, but it seems not wanna work with vB's cron.

This should make a great replacement. :)

Works good, except when I manually ran the script in the Cron section i get this outputted.

DB Backup

Warning: array_merge() [function.array-merge]: Argument #1 is not an array in /global.php on line 329

Warning: in_array() [function.in-array]: Wrong datatype for second argument in /includes/functions.php on line 2359

Warning: Invalid argument supplied for foreach() in /includes/functions.php on line 2364
MySQL Backup was completed successfully with no errors.

Like I said, the backup was sucessful, Just I get that error. :)

Again very Nice work.

Edit:

I ran this on my test linux system AND windows system, and cannot reproduce this error.. it may be a problem with your current setup. The merge arrays sounds like something have to do with the cache templates.. and that error I believe is unique to PHP5.

It was tested with large databases?
I have a 1GB Database, i'm not sure to test it in my installation...

...add the installation process in a text files please, it will be easier to follow for reinstall etc...

The script by default will use the MySqlDump command utility to handle backing up your database.

For a database of your size, I would sugget to

1. Use the MySqlDump utility as is default in the script.
2. For compression, select the tgz option (use shell tar with gzip), as this will also use the command line to compress your backup.

There is no installation process.

You upload the files, modify configuration, and add a task to cron.
If that can't be figured out, maybe they shouldn't be using the script :ermm:

Edit:

I added the "installation" to a text file. Although I still don't see the point to it being that.. it's common sense.

error:
is missing the table_prefix:in mysqlbackup.php ...

I always forget that, I never use prefixes..

Edit. Updated zip file/configuration with patch.

Edit2. Optimized directory/file cleanup routine.

Looks great. I will try it soon.

I hope you do :) The more people that test it, the better.

Can this also deleted backups so they dont stockpile on the server like the old hack? If i go away on holiday i have to turn the old hack OFF as not to be flooded with backups on the server.

maybe keep the last 2 or 3, depending on a config we can add to the hack... admins would be able to set the number of old backups to be kept...

Yes great idea.
[high]* SnowBot always likes options :)[/high]

Ya I can code that in there, I actually created a log rotate script for brad.loos old hack a while back, I'll go ahead and remake one for this.

Can it upload backups to another server? If not that would be a great thing to add :)

I will install it when it has this feature.. Webspace is limited so I can't just upload them to my forum's space :(

Yes, im workin on FTP support.

... OR email the backup to the owner... but i suppose that 45gigs backups will be hard to email.. ;)

Don't know if that would be prudent or not.. It is a possibility tho, I will add it to the To-Do List.

Great add-on and am looking forward to some of the suggestions that seem to be planned.

Click install then, lol.

Edit:
Just uploaded version 1.3 with a small optimization fix for InnoDb users.

I get this when I run the task:

Failover was initiated for MySQLDump Binary & An error occured during the MySQL backup. Details (Could not open Destination SQL file for writing.)

-------------------
what am I doing wrong???

Thanks
Niklas

Thats telling you it could not open the destination file. That could mean 2 things.

1. The destination directory for backups you specified does not exist.
2. It Does exist, but you havnt chmod'd it 777

does it affect the site performance and consume lots of bw?

Thats why it runs on vbulletins cron system. It is ran whenever you have it scheduled to run.

Thanks, this looks great! I'm going to remove brad.loo's (a year later and still waiting for version 2.0 :p) and install this now.

One nitpick: Any reason you don't pull the db config info from includes/config.php?

Because I have a lot of commentary in the mysqldump configuration file, and see no point in cluttering up vbulletins primary config file.

Sorry - I'm not a coder but do you have to modify the primary config file to get the db info out of it? Can't you just require ./includes/config.php in mysqlbackupconfig.php instead of putting the db login info in there?

I'm just thinking it's best to limit the number of places where your db login info is.

Im using my own settings array to store the info, I could include the primary config file, but I would still have to reference the specified variables.

As they are PHP files, and are simply for configurion having no functions for direct output, it's not gonna hurt anything.

Okay cool, thanks for explaining. I just ran the backup and it worked great. Thanks. :)

Update:

Version 1.4, included backupRotate support to prune old backups, and keep a limited amount of backups at any one time.

I keep getting mysql error: Access denied. I am using the same username and password that i use in my VB config.

paratek

Well if it is, then you figured it out then, I'm running php 5.0.3.

How about this error?

Parse error: parse error, expecting `T_STRING' or `T_VARIABLE' or `'{'' or `'$'' in /home/goldis/public_html/forums/includes/mysqlbackup.php(1414) : eval()'d code on line 1

Thanks Carl

Great job I hope I can get this going...

Can you paste the error directly as it shows?

What MySQL Version?

That would be an error with VB's templates, the MySQL Backup script does not use them. I may know a small fix for it tho.

Eval's will only give that error, if you've messed up something its trying to evaluate. In other words, you probably messed some part of the configuration file up.